Simple Versioned MySQL Backups With CVS or Subversion

Your CVS or Subversion server can do more than just safeguard your source code. It can double as an easy to use, versioned, low disk space database backup solution as well. Typically backing up a MySQL database involves writing a script to execute mysqldump or mysqlhotcopy to create backup files, appending the date to the file names or creating dated folders, deleting older backups to keep from filling up the hard drive, and then FTP’ing or SCP’ing the files to the backup server.

If you already have a CVS or Subversion server, you can put it to use to simplify your database backup process, manage access to backup files, and keep backups longer while using less disk space. Both CVS and Subversion track versions of files by recording deltas — only what’s changed in each version compared to some other version. Each time you commit changed files to the repository, only the deltas need to be stored, not an entire copy of the changed file. That cuts down on disk space by a huge factor — the savings will be directly related to how much of your database remains static and how much changes each day. Unless every row gets touched, you’ll be saving disk space over making a new copy for each date.

To make this work, you’ll need a CVS or Subversion repository to store the backups in, a CVS or Subversion client installed on the server making the backups, mysqldump to make the backup files, and cron or some other task scheduler to have the backups run daily.

1. Create a repository with your favorite version control system to store the backups. Give read permissions to whatever groups and users should be able to retrieve the backups.
2. Create a directory to store the backups. Check out a working copy of the (empty) repository or import your initial backups from that directory to place it under version control.
3. Create the database backups. To make best use of the space saving benefits of CVS and Subversion, use mysqldump with the --extended-insert=false flag. This will generate INSERT queries, one per line, when exporting your database. That makes it easier for CVS or Subversion to pick out what rows are new or changed and not store the rest. To make restoring from backup easier, make one file per database:
   

   mysqldump -uusername -ppassword -c --extended-insert=false --database dbname > dbname.sql

4. The first time you make your backups, add the backup files to the repository. On subsequent dates, simply commit the files to send the changes to the repository.
5. Automate the process by adding the mysqldump commands and repository commit to a cron job or script to run manually.

In the past I’ve never kept good, regular backups of my databases. It’s a hassle scripting the date rotations on two servers to have off-site backups without filling up a drive with hundreds of copies. Plus I’m getting more bang for my buck with my CVSDude Subversion hosting. I have a couple gigabytes of space while my source code uses only about 150MB. This post isn’t a very good tutorial for someone not familiar with these programs already, so post a comment if you need any pointers setting up something similar.