MySQL backup lock and mysql backup

MySQL backup lock and mysql backup

Both logical and physical backups depend heavily on FTWRL (Flush Table With Read Lock) to obtain consistency points ). This lock is highly lethal, because during the lock period, the entire database cannot provide write services externally. In addition, because FTWRL needs to close the table, if there is a large query, it will lead to FTWRL waiting, which leads to a longer DML blocking time. Even in the standby database, some SQL threads copy updates from the primary database. When the master database is locked globally, the Standby database may be delayed. The FTWRL lock mainly depends on the data volume of the non-innodb table. If the data volume of the non-innodb table is large and the backup is slow, the lock will take a long time. Even if all the tables are innodb tables, a certain period of time will be locked due to the existence of mysql database system tables. To solve this problem, Percona improved the Mysql Server layer and introduced backup lock. Specifically, use the "lock tables for backup" command to obtain consistent data (including non-innodb TABLES); Use "lock binlog for backup" to obtain consistent points, to minimize service damage caused by database backup, we introduce this feature to AliSQL. The following describes this feature in detail.

Features

Two types of MDL global range locks, backup-lock and binlog-lock are added to the MysqlServer layer, and three syntaxes are added:

1. lock tables for backup. Execute the statement to apply FOR the shared lock of backup-LOCK and release the lock through unlock tables.

2. lock binlog for backup. Execute the statement to apply FOR the binlog-lock shared LOCK and release the lock through unlock binlog.

3. unlock binlog to release the LOCK held by lock binlog for backup.

For backup-lock:

If an update operation (not an innodb table) is performed in this session after the lock table for backup is held, an error is returned. If an update operation is performed in another session, the system waits. Show processlit you can see that the session is in the "Waiting for backup lock" status.

For binlog-lock:

After the lock binlog for backup is held, if the update operation is performed in this session, no error is reported because the session will not be blocked; if other sessions are executed, the session will wait. Show processlist: the session is in the "Waiting for binlog lock" status.

The following describes the specific principles and implementation of related interfaces.

A: backup operation

Apply for backup-lock, hold (backup, MDL_SHARED, MDL_EXPLICIT) lock

B: DDL operations on the database

Call lock_schema_name to add the database object lock (modify the database operation, schema_lock)

Interface (mysql_create_db, mysql_alter_db, mysql_rm_db, mysql_upgrade_db, etc)

1. If you already have a global lock (backup, global), an error is returned.

2. Apply the database exclusion lock (SCHEMA, MDL_EXCLUSIVE, MDL_TRANSACTION)

3. Apply for the IX range lock to avoid future global and backup locks.

(Global, MDL_INTENTION_EXCLUSIVE, MDL_STATEMENT)

(Backup, MDL_INTENTION_EXCLUSIVE, MDL_STATEMENT)

C: DDL operations on tables

Call lock_table_names to add table object lock (Modify Table Operation)

Interface (mysql_rename_tables, mysql_rm_table, mysql_drop_view, and truncate_table)

1. Add the TABLE object lock (TABLE, MDL_EXCLUSIVE, MDL_TRANSACTION)

2. Add the schema object lock (SCHEMA, MDL_INTENTION_EXCLUSIVE, MDL_TRANSACTION) to avoid ddl operations on the database.

3. If you already have a global lock (backup, global), an error is returned.

4. Apply for the IX range lock to avoid future global and backup locks.

(Global, MDL_INTENTION_EXCLUSIVE, MDL_STATEMENT)

(Backup, MDL_INTENTION_EXCLUSIVE, MDL_STATEMENT)

D: Table DML operations

Call acquire_protection to apply for the IX range lock

Interface open_table

This is only applicable to non-innodb engines and write tables.

Mdl_request.type> = MDL_SHARED_WRITE & share-> db_type ()-> flags & HTON_SUPPORTS_ONLINE_BACKUPS

Advantages of introducing backup locks

LOCK TABLES FOR BACKUP
Purpose: Get consistent data
1. Prohibit Non-innodb table updates
2. Disable ddl for all tables
Optimization points:
1. It will not be blocked by large queries (Table operations are disabled without flush tables)
2. Reading and updating innodb tables will not be blocked. This is very important. If all the business tables are innodb, DML will not be damaged during the backup process.

LOCK BINLOG FOR BACKUP

Purpose: Obtain the consistent position.
1. Do not update the position.
Optimization points:
1. Allow DDl and update 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 needs to be modified in the command execution process.

Functional limitations

1. For Myisam tables, when delay_key_write = ALL, the index is not flushed in time, resulting in xtrabackup unable to obtain consistent backup. In this case, the backup-lock operation fails.

References

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/