I use this command to pull some remote db to local db:

$ ssh remote.com "mysqldump remotedb | gzip" | gzip -d | mysql localdb

I see this runs a bunch of individual commands on the database. Is there a faster way?

I will initiate the command from the local machine, and will be run by cron.


This methods leads to: Lost connection to MySQL server during query when dumping table


I don't see any problem with this command. You are:

  1. Exporting mysql and compressing the dump on the remote machine.
  2. Getting the compressed dump to local machine and decompressing it.
  3. Importing the dump to local mysql.

This should be good enough. Clearly, the execution time of such command is dependent on the connection speed and DB size.

If you are experiencing a high execution time, you can think of an alternative way to sync your DBs. For example, you can try exporting only the changes not the whole DB.

  • I see chunks processing. I was wondering if wouldn't be faster to export to a file, than transfer the file, then import it. Could this be achieved with 1 line of commands, or some chain of commands? – Pentium10 Mar 8 '12 at 8:00
  • This should be OK without creating file unless your DB is huge. If you want to try such option, you can create a simple script that will execute these commands individually and invoke the script in a cron job. – Khaled Mar 8 '12 at 8:03
  • phpMyadmin reports 1.2G of size, and I have doubts this is efficient, as I see chunks execution, like individual insert statements instead of loading all the table at once. – Pentium10 Mar 8 '12 at 8:04
  • Would you guide me to an article where I can build these commands, as I barely found the above. – Pentium10 Mar 8 '12 at 8:07

Maybe. It depends on what the bottleneck is on your system. If you have a fast network, and the bottleneck is the dump process, then doing an LVM snapshot and rsyncing the data files across will be quite quick. Otherwise, if the network is the bottleneck, you're probably at about the limit of what you're going to be able to achieve.

  • One is that I get: Lost connection to MySQL server during query when dumping table – Pentium10 Mar 8 '12 at 8:54

If you determine that the mysqldump command is your bottleneck, you might try using MyDumper instead.

It doesn't look like you can stream the output directly to the target server but you can use rsync as many times as you like while the dump is happening and again when it's finished so that the final rsync is relatively quick.

You can either have MyDumper do the compression with -c, compress separately with gzip or use -e "ssh -C" in your rsync command to have SSH handle the compression.

  • Good point on using ssh's internal ability to gzip. However, in the modern times, it's advisable to use things like pbzip2, as it can take advantage of (sometimes significant) number of cores. The traditional bzip2 is rather slow, but pbzip2 on a modern box will amaze you. – Marcin Mar 8 '12 at 14:42
  • How to start this from the local machine. I need to start this command from the local machine, and it will transfer from remote to me. – Pentium10 Mar 8 '12 at 15:56
  • I hadn't come across pbzip2 before. This looks impressive. Thanks, Marcin. A quick bit of research also showed up pigz (a parallel drop-in for gzip) and this benchmark page. – Ladadadada Mar 8 '12 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.