Piping mysqldump to mysql Piping mysqldump to mysql linux linux

Piping mysqldump to mysql


"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

Modify your command to specify a larger value for 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


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.


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 --opt db1 | mysql db2