TL;DR version: The backup locks feature introduced in Percona
Server 5.6.16-64.0 is a lightweight alternative to FLUSH
TABLES WITH READ LOCK and can be used to take both
physical and logical backups with less downtime on busy servers.
To employ the feature with mysqldump, use
mysqldump --lock-for-backup --single-transaction.
The next release of Percona XtraBackup will also be using backup
locks automatically if the target server supports the
feature.
Now on to the gory details, but let’s start with some history.
In the beginning…
In the beginning there was FLUSH TABLES, and users
messed with their MyISAM tables under a live server and were not
ashamed. Users could do nice things like:
mysql> FLUSH TABLES; # execute myisamchk, myisampack, backup / restore some tables, etc.
And users were happy until someone realized that tables must be …
[Read more]