Home / server / Questions / 48170
Accepted
Arie K
Asked: 2009-07-31 09:14:08 +0800 CST

Incremental MySQL Backup using Subversion

  • 772

Our MySQL database servers host several dozens of databases, with dump file size ranging from 1 to ~100 MB.

Currently, our backup approach is mysqldump, wrapped in a shell script, and run from a crontab. This has worked great for us. The only main drawback is large storage requirement to store the dump files.

Since MySQL database dump is a text file, naturally I consider storing it in a version control system, such as Subversion. I recall Subversion only store the delta of a file in each commit.

Is this approach recommended? Are there some gotchas we should aware of?

backup mysql svn

6 Answers

  1. The MySQL documentation might have exactly what you need. Binary incremental backups! (Also great for a poor-mans slave server over rsync/ftp/etc).

    MySQL supports incremental backups:

    You must start the server with the --log-bin option to enable binary logging; see Section 5.2.4, “The Binary Log”. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained in Section 6.3, “Point-in-Time Recovery”. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS, mysqldump --flush-logs, or mysqlhotcopy --flushlog. See Section 4.5.4, “mysqldump — A Database Backup Program”, and Section 4.6.9, “mysqlhotcopy — A Database Backup Program”.

    • 2
  2. Best Answer

    binlog mentioned by SirStan is good approach.

    alternatively you can run mysqldumps and then use rdiff-backup to create backup of dumpfile. rdiff will keep n last backups [ you decide how many ], and will be quite space efficient, since it keeps only full snapshot of latest version of file + set of diffs allowing it to reconstruct previous versions.

    whatever you put in svn, stays in svn. repository only grows - so it's good place to keep your sql schemas, source code and maybe docs; but not actual data from sql.

    • 2

  3. using svn would take up a huge amount of space.

    say you commit 100mb file and svn becomes version 1 then if you add a new file of 200mb the version will be 2.

    Your svn repository will be 300mb.

    if you woule like your file in version 1 you would have to svn co -r 1 svnrepourl

    • 1

  4. I know this question is a little dated, but at my company we've been experimenting with something similar. We've been pulling xml-formatted backups (not from MySQL, but still simply text files) that are ~300 MB in size and committing them to an SVN repository, every night for a little over a week. The first commit put the repository at 41 MB, but each commit since has been smaller. We are now at revision 8, and the total size of the repository is a scant 57 MB. This is about 2.5% of the size of all of the history it contains!

    I just tried out rdiff-backup using the same data; below is a comparison (all sizes in MB):

    Backup #    Backup Size    repo size    delta    rdiff size    delta
0           0              0.03         0.03     0             0
1           286.05         41.49        41.46    286.05        286.05
2           285.93         45.97        4.49     322.13        36.08
3           286.23         50.52        4.54     323.27        1.14
4           286.63         50.79        0.27     324.54        1.26
5           287.46         55.52        4.73     326.54        2.00
6           287.76         55.77        0.25     327.72        1.19
7           288.15         56.14        0.37     328.98        1.26
8           288.41         56.63        0.50     330.36        1.38

    Looks like the deltas are about the same (sometimes svn is better, sometimes rdiff-backup is), so on overall size, SVN wins. An advantage to rdiff is that it is easy to remove old backups, where with SVN you would have to do an svnadmin dump and svnadmin load to get rid of old revisions. I guess it's a matter of preference but SVN is easy to use, and has a ton of tools around to support it.

    • 1

  5. Check out this script which automates the process, allowing for specific selection of databases and exclusion of tables [disclaimer I am the author] - http://mysql-svn-backup.redant.com.au/

    • 1

  6. Percona XtraBackup supports incremental hot backups of MySQL databases.

    • -2