Backup/Restore MySQL Database


MySQL is a highly used database that has some great utilities to help backup your files.

Syntax to Backup Databases


mysqldump --opt -u USERNAME -p databasename > /backups/databases.sql
Be sure you change your username and database to the correct values. This will prompt you for your mysql database password and write the contents of that database to /backups/databases.sql

You can backup multiple databases: 
mysqldump --opt -u USERNAME -p --databases databasename1 \
databasename2 databasename3 > /backups/databases.sql

You can backup all of your databases databases: 
mysqldump --opt -u USERNAME -p --all-databases > /backups/databases.sql

You can just enter the password on the command line so you aren't prompted for it (this is good for automated backups): 
mysqldump --opt -u USERNAME -p=backup --all-databases > /backups/databases.sql

Syntax to Restore Databases


mysql -u USERNAME -p databasename < /backups/databases.sql
This will restore a single database backup. 

mysql -u USERNAME -p < /backups/databases.sql
This will restore a multiple databases backup.

Suggestions


  • Automate your mysql backups with a cron job
  • Compress your mysql backups with a tar/gzip