Downloading a database from remote server using ssh? 1

[ChrisMacPherson]

Hi,

I need to copy a database from a remote server where I have ssh access to my local computer. Can anyone tell me how to do this?

I was thinking something like this:

- login to ssh
$mysql_admin database_name | local_pc (very simplified)

Any help would be appreciated.

Thanks.

Chris MacPherson

Chris MacPherson

 

[DonQuichote]

I would use mysqldump. However, using SSH, you can set up a tunnel and connect to the remote database directly (if networking is allowed at all). If you use Putty (search and download if you do not), you can set up a tunnel from local port (for instance) 3307 to remote 127.0.0.1:3306. If you then connect to port 3307 on your own machine, you can talk to the database and use whichever (graphical) database front-end you are familiar with.

Be careful! You then have a *real* connection with write and administer possibilities. You are connecting from 127.0.0.1 in the viewpoint of the server.

Oh, and if you set up the tunnel, do not forget to check "Compressed" if the connection is not over the local network.

Good luck!


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[ChrisMacPherson]

Sorry, I meant mysql_dump, not mysql_admin in my first post! that wouldn't have done much eh

Yes I am working on a mac, do I need to use putty or can I just do all this from shell?

if I set up a tunnel between my local machine and a certain port on the remote server:

- Can I transfer databases from the remote mysql server to a local one?

maybe like mysql_dump remote_db | mysql -h 127.0.0.1:3307 local_db


Would the 127.0.0.1:3307 here point to my local server?





Chris MacPherson

 

[DonQuichote]

You do not need Putty for this, but if you do not have any experience it is more convenient to use a graphical application. The same can be done with the "ssh" command, but I never tried it.

So, if you have set up a tunnel, your can access the database directly through the forwarded port. Assuming that both the remote server and your local PC have MySQL running on port 3306 (default and standard), you will have to select a different port to represent the remote dabatase on your own PC. That is why I would set up a tunnel connecting (local) 3307 to (remote) 3306.

BE CAREFUL!

always give the host as something else than "localhost" when using tunnels. "localhost" does NOT mean 127.0.0.1 in MySQL, but makes MySQL use a socket file (on *nix). What's even worse, if you explicitly state an IP port with "localhost", that port is ignored without even a warning. So "mysql -h localhost -P 3307" would connect to your local database through a socket, while "mysql -h 127.0.0.1 -P 3307" would connect to the remote database. Quite a difference!

mysql_dump remote_db | mysql -h 127.0.0.1:3307  local_db

The above command would exactly do the opposite: dump a database on your local machine (which is called "remote_db") and send it to the remote server to run on a database called "local_db" there.

Please take a good look at the man pages for both mysql_dump and mysql. I do not know if ports can be given direcly in the IP address. there is a separate command-line option to do so (--port, I think also -P, as -p is for a password).

Also, if you did not specify compression for the tunnel, you may do this for the mysql_dump connection (you can do that for the local mysql command as well, but there is little sense in that).

If you can use the MySQL command-line on the remote server, test the whole thing with a small test database first.

Do you want the whole database? I copied a few live databases for development use, but I usually take a good lot of data out first (in the copying process, of course, not removing them from the live system). If you have sufficient knowledge of the remote database, you can copy all "definition" tables completely and only a percentage of the "live data". It is possible to specify separate tables or even queries in mysql_dump.

One final note: if you can do the whole import of data in a transaction, it will greatly speed the import process up.

Good luck!

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[ChrisMacPherson]

Thanks for your reply DonQuichote, I'm going to look into all this today and see if I can get it to work. I have a test server available to use so I will attempt anything on that first!

It looks like it's not going to be straight forward to get putty working so it might be a little while before I reply again. I'll let you know how it goes though.

Thanks again

Chris MacPherson

 

[DonQuichote]

OK, if you have not done a port forwarding in Putty yet, let me give you a hint:
When opening a connection, click Connection -> SSH -> Tunnels in the options tree. In the field "Source port", type an unused port number on your own computer (for instance, 3307. All ports below 1000 are only available to programs running as root).
In the field "Destination", type the address or IP of the remote computer and its port relative to the destination of the connection. So if you connect to mysql.example.com and the MySQL server is on that same machine, you type 127.0.0.1:3306 in the Destination field. Do not forget to click the "Add" button.

If you are connecting over the slow big internet, take one step back (Connection -> SSH) and check "Enable compression".

Now connect, and you have set up an SSH tunnel! You can now connect to 127.0.0.1:3307 and see the remote database.

Hope this helps.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)