标签： Mysql dump

一：转载总结说明
数据备份、特别是大表数据备份时，这个参数很有用处

用处一：比如设置成 –net-buffer-length=10m ,那么一旦你使用到还原时，你的数据一次commit提交是默认1m 提交时的10倍速度

用户二：比如设置成 –net-buffer-length=10m ,小表估计就是一个insert就完事了，导入时效率会很高。当你备份文件有5G 10G了，那个时候这些参数非常有用
具体的配置使用参考：

Windows版本

D:
cd D:\mmxp\mysql\bin
set “Ymd=%date:~,4%%date:~5,2%%date:~8,2%”
mysqldump –default-character-set=utf8 –net-buffer-length=10m –ignore-table=yn.gov_price_category_detail –skip-lock-tables yn> D:\zcsjw\backup\yn\yn_%Ymd%.sql
“C:\Program Files\7-Zip\7z.exe” a “D:\zcsjw\backup\yn\yn_%Ymd%.sql.zip” “D:\zcsjw\backup\yn\yn_%Ymd%.sql”
Windows使用请参考：《Windows系统Mysql数据库备份》

Linux版本

#!/bin/bash
# 需要的话，自己改这里哦
#db_user=’root’
#db_password=`cat /data/www/mysql_password`
db_name=’guides’
backup_dir=’/data/backup/mysql/’
current_time=$(date +’%Y-%m-%d_%H%M%S’)
filepath=$backup_dir$current_time’.sql.gz’
#此处没有使用 $db_password $db_user, 已经写入到配置文件中
echo ‘开始导出数据库…’
mysqldump –defaults-extra-file=/data/backup/my_mysql.cnf $db_name –net-buffer-length=10m | gzip > $filepath
echo ‘导出成功，文件名为: ‘$filepath
Linux使用请参考：《CentOS7 MySQL定时自动备份实现》

注意：备份数据做恢复时，务必将你的数据库配置设置很大

#设置在网络传输中一次消息传输量的*大值 大备份文件大于1G以上
max_allowed_packet=1G
参考：《dump备份出来的还原回去很慢》

二：转载正文
有大师说mysqldump的net-buffer-length这个参数是个鸡肋,对与性能提升不是很大.之前也就没关注过.偶然一次测试.碰到了.就研究了下..其实还是很有用的(对于我们这种菜鸟来说).
下面结合实例讲讲:

讲net-buffer-length之前,先讲另外一个mysqldump的参数–extended-insert
这个参数的意思就是是否开启合并insert(默认是开启的,不想开启直接加skip-extended-insert).用白话讲就是用mysqldump导出生成的insert数据合并成一条,如果不开启就是一条数据一个insert.

开启后导出的格式类似这样: insert into table_name values (xx,xx,xx),(xx,xx,xx),(xx,xx,xx)….
关闭后导出的格式类似这样: insert into table_name values (xx,xx,xx);
insert into table_name values (xx,xx,xx);
insert into table_name values (xx,xx,xx);
.
ok,这个参数应该解释清楚了吧.理想是很丰满的,但现实却是很骨感的.
现实情况是开启了extended-insert参数,如果数据超过1M,也会生成多个insert

[root@testdb3 ~]#/home/mysql3310/bin/mysqldump -S /tmp/mysql3310.sock –databases bigdata –tables t1 >t1.sql
[root@testdb3 ~]# cat t1.sql |grep INSERT |wc -l
125

这就引入net-buffer-length这个参数了
mysqldump(5.7.5以后,官方建议使用mysqlpump)的net-buffer-length 官方的解释就是通信时缓存数据的大小.*小4k,*大16M,默认是1M.
大家都知道.msyqldump导出的数据就包括两部分,一部分是DDL(包含建表,建存储,建视图等sql语句),另一部分就是insert了,所有的数据都是生成insert了,所以insert这部分才是mysqldump的*大部分.

结合上面说到的情况.启用extended-insert,理论上应该一个表只生成一个insert,但如果一个insert的数据超过1M(默认值),就会生成第二个insert,如果在超过1M,就生成第三个insert,以此类推,直到数据全部导完.
下面结合实例来看:

[root@testdb3 ~]#/home/mysql3310/bin/mysqldump -S /tmp/mysql3310.sock –databases bigdata –tables t1 >t1.sql
[root@testdb3 ~]# cat t1.sql |grep INSERT |wc -l
125
[root@testdb3 ~]# sed -n ’99p’ t1.sql >t2.sql
[root@testdb3 ~]# ls -al t2.sql
-rw-r–r–. 1 root root 1042300 Jan 19 10:44 t2.sql

可以看到一个insert就差不多是1M
现在加上net-buffer-length 在来测试:

[root@testdb3 ~]# /home/mysql3310/bin/mysqldump -S /tmp/mysql3310.sock –databases bigdata –tables t1 –net-buffer-length=5000000 >t3.sql
[root@testdb3 ~]# sed -n ‘100p’ t3.sql >t4.sql
[root@testdb3 ~]# ls -al t4.sql
-rw-r–r–. 1 root root 4979542 Jan 19 10:47 t4.sql
设置了差不多5M,看到一条insert就差不多5M的大小了
*大值是16M

[root@testdb3 ~]# /home/mysql3310/bin/mysqldump -S /tmp/mysql3310.sock –databases bigdata –tables t1 –net-buffer-length=25000000 >t5.sql
[root@testdb3 ~]# sed -n ‘100p’ t5.sql >t6.sql
[root@testdb3 ~]# ls -al t6.sql
-rw-r–r–. 1 root root 16712034 Jan 19 10:59 t6.sql

设置了25M,但一个insert还是只有16M.

在来看看导入,导入就是按导出的时候有多少个insert就会有多少个事务(前提是autocommit是开启的);
所以我们在source的时候就会开到类似下面的提示:

Query OK, 4053 rows affected (0.16 sec)
Records: 4053 Duplicates: 0 Warnings: 0

Query OK, 4053 rows affected (0.51 sec)
Records: 4053 Duplicates: 0 Warnings: 0

Query OK, 4053 rows affected (0.16 sec)
Records: 4053 Duplicates: 0 Warnings: 0

Query OK, 4053 rows affected (0.16 sec)
Records: 4053 Duplicates: 0 Warnings: 0

Query OK, 4053 rows affected (0.16 sec)
Records: 4053 Duplicates: 0 Warnings: 0
这是因为导出的时候默认是1M,刚好4053行就是1M,所以会有这样的提示,如果不是1M,就会有如下的提示:

Query OK, 19364 rows affected (0.74 sec)
Records: 19364 Duplicates: 0 Warnings: 0

Query OK, 19363 rows affected (0.75 sec)
Records: 19363 Duplicates: 0 Warnings: 0

Query OK, 19363 rows affected (0.75 sec)
Records: 19363 Duplicates: 0 Warnings: 0

Query OK, 19363 rows affected (0.75 sec)
Records: 19363 Duplicates: 0 Warnings: 0

Query OK, 19364 rows affected (0.74 sec)
Records: 19364 Duplicates: 0 Warnings: 0

这里设置的是5M,就是差不多19364行.

这两个例子都是同样的表结构和数据.
在导入的时候还涉及另外一个参数max_allowed_packet,如果这个值设置过低,会导致数据无法导入的.如下:

mysql> set global max_allowed_packet=1048576;

mysql> show variables like ‘max%’;
+—————————-+———————-+
| Variable_name | Value |
+—————————-+———————-+
| max_allowed_packet | 1048576 |

mysql> source t4.sql;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 18
Current database: tt2

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 19
Current database: tt2

ERROR 2006 (HY000): MySQL server has gone away
mysql> source t2.sql;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 20
Current database: tt2

Query OK, 4053 rows affected (0.51 sec)
Records: 4053 Duplicates: 0 Warnings: 0

max_allowed_packet设置成了1M,t4.sql是之前的5M的sql,就会导入失败.在看看错误日志:
2017-01-19T13:48:09.975902+08:00 5 [Note] Aborted connection 5 to db: ‘tt2’ user: ‘root’ host: ‘localhost’ (Got a packet bigger than ‘max_allowed_packet’ bytes)

就会有提示的.. 这里就顺带提一下…

好了.到这基本上就清楚了net-buffer-length的功能了.有的人说net-buffer-length对性能提升效果不大.我没去做性能测试,从理论上讲肯定是有所提升的.
首先,导出的性能肯定有所提升.每1M就要分段,和16M在分段,如果是大数据(具体多大,以G为单位吧)导出,肯定会有所提升,如果数据小,当然就看不出啥效果了.
其次,导入.导入的时候,如果1M就要提交一个事务,和16M在提交一个事务,如果磁盘够快,肯定性能和时间是会节省很多的.

具体就不去做性能测试了.因为毕竟只是个小功能,对于有需求的就了解下,没需求就飘过吧……