备份策略 - 单表备份的隐患与应对方案-CSDN博客

作者介绍：
刘鹏松，任职云和恩墨北区交付部，负责山东部分客户业务交付。

在通过mysqldump进行MySQL数据库(InnoDB引擎)逻辑备份的时候经常使用的参数组合就是--single-transaction --master-data=2。

今天的案例就是来分析下通过--single-transaction --master-data=2参数组合进行单表备份而引发的性能问题。

问题描述

某业务系统(数据库采用的MySQL数据库)上午10点左右部分业务业务反应系统缓慢，用户登陆系统出现超时的现象。

问题分析与处理

登录数据库服务器使用 top 来查看操作系统负载。

top发现：CPU:load average 超过10且是mysqld进程占用，确定是MySQL等待。

通过show processlist查看：发现大量的Waiting for global read lock 。（信息已经脱敏处理）

这里第一感觉是正在进行mysqldump备份数据，然后show processlit查看数据库的进程信息，果然发现了backup 用户正在执行 FLUSH TABLES WITH READ LOCK。

通过操作系统 ps –ef|grep mysqldump 查看，发现mysqldump进程。

mysqldump进程是root发起的，仔细查看mysqldump 进程的pid是不同的，且dbname和tablename也是不同的，很显然这是一个for循环在mysqldump备份单个表。

切换到root用户，查看定时任务通过crontab –l发现有一个每天上午 4点30分开始执行的mbak.sh脚本，查看脚本是mysqldump备份数据库，通过分析备份脚本发现这个脚本的逻辑是这样的：
整库备份一次使用的是--all-database参数
分别备份每个数据库为一个备份文件
单表备份一次，即一个表备份成一个文件

部分脚本节选如下：

所有的数据库备份一个文件的脚本

每个库一个备份文件的脚本

每个表一个备份文件的脚本

很显然出问题的时候是在备份单个表，通过mbak.sh脚本的逻辑来看，是先全库备份，全库完成再单库备份，单库备份完成之后再单表备份。
现在卡在单表备份的FLUSH TABLES WITH READ LOCK，这是一个全库级别的锁，单表备份为什么会锁整个库呢？仔细查看上面的mysqldump备份命令，可以发现每次mysqldump都添加了 --single-transaction --master-data=2，这是问题的关键。
下面通过开启general log来分析问题。

开启general_log。

备份test库下面的t表，使用--single-transaction --master-data=2参数

查看general log：

发现其执行了

FLUSH /*!40101 LOCAL */ TABLES
FLUSH TABLES WITH READ LOCK

备份test 库下面的 t 表，不使用--single-transaction --master-data=2 参数


mysqldump -uroot –ppassword --default-character-set=utf8 test t  >test_t.sql

查看general log：

发现：没有执行FLUSH /!40101 LOCAL / TABLES、FLUSH TABLES WITH READ LOCK。而是执行的LOCK TABLES t READ，对备份的t表进行一个lock。

备份全库使用--all-databases及--single-transaction --master-data=2 参数


mysqldump -uroot -ppassword --default-character-set=utf8  --single-transaction --master-data=2 --all-databases   >all.singel-2.sql

结论：不管是全库备份还是单表备份使用了--single-transaction --master-data=2 参数会执行FLUSH /!40101 LOCAL / TABLES、FLUSH TABLES WITH READ LOCK 来获取 show master status的一致性。

通过进一步测试发现mysqldump备份的时候只使用 --single-transaction 不使用--master-data=2参数是不会进行锁表的。只使用 --master-data=2参数为了获取show master status，会执行FLUSH TABLES WITH READ LOCK的全局锁。
只使用 --single-transaction 备份单表的general log

结论：只使用--single-transaction 不执行任何lock table，也就是说可以进行热备份。

只使用 --master-data=2参数备份单表的general log

也就是说通过这几次实验发现：
只要添加了--master-data=2参数就会执行 FLUSH TABLES WITH READ LOCK全局锁，即使你是备份单个表也会锁整个库来获取show master status;从而导致整个库的一个等待。

在业务低峰期，获取全局锁会很快，但是由于整个库有700多个表，有部分表在上午10点也就是业务高峰期还没备份完成，由于高峰期有大量的DML操作，从而获取全局锁更加困难，Waiting for global read lock 的时间会更长，从而影响了业务的正常运行。

问题的解决与改善

问题解决

找到mbak.sh 脚本的pid号，进行kill操作，数据库恢复正常。

改善

调整备份策略：
1、取消备份每个单表为一个文件，减少全局锁（经过生产环境实际测试mysqldump全库(17G数据)备份一次不到5分钟）；
2、如果有必要进行单表备份的话，禁用--master-data=2参数，只使用--single-transaction 参数；
3、采用XtraBackup 物理备份替换mysqldump的逻辑备份，来进行在线热备数据库（InnoDB引擎）。

总结

单表备份：

禁用--master-data=2参数

全库备份：

--master-data=2 和 --single-transaction 组合，实现InnoDB的在线热备。

来源：数据和云
原文链接