Geek Answers Handbook

Mysqldump from a remote server

I need to copy mysql db from a remote server to a local server. The only way to access this database is to connect to the remote computer (allow X) with ssh, and then from this computer I connect to mysql using mysql -h address -u username -p password . My limitation is that I cannot dump on the mysql server that runs the database and cannot dump on computer X (for an administrative reason that cannot be changed).

Is there any way to tell mysql to dump directly on my local server? (Perhaps using PIPE, although I am not familiar with it). For what it's worth, my server runs on an Ubuntu server, X also runs on Linux.

I tried to find a solution, but could not find anything in this exact scenario.

Appreciate any help.

Regards, Elad

+7
source share
2 answers

You can use the STDIN / STDOUT redirection function in 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. Using > dbname.sql you write the STDOUT stream to a local file.

You can even direct output through gzip (or any other compression tool) to reduce bandwidth if you want:

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

You can also use the channel in reverse to restore the database from a backup:

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

Or remotely recover without compression (not recommended):

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

You can use the SSH tunneling function:

 ssh server-x -L 12345:remote-db-server:3306

This will tell the SSH client to listen for TCP connections to port 12345 on the local computer (the one you are connecting to) and transfer them as connections from server-x to port 3306 (the default MySQL port) to remote-db-server .

Then you can simply run mysqldump on your local computer and tell it to connect to the localhost port 12345. It will actually connect to the remote database server, and from this point of view it will look like the connection comes from server-x .

+3
source

More articles:


All Articles