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

作者介绍:

刘鹏松,任职云和恩墨北区交付部,负责山东部分客户业务交付。

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

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

1

问题描述

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

2

问题分析与处理

登录数据库服务器使用 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备份数据库,通过分析备份脚本发现这个脚本的逻辑是这样的:

  1. 整库备份一次使用的是--all-database参数
  2. 分别备份每个数据库为一个备份文件
  3. 单表备份一次,即一个表备份成一个文件

部分脚本节选如下:

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

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

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

很显然出问题的时候是在备份单个表,通过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 的时间会更长,从而影响了业务的正常运行。

3

问题的解决与改善

问题解决

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

改善

调整备份策略:

1、取消备份每个单表为一个文件,减少全局锁(经过生产环境实际测试mysqldump全库(17G数据)备份一次不到5分钟);

2、如果有必要进行单表备份的话,禁用--master-data=2参数,只使用--single-transaction 参数;

3、采用XtraBackup 物理备份替换mysqldump的逻辑备份,来进行在线热备数据库(InnoDB引擎)。

4

总结

单表备份:

禁用--master-data=2参数

全库备份:

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

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-08-31

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏L宝宝聊IT

SQL server 数据库的存储过程和触发器

1133
来自专栏喵了个咪的博客空间

原 EMQ百万级MQTT消息服务(ACL鉴权)

虽然EMQ已经搭建起来了,但是投入到业务使用中还面临着一些问题,当然MQTT设计之初也考虑了这一点,比如不是任何一个客户端都能链接到服务器和限制客户端能够对to...

3174
来自专栏cloudskyme

使用oracle的大数据工具ODCH访问HDFS数据文件

软件下载 Oracle Big Data Connectors:ODCH 下载地址: http://www.oracle.com/technetwork/bdc...

3278
来自专栏杨建荣的学习笔记

MySQL主从不一致的修复过程(r10笔记第96天)

昨天发现一个5.7的MySQL从库在应用日志的时候报出了错误。从库启用过了并行复制。Last Error的内容为: Last_Error: Coordinato...

3459
来自专栏python3

MySQL开启慢查询

编辑Mysql 配置文件(my.cnf),在[mysqld]字段添加以下几行,记录超过1秒请求的SQL语句:

471
来自专栏北京马哥教育

pt-table-checksum使用实践

在工作中接触最多的就是mysql replication。由于现在公司也还在使用mysql 5.1.x版本,在复制方面还是比较多的问题: 比如主库宕机或者从库宕...

3678
来自专栏杨建荣的学习笔记

由小见大-MySQL脚本部署中的一些策略

在线上环境中部署脚本,可谓是常在河边走,哪有不湿鞋,所以大大小小的案例总结下来,还是会发现一些有趣的地方,这些可以作为操作时的一些参考,仅供参考而已。 第一类...

3166
来自专栏数据库

试用ODU软件恢复corrupt block

Oracle的数据恢复处理,有各种方法工具支持,在这方面,我算是一个新手,也是处于不断的学习中。 业界有一些著名的恢复软件,简单罗列一下, 1. Oracle ...

2195
来自专栏沃趣科技

mysqldump与innobackupex备份过程你知多少(三)

相关阅读: mysqldump与innobackupex备份过程你知多少(二) mysqldump与innobackupex备份过程你知多少(一) mysqld...

42613
来自专栏Ceph对象存储方案

Luminous下删除和新建OSD的正确姿势

L版本开始极大的降低了对运维操作复杂度,新增了很多命令去确保数据安全,很多新手在删除OSD的时候很容易忽视了集群PGs的状态最终导致数据丢失,因此官方加入以下几...

1062

扫描关注云+社区