xtrabackup备份MySQL时，可能会出现拿不到锁而超时失败的现象，这里说明解决办法。

现象

xtrabackup备份MySQL数据库出现超时的错误：

Error: failed to execute query 'FLUSH NO_WRITE_TO_BINLOG TABLES': 1205 (HY000) Lock wait timeout exceeded; try restarting transaction
...
Error: failed to execute query 'FLUSH NO_WRITE_TO_BINLOG TABLES': 1205 (HY000) Lock wait timeout exceeded; try restarting transaction

解决方法

解决方法有两个，一个是修改xtrabackup的参数，另一个是杀死长时间执行的线程。

杀死长SQL的线程：

查询执行时间长的线程：

mysql> select * from processlist where command not like 'Sleep' order by time desc limit 3;
+-----+-----------------+-----------+--------+---------+--------+-------------------------+-----------------------------------------------+
| ID  | USER            | HOST      | DB     | COMMAND | TIME   | STATE                   | INFO                                          |
+-----+-----------------+-----------+--------+---------+--------+-------------------------+-----------------------------------------------+
|   5 | event_scheduler | localhost | NULL   | Daemon  | 432662 | Waiting on empty queue  | NULL                                          |
| 132 | root            | localhost | sakila | Query   |    632 | User sleep              | select * from actor where actor_id=sleep(100) |
| 131 | root            | localhost | NULL   | Query   |    628 | Waiting for table flush | flush table with read lock                    |
+-----+-----------------+-----------+--------+---------+--------+-------------------------+-----------------------------------------------+
3 rows in set (0.00 sec)

mysql> 

mysql> kill 132;
Query OK, 0 rows affected (0.00 sec)

也可以在sys.processlist、INFORMATION_SCHEMA.PROCESSLIST或者performance_schema.threads里面找到长时间执行的SQL，把它们杀死即可。

修改xtrabackup的参数

对于执行时间长的SQL，还可以通过修改相关参数来解决：

• 使用–ftwrl-wait-*系列参数，意思是备执行FLUSH TABLES WITH READ LOCK时遇到等待长SQL语句执行时等待设置时间，超时还没有执行完，备份退出宣布失败。
• 使用–kill-long-query-*系列参数，意思是遇到等待长SQL语句执行时，超时就是kill掉执行的SQL语句，并且可以设置SQL类型默认全部。
• 使用–safe-slave-backup-*系列参数，意思是在从库备份，备份开始先停止slave sql线程，防止有超长SQL语句执行，备份完再启动slave sql线程。

文章下方是我的微信，欢迎加我(托业890分的Oracle ACE)。👇