mysqldump的锁表的问题

缺乏、安全感 2022-05-22 07:52 238阅读 0赞

今天凌晨，公司的一台MySQL生产库备份时间从2:30一直备份到8:30，正常情况下这个备份应该只会备份20分钟，3:00之前就会备份完毕，  
但是这次备份时间太长了，也影响了公司业务的使用。  
先写一下公司的备份语句  
mysqldump -uroot -p123456 --opt --master-data=2 -R dbname | gzip >/data/dbname.sql.gz  
分析一下上面这个语句  
\--opt 会lock 本次需要备份的所有表，因为本次备份的是 dbname数据库，所以会锁住dbname的所有表。  
\--master-data=2，--master-data=1 默认的话会--lock-all-tables，会锁住整个mysql数据库中的所有表。  
但是如果加上--single-transaction会加上事务，不会锁表。  
所以我在原有参数的基础上加上--single-transaction  
mysqldump -uroot -p123456 --opt --single-transaction --master-data=2 -R dbname | gzip >/data/dbname.sql.gz  
如果加上--single-transaction，备份的时候就不会锁表了。  
以前一直以为locks级别大于single transaction，经过这次实践证明，single-transaction级别更高。  
选择备份多个数据库的简单语法：  
mysqldump -uroot -p123 -h192.131.1.9 --single-transaction --master-data=2 -R --no-data --databases vgos\_mcenter vgos\_statnum>11.dmp  
顺便说一下，mysqldump默认是lock-tables的。  
当执行mysqldump -uroot -p123 -h192.131.1.9 -R  vgos\_statnum>11.dmp 的时候，lock整个vgos\_statnum库

当执行mysqldump -uroot -p123 -h192.131.1.9 -R  vgos\_statnum t1>11.dmp 的时候，没有lock整个vgos\_statnum库，lock的是t1表

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

由于某人在生产库上测试导出一张2G的表，大概多长时间，没有加\--single-transaction 参数，导致整个系统沆住；连接请求并发暴增，出现大量所等待，磁盘IO存在大量等待，数据库最后整体崩溃；