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?

Improve this question
3
  • 1
    It would be better doing this in a SLAVE. Commented Oct 10, 2016 at 7:56
  • 2
    @RuiFRibeiro Yes, for a funded professional project, definitely. Commented 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. Commented Oct 10, 2016 at 14:28

2 Answers 2

Reset to default
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.

Improve this answer
3
  • how exactly in a multiuser system you gurantee the transsctions will stop? Commented 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. " Commented 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. Commented Oct 10, 2016 at 14:31
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
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.