MySQL备份导致的waiting for global read lock

zyllf2009

　　最近业务高峰期间经常会有开发跳起来说应用连接数据库超时了！
　　我们来看下mysql的运行状态

　　Waiting for>
　　The thread is waiting for a global read lock obtained by another thread (with FLUSH TABLES WITH READ LOCK) to be>　　Waiting for table：等待表
　　Waiting for tables, Waiting for table, Waiting for table flush
　　The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.

　　This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name,>　　In MySQL 5.5.6, Waiting for table was replaced with Waiting for table flush.
　　线程获得一个通知，底层表结构已经发生变化，它需要重新打开表来获取新的结构。然而，重新打开表，它必须等到所有其他线程关闭这个有问题的表。
　　这个通知产生通常因为另一个线程对问题表执行了FLUSH TABLES或者以下语句之一：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.
　　查看crontab，每天定时执行备份任务
　　/usr/local/mysql/bin/mysqldump --user=$bakuser --patestdbword=$bakpwd--skip-opt --master-data=2 --single-transaction --add-drop-table --create-options --quick --extended-insert --set-charset --disable-keys --triggers -R --flush-logs --databases testdb > testdb.sql
　　--master-data[=#]   This causes the binary log position and filename to be
　　appended to the output. If equal to 1, will print it as a
　　CHANGE MASTER command; if equal to 2, that command will
　　be prefixed with a comment symbol. This option will turn
　　--lock-all-tables on, unless --single-transaction is
　　specified too (in which case a global read lock is only
　　taken a short time at the beginning of the dump; don't
　　forget to read about --single-transaction below). In all
　　cases, any action on logs will happen at the exact moment
　　of the dump. Option automatically turns --lock-tables  off.
　　这个参数会运行--lock-all-tables，将master的binlog和postion信息写入SQL文件的头部，除非结合--single-transaction（但并不是说就完全的不会锁表了，执行的时候也会添加短暂的全局读锁）

　　我们来重现一下这个场景
　　/usr/local/mysql/bin/mysqldump -u root -p  --skip-opt --master-data=2--single-transaction --add-drop-table --create-options --quick --extended-insert --set-charset --disable-keys --triggers -R --flush-logs --databases testdb > testdb.sql
　　执行插入
　　mysql> call insT1(10000000);
　　30s后执行【如果同时执行，效果不明显】
　　/usr/local/mysql/bin/mysqldump -u root -p  --skip-opt --master-data=2 --single-transaction --add-drop-table --create-options --quick --extended-insert --set-charset --disable-keys --triggers -R --flush-logs --databases testdb > testdb1.sql
　　执行插入
　　mysql> call insT2(1000000);

　　等待刷表
　　不使用--single-transaction

　　等待全局读锁释放
　　不使用--master-data，再跑上面的2个场景，mysql不会加锁，所以SQL很快执行完成
　　结论：因为选用--master-data参数在SQL文件的头部会写入binlog和position信息，所以在执行备份前mysql需要执行flush tables，搭建过从库的同学都了解，我们在获取完整备份前都要执行FLUSH TABLES WITH READ LOCK;来获取这些主库当前信息，这里也是这样。 www.it165.net
　　生产环境还是复杂的，大家会注意到我们同时使用了--msater-date和--single-transation但还是出现了全局读锁，可是在测试环境，只有不加--single-transation的时候才会出现。
　　解决方法：
　　1.如果你只需要文件备份，不需要经常建立从库，那么可以去掉--master-data。
　　2.如果你的数据量很大 or 备份时的master信息非常需要，那么可以调整备份周期，避开两次备份出现重叠的情况。
　　转载：http://www.it165.net/database/html/201303/3658.html