Backing up a MySQL database via ZFS snapshots

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

  • 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.

My answer:


I’ve ripped off and adapted a conceptually simple script in Bash which I found in another Server Fault post by Tobia. It should get you about 90% of the way there.

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

Here, the 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).


View the full question and answer on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.