0

With a MySQL database, is there a way of logging and/or backing up incremental changesets?

We have a number of large databases (100-200 GB), for which we take daily backups using mysqldump. These backups are a snapshot of the entire database, which can be restored as whole. However, taking/restoring from these backups is not a quick process.

As such, I'd like to be able to take backups of incremental changes - i.e. a backup of what's changed in a given day. Then, assuming a database was in a state consistent with a previous snapshot, we could restore the set of incremental changes to get it to the same/current state.

To some extent, this could be done by operating on mysqldump files - i.e. comparing today's snapshot dump with the previous day's snapshot dump to create a .sql backup of commands to run to convert from the previous snapshot state to today's snapshot state. However, I'd rather not write this myself if there's already a better way of doing this.

Also, I'm conscious that, to some degree this is analogous to MySQL master-slave replication, with the exceptions that the changesets are grouped by time period (i.e. a 24hr window) and not automatically read into another database / server. Furthermore, the binary logs are for the entire database server, not for individual databases.

Additional Comments:

With some further research on this, I've also come across the incremental backup functions in MySQL Enterprise, but we're not using the enterprise version.

0

It is probably your only option to use the Percona Xtrabackup tool. Fortunately, this is a free tool that has very good documentation.

This tool takes backups in a physical way, rather than logical backups that mysqldump produces. This means that restore time is simply the amount of time required to copy the backup files into the MySQL datadir and not the time to process all the SQL statements in a mysqldump backup. Backup time is generally faster as well, and allows for the possibility for incremental backup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.