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:
./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.