mysqldump备份表中有大字段退步的排错过程,深入

2019-12-02 18:42 来源:未知

       几日前收到有个别业务品种,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卡塔尔(英语:State of Qatar) 数据库名 > 文件名.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自动制动踏板、因此有着该目的之后的就都没备份了

  

  

  ③ 场景模拟重放:

 

[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动荡形成的

       解决方案:

       开启skip-name-resolve选项将会最大程度幸免那么些难点

     

  ② 错误现象:

       Lost connection to MySQL server at ‘reading authorization packet’:

       原因解析:

       从MySQL获取三个可用的接二连三是每每握手的结果。在三翻五次抓手的长河中,互联网波动会促成握手战败

       技术方案:

       最棒的解决办法是让mysqldump重新发起连接央求

     

  ③ 错误现象:

       Lost connection to MySQL server during query

       原因解析:

       mysqldump管理多少过慢(NFS、gzip引起)会招致MySQL主动断开连接

       建设方案:

       加大net_write_timeout的设置

㈠ 常用操作 ① 备份全库 语法: mysqldump -h主机名 -P端...

TAG标签:
版权声明:本文由金沙澳门唯一官网发布于数据库管理,转载请注明出处:mysqldump备份表中有大字段退步的排错过程,深入