19

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.

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

6 Answers 6

7

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

1
  • With the data dictionary introduced in MySQL 8, even DDL (schema modification) operations are now atomic. Before that, DDL operations during a filesystem snapshot could give partially committed (i.e. corrupted) results.
    – bernie
    Sep 20, 2019 at 8:31
14

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.

4
  • 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. Sep 26, 2016 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. Sep 26, 2016 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. Sep 26, 2016 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. Sep 26, 2016 at 14:51
8

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

5
  • 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, 2016 at 11:01
  • @Tripehound both things needs to happen in parallel somehow Sep 26, 2016 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. Sep 26, 2016 at 15:07
  • @Andy I just said they need to happen in parallel. Doesn't matter how you go about it. Sep 26, 2016 at 15:40
  • SYSTEM can help in simple cases, but not when you have to run zfs snapshot in another environment, different from MySQL server's environment. Jun 8, 2021 at 23:17
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
3

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.

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .