澳门在线威尼斯官方 > 电脑数据库 > 深入浅出mysqldump,mysqldump备份表中有大字段失败

原标题:深入浅出mysqldump,mysqldump备份表中有大字段失败

浏览次数:115 时间:2019-09-13

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

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

       首先我们了解下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也没有出现过这个问题,表数据量太大的可能性不大,但单行备份失败,怀疑大字段的问题)

     语法:

      查看表结构如下:

     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) 

     例子:

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

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

     然而新的问题又出来了,很多前辈都讲增大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,至此问题彻底解决了,也没有改变参数的全局值影响会话。

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

     语法:

     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,mysqldump备份表中有大字段失败

关键词:

上一篇:没有了

下一篇:没有了