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

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 databas开发者_JAVA技巧e_name

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.

I always read the man pages for these types of things. If you look at the man pages for mysqldump you can see that the -C (thats a capital C) flag makes the mysqldump compress all data in transit only. this makes it stream compressed but arrive, as you will see it, uncompressed. You could dump the file to the local system then transfer a gzip of everything at once too.

from the man page:

   o   --compress, -C

       Compress all information sent between the client and the server if
       both support compression.