I've found a number of sites talking about doing exactly this, but I'm missing a few important details. The general steps are
FLUSH TABLES WITH READ LOCK
- Take the ZFS snapshot
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.