Andy Shulman asked:
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.
mysql_locked=/var/run/mysql_locked # flush & lock MySQL, touch mysql_locked, and wait until it is removed mysql -hhost -uuser -ppassword -NB <<-EOF & flush tables with read lock; delimiter ;; system touch $mysql_locked system while test -e $mysql_locked; do sleep 1; done exit EOF # wait for the preceding command to touch mysql_locked while ! test -e $mysql_locked; do sleep 1; done # take a snapshot of the filesystem, while MySQL is being held locked zfs snapshot zpool/$dataset@$(date +"%Y-%m-%d_%H:%M") # unlock MySQL rm -f $mysql_locked
mysql command you use is run in the background and touches a file. It waits in the background for the file to disappear before exiting and thus unlocking the tables. Meanwhile the main script waits until the file exists, then creates the snapshot and deletes the file.
The file pointed to by
$mysql_locked needs to be accessible to both machines, which you should be able to do easily enough since they can both access a common dataset (albeit they might use different paths, and you should account for this).
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.