Backing up a MySQL database via ZFS snapshots

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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

mysql << EOF
    FLUSH TABLES WITH READ LOCK;
    system zfs snapshot data/db@snapname
    UNLOCK TABLES;
EOF

Answer 5

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.

Answer 6

Since we also had trouble with the FLUSH TABLES WITH READ LOCK for innoDB several years back, and we did also not want to rely on innoDBs ability to recover from non-consistent data-files, we did the following: Use a 2nd mariadb/mysql Database machine configured as a secondary (formerly known a "slave"). Whenever you want to make a zfs snapshot we simply shut down the mariadb/mysql on the secondary, then make the zfs snapshot, then start the mariadb/mysql again.

This setup runs since about 4 years using mysqld_multi (for running multiple DB-instances on the same machine but on different ports) without any hick-ups. Moreover we wanted to use the zfs snapshots right away as a writeable mariaDB/mysql instance. So we immediately make a zfs-clone out of the zfs-snapshot and spin up a mariaDB-Docker Container using the just created zfs-clone as its data directory. In addition, we use a separate BindDNS and routing to make the mariaDB-Docker-container available in our internal net with a DNS name. So we can access the db-copys for read/write from anywhere in our internal office net. (see https://github.com/ggtools/docker-tools/blob/master/dockerDDNS.py)

This is a great tool for our software develpers: They can request a full copy of a several hundred Gbyte or even Tbyte big mysqsl/mariadb instance in a matter of seconds for testing and development purposes.

All this is automated by a few ruby scripts, which itself is made available as a Jenkins/Hudson Job to the developers. This way we give the developers a nice and easy to use web-UI for making DB-Copys, whenever they need one.