如何防止mysqldump将转储分成1MB的增量？

Question

如何防止mysqldump将转储分成1MB的增量？

3

我有一个相当大的MySQL表（1150万行）。就数据大小而言，该表约为2GB。

我的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参数，但我想我以后不会再用到它了。

- RTF

你提到“我的max_allowed_packet”好像它是一个单一的设置。实际上，它是两个独立的设置——服务器端和客户端。传递--max-allowed-packet=...一个等于服务器端值的数值可能是避免net_buffer_length强制转换下降的关键。或者，你可以将其设置为16MiB，这可能是一个功能缺陷，因为文档中似乎限制为1MiB。 - Michael - sqlbot

我在问题中忘了提到，但备份永远不会在网络上进行，始终是本地主机。my.cnf配置文件的mysqld和mysqldump部分下max_allowed_packet均为64M。 - RTF

啊，原来你已经解决了这个问题。顺便说一下，max_allowed_packet 是一个命名不太合适的选项。它实际上是指 MySQL 第七层的“数据包”大小，而不是 IP 数据包或以太网帧。MySQL 数据包将被分成多少个片段/数据包/数据报/帧，以在网络上传输。为了增加术语混淆，一个最大为 max_allowed_packet 的 MySQL “数据包”可以分成多个 MySQL “数据包”，除了最后一个之外，所有其他数据包的长度都恰好为 0xffffff 字节。（如果不是因为差一，我会认为这在 16MB 限制中起到了作用）。 - Michael - sqlbot

1

--skip-extended-insert非常有用，如果您想逐行进行操作。我们在配置表中使用它，以便我们可以比较版本。对于差异而言，一行很长的代码并不是很有用。;-) - flaschenpost

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- flaschenpost · Accepted Answer

mysqldump根据my.ini设置限制行长，可能在客户端上比服务器上小。选项是net_buffer_length。

通常你会遇到相反的问题：在大型服务器上，此选项具有很大的值，当您连续获取512 MB的行时，您无法插入到本地数据库或测试数据库中。

选项

从那里窃取：

要检查此变量的默认值，请使用mysqldump这样做：--help | grep net_buffer_length

对于我来说，它几乎达到1 MB（即1046528），并且它生成了巨大的转储文件中的行。根据5.1文档，该变量可以在1024和1048576之间设置。但是，对于低于4096的任何值，它告诉我这一点：警告：选项'net_buffer_length'：调整为4096的无符号值4095。因此，我的系统上的最小值可能设置为4096。

使用这个结果产生了更多理智的SQL文件：mysqldump --net_buffer_length=4096 --create-options --default-character-set="utf8" --host="localhost" --hex-blob --lock-tables --password --quote-names --user="myuser" "mydatabase" "mytable" > mytable.sql