Cannot SSH tunnel with PuTTY (Windows Vista) to connect to MySQL on a Linux box

Goal:

I am trying to copy a database onto my local box running Windows Vista.

Setup:

• Using MySQL 5.1 (mysql-essential-5.1win32) on Windows Vista
• On Linux box: MySQL Ver 14.12 Distrib 5.0.51a,
• I set up SSH tunneling on PuTTY this way:
  • Source port 3306, Destination:localhost:3307
  • I am using 3307, because I have my own database on Windows Vista running on 3306

I can locally log in to the database on my Linux box however, when I tried to login from MySQL administrator using

• Server Host: 127.0.0.1 Port 3307
• username:someUser
• password:somepassword

it failed with this message: MySQL Error Number 2003 Can't connect to MySQL server on 127,0.0.1 (10061)

How can I fix this?

Solution 1:

It sounds like you've got it "backwards". The destination is not your loopback, it's the remote machine on the other network. That, or you're using Remote instead of Local for the tunnel.

Try flipping the IP addresses around, example:

putty.exe -L 3307:mysql.yourdomain.com:3306 

Where mysql.yourdomain.com is the name of the host that has the MySQL service you're trying to contact. When putty pops up the connection dialog, put in the address, connect, and log in. Then connect to localhost:3307 using the Administrator tool.

Solution 2:

I've just tried with Windows 7 and it does not work for me either. The port will be forwarded, but not remapped. It will be still localhost:3306, which collides with the local server in your case. The PuTTY documentation also says that this would be the right way, but maybe the new security model of Windows Vista blocks the remapping of the ports.

I'd suggest, if you are using port 3306 locally, not to tunnel to localhost (which is normally mapped to 127.0.0.1), but to tunnel to another IP address in your loopback range (maybe 127.0.0.2). If your local SQL server is configured well, it only listens to 127.0.0.1:3306, your remote tunnelled SQL server can listen on 127.0.0.2:3306, so a remapping of the port is not necessary.

Solution 3:

You don't want or need PuTTY for remote access to the database.

If you have permissions to connect to the remote database from your local machine, you can just use mysqldump --host=remotemachine --user=remotesqluser --password=remotepassword --databases databasename>database.dump on Vista, then load it onto your local box with mysql --port=3307 --host=127.0.0.1 --user=localsqluser --password=localpassword <database.dump - if you were running Linux locally I'd just pipe one program into the other, but I don't know if Vista's cmd.exe supports that.

If you can't connect remotely, but have administrator access to the Linux database, you can give yourself permissions (at the remote MySQL command line) with grant select on databasename.* to 'remotesqluser'@'yourexternalIPaddress' identified by 'remotepassword'; (I think select is all you need). Once you have access, you can copy the database as above. Use revoke to lose the privileges when you've finished.

If you can't do any of this, you will need PuTTY: Connect to the Linux box and run the mysqldump command there (with --host=127.0.0.1). Copy the dump file to your Windows Vista machine by whatever means you like - SCP, FTP, and HTTP if you have a handy web server - and then you can load the database locally as above. Don't forget to delete the dump file on the Linux box after you're done.

NOTE if your mysqldump options aren't set up sensibly, you'll probably want to add --extended-insert to the command to speed things up (allows usage of multi-row inserts), and if there are any stored procedures in the database to transfer, --routines as well.