Mysql – Incremental MySQL Backup using Subversion

backupMySQLsvn

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?

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.

Related Solutions

MySQL equivalent to .pgpass, or automatic authentication in a cron job for theSQL

Create a ~/.my.cnf file for the user running mysql. It should contain the following:

[client]
user = root
password = yourpassword

Mysql – dumping/restoring a large thesql database, best practice

First off, going from 32bit to 64bit should not have any correlation to your filesystem, so copying your DB files directly should not be a concern. Dumping a large DB could be slow, so copying the raw files is probably the best option. Are you using MyISAM, or are all your tables in InnoDB? If you are using InnoDB, you could try using xtrabackup from Percona to do a "live" backup of your database without downtime:

https://launchpad.net/percona-xtrabackup

If you're using MyISAM and downtime is a concern, what you can do is perform an rsync on the data files directly while the server is running. Run it multiple times in a row until the "changed" tables are few and rsync is quick to finish. Then, you can perform a quick shutdown of MySQL, run the rsync one last time to grab the files in a consistent state and start MySQL again. You can then copy them to your new server, start MySQL and go from there.

Hope this helps!

Best Answer

Related Solutions

MySQL equivalent to .pgpass, or automatic authentication in a cron job for theSQL

Mysql – dumping/restoring a large thesql database, best practice

Related Topic