MySQL备份工具Xtrabackup锁问题

从XtraBackup的备份过程可以看出，XtraBackup可以实现Innodb表的无锁备份，但是一个数据库中，即使所有的业务表都是innodb表，但是还存在一些MySQL系统库下的user表等，均是myisam表（MySQL 8.0均替换为InnoDB），同时备份过程需要获取Binlog文件名和位置，也要保证表定义文件的一致性，所以从整个实例的角度，即使用XtraBackup还是有一段时间需要执行Flush table with read lock全局锁的，会对用户访问产生影响，同时由于Flush table with read lock的一些特殊性，如果稍不注意，可能会对用户访问数据库产生致命影响。

MySQL官网文档对Flush tables with read lock的解释：

Closes all open tables and locks all tables for all databases with a global read lock.

If the thread that is doing FLUSH TABLES has a lock on some tables, it will first close the locked tables, then wait until all other threads have also closed them, and then reopen them and get the locks. After this it will give other threads a chance to open the same tables.

从上面的这段话，我们可以得到两个结论:

• Flush tables with read lock会上一个实例级别的全局锁，该锁与Lock tables或者Select for update等互斥，即如果前面有上述锁，会导致Flush tables with read lock阻塞。

• Flush tables with read lock首先需要关闭所有的表，然后再打开所有的表。如果有线程正在扫描表，Flush tables with read lock会被阻塞，一直等到该表允许被关闭为止。如果有一个select count(*)的慢查询，会阻塞Flush tables with read lock。

可能大家认为Flush tables with read lock仅仅是一把读锁，即使阻塞了也不会影响正常的读，但是事实不是这个样子的。Peter Zaitsev 在文献【1】中提到了Flush tables with read lock的潜在风险。概括的讲，如果Flush table with read lock执行完毕，成功获取到了全局实例锁，后续的快照读和S锁的读是没有问题的，只是阻塞DDL、写；但是如果一旦因为表无法关闭或者因为其他的锁导致无法正常获取到表锁使得Flush table with read lock阻塞，这个后果将是灾难性的，所有的读，无论是快照读，还是S锁或者X锁的读，均会被阻塞，因为Flush table with read lock需要关闭表，这点是需要所有数据库运维人员警惕的，我们的数据库也因此导致了服务的长时间不可用。

XtraBackup从1.4到2.1.3均存在这样的隐患，在2.1.4的Release Notes中我们找到了相关描述：

Percona XtraBackup has introduced additional options to handle the locking during the FLUSH TABLES WITHREAD LOCK.These options can be used to minimize the amount of the time when MySQL operates in the read-only mode.

显然，Percona已经意识到了这个问题的严重性，提供了两种解决问题的思路：

1、设置超时时间

XtraBackup设置一个超时时间，避免无限期的等待。Xtrabackup提供了一下参数实现该功能：

• –lock-wait-timeout=SECONDS ：一旦Flush table with read lock被阻塞超过预定时间，则XtraBackup出错返回退出，该值默认为0，也就是说一旦阻塞，立即返回失败。

• –lock-wait-query-type=all|update ：该参数允许用户指定，哪类的SQL语句是需要Flush table with read lock等待的，同时用户可以通过–lock-wait-threshold=SECONDS设置等待的时间，如果不在query-type指定的类型范围内或者超过了wait-threshold指定的时间，XtraBackup均返回错误。如果指定update类型，则UPDATE/ALTER/REPLACE /INSERT 均会等待，ALL表示所有的SQL语句。

2、kill其他阻塞线程

Kill掉所有阻塞Flush table with read lock的线程：

• –kill-long-queries-timeout=SECONDS ：参数允许用户指定了超过该阈值时间的查询会被Kill，同时也允许用户指定Kill SQL语句的类型。

• –kill-long-query-type=all|select ：默认值为ALL，如果选择Select，只有Select语句会被Kill，如果Flush table with read lock是被Update语句阻塞，则XtraBackup不会处理。

数据库运维人员在备份数据库时，应选择正确的XtraBackup版本规避该问题。同时，个人在使用XtraBackup在Slave做备份时，还碰到跟SQL线程产生死锁的情况。MariaDB并行复制，死锁信息如下：

mysql> show processlist;
+---------+-------------+----------------------+--------------------+-------------+---------+--------------------------------------------------------------------------------+-----------------------------+----------+
| Id      | User        | Host                 | db                 | Command     | Time    | State                                                                          | Info                        | Progress |
+---------+-------------+----------------------+--------------------+-------------+---------+--------------------------------------------------------------------------------+-----------------------------+----------+
| 3335182 | system user |                      | NULL               | Connect     | 9556202 | Waiting for master to send event                                               | NULL                        |    0.000 |
| 3335183 | system user |                      | NULL               | Connect     |   15622 | Waiting for prior transaction to start commit before starting next transaction | NULL                        |    0.000 |
| 3335184 | system user |                      | NULL               | Connect     |   15622 | Waiting for global read lock                                                   | NULL                        |    0.000 |
| 3335185 | system user |                      | NULL               | Connect     |   14920 | Waiting for prior transaction to commit                                        | NULL                        |    0.000 |
| 3335195 | system user |                      | NULL               | Connect     |   15622 | Waiting for prior transaction to start commit before starting next transaction | NULL                        |    0.000 |
| 3335196 | system user |                      | NULL               | Connect     |   14920 | Waiting for global read lock                                                   | NULL                        |    0.000 |
| 3335197 | system user |                      | NULL               | Connect     |   14920 | Waiting for prior transaction to start commit before starting next transaction | NULL                        |    0.000 |
| 3335198 | system user |                      | NULL               | Connect     |   14920 | Waiting for prior transaction to start commit before starting next transaction | NULL                        |    0.000 |
| 3335199 | system user |                      | NULL               | Connect     |   21335 | Waiting for room in worker thread event queue                                  | NULL                        |    0.000 |
| 4525735 | backupuser  | localhost            | NULL               | Query       |   14920 | Waiting for commit lock                                                        | FLUSH TABLES WITH READ LOCK |    0.000 |
+---------+-------------+----------------------+--------------------+-------------+---------+--------------------------------------------------------------------------------+-----------------------------+----------+
26 rows in set (0.01 sec)