【数据库】mysqldump中使用flush tables with read lock的风险分析

【数据库】mysqldump中使用flush tables with read lock的风险分析

by felix

    我们使用mysqldump --single-transaction --master-data=2来得到数据一致性的备份，其中，--master-data=2会告诉mysqldump在备份事务开启前，先获取一个全局读锁（该锁会阻塞所有的write操作），然后开启事务，并使用show master status来获取当前的binlog文件和位置，以下是开启了mysqld的通用日志general-log情况下，上述mysqldump工具给 mysqld发出的命令：

2 Query FLUSH TABLES

2 Query FLUSH TABLES WITH READ LOCK

2 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

2 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

2 Query SHOW MASTER STATUS

2 Query UNLOCK TABLES

    单纯地使用mysqldump --single-transaction，是没有粗体的三条命令的，其中的--master-data=2参数，是为了获得备份时间点的binlog文 件和位置（出于后续结合binlog恢复数据的需要），这里讨论的是flush tables with read lock这个全局读锁的影响范围。

    ben推荐了一篇关于flush tables with read lock的文章，http://www.mysqlperformanceblog.com/2010/04/24/how-fast-is- flush-tables-with-read-lock/，提到了该语句的风险，在测试环境的测试如下：

1.测试表：

    t1表 innodb引擎 20000000行，含c1，c2，c3三列，无索引

    t2表 innodb引擎 0行 ，含c1，c2两列

2.测试过程

    第一个mysql console：执行select * from t1 where c1 = 'felix';# 该语句返回需要59秒

    第二个mysql console：执行FLUSH TABLES WITH READ LOCK； # 该语句获取全局锁，但现在阻塞，因为第一个console还未返回，该语句等待获得全局读锁

    第三个mysql console：执行insert into t2 values(1,'felix'); # 该语句阻塞，等待第二个console的返回，注意，这里操作的是t2表，而不是t1表

3.测试结论

    从2得出，即使flush tables with read lock语句还没成功获得全局锁，也会阻塞后续其他线程的任何写操作，这个结论与 http://www.mysqlperformanceblog.com/2010/04/24/how-fast-is-flush-tables-with-read-lock/ 开发者Baron的分析一致。

    而从mysqldump给mysqld发送的sql分析来看，开发者应该已经意识到了直接用flush tables with lock导致的锁等待问题，所以先做了flush tables，再做flush tables with read lock，显然，这种处理方式会较大程度的规避锁等待，但严谨一点来看，如果在两个语句之间，另外的进程执行了一个耗时的select操作，依然有可能会 导致flush table with read lock的等待，从而阻塞其他人对DB的写访问。

    Baron说可能可以从innodb的redo log中得到binlog的文件和位置，如果找到该方法，就可以去掉flush tables with read lock的语句，减少备份导致的锁问题！

本文原创自无线技术运营空间： http://wireless.qzone.qq.com 及 http://blog.csdn.net/wireless_tech （专注无线技术运营——无线技术(操作系统/数据库/WEB前端/负载均衡/系统容灾/系统安全/短信接入/WAP接入/3G等)、无线业务运营、无线开放平台、统计分析(用户行为分析/数据挖掘)、CP合作，联系我们：[e-mail]）