Linux 管道 mysqldump 到 mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9509008/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Piping mysqldump to mysql
提问by parserr
Sometimes I need to copy MySQL database (db1) to another database (db2). I found this command to be concise and effective:
有时我需要将 MySQL 数据库 (db1) 复制到另一个数据库 (db2)。我发现这个命令简洁有效：
mysqldump --opt db1 | mysql db2
It was working fine, but now it breaks with following error:
它工作正常，但现在它因以下错误而中断：
ERROR 1064 (42000) at line 1586: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE 'some_table_name'': MySQL server ' at line 1
第 1586 行的 ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行附近使用的正确语法 'mysqldump: 无法执行 'SHOW TRIGGERS LIKE 'some_table_name'': MySQL server '
First thing that comes to mind is that database is too big (uncompressed SQL dump is >1G, 1090526011 bytes at the moment, to be precise) for piping it like this. When I do mysqldump > fileand then mysql < fileit works fine, no errors. Table mentioned in error message (some_table_name) is not big or special.
首先想到的是数据库太大了（未压缩的 SQL 转储 >1G，准确地说是 1090526011 字节），无法像这样进行管道传输。当我这样做时mysqldump > file，mysql < file它工作正常，没有错误。错误消息 (some_table_name) 中提到的表并不大或特殊。
Second idea comes from the impression that error message might be truncated, and that it says
第二个想法来自错误消息可能会被截断的印象，它说
"...MySQL server has gone away"
“...MySQL 服务器已经消失”
Quick research on that says it's possible that maximum number of open files (for MySQL and/or system) is reached. So I've tried adding --skip-lock-tableto mysqldumpand raising open-files-limit, but no luck, same error.
对此的快速研究表明，可能已达到最大打开文件数（对于 MySQL 和/或系统）。所以我尝试添加--skip-lock-table到mysqldump和提高open-files-limit，但没有运气，同样的错误。
Obvious solution is to do dump and then import (as it works fine), but piping seems better and more clean to me (let me know if I'm wrong), plus I'm curious to find out what causes this problem. Did I hit some limit that affects command piping?
明显的解决方案是先转储然后导入（因为它工作正常），但管道对我来说似乎更好更干净（如果我错了，请告诉我），而且我很想知道是什么导致了这个问题。我是否达到了影响命令管道的限制？
I've been doing this on hosting server, running MySQL 5.1.60 on Linux and on my dev machine - MySQL 5.1.58 on Linux. Latter gives a bit different error:
我一直在托管服务器上执行此操作，在 Linux 和我的开发机器上运行 MySQL 5.1.60 - Linux 上的 MySQL 5.1.58。后者给出了一些不同的错误：
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table
other_table_nameat row: 7197
mysqldump: 错误 2013: 在查询期间
other_table_name在行转储表时丢失与 MySQL 服务器的连接：7197
UPDATE: Problem is solved by doing separate dump and import, without pipe. Even though I feel it's not really answer to my question, ssmusoke's suggestions were most to the point resulting in accepted answer.
更新：问题是通过单独的转储和导入来解决的，没有管道。尽管我觉得这不是我的问题的真正答案，但 ssmusoke 的建议最重要的是导致接受的答案。
采纳答案by Stephen Senkomago Musoke
The problem may be that the load on the servers gets too high doing both dumping and loading at the same time. This also means that you lose some optimizations like extended inserts, ability to disable foreign keys which can be achieved when you dump a file then import it.
问题可能是服务器上的负载太高，同时进行转储和加载。这也意味着你会失去一些优化，比如扩展插入，禁用外键的能力，当你转储文件然后导入它时可以实现。
I would recommend that you use mysqldump to generate the backup then load it using mysql. That way the load on your server is reduced, and like you said it always works. You can even automate it into a bash script to do both so that you do not need to execute the mysqldump and loading commands.
我建议您使用 mysqldump 生成备份，然后使用 mysql 加载它。这样，您服务器上的负载就会减少，就像您说的那样，它始终有效。您甚至可以将它自动化到一个 bash 脚本中来执行这两项操作，这样您就不需要执行 mysqldump 和加载命令。
回答by John Retterer
Do you need to redirect the stderr stream as well as the stdout from the mysqldump? Error messages may be getting interleaved with the dump output. Try
您是否需要重定向标准错误流以及来自 mysqldump 的标准输出？错误消息可能与转储输出交错。尝试
mysqldump --opt db1 | mysql db2
mysqldump --opt db1 | 数据库表
回答by Aaron Brown
The problem is that you are redirecting stderr to stdout, thus any errors are being interpreted as SQL. Remove 2>&1. Then the real error will show up.
问题是您将 stderr 重定向到 stdout，因此任何错误都被解释为 SQL。删除 2>&1。那么真正的错误就会出现。
回答by txyoji
"MySQL server has gone away" is a symptom of a max packet error. http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
“MySQL 服务器已消失”是最大数据包错误的症状。 http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
Modify your command to specify a larger value for max_allowed_packet.
修改您的命令，为 max_allowed_packet 指定更大的值。
mysqldump --opt db1 | mysql --max_allowed_packet=32M db2
The default is 1M. It may take trial and error to get the right value. http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_packet
默认值为 1M。可能需要反复试验才能获得正确的值。 http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_packet
回答by Jauyzed
it is possible that the backup is hitting the MySQL timeout limits.
备份可能达到 MySQL 超时限制。
The variables can be changed in my.cnf
可以在 my.cnf 中更改变量
net_read_timeout = 120
net_write_timeout = 900
net_read_timeout = 120
net_write_timeout = 900
If you prefer to change these settings without needing to reboot MySQL you can do that with the following SQL statements:
如果你更喜欢在不需要重启 MySQL 的情况下更改这些设置，你可以使用以下 SQL 语句来做到这一点：
set global net_read_timeout = 120;
set global net_write_timeout = 900;
set global net_read_timeout = 120;
set global net_write_timeout = 900;
^ you might need super privilege
^ 你可能需要超级特权
