mysqldump备份表中有大字段战败的排错进度,还原大备份mysql文件战败的化解措施分享

     
 几天前收到某个业务品种,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文件战败的化解措施分享。 ㈠ 常用操作

大约就是因为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

997755.com澳门葡京,     

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

     

     语法:

     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端…

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website