Backup and restore MySQL databases

December 20th, 2011

Previous: Backup MySQL databases

Restore MySQL database backups

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

gunzip backup_dump.sql.gz

And then restore the database using the mysql command:

mysql -p -u database_user < backup_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:

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.

Pages: 1 2

2 Responses to “Backup and restore MySQL databases”

  1. [...] and foremost make a complete backup of your s9y powered site. Make sure you take a MySQL database backup [...]

    Posted on 16 Feb 2012
  2. [...] I have written about MySQL backups using mysqldump. But there is another way to backup MySQL databases and that is by using a perl script included [...]

    Posted on 18 Mar 2012

Leave a Reply

Your email address will not be published. Required fields are marked *