案例分享，mysqldump备份表中有大字段退步的排遗

2019-10-13 06:10来源：未知

几天前收到某个业务项目，MySQL数据库逻辑备份mysqldump备份失败的邮件，本是在休假，但本着工作认真负责，7*24小时不间断运维的高尚职业情操，开始了DBA的排错之路（一开始数据库的备份都是成功的，巧的是我休假就出问题，怀疑是数据量又有增长）

【逻辑备份】深入浅出mysqldump：常用操作、案例分享、意外终止的原因以及解决方法

导出数据库的时候报如下错误 mysqldump: Error 2013: Lost connection to MySQL server during query when dumping tablemailat row: 2637433

用下面方法解决(管理mysql用的是navicat).,设置以下几个参数的值后就正常了,以下语句也可以在mysql的控制台上执行 .

首先我们了解下mysqldump备份，数据流向的一个过程：MySQL Server端从数据文件中检索出数据，然后分批将数据返回给mysqldump客户端，然后mysqldump再把数据写入到NFS上。一般情况下存储不是SSD或者是普通磁盘，那么向NFS上写入数据比Server端检索完数据发送给mysqldump客户端要慢得多，这就有可能mysqldump无法及时接收MySQL Server端发送过来的数据，导致Server端检索出来的数据在内存中积压等待发送。当超过等待的时间net_write_timeout（默认60s）时就连接断开，同时抛出错误。

查询资料

复制代码代码如下:

1、定位问题

㈠常用操作

大概说是因为mysqldump来不及接受mysql server端发送过来的数据，Server端的数据就会积压在内存中等待发送，这个等待不是无限期的，当Server的等待时间超过net_write_timeout（默认是60秒）时它就失去了耐心，mysqldump的连接会被断开，同时抛出错误Got error: 2013: Lost connection。

show variables like '%timeout%';
show variables like '%packet%';
set global max_allowed_packet=99328000;
set global wait_timeout=2880000;
set global interactive_timeout=2880000;
-- wait_timeout=2880000
-- interactive_timeout=2880000
-- max_allowed_packet=100M

登录到机器上，先查看了备份文件的逻辑，再查看备份的日志和备份文件大小，确认备份失败并定位到是备份命令mysqldump行执行一半失败（根据备份文件较之前的几天减少了一半且脚本运行日志来断定）。凌晨的备份无效，因而手动触发脚本执行备份，发现了报错如下：

解决方案一

PS:如果是在服务器上,要记得恢复后这几个参数要调回去,不然性能会下降的,具体这几个参数功能,大家自己找找吧.

[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

① 备份全库

增加net_write_timeout可以解决上述的问题的。在实践中发现，在增大 net_write_timeout后，Server端会消耗更多的内存，有时甚至会导致swap的使用（并不确定是不是修改 net_write_timeout所至）。建议在mysqldump之前修改net_write_timeout为一个较大的值（如1800），在 mysqldump结束后，在将这个值修改到默认的60。

您可能感兴趣的文章:

mysqldump备份还原和mysqldump导入导出语句大全详解
PHP备份/还原MySQL数据库的代码
mysql 数据库备份和还原方法集锦推荐
MySQL数据库备份和还原的常用命令小结
MySQL中使用innobackupex、xtrabackup进行大数据的备份和还原教程
详解MYSQL的备份还原(PHP实现)
命令行模式下备份、还原 MySQL 数据库的语句小结
深入解析Linux下MySQL数据库的备份与还原
MySQL 备份还原数据库批处理
PHP程序员玩转Linux系列备份还原MySQL

2、排查问题

在sql命令行里面设置临时全局生效用类似如下命令：
SET GLOBAL net_write_timeout=1800;

查看备份失败的表的行数为4982704，查看手动备份失败处的行信息是2017-02-05 04:03:18写入，之前都没有出现过这个备份失败的问题。于是开始怀疑是不是最近数据增长太大或者表的字段太宽的问题（其他数据库的表更大，有的甚至达到400G也没有出现过这个问题，表数据量太大的可能性不大，但单行备份失败，怀疑大字段的问题）

语法：

修改了这个参数后再备份，不再报错
注意，这个参数不是mysqldump选项，而是mysql的一个配置参数

查看表结构如下：

mysqldump -h主机名 -P端口 -u用户名 -p密码 (–database) 数据库名 > 文件名.sql

解决方案二

[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)

例子：

在执行 mysqldump 的时候可以通过添加 --quick 的参数来避免出现这样的问题

方向定了剩下的就是验证自己的猜想了，于是我开始查找资料，果然被我百度到了一些有价值的东西，包括MySQL官方的一些说法：

mysqldump -hlocalhost -P3306 -urocky -p123456 db_test > bakfile1.sql

 --quick，-q

该选项用于转储大的表。它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。

然而新的问题又出来了，很多前辈都讲增大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的大表，且部分表比这更大，也有大字段就没出现过这个问题，怎么会突然出现呢，因此我怀疑不是表数据过多的问题，还是大字段的问题。

http://www.linuxyw.com/linux/yunweiguzhang/20130609/566.html

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

备份MySQL数据库为带删除表的格式、能够让该备份覆盖已有数据库而不需要手动删除原有数据库

http://www.cnblogs.com/haven/archive/2012/10/27/2742141.html

语法：

mysqldump -–add-drop-table -u{username} -p{password} {databasename} > {backfile.sql}

例子：

mysqldump -–add-drop-table –urocky -p123456 db_test > bakfile2.sql

③ 压缩备份

语法：

mysqldump -h{hostname} -u{username} -p{password} {databasename} | gzip > {backfile.sql.gz}

例子：

mysqldump –hlocalhost –urocky –p123456 db_test | gzip > bakfile3.sql.gz

④ 备份某些表

语法：

mysqldump -h主机名 -P端口 -u用户名 -p密码 (–tables | –quick) 数据库名表名1 (表名2 …) > 文件名.sql

例子：

mysqldump -hlocalhost -urocky -p123456 db_test tbl_test > bakfile4-1.sql

mysqldump -hlocalhost -P3306 -urocky -p123456 db_test tbl_test > bakfile4-2.sql

mysqldump -hlocalhost -P3306 -urocky -p123456 --quick db_test tbl_test > bakfile4-3.sql

mysqldump -hlocalhost -P3306 -urocky -p123456 --tables db_test tbl_test1 tbl_test2 > bakfile4-4.sql

⑤ 同时备份多个库

语法：

mysqldump -h{hostname} (-P{port}) -u{username} -p{password} –databases {dbname1} {dbname2} {dbname3} > multibackfile.sql

例子：

mysqldump -hlocalhost -urocky -p123456 –databases db_test1 db_test2 db_test3 > multibackfile.sql

⑥ 备份服务器上的所有数据库

语法：

mysqldump –all-databases > allbackupfile.sql

⑦ 仅仅备份数据库结构

语法：

mysqldump –no-data –databases {databasename1} {databasename2} > {structurebackfile.sql}

例子：

mysqldump –no-data –databases db_test1 db_test2 > structurebackfile.sql

⑧ 导出某个表的部分数据

语法：

mysqldump -u用户名 -p密码数据库名表名 --where="筛选条件" > 导出文件路径

例子：

mysqldump -uroot -p123456 test test_data --where=" id > 100" > /tmp/test.sql

㈡案例分享

① 案例描述：

mysqldump命令常规方式创建备份拉到某机器上恢复、恢复执行很成功、一条错误信息都没看着

但等恢复完登录到数据库中一瞅、你猜怎么地、数据不全

第一反应当然是查看备份文件、经过检查、果然、恢复操作确实没有问题、

因为备份集中的内容就不全，那么，为什么备份集内容不全

② 原因分析：

分析发现、原来是在导出某个视图对象时报错、mysqldump自动中止、因此所有该对象之后的就都没备份了

③ 场景模拟回放：

[plain]

Session_A：

mysql> use test;

Database changed

mysql> create table rocky (id int,name varchar(100));

Query OK, 0 rows affected (0.04 sec)

mysql> create view rocky_view as select * from rocky;

Query OK, 0 rows affected (0.01 sec)

mysql> rename table rocky to robbin;

Query OK, 0 rows affected (0.03 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

Session_B：

[[email protected] bin]$ ./mysqldump --tables test robbin rocky_view > bak.sql

mysqldump: Got error: 1356: View 'test.rocky_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when doing LOCK TABLES

创建备份时、view对象引用的表对象不存在、执行LOCK TABLES失败、于是mysqldump就中止了

这其实真不怪 mysqldump、因为mysqldump执行过程中遇到任何问题、默认情况下都是直接退出

④ 解决方案：

执行mysqldump时附加--force参数、该参数功能是当遇到错误时忽略、继续执行后面的操作

这个参数提供类似 Oracle 数据库中exp命令的ignore=y参数的功能、

事实上在 Oracle 数据库中执行exp时通常都会指定ignore、

对应到MySQL数据库、我想在执行mysqldump命令行过程中、--force参数也应做为必备参数调用

㈢ mysqldump意外终止的原因以及解决方法

① 错误现象：

Lost connection to MySQL server at ‘reading initial communication packet’：

原因分析：

因为DNS不稳定导致的