what's the difference between -C and gzipping a mysqldump?

Question

I want to do a mysqldump directly to my remotehost. I've seen suggestions to use the -c switch or use gzip to compress the data on the fly (and not in a file). What's the difference between the two? How do I know if both machines support the -C switch? How would I do a gzip on the fly? I am using linux on both machines.

mysqldump -C -u root -p database_name | mysql -h other-host.com database_name

Answer 1

The -C option uses compression that may be present in the MySQL client-server protocol. Gzip'ing would use the gzip utility in a pipeline. I'm pretty sure that the latter would not do any good since the compression and uncompression would occur on the same machine in this case. If the machine that you are dumping from is local, then the -C option is probably just wasting CPU cycles - it compresses the protocol messages between mysqldump and the mysqld daemon.

The only command pipeline that might make sense here is something like:

mysqldump -u root -p database_name | mysql -C -h other-host -Ddatabase_name -B

The output of mysqldump is going to the pipeline which the mysql command-line client will read. The -C option tells mysql to compress the messages that it is sending to other-host. The -B option disables buffering and interactive behavior in the mysql client which might speed things up a little more.

It would probably be faster to do something like:

mysqldump -u root -p database_name | gzip > dump.gz
scp dump.gz user@other-host:/tmp
ssh user@other-host "gunzip /tmp/dump.gz | mysql -Ddatabase_name -B; rm /tmp/dump.gz"

Provided that you have SSH running on the other machine anyway.