mysqldump to remote mysql database using gzip

Question

I'm trying to transfer data from one MySQL server to another with gzip compression. What I have so far is:

mysqldump -u [user] -p[pwd] --no-create-db --no-create-info [db] [table] | gzip -c | mysql -h [host] -u [user] -p[pwd]

Of course this doesn't work because the compressed data is being imported into to the remote DB. How do I go about uncompressing the data on the remote server before import - or will this not work and do I need to use SSH?

Thanks

I have this working through multiple commands with SSH, but I'd like to get working with one line command using pipes, if possible — user3571061, Feb 26 '16 at 17:19
I'd just go with `mysql --compress` myself. It's already built into the client/server protocol. Reference: [MySQL command options - compress](http://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html#option_mysql_compress) — , Feb 27 '16 at 00:15

score 2 · Answer 1 · answered Feb 26 '16 at 17:34

You can - via multiple SSH pipes.

For example:

ssh user@host1 "mysqldump -u [user] -p[pwd] --no-create-db --no-create-info [db] [table] | gzip -c" | ssh user@host2 "gzip -c -d | mysql -h [host] -u [user] -p[pwd]"

Breaking this down a bit, you have two SSH commands, piped to each other:

The first one runs the mysqldump, then pipes the result to gzip, which in turns sends the result to STDOUT.
The second command takes STDIN and decompresses it, then pipes it to the mysql command.

When you combine the two commands, you can pass data between two hosts directly using SSH.

Example in practice below:

cwatson@zeus:~$ ssh tyr

cwatson@tyr:~$ echo stuff123tyr > testfile.txt
cwatson@tyr:~$ cat testfile.txt
stuff123tyr
cwatson@tyr:~$ logout

ssh tyr "cat ~/testfile.txt | gzip -c" | ssh thor "gzip -c -d > ~/testfile.txt"

cwatson@zeus:~$ ssh thor
Last login: Fri Feb 26 17:28:01 2016 from host217-44-218-9.range217-44.btcentralplus.com
cwatson@thor:~$ cat testfile.txt
stuff123tyr

@user3571061 no problem. Also take note of funkyhat's answer too - SSH does natively compress data, but you may get better transfer times if you compress before the data hits SSH by using something like `pigz` which is multi-threaded `gzip`. — Craig Watson, Feb 26 '16 at 17:41

score 2 · Answer 2 · edited Sep 18 '20 at 08:02

By using the parameter --compress, MySQL client can send compressed data to the remote server. So, the transfer command line may be modified to:

mysqldump -u [user] -p[pwd] --compress --no-create-db --no-create-info [db] [table] | \
 mysql --compress -h [host] -u [user] -p[pwd]

Note that data will be sent unencrypted over the network. If it concerns you, piping the dump through SSH is a better choice.

funkyhat · Answer 3 · 2016-03-03T12:05:57.133

Yes you will need to use some kind of remote execution in order to get the remote side to do the decompression:

mysqldump -u [user] -p[pwd] --no-create-db --no-create-info [db] [table] | gzip -c | ssh [host] "gunzip | mysql -h localhost -u [user] -p[pwd]"

However bear in mind that ssh does compression transparently by default - the g(un)zip steps are unnecessary.

mysqldump -u [user] -p[pwd] --no-create-db --no-create-info [db] [table] | ssh [host] mysql -h localhost -u [user] -p[pwd] should be sufficient

score 0 · Answer 4 · edited Feb 13 '17 at 13:34

Pre Requesite

create sender database if it does not exist
create receiver database if it does not exist
file will be saved in home directory if path not changed
make sure u r using correct credentials
To get receiver user type this on commandline(ctrl+alt+t) ---> whoami
To get receiver ip address type this on commandline(ctrl+alt+t) ---> ifconfig |grep "inet addr"|head -1

mysqldump -u{sender dbuser} -p{sender dbpassword} {sender database name} | gzip -cf | ssh {receiver user}@{receiver ip address} ' cat >/home/myfilename.gz | gzip -dc < myfilenamegz |mysql -u{receiver username} -p{receiver dbuser} {receiver dbname} '

remove { } from command and insert your values

mysqldump to remote mysql database using gzip

4 Answers4