mysql - 如何防止 mysqldump 将转储拆分为 1MB 的增量？

Question

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

我的 max_allowed_pa​​cket 是 64MB。我通过创建一批插入(每个 500,000 个值)使用 mysqldump 备份表，因为生成的 sql 文件是使用 mysqldump 选项 --skip-extended-insert 重新插入的时间太长了。

这是我正在运行的(来自 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 版本 14.14 Distrib 5.5.44
mysqldump 版本 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 是。我也将它们都设置为 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

...对于任何表，无论有多大，我永远不必担心插入太大，因为 mysqldump 永远不会创建大于 16MB 的插入。

我不知道还需要什么 --skip-extended-insert，但我无法想象我将不得不再次使用它。

Answer 1

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

通常你会遇到相反的问题:在大服务器上这个选项有很大的值(value)，当你得到连续 512 MB 的行时，你不能插入到本地数据库或测试数据库中。

Option

从那里偷来的:

To check the default value of this variable, use this: mysqldump --help | grep net_buffer_length

For me it was almost 1 MB (i.e. 1046528) and it produced enormous lines in the dump file. According to the 5.1 documentation the variable can be set between 1024 and 1048576. However for any value below 4096 it told me this: Warning: option 'net_buffer_length': unsigned value 4095 adjusted to 4096. So probably the minimum on my system was set to 4096.

Dumping with this resulted in a lot more sane SQL file: 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