MySQL XtraBackup & Replication D

问题

数据库备份期间主从延时

MySQL: percona-server-5.6.25-73
innobackupex version 2.4.7

原因

1. percona-server

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.

2. 备份输出日志

...
Executing LOCK TABLES FOR BACKUP...
...

从库备份时，当 xtrabackup 执行 LOCK TABLES FOR BACKUP 后，如果此时主库开始有MyISAM表的更新，从库SQL线程将会阻塞，导致从库复制延时。

问题复现

CREATE TABLE `test_myisam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

select * from test_myisam;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

表已经准备，在从库执行 lock tables for backup

/*Slave*/
LOCK TABLES FOR BACKUP;

主库插入一条数据

/*Master*/
insert into test_myisam value(null);

从库 PROCESSLIST 上看到 SQL Thread 被阻塞

/*Slave*/
           Id: 22
         User: system user
         Host:
           db: NULL
      Command: Connect
         Time: 142
        State: Waiting for backup lock
         Info: NULL
    Rows_sent: 0
Rows_examined: 0

/* Slave */
               Slave_IO_State: Waiting for master to send event
                  Master_Host: ******
                  Master_User: ******
                  Master_Port: ******
                .....
              Master_Log_File: ******
          Read_Master_Log_Pos: 329416942
               ......
        Relay_Master_Log_File: ******
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
             ......
          Exec_Master_Log_Pos: 329416721
             ......

/*Master*/
select * from test_myisam;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+

/*Slave*/
select * from test_myisam;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+