Backup and restore MySQL databases
December 20th, 2011Previous: 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.
Final Words
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
[...] and foremost make a complete backup of your s9y powered site. Make sure you take a MySQL database backup [...]
[...] 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 [...]