5

How can I instruct MySQL to complete all "in-progress" transactions, but to delay starting new ones (without kicking clients off) until I have taken a ZFS or LVM snapshot (which takes less than a second).

e.g.

  1. pause MySQL, waiting for "in-progress" transactions to complete
  2. sync to disk
  3. take ZFS/LVM snapshot
  4. resume MySQL The point of this is to get a consistent snapshot for backup purposes. Step 2 takes a fraction of a second.

Step one should not cause client errors, just a very short pause until step 4 is reached.

Are there MySQL commands which can do 1 and 4? What are they?

3
  • 1
    It would be better doing this in a SLAVE. Oct 10, 2016 at 7:56
  • 2
    @RuiFRibeiro Yes, for a funded professional project, definitely.
    – fadedbee
    Oct 10, 2016 at 9:06
  • 1
    If you're running with transactions and your snapshot is instantaneous, why worry? The database snapshot may contain incomplete transactions, but they'll get rolled back automatically when you bring the database online. Oct 10, 2016 at 14:28

2 Answers 2

4

A hacky way would be, to wait for the transactions to finish:

mysql> FLUSH LOCAL TABLES; Query OK, 0 rows affected (11.31 sec)

and then getting a read lock:

mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (22.55 sec)

Now all queries are blocked (ie. they wait for the lock to get released) until your session ends. Mind you - you still have to wait until all transactions are finished. Depending on your workload this can take a while (UPDATE-ing a few million rows...).

You can code this in your favourite script language.

But seriously - why not use Xtrabackup ? It does take care of a consistent snapshot of mysql for you and you can dump it on the filesystem, and zfs/lvm snapshot it.

4
  • how exactly in a multiuser system you gurantee the transsctions will stop? Oct 10, 2016 at 13:50
  • 2
    The read lock is a global read lock: dev.mysql.com/doc/refman/5.7/en/flush.html " FLUSH TABLES WITH READ LOCK Closes all open tables and locks all tables for all databases with a global read lock. This is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLES to release the lock. "
    – Thomas
    Oct 10, 2016 at 14:13
  • 1
    Adding, it does not stop any transactions, but waits for them to finish. You can do without the first command, it just waits for the transactions to finish.
    – Thomas
    Oct 10, 2016 at 14:31
  • Interesting, thanks for the clarification. +1 Oct 10, 2016 at 14:42
3

This is my solution how to create an ZFS snapshot while keeping the global lock, so that MyISAM and InnoDB data files are consistent.

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

You must log in to answer this question.

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