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

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

  • Why have a static list of tables? You can surely generate a list dynamically at runtime. – EEAA Sep 26 '16 at 1:05
  • 1
    Is the database on a VM or on the bare metal? Is the storage even on the same machine? – Michael Hampton Sep 26 '16 at 3:48
  • EEAA, fair enough. – Andy Shulman Sep 26 '16 at 4:04
  • Michael, the database and the ZFS box are different machines, but neither is virtualized. – Andy Shulman Sep 26 '16 at 4:06
  • @AndyShulman I think you should explain the layout a bit better. This doesn't make sense. – ewwhite Sep 26 '16 at 12:11

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


You need a full database lock to backup a (most) database(s) consistently.

The manual https://dev.mysql.com/doc/refman/5.5/en/backup-methods.html says FLUSH TABLES WITH READ LOCK is correct for ZFS snapshots specifically.

Making Backups Using a File System Snapshot

If you are using a Veritas file system, you can make a backup like this:

  1. From a client program, execute FLUSH TABLES WITH READ LOCK.
  2. From another shell, execute mount vxfs snapshot.
  3. From the first client, execute UNLOCK TABLES.
  4. Copy files from the snapshot.
  5. Unmount the snapshot.

Similar snapshot capabilities may be available in other file systems, such as LVM or ZFS.

It is kind of ridiculous that they left out the fact that you need FLUSH TABLES table_a, table_b, table_c FOR EXPORT for InnoDB from these instructions. It's also stupid to have to specify each table like that. But as EEAA says, you can generate a list of tables as you begin the backup fairly easily.

As for holding the lock, you must keep the db connection active while performing the snapshot

Generally I'd use something like Perl or another programming language that can connect, lock the db and while maintaining the db connection take the snapshot, then unlock and disconnect. It's not complex. I'd wager that there are tools out there that do this already but writing one is easy.

I say easy, not complex, etc.. a few times. I'm assuming you have some basic programming or good scripting skills.

  • I had hoped to keep such a conceptually simple script in Bash, but you're right switching languages makes this much easier. I might be reading your answer wrong, but it looks you're saying I need to execute both FLUSH TABLES WITH READ LOCK and then FLUSH TABLES...FOR EXPORT, whereas my reading of the MySQL manual says only one should be necessary. – Andy Shulman Sep 26 '16 at 4:12
  • Sorry I wasn't clear. I'm just going by the manual and it says two different things. Im guessing you are correct and only need the later. But all tables should be locked in one command. – Ryan Babchishin Sep 26 '16 at 5:00
  • 1
    Given that the documentation isn't very clear, the entire database needs to be locked, and that a DB connection has to be maintained while the snapshot is being taken, it seems easier to just shut the DB down, back it up, and restart it. – Andrew Henle Sep 26 '16 at 10:51
  • 2
    @andrew sigh... I understand. But that will be slow , cause connections to drop/fail and I've seen it cause databases to fail to come back up properly (bad for automation). It would be good to get a definitive answer from mysql/Oracle. They must have a mailing list. – Ryan Babchishin Sep 26 '16 at 14:51

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.


# 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

# 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).

  • I don't know MySQL scripting, so this may be a silly idea, but couldn't you just do system zfs snapshot... inside the main script? Or does the snap-shotting have to run in a separate process? – TripeHound Sep 26 '16 at 11:01
  • @Tripehound both things needs to happen in parallel somehow – Ryan Babchishin Sep 26 '16 at 14:47
  • @RyanBabchishin I think he's right, actually. The SYSTEM command runs things locally. If I run the mysql client on the FreeBSD box and execute LOCK; SYSTEM zfs snapshot; UNLOCK, that seems like it would work. – Andy Shulman Sep 26 '16 at 15:07
  • @Andy I just said they need to happen in parallel. Doesn't matter how you go about it. – Ryan Babchishin Sep 26 '16 at 15:40

You need FLUSH TABLES WITH READ LOCK for myisam because it isn't journaling.

You don't really need anything for innodb at all, IMO, because it's journaling. It'll be consistent anyway, just rolls back the journal automatically if anything is happening at the atomic instant you snapshot.

If you want application level consistency, your application should use transactions. If your application uses transactions and innodb, any snapshot will be consistent ask the way up to application level automatically.


This is my solution how to create an ZFS snapshot while keeping the lock:

mysql << EOF
    system zfs snapshot data/db@snapname

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.