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

导读

mysql备份有时候会报错如下

代码语言：sql复制

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备份过程大致如下

代码语言：sql复制

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))的描述吧.