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://gist.github.com/servergrove/1670895/raw/1ccccc4bc01acca2d72bbeaaa15df3d84bfde6a5/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.