Previous: Backup MySQL databases

Restore MySQL database backups

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

[mysqldump]
user=username
password=password

And then restore the database using the mysql command:

mysqldump -Ae |gzip > all_databases.sql.gz ## backup all databases;

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:

php split_dump.php /path/to/multiple-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.