mysqldump compression

Question

I am trying to understand how mysqldump works:

if I execute mysqldump on my pc and connect to a remote server:

mysqldump -u mark -h 34.32.23.23 -pxxx  --quick | gzip > dump.sql.gz

will the server compress it and send it over to me as gzip or will my computer receive all the data first and then compress it?

Because I have a very large remote db to export, and I would like to know the fastest way to do it over a network!

Answer 1

You should make use of ssh + scp,
because the dump on localhost is faster,
and you only need to scp over the gzip (lesser network overhead)

likely you can do this

ssh [email protected] "mysqldump -u mark -h localhost -pxxx --quick | gzip > /tmp/dump.sql.gz"

scp [email protected]:/tmp/dump.sql.gz .

(optional directory of /tmp, should be change to whatever directory you comfortable with)

Answer 2

Have you tried the --compress parameter?

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_compress

Answer 3

This is how I do it:

Do a partial export using SELECT INTO OUTFILE and create the files on the same server.

If your table contains 10 million rows. Do a partial export of 1 million rows at a time, each time in a separate file.

Once the 1st file is ready you can compress and transfer it. In the meantime MySQL can continue exporting data to the next file.

On the other server you can start loading the file into the new database.

BTW, lot of this can be scripted.