1

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.

Update

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

Improve this question

3 Answers 3

Reset to default
1

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.

Improve this answer
4
  • 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, 2012 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, 2012 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, 2012 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, 2012 at 8:07
1

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.

Improve this answer
1
  • One is that I get: Lost connection to MySQL server during query when dumping table
    – Pentium10
    Mar 8, 2012 at 8:54
1

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.

Improve this answer
3
  • 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, 2012 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, 2012 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, 2012 at 16:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .