Databases
StackOverflow2013
Postsdbo
POPiping mysqldump to mysql
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPiping mysqldump to mysql
    text
    copied!<p>Sometimes I need to copy MySQL database (db1) to another database (db2). I found this command to be concise and effective:</p> <pre><code>mysqldump --opt db1 | mysql db2 </code></pre> <p>It was working fine, but now it breaks with following error:</p> <blockquote> <p>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</p> </blockquote> <p>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 <code>mysqldump &gt; file</code> and then <code>mysql &lt; file</code> it works fine, no errors. Table mentioned in error message (some_table_name) is not big or special.</p> <p>Second idea comes from the impression that error message might be truncated, and that it says </p> <blockquote> <p>"...MySQL server has gone away"</p> </blockquote> <p>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 <code>--skip-lock-table</code> to <code>mysqldump</code> and raising <code>open-files-limit</code>, but no luck, same error.</p> <p>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?</p> <p>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:</p> <blockquote> <p>mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table <code>other_table_name</code> at row: 7197</p> </blockquote> <hr> <p>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.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload