Previous: Backup MySQL databases

Restore MySQL database backups

Restoring backups is easy. If the backup file is compressed you first uncompress it:

mysqldump -p -u username database_name > database_dump.sql

And then restore the database using the mysql command:

mysqldump -B -p -u username database_1 database_2 database_3 > combined_db_dump.sql

Selectively restoring MySQL databases from a multiple database backup

If you’ve used the mysqldump multiple database or all databases option and want to restore some of the databases you will need to split the dump file into the individual database backups. One Nick Peirson wrote a PHP script that does this job for you. Download it, rename it split_dump.php and run it on your multiple database dump file like this:

mysqldump -A -p -u username > all_db_dump.sql

It will now create multiple .sql files each one representing a different database. You then restore just the databases you want to.

Conclusion

It’s always good to automate backups. You can use rsnapshot and rsync to do this. Or, if you have a much smaller site, you could just setup a cron job to email you the MySQL database backup.