Add functionality to Percona Server lightweight locks

Registered by Alexey Kopytov on 2013-03-18

This is to track the server-side work of https://blueprints.launchpad.net/percona-xtrabackup/+spec/backup-locks

<get all normal xtrabackup process>
LOCK TABLES FOR BACKUP
which will flush block updates to MyISAM and other non-transactional tables, and stop DDL for InnoDB tables.
LOCK BINLOG FOR BACKUP
This can be very short time when we can get current replication position galera cluster state; complete copy etc
UNLOCK BINLOG
UNLOCK TABLES

Blueprint information

Status:
Complete
Approver:
Alexey Kopytov
Priority:
High
Drafter:
Alexey Kopytov
Direction:
Approved
Assignee:
Alexey Kopytov
Definition:
Approved
Series goal:
Accepted for 5.6
Implementation:
Implemented
Milestone target:
milestone icon 5.6.16-64.0
Started by
Alexey Kopytov on 2014-02-17
Completed by
Alexey Kopytov on 2014-02-25

Sprints

Whiteboard

The patch introduces 3 new SQL statements: LOCK TABLES FOR BACKUP, LOCK
BINLOG FOR BACKUP and UNLOCK BINLOG. These statements can be used as a
lightweight alternative to FLUSH TABLES WITH READ LOCK for both physical
and logical backups.

LOCK TABLES FOR BACKUP
----------------------

LOCK TABLES FOR BACKUP uses a new MDL lock type to block updates to
non-transactional tables and DDL statements for all tables. More
specifically, if there’s an active LOCK TABLES FOR BACKUP lock, all
DDL statements and all updates to MyISAM, CSV, MEMORY and ARCHIVE
tables will be blocked in the “Waiting for backup lock” status as
visible in PERFORMANCE_SCHEMA or PROCESSLIST. SELECT queries for all
tables and INSERT/REPLACE/UPDATE/DELETE against InnoDB, Blackhole and
Federated tables are not affected by LOCK TABLES FOR BACKUP. Blackhole
tables obviously have no relevance for backups, and Federated tables are
ignored by both logical and physical backup tools.

Unlike FLUSH TABLES WITH READ LOCK, LOCK TABLES FOR BACKUP does not
flush tables, i.e. storage engines are not forced to close tables and
tables are not expelled from the table cache. As a result, LTFB only
waits for conflicting statements to complete (i.e. DDL and updates to
non-transactional tables). It never waits for SELECTs, or updates to
InnoDB tables to complete, for example.

If an “unsafe” statement is executed in the same connection that is
holding a LTFB lock, it fails with the following error:

ERROR 1880 (HY000): Can’t execute the query because you have a conflicting backup lock

UNLOCK TABLES releases the lock acquired by LOCK TABLES FOR BACKUP.

LOCK BINLOG FOR BACKUP
----------------------

LOCK BINLOG FOR BACKUP uses another MDL lock type to block all
operations that might change either binary log position or
Exec_Master_Log_Pos or Exec_Gtid_Set (i.e. master binary log coordinates
corresponding to the current SQL thread state on a replication slave) as
reported by SHOW MASTER/SLAVE STATUS. More specifically, a commit will
only be blocked if the binary log is enabled (both globally, and for
connection with sql_log_bin), or if commit is performed by a slave
thread and would advance Exec_Master_Log_Pos or
Executed_Gtid_Set. Connections that are currently blocked on the global
binlog lock can be identified by the “Waiting for binlog lock” status in
PROCESSLIST.

UNLOCK BINLOG
-------------

UNLOCK BINLOG releases the LOCK BINLOG FOR BACKUP lock, if acquired by
the current connection. The intended use case for XtraBackup is:

LOCK TABLES FOR BACKUP
... copy .frm, MyISAM, CSV, etc. ...
LOCK BINLOG FOR BACKUP
UNLOCK TABLES
... get binlog coordinates ...
... wait for redo log copying to finish ...
UNLOCK BINLOG

Privileges
----------

Both LOCK TABLES FOR BACKUP and LOCK BINLOG FOR BACKUP require the
RELOAD privilege. The reason for that is to have the same requirements
as FTWRL.

Interaction with other global locks
-----------------------------------

Both LOCK TABLES FOR BACKUP and LOCK BINLOG FOR BACKUP have no effect if
the current connection already owns a FTWRL lock, as it’s a more
restrictive lock. If FTWRL is executed in a connection that has acquired
LTFB or LBFB, FTWRL fails with an error.

If the server is operating in the read-only mode (i.e. read_only=1),
statements that are unsafe for backups will be either blocked or fail
with an error, depending on whether they are executed in the same
connection that owns LTFB lock, or other connections.

MyISAM index and data buffering
----------------------------—--

MyISAM key buffering is normally write-through, i.e. by the time each
update to a MyISAM table is completed, all index updates are written to
disk. The only exception is delayed key writing feature which is
disabled by default.

When the global system variable delay_key_write is set to ALL, key
buffers for all MyISAM tables are not flushed between updates, so a
physical backup of those tables may result in broken MyISAM indexes. To
prevent this, LOCK TABLES FOR BACKUP will fail with an error if
delay_key_write=ALL. An attempt to set delay_key_write to ALL when
there’s an active backup lock will also fail with an error.

Another option to involve delayed key writing is to create MyISAM
tables with the DELAY_KEY_WRITE option and set the delay_key_write
variable to ON (which is the default). In this case, LOCK TABLES FOR
BACKUP will not be able to prevent stale index files from appearing in
the backup. Users are encouraged to set delay_key_writes to OFF in
my.cnf, or repair MyISAM indexes after restoring from a physical backup
created with backup locks.

MyISAM may also cache data for bulk inserts, e.g. when executing
multi-row INSERTs or LOAD DATA statements. Those caches, however, are
flushed between statements, so have no effect on physical backups as
long as we block all statements updating MyISAM tables.

mysqldump
---------

mysqldump has also been extended with a new option, --lock-for-backup
(disabled by default). When used together with the --single-transaction
option, the option makes mysqldump issue LOCK TABLES FOR BACKUP before
starting the dump operation to prevent unsafe statements that would
normally result in an inconsistent backup.

When used without the --single-transaction option, --lock-for-backup is
automatically converted to --lock-all-tables.

--lock-for-backup is mutually exclusive with lock-all-tables,
i.e. specifying both on the command line will lead to an error.

If the backup locks feature is not supported by the target server, but
--lock-for-backup is specified on the command line, mysqldump aborts
with an error.

If --master-data is used together with --single-transaction,
--lock-for-backup does not have any effect, i.e. FTWRL will still be
used to get the binary log coordinates. This limitation will be removed
after implementing the following blueprint:
https://blueprints.launchpad.net/percona-server/+spec/enhancements-for-start-transaction-with-consistent

New server and status variables
--------------------

have_backup_locks

  This is a server variable implemented to help other utilities decide
  what locking strategy can be implemented for a server. When
  available, the backup locks feature is supported by the server and
  the variable value is always YES.

Com_lock_tables_for_backup
Com_lock_binlog_for_backup
Com_unlock_binlog

  These status variables indicate the number of times the corresponding
  statements have been executed.

(?)

Work Items

Dependency tree

* Blueprints in grey have been implemented.

This blueprint contains Public information 
Everyone can see this information.