Smart MySQL backup
Only backup changed tables
Posted by gf on May 25th, 2015

I’m currently sysadmin for a bunch of dedicated servers, mainly running LAMP stacks. One the main issues with running a dedicated server is backups (the other being security). Remote filesystem backup can be easily performed with ribs (or any other rsync-based backup tool). ribs only copies changed files and creates hard-link snapshots to make each backup appear as a full backup. The main problem with rsync&hard-link based backup is MySQL.

It’s not wise to directly backup MySQL files; MyIsam files can be backed-up individually but their restore is not always guaranteed. Shared InnoDb tablespaces are very large and are modified each time you look at them. Single-table InnoDb tablespaces (with innodb_flle_per_table in my.cnf) are cumbersome to restore. On top of all that, data can be in a non-consistent state, so the copied file is corrupted (and useless).

My solution was a shell script to dump my DBs (using mysqldump) to gzipped text files and backup those files. They guarantee integrity and are easy to restore. The biggest concern is that every day, every DB is copied entirely via rsync. Even a change in a single value in a single table, results in a different dump file, which is then copied by rsync. Moreover, mysqldump adds a timestamp at the end of the dump file; it must be removed with --disable-dump-date or cutting the last line.

So I hacked a couple of Makefiles and used GNU make to backup each table separately. Also, make takes care of doing backups for changed tables only. Complete source for the script is on Github.

In the first step, a list of DBs is extracted to a text file (tablelist)

    ./tablelist.php \>$@

and a list of dependencies is built (tablelist.d).

tablelist.d: tablelist  
    cat $< | sed 's/^/backup: /' | sed 's/$$/.BK/' \>$@

The list of dependencies is then included in the Makefile and each table is dumped using a second Makefile: Makefile.db. Each table dump is only performed when DB data is newer than the current backup file.

    @mkdir -p $(DB)  
    cd $(DB) && $(MAKE) \-f ../Makefile.db $(TBL).sql.gz DB=$(DB)

For InnoDb tables, a tablespace per table is required (innodb_file_per_table option in my.cnf) in order to check if the data is newer than the current backup.

Since I’m not a bash ninja, I resorted to PHP to get the list of MySQL tables with proper exclusion handling. The PHP script can be substituted by anything capable of reading the list of tables and filtering it for exclusion handling. Alternate implementations for tablelist.php are welcome as well as suggestions/improvements; just send me a Pull Request.