Backup mysql databases with mysqldump

Addendum 2015-0318: I am now using automysqlbackup for mysql backups. It is simple to use and offers rotating daily/weekly/monthly backups. See my post at https://blog.chadchenault.com/2015/04/25/rotating-backups-of-mysql-database-with-automysqlbackup-version-3-0/.

I have multiple (five) databases in mysql and need to perform regular backups and off-site backups. I am using a python script that uses the mysqldump program to backup my databases.  I am doing daily backups to my home folder at ~/Documents/backup.mysql/. My home directory is backed up daily to another hard drive with a daily cron job and using rsync to create differential backups. I plan on backing up all databases to individual files because I read that it makes restoring a single database easier than if all databases are backed up into one big file. The files will also be compressed with gzip. I did a gzip compression test using the default compression and the “-9” option (best compression) and the difference was minimal for my needs. I decided to use the default compression for speed and simplicity.

mysqldump from command line for testing

sudo mysqldump -u user --all-databases --events | gzip -c > /home/chad0320/Documents/backup.mysql/testbackup.all-databases.sql.gz

 

References

1. python help using mysqldump: http://whatthehost.net/python-s3-backup-script-for-ubuntudebian-web-and-mysql-database-servers/

2. mysqldump manpage: http://manpages.ubuntu.com/manpages/saucy/man1/mysqldump.1.html

 

 

 

Leave a Reply

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