Saturday 21 May 2011

MySQL Dump and Restore

DUMP:

All databases:
mysqldump --user=XXXXXXXX --password=XXXXXXX -A > /PATH/TO/DUMPFILE.SQL
Just some DB:
mysqldump --user=XXXXXXXX --password=XXXXXXX --databases DB_NAME1 DB_NAME2 DB_NAME3 > /PATH/TO/DUMPFILE.SQL
Just some tables:
mysqldump --user=XXXXXXXX --password=XXXXXXXX --databases DB_NAME --tables TABLE_NAME > /PATH/TO/DUMPFILE.SQL

NOTE: if you get and error saying "(...)when using LOCK TABLES" append this to the dump command:
--single-transaction
RESTORE:
mysql --verbose --user=XXXXXXXX --password=XXXXXXXX DB_NAME < /PATH/TO/DUMPFILE.SQL
Permissions:
GRANT ALL ON db_base.* TO db_user @'%' IDENTIFIED BY 'db_passwd';
Change User password:
$ mysql -u root -p
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-PASSWORD-HERE") where User='tom';

Possibly Related Posts

No comments:

Post a Comment