2

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?

Peter Mortensen
  • 2,319
  • 5
  • 23
  • 24
ultraman
  • 129
  • 4
  • 10

3 Answers3

3

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.

Avery Payne
  • 14,326
  • 1
  • 48
  • 87
1

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.

Peter Mortensen
  • 2,319
  • 5
  • 23
  • 24
Manuel Faux
  • 497
  • 3
  • 13
1

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.

Peter Mortensen
  • 2,319
  • 5
  • 23
  • 24
Pete Jordan
  • 111
  • 1
  • 1
  • MySQL connections aren't encrypted unless you explicitly setup SSL and generally there's not often a good reason to have MySQL listening for the world. Unless you're utilising a VPN, it's far far safer to wrap the connection in an SSH tunnel. – Dan Carley Jul 27 '09 at 09:39
  • I wanted to set up the db using navicat on my local machine. So I need to copy the tables/data from the server to my local machine. Port 3306 on the server is blocked, so I was trying to set up an ssh tunnel (using Putty) and connect via that. – ultraman Jul 27 '09 at 10:49
  • You can have Navicat setup the tunnel itself. I wouldn't necessarily advise using it to copy data though. Pete is right on account of using `mysqldump`. – Dan Carley Jul 27 '09 at 14:04
  • We're hardened to having 3306 open to specific addresses - separate DB server from web server, and a replication loop that includes our dev server here in the office - but in this case, I agree that an ssh tunnel would be the best solution. I haven't had to use putty outside my phone for over a decade though, so can't advise on the incantation required! – Pete Jordan Jul 28 '09 at 06:48