Photo By rachfog

Everybody knows backups are important. We backup all customers VPS files daily, and we keep at least 7 days of backups. Still, this type of backup is not ideal for backing up MySQL  instances that use InnoDB tables. Especially if you try to restore a specific DB.

To generate MySQL backups we recommend you dump your DBs using mysqldump. Mysqldump can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). Here we are sharing a simple shell script that will dump all DBs of a MySQL server, and keep a number of backups in your VPS. You can adjust the number of days to keep to fit your confort level. The end result is a compressed file for each DB backup.

To add this script to the server cronjobs, simply store the script in /etc/cron.daily/ and give it execution permissions:

$ cd /etc/cron.daily/; curl -O https://raw.github.com/gist/1670895/backup_mysql.sh

$ chmod +x backup_mysql.sh

Make sure you edit the file for the username/password so mysqldump can connect and generate the dump. The files will be stored in /var/archives/mysql/ by default.

You can view or download the Gist we have created for this script.