Mysql notes

From DiLab
Jump to: navigation, search


Backup a database

You may want to log in mysql and do "show databases;" to see which to backup, then do it like this.

mysqldump -u root -p [databasename] > [backupfile.sql]

Restore a database

mysql -u root -p [databasename] < [backupfile.sql]

Change a root password

Change an existing Mysql root password like this:

mysqladmin -u root -p'oldpassword' password 'newpassword'

Lost MySQL password?

By example: recreated mysql passwords for the wikiuser and mysql-root. You can reset root password - stop mysql and start the daemon like this:

  • reset mysql root password
 sudo /etc/init.d/mysql stop
 sudo mysqld --skip-grant-tables

 mysql -u root 
 mysql> UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';
 mysql> exit
 sudo mysqladmin shutdown -u root -p
 sudo /etc/init.d/mysql start
  • Change mysql wikiuser password
 $ mysql -u root -p
 mysql> use mysql;
 mysql> update user set password=PASSWORD("NEWPASSWORD") where User='wikiuser';
 mysql> flush privileges;
 mysql> quit