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

几天前收到某个业务项目，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

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

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

Linux公社的RSS地址 ： https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址： https://www.linuxidc.com/Linux/2018-09/154396.htm