mysqldump from a remote server

Question

I need to copy mysql db from a remote server to a local server. The only way I can get access to that DB is by connecting to a remote computer (let's call it X) with ssh, and then from that computer I connect to mysql with mysql -h address -u username -p password. My limitation is that I cannot do the dump on the mysql server that runs the DB, and cannot do the dump to computer X (for administrative reason that cannot be change).

Is there a way to tell mysql to do the dump directly to my local server? (Perhaps using PIPE, though I'm not familiar with it). For what it's worth, my server runs on Ubuntu server, X is also running on linux.

I tried looking for a solution but couldn't find something to this exact scenario.

Appreciate any help.

Regards, Elad

Answer 1

You can use the STDIN/STDOUT redirection feature of SSH:

$ ssh user@remote "mysqldump -h host -u username -p dbname" > dbname.sql

With this command, mysqldump writes its dump to STDOUT, which is redirected to the STDOUT of your local shell. With > dbname.sql you write the stream of STDOUT to the local file.

You can even pipe the output via gzip (or any other compression tool) to reduce bandwidth if you like:

$ ssh user@remote "mysqldump -h host -u username -p dbname | gzip" | gunzip > dbname.sql

You can also use the pipe the other way round to restore a database from the backup:

$ gzip dbname.sql | ssh user@remote "gunzip | mysql -h host -u username -p dbname"

Or remotely restore without compression (not recommended):

$ dbname.sql > ssh user@remote "mysql -h host -u username -p dbname"