几天前收到某个业务项目,MySQL数据库逻辑备份mysqldump备份失败的邮件,本是在休假,但本着工作认真负责,7*24小时不间断运维的高尚职业情操,开始了DBA的排错之路(一开始数据库的备份都是成功的,巧的是我休假就出问题,怀疑是数据量又有增长)
首先我们了解下mysqldump备份,数据流向的一个过程:MySQL Server端从数据文件中检索出数据,然后分批将数据返回给mysqldump客户端,然后mysqldump再把数据写入到NFS上。一般情况下存储不是SSD或者是普通磁盘,那么向NFS上写入数据比Server端检索完数据发送给mysqldump客户端要慢得多,这就有可能mysqldump无法及时接收MySQL Server端发送过来的数据,导致Server端检索出来的数据在内存中积压等待发送。当超过等待的时间net_write_timeout(默认60s)时就连接断开,同时抛出错误。
1、定位问题
登录到机器上,先查看了备份文件的逻辑,再查看备份的日志和备份文件大小,确认备份失败并定位到是备份命令mysqldump行执行一半失败(根据备份文件较之前的几天减少了一半且脚本运行日志来断定)。凌晨的备份无效,因而手动触发脚本执行备份,发现了报错如下:
[root@mysql_query hk_sa]# bash /opt/shells/mysqldump.sh
Warning: Using a password on the command line interface can be insecure.
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `rrd_api_log` at row: 2821866
2、排查问题
查看备份失败的表的行数为4982704,查看手动备份失败处的行信息是2017-02-05 04:03:18写入,之前都没有出现过这个备份失败的问题。于是开始怀疑是不是最近数据增长太大或者表的字段太宽的问题(其他数据库的表更大,有的甚至达到400G也没有出现过这个问题,表数据量太大的可能性不大,但单行备份失败,怀疑大字段的问题)
查看表结构如下:
[root@localhost] | 08:42:21 | [heika0516] > desc rrd_api_log;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| api_command | varchar(30) | NO | | NULL | |
| request_info | text | NO | | NULL | |
| response_info | text | NO | | NULL | |
| create_time | datetime | NO | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
方向定了剩下的就是验证自己的猜想了,于是我开始查找资料,果然被我百度到了一些有价值的东西,包括MySQL官方的一些说法:https://bugs.mysql.com/bug.php?id=46103。其中大概的意思就是这个问题老版本会出现,可以调整两个参数net_write_timeout或者max_allowed_packet的值大一些。net_write_timeout的默认值是60s,而max_allowed_packet的默认值是67108864,又是一通查资料后发现,有些前辈调整max_allowed_packet无效,而调整net_write_timeout才有效,于是我也站着巨人的肩膀上搞了一把,果然备份成功了,挺过了休假时被烦恼的问题。
然而新的问题又出来了,很多前辈都讲增大net_write_timeout的值,Server端会消耗更多的内存甚至导致swap的使用影响性能,但又不确定是不是参数调整所致,存在潜在的风险。不过这种说法我并不同意,因为我执行的过程中发现,MEM的free反而变多了,你没有听错真的变多了。
#mysqldump备份执行前
[root@mysql_query hk_sa]# free -m
total used free shared buffers cached
Mem: 16080 13305 2775 0 121 3729
-/+ buffers/cache: 9454 6626
Swap: 8191 349 7842
#增大了net_write_timeout的值
[root@localhost] | 08:51:53 | [(none)] > set @@global.net_write_timeout=500;
Query OK, 0 rows affected (0.01 sec)
#bash完脚本发现备份OK的
[root@mysql_query hk_sa]# ls -lh /opt/app/mysql/data/heika0516/rrd_api_log.ibd
-rw-r--r-- 1 mysql mysql 4.1G Aug 7 22:03 /opt/app/mysql/data/heika0516/rrd_api_log.ibd
#mysqldump备份执行后
[root@mysql_query hk_sa]# free -m
total used free shared buffers cached
Mem: 16080 12434 3646 0 93 2890
-/+ buffers/cache: 9450 6630
Swap: 8191 349 7842