MySQL backup lock

Both logical and physical backups are strongly dependent on FTWRL (Flush Table with Read Lock) in order to obtain consistency sites. This lock is very lethal, because the whole database is essentially unable to provide write service to the time the lock is held. In addition, because FTWRL needs to close the table, if there is a large query, it causes FTWRL to wait, which in turn causes the DML to become clogged longer. Even if it is a standby, there are SQL threads that replicate updates from the main library, which causes the master repository to be delayed when the global lock is on. FTWRL This lock holding time is mainly related to the data volume of non-InnoDB table, if the non-InnoDB table data volume is very large, backup is very slow, then the time to hold the lock will be very long. Even if all the InnoDB tables are present, the MySQL library system table exists, which can cause a certain amount of time to be locked. To solve this problem, Percona has made improvements to the MySQL server layer by introducing BACKUP lock, specifically through the "lock TABLES for BACKUP" command to obtain consistent data (including non-innodb tables); BINLOG for backup "to get the consistency bit, minimizing the service damage caused by database backups, we introduce this feature to Alisql, which is described in detail below.

function Introduction

New 2 types of MDL Global lock, Backup-lock and Binlog-lock are added at the MySQLServer layer, and 3 new syntaxes are added:

1.lock TABLES for BACKUP, execute statement request Backup-lock shared lock, release lock via Unlock TABLES.

2.lock BINLOG for BACKUP, execute statement request Binlog-lock shared lock, release lock via Unlock BINLOG.

3.UNLOCK BINLOG, Release lock BINLOG for BACKUP hold locks.

For Backup-lock:

If you have already held lock table for backup, you will get an error if you perform an update operation (not a InnoDB table) in this session, and if you perform an update operation in another session, you will be waiting. Show Processlit can see that the session is in the "Waiting for backup lock" state.

For Binlog-lock:

Once the lock Binlog for backup has been held, if the session performs an update operation, no error is made, because the session will not be blocked, and if other sessions are executed, it will wait. Show Processlist can see that the session is in the "Waiting for Binlog lock" state.

The following describes the specific principles and the implementation of the relevant interfaces

A: Backup operation

Apply for Backup-lock, hold (backup,mdl_shared,mdl_explicit) lock

B: DDL Operations for libraries

Call Lock_schema_name Object Lock (Modify library operation, Schema_lock)

Interfaces (mysql_create_db, mysql_alter_db, mysql_rm_db, mysql_upgrade_db, etc.)

1. If the global Lock (Backup,global) is already held, an error is made.

2. Gakou Lock (SCHEMA, mdl_exclusive, mdl_transaction)

3. Apply IX range lock to avoid subsequent global and backup lock in

(global,mdl_intention_exclusive,mdl_statement)

(backup,mdl_intention_exclusive,mdl_statement)

C: DDL operation of the table

Call Lock_table_names Table Object Lock (Modify table action)

Interfaces (Mysql_rename_tables, mysql_rm_table, Mysql_drop_view, truncate_table, etc.)

1. Add Table Object Lock (table, mdl_exclusive, mdl_transaction)

2. Add the object Lock (schema,mdl_intention_exclusive,mdl_transaction) corresponding to the SCHEMA to avoid the DDL operation of the library.

3. If the global Lock (Backup,global) is already held, an error is made.

4. Apply IX range lock to avoid subsequent global and backup lock in

(global,mdl_intention_exclusive,mdl_statement)

(backup,mdl_intention_exclusive,mdl_statement)

D: DML Operations on tables

Call Acquire_protection to request an IX range lock

Interface open_table

This is only for non-InnoDB engines, and is a table for write operations.

Mdl_request.type >= mdl_shared_write && share->db_type ()->flags & Hton_supports_online_backups

Advantages of introducing backup locks

LOCK TABLES for BACKUP
Role: Get Consistent data
1. Prohibit non-InnoDB table update
2. Disable DDL for all tables
Optimization points:
1. Cannot be blocked by large queries (no flush tables results in closed table operation)
2. It is important that the InnoDB table is not blocked from reading and updating, and that the DML is completely intact during the backup process for all and innodb of the business table

LOCK BINLOG for BACKUP

Role: Gets the consistency site.
1. Disable the operation of the site update
Optimization points:
1. Allow DDL and updates until Binlog is written.
UNLOCK BINLOG

Physical backup process changes

Before modification:

1. Get REDO-LSN

2. Copy InnoDB data

3. FLUSH TABLES with READ LOCK;

4. Copy. frm, MyISAM, etc.

5. Get the binary log coordinates

6. Finalize the background copy of REDO log

7. UNLOCK TABLES;

After modification:

1. Get REDO-LSN

2. Copy InnoDB data

3. LOCK TABLES for BACKUP;

4. Copy. frm, MyISAM, etc

5. LOCK BINLOG for BACKUP;

6. Finalize the background copy of REDO log

7. UNLOCK TABLES;

8. Get the binary log coordinates

9. UNLOCK BINLOG;

The corresponding Xtrabackup tool requires a corresponding change in the execution of the command flow.

Functional limitations

1. For the MyISAM table, when Delay_key_write=all, the index does not have a timely brush disk, resulting in xtrabackup unable to obtain a consistent backup, so in this case, add Backup-lock failed.

Reference documents

Https://www.percona.com/doc/percona-server/5.6/management/backup_locks.html#interaction-with-other-global-locks

https://www.percona.com/blog/2014/03/11/introducing-backup-locks-percona-server-2/

MySQL backup lock