将mysqldump管道传输到mysql [英] Piping mysqldump to mysql
问题描述
有时我需要将MySQL数据库(db1)复制到另一个数据库(db2).我发现此命令简洁有效:
Sometimes I need to copy MySQL database (db1) to another database (db2). I found this command to be concise and effective:
mysqldump --opt db1 | mysql db2
它工作正常，但现在由于以下错误而中断:
It was working fine, but now it breaks with following error:
第1586行的错误1064(42000):您的SQL语法有错误； 检查与您的MySQL服务器版本相对应的手册 在'mysqldump附近使用正确的语法:无法执行'SHOW TRIGGERS 就像"some_table_name":第1行的MySQL服务器"
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
首先想到的是数据库太大(准确地说，目前未压缩的SQL转储为> 1G，目前为1090526011字节)，无法通过这种方式进行管道传输.当我先执行mysqldump > file然后执行mysql < file时，它运行正常，没有错误.错误消息(some_table_name)中提到的表不是很大还是很特殊.
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 > file and then mysql < file it works fine, no errors. Table mentioned in error message (some_table_name) is not big or special.
第二个想法来自这样的印象，即错误消息可能会被截断，并且显示为
Second idea comes from the impression that error message might be truncated, and that it says
"... MySQL服务器已消失"
"...MySQL server has gone away"
对此进行的快速研究表明，有可能达到(对于MySQL和/或系统)打开文件的最大数量.因此，我尝试将--skip-lock-table添加到mysqldump并提高open-files-limit，但是没有运气，同样的错误.
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-table to mysqldump and raising open-files-limit, but no luck, same error.
一个显而易见的解决方案是先转储然后导入(因为它可以正常工作)，但是管道对我来说似乎更好，更干净(让我知道我是否错了)，再加上我很想知道是什么原因造成的问题.我是否达到了一些会影响命令管道的限制?
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?
我一直在托管服务器上执行此操作，在Linux和我的开发机上运行MySQL 5.1.60，在Linux上运行MySQL 5.1.58.后者给出了一些不同的错误:
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:
mysqldump:错误2013:查询期间与MySQL服务器的连接断开 将表
other_table_name转储到第7197行
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table
other_table_nameat row: 7197
更新:通过单独的转储和导入(不使用管道)解决了问题.即使我觉得这并不是我的问题的真正答案，但ssmusoke的建议最能引起人们的接受.
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.
推荐答案
问题可能是服务器上的负载过高，无法同时进行转储和加载.这也意味着您将失去一些优化功能，例如扩展插入，禁用外键的能力，这些功能可以在转储文件然后导入文件时实现.
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.
我建议您使用mysqldump生成备份，然后使用mysql加载它.这样，您的服务器上的负载就减少了，就像您所说的那样，它始终可以工作.您甚至可以将其自动化到bash脚本中，以同时执行这两项操作，因此您无需执行mysqldump和加载命令.
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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
