Mysql – Backing up a MySQL database via ZFS snapshots

backupfreebsdinnodbMySQLzfs

I've found a number of sites talking about doing exactly this, but I'm missing a few important details. The general steps are

  • Run FLUSH TABLES WITH READ LOCK
  • Take the ZFS snapshot
  • Run UNLOCK TABLES

Various sources report that InnoDB, which I'm using, doesn't actually honor a FLUSH. The MySQL users manual notes there's a FLUSH TABLES...FOR EXPORT variant for use with InnoDB, but that requires specifying each table individually, rather than backing up the whole database. I'd prefer to avoid specifying each table individually because there's a decent chance the list of tables would become out of sync with the tables that actually exist.

The other problem I have is I planned to do something like mysql -h"$HOST" -u"$USERNAME" -p"$PASSWORD" --execute="FLUSH TABLES WITH READ LOCK". However, this drops the lock immediately after the session exits. This makes sense, but is also pretty annoying since I need to hold the read lock when I take my snapshot.

My other idea is to take a hot backup using a tool like Percona XtraBackup and taking snapshots of the backup, but I'd prefer not to pay the cost to write all of my data to a second location just to snapshot it.

Best Answer

If you only use InnoDB for all tables and set innodb_flush_log_at_trx_commit to:

  • 1 (the contents of the InnoDB log buffer are written out to the log file at each transaction commit and the log file is flushed to disk) or,
  • 2 (the contents of the InnoDB log buffer are written to the log file after each transaction commit and the log file is flushed to disk approximately once per second),

then you don't need FLUSH TABLES before doing snapshot, just run ZFS snapshot directly. InnoDB can recover data from transaction commit logs without data loss.

Ref: https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

Related Topic