mysqldump备份表中有大字段失败的排错过程

152次阅读

共计 3108 个字符，预计需要花费 8 分钟才能阅读完成。

几天前收到某个业务项目，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 也没有出现过这个问题，表数据量太大的可能性不大，但单行备份失败，怀疑大字段的问题）

查看表结构如下：

方向定了剩下的就是验证自己的猜想了，于是我开始查找资料，果然被我百度到了一些有价值的东西，包括 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

到此为止，mysqldump 备份失败确实是解决，但是前辈们反映的问题是消耗更多的内存，到我这反而释放了更多的内存，这样修改参数终究是会存在安全隐患，且这个参数会影响所有的会话连接。那就先不管了，过个好假期再搞。不过我始终有个疑问，我这维护的 400G 的大表，且部分表比这更大，也有大字段就没出现过这个问题，怎么会突然出现呢，因此我怀疑不是表数据过多的问题，还是大字段的问题。

休假回来后，立马开始 test 排错的工作，先分析确认好切入点，我就把 net_write_timeout 的值改为默认 60，但这样备份肯定会失败的，于是想到了 max_allowed_packet 参数，但全局调整这个参数，对网络发包和各个会话也有影响，继续对 mysqldump 这个备份进行调研，居然让我发现了一个牛逼的可接参数 max_allowed_packet，在 mysqldump 后面加了这个选项，值大小大家可以根据表的大小进行设置，我这里给的 500M，至此问题彻底解决了，也没有改变参数的全局值影响会话。

：

正文完

星哥说事-微信公众号