問題

數(shù)據庫備份期間主從延時

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 執(zhí)行 LOCK TABLES FOR BACKUP 后，如果此時主庫開始有MyISAM表的更新，從庫SQL線程將會阻塞，導致從庫復制延時。

問題復現(xiàn)

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

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

表已經準備，在從庫執(zhí)行 lock tables for backup

/*Slave*/
LOCK TABLES FOR BACKUP;

主庫插入一條數(shù)據

/*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 |
+----+