Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Goal:

I am trying to copy a database onto my local box running window vista

Setup:

  • Using MySql 5.1 (mysql-essential-5.1win32) on 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 DB on vista running on 3306

I can locally login to the db 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)

Please recommend the fix.

Thanks

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer

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 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.

share|improve this answer
    
Not necessary - just tell PuTTy to bind the forward to local port 3307. –  Dan Carley Jul 27 '09 at 9:36
    
Have you ever tried? It does not work for me in Windows 7. –  Manuel Faux Jul 27 '09 at 9:50
    
Did you follow the correction by Avery? –  Dan Carley Jul 27 '09 at 10:12
    
Yes, you're right, its vice versa. –  Manuel Faux Jul 27 '09 at 10:46
1  
Putty ssh tunnel doesn't work on my 64bit Windows 7, I tried SSH in git bash (Windows Git) and it worked beautifully! Fix it please putty! code.google.com/p/msysgit –  Jeffrey Dec 23 '09 at 5:37

You don't want or need putty for remote access to the db.

Do you have permissions to connect to the remote database from your local machine?

If so, 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 admin access to the linux db, 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 Vista machine by whatever means you like - scp, ftp, 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.

share|improve this answer
    
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 9: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 6:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.