3 de febrero de 2011

Backup/Restore a MySQL dump from the console

Given that tools like MySQL Administrator or MySQL Query Browser most usually won't behave very good when it comes to recovering large db backups, moreover if there is UTF-8 stuff in the dump, I decided to go for the mysql shell, which does much better.

Here some useful commands to have handy:

Dump ALL MySQL Databases

mysqldump --user=XXXXXXXX --password=XXXXXXX -A > PATH_TO_SQL_DUMP_FILE.SQL

Dump Individual or Multiple MySQL Databases

mysqldump --user=XXXXXXXX --password=XXXXXXX --databases DB_NAME1 DB_NAME2 DB_NAME3 > PATH_TO_SQL_DUMP_FILE.SQL

Dump some particular tables from a MySQL Database

mysqldump --user=XXXXXXXX --password=XXXXXXXX --databases DB_NAME --tables TABLE_NAME > PATH_TO_SQL_DUMP_FILE.SQL


If you want to make sure of not having backwards compatibility issues from the MySQL Server versions between your development and production environments, you may want to add this to the commands:

 --compatible=mysql323

Reloading the full contents of a database:
  1. Unzip the backup file you wish to use.
  2. Open it up and pull out only the information that you will need.
  3. Save this text file.
  4. Use the following command to feed back in the contents of a text file: 
mysql --verbose --user=XXXXXXXX --password=XXXXXXXX DB_NAME < PATH_TO_SQL_DUMP_FILE.SQL

No hay comentarios: