[MYSQL] mysql慢SQL导致mysqldump备份失败 1205

导读

mysql备份有时候会报错如下

mysqldump: [Warning] Using a password on the command line interface can be insecure.
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
mysqldump: Couldn't execute 'FLUSH /*!40101 LOCAL */ TABLES': Lock wait timeout exceeded; try restarting transaction (1205)

分析

这个报错一眼就能看出来是 执行FLUSH /*!40101 LOCAL */ TABLES超时. 也就是一开始就失败了. (简单的锁超时, 问题就结了...)

mysqldump备份过程大致如下

SHOW VARIABLES LIKE 'gtid_mode'
FLUSH /*!40101 LOCAL */ TABLES
FLUSH TABLES WITH READ LOCK --master-data才有
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
SELECT @@GLOBAL.GTID_EXECUTED
SHOW MASTER STATUS --master-data才有
UNLOCK TABLES
开始备份表......

那么FLUSH TABLES是干嘛的呢?

Closes all open tables, forces all tables in use to be closed, and flushes the prepared statement cache.

This operation requires the FLUSH_TABLES or RELOAD privilege.

For information about prepared statement caching, see Section 10.10.3, “Caching of Prepared Statements and Stored Programs”.

FLUSH TABLES is not permitted when there is an active LOCK TABLES ... READ. To flush and lock tables, use FLUSH TABLES tbl_name ... WITH READ LOCK instead.

就是flush table .....

强制关闭所有打开的表. 这说的啥玩意儿.... 看看对应函数(void mysql_ha_flush(THD *thd))的描述吧.

Flush (close and mark for re-open) all tables that should be should be reopen.

原来还要做一个re-open标记的. 具体的细节我们就不深究了.

多久超时呢?

和参数lock_wait_timeout 有关. 就是普通的锁等待超时. 等待的时候状态是Waiting for table flush (我模拟的时候设置的是120秒. 默认600秒)

总结

1. 备份通常是在凌晨, 这个时候还有慢sql的情况是不常见的.(查看对应慢日志可以找到对应慢sql, 可以看看执行计划是否使用了索引)
2. 1205 只有sql执行阶段超长才会遇到, 未提交的事务不会导致这个问题.
3. 如果无法优化sql/建索引的话, 那就只有修改备份时间或者增大超时时间lock_wait_timeout