我的
max_allowed_packet
为64MB。我正在使用mysqldump
备份表格,通过创建一批插入语句(每个插入语句包含500,000个值),因为使用mysqldump选项--skip-extended-insert
生成的结果sql文件重新插入需要太长时间。这是我正在运行的内容(来自perl脚本):
`mysqldump -u root -pmypassword --no-data mydb mytable > mybackup.sql`
my $offset = 0;
while ($offset < $row_count) {
`mysqldump -u root -p[mypassword] --opt --no-create-info --skip-add-drop-table --where="1 LIMIT $offset, 500000" mydb mytable >> mybackup.sql`
}
生成的SQL文件大小为900MB。请查看以下
grep -n '\-\- WHERE\: 1 LIMIT' mybackup.sql
的输出结果:80:-- WHERE: 1 LIMIT 0, 500000
158:-- WHERE: 1 LIMIT 500000, 500000
236:-- WHERE: 1 LIMIT 1000000, 500000
314:-- WHERE: 1 LIMIT 1500000, 500000
392:-- WHERE: 1 LIMIT 2000000, 500000
469:-- WHERE: 1 LIMIT 2500000, 500000
546:-- WHERE: 1 LIMIT 3000000, 500000
623:-- WHERE: 1 LIMIT 3500000, 500000
699:-- WHERE: 1 LIMIT 4000000, 500000
772:-- WHERE: 1 LIMIT 4500000, 500000
846:-- WHERE: 1 LIMIT 5000000, 500000
921:-- WHERE: 1 LIMIT 5500000, 500000
996:-- WHERE: 1 LIMIT 6000000, 500000
1072:-- WHERE: 1 LIMIT 6500000, 500000
1150:-- WHERE: 1 LIMIT 7000000, 500000
1229:-- WHERE: 1 LIMIT 7500000, 500000
1308:-- WHERE: 1 LIMIT 8000000, 500000
1386:-- WHERE: 1 LIMIT 8500000, 500000
1464:-- WHERE: 1 LIMIT 9000000, 500000
1542:-- WHERE: 1 LIMIT 9500000, 500000
1620:-- WHERE: 1 LIMIT 10000000, 500000
1697:-- WHERE: 1 LIMIT 10500000, 500000
1774:-- WHERE: 1 LIMIT 11000000, 500000
1851:-- WHERE: 1 LIMIT 11500000, 500000
运行命令grep -c 'INSERT INTO ' mybackup.sql
的结果是923。
这923个插入语句几乎每个都接近1MB。为什么mysqldump对每个命令都产生了如此多的插入语句?我本来期望只会看到24个插入语句,但是这个命令似乎为每个批次产生了38个插入语句。
我是否可以在my.cnf中设置一些内容或者传递给mysqldump以阻止它将备份分成1MB增量的插入语句?
mysql Ver 14.14 Distrib 5.5.44
mysqldump Ver 10.13 Distrib 5.5.44
我在mysqldump命令中加入了附加选项net_buffer_length=64M
重新运行了作业。但是我收到了警告:Warning: option 'net_buffer_length': unsigned value 67108864 adjusted to 16777216
。我查看了my.cnf
,看看是否有任何设置为16M的内容,发现key_buffer
和query_cache_size
都被设置为了16M。我也将它们都设置为了64M并重新运行了作业,但是仍然收到了相同的警告。
生成的备份文件似乎没有问题,插入语句现在每个约为16MB。是否可能进一步增加它?是否有限制缓冲区长度的选项?
我在my.cnf
中将mysql的net_buffer_length
变量设置为64M,但是如文档所述,它被设置为其最大值1048576(1MB)。但是mysqldump的net_buffer_length
选项让我将最大插入大小提高到了16MB(尽管它从请求的64MB减少了)。
我很满意使用16MB的插入语句,但如果可以的话,我有兴趣进一步增加它。
最后一个想法。看起来我完全浪费时间自己尝试进行任何批处理,因为mysqldump默认会做我想要的一切。所以如果我只运行:
mysqldump -u root -p[mypassword] --net_buffer_length=16M mydb mytable > mybackup.sql
无论是多大的表格,我都不需要担心插入的大小超过16MB,因为mysqldump不会创建超过此大小的插入语句。
我不知道还有哪些情况需要使用
--skip-extended-insert
参数,但我想我以后不会再用到它了。
max_allowed_packet
”好像它是一个单一的设置。实际上,它是两个独立的设置——服务器端和客户端。传递--max-allowed-packet=...
一个等于服务器端值的数值可能是避免net_buffer_length强制转换下降的关键。或者,你可以将其设置为16MiB,这可能是一个功能缺陷,因为文档中似乎限制为1MiB。 - Michael - sqlbotmy.cnf
配置文件的mysqld
和mysqldump
部分下max_allowed_packet
均为64M。 - RTFmax_allowed_packet
的 MySQL “数据包”可以分成多个 MySQL “数据包”,除了最后一个之外,所有其他数据包的长度都恰好为 0xffffff 字节。(如果不是因为差一,我会认为这在 16MB 限制中起到了作用)。 - Michael - sqlbot