SSH to remote mysql
| User, date | Message |
|---|---|
|
Written by maniacal
3 months ago Category: Creating a connection 5 posts since Tue, 12 Feb 13 |
Hi, Can somebody help me understand how to connect to a mysql database on a remote server through SSH? I think I may just be slow... I can see that from the dialogue, I need to specify a link with plink.exe. Is this necessary? I can SSH in through a terminal, can't I simply specify the SSH username and password plus the mysql details, and the server IP? This is how I've done it on other software such as navicat. Incidentally, I'm using Heidi on a VM through Parallels on a mac. I don't think this should make any differentce though. Thanks, |
|
Written by kalvaro
3 months ago 441 posts since Thu, 29 Nov 07 |
Given that it asks for the path to "plink.exe", I guess it is necessary. I suppose that Anse preferred to use his time writing MySQL stuff for HeidiSQL rather than cloning a SSH utility. You can grab it from http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html Not sure about what you mean with I can SSH in through a terminal but computer programs are not people 
|
|
Written by maniacal
3 months ago 5 posts since Tue, 12 Feb 13 |
Thanks for your comments. So I've downloaded plink, and set the path to it on the heidi connection settings dialogue. I am however failing to connect with this error: SQL Error (2003) in statment #0. Can't connect to MySQL server on 'localhost' (10061) This is the same error if I delieberately break the SSH details, so I'm fairly sure I've just not got the right settings. SSH host, user and password work in shell, but just not in Heidi, so I'm guessing I haven't set up my dialogues correctly. These are my settings: Settings tab Network type: MySQL (SSH tunnel) Hostname / IP: 127.0.0.1 User: [mysql user] Pwd: [mysql password] Port 3306 SSH tunnel tab plin.exe location: C:\Program Files (x86)\HeidiSQL\plink.exe SSH host + port [remote server IP] 22 Username: root Password: [ssh password] Private key file: [blank] Local port: 3307 The bits I'm not sure about are the three port settings, and the Private key file. Can anybody help me understand how this works? |
|
Written by jfalch
3 months ago 222 posts since Sat, 17 Oct 09 |
Try [remote server IP] instead of 127.0.0.1 in Settings tab - works for me. |
|
Written by maniacal
3 months ago 5 posts since Tue, 12 Feb 13 |
Still no banana. Not sure what else to try. I know other people in my office have tried but nobody has successfully connected to mysql through SSH in Heidi. I'm still not sure what 'Local port' is, and is there a way I can confirm that 22 is the right ssh host port? |
|
Written by jfalch
3 months ago 222 posts since Sat, 17 Oct 09 |
local port is the "local" end of the SSH tunnel (auto established by plink from heidisql), ie the part of the tunnel on your computer. the other end of the tunnel (the remote port) is on the computer where the mysql server runs (ie the one with [remote server IP]). it is customary to set local port to 3307, but you can set it to any other unused port on your computer. to confirm that SSH works, you can use an ssh client, eg putty, and try to connent to [remote server IP] with it. do not be put off by it´s rather complex looking configuration screen - simply enter [remote server IP] in the "host name" field on the upper right, click on "SSH" below it, and click "open" on the bottom, you should be asked for user and password in the following console screen. if simple ssh access works, try to set up a tunnel manually using plink, with a command line of plink.exe -ssh root@[remote server IP] -L 3307:[remote server IP]:3306 |
|
Written by maniacal
3 months ago 5 posts since Tue, 12 Feb 13 |
Thanks again for the information. I have successfully connected using putty, but am stuck with your command line suggestion. Probably a stupid question, but where do I run this from? I tried from a windows command terminal, but it didn't recognise plink.exe as an internal or external command. There doesn't seem any point in doing it in putty as I'm already connected to SSH (is there?), and plink.exe itelf doesn't actually open anything... Sorry still confused! Incidentally, I did get a warning when connecting with putty: "The server's host key is not cached in the registry. You have no guarantee that the servers is the computer you think it is.... If you trust this host, hit Yes toadd the key to PuttY's cache and carry on connecting... I did this, and was able to connect. Might this be blocking plink.exe from heidi? |
|
Written by jfalch
3 months ago 222 posts since Sat, 17 Oct 09 |
Ok, this means SSH connection to the mysql server´s host works in principle, using this username and password. Now, to use plink for the tunnel test, you must specify it´s .exe (in a command terminal) with the path it is stored in, i.e. (if this is it´s location, as per your above post): "C:\Program Files (x86)\HeidiSQL\plink.exe" -ssh root@[remote server IP] -L 3307:[remote server IP]:3306 if it should work, you should get one or more messages from the server, and then its prompt, which you can leave with logout to get back to window cmd line. The "server´s host key is not cached" message is harmless, and should not occur again once you have answered "yes" to it. |
|
Written by jfalch
3 months ago 222 posts since Sat, 17 Oct 09 |
"server´s host key is not cached": plink (and putty) will always ask this queston when connecting to a new host (unknown to the program). if it is answered with "yes", the server´s ssh public key (fingerprint) will be cached by plink, and the key received upon connection will thenn be compared to this stored value on each subsequent connect. this is used to prevent DNS spoofing or other MITM attacks - ie it makes sure that the server you are trying to connect to always remains the same; will put up a warning if the received key should ever change. |
|
Written by maniacal
3 months ago 5 posts since Tue, 12 Feb 13 |
Once again, thanks for your continued support. I can successfully log in via plink from a windows command terminal using "C:\Program Files (x86)\HeidiSQL\plink.exe" -ssh root@[remote server IP] -L 3307:[remote server IP]:3306 However I still could not connect with heidi to mysql. However, the error message changed to SQL Error (2013 in statement 0. Lost connection to Mysql server... I switched Hostname / IP on settings to tab to localhost rather than the remote IP, and it fixed it! So all is well now. One thing to note, that may be part of the problem is that I believe changing the settings and hitting Open doesn't (I think use the updated settings until you hit save). So at some point I may already have got the right combination, but didn't realise it. These are my final settings for reference: Settings tab Network type: MySQL (SSH tunnel) Hostname / IP: localhost User: [mysql user] Pwd: [mysql password] Port 3306 SSH tunnel tab plin.exe location: C:\Program Files (x86)\HeidiSQL\plink.exe SSH host + port [remote server IP] 22 Username: root Password: [ssh password] Private key file: [blank] Local port: 3307 |
|
Written by subra010
3 months ago 3 posts since Wed, 27 Feb 13 |
I am new to HeideSql..am loving it now but also had a frustrating time connecting to the MySql server on an ec2 instance. I was really helped by details in this link that provides details on how to connect...thought I would put to help others who are stuck at step 0! http://vtvlab.wordpress.com/2011/12/13/connect-to-mysql-server-from-heidisql-with-ssh/ |
|
Written by speroshiropetto
2 months ago 1 posts since Sat, 16 Mar 13 |
I'll add to make it clearer for other wonders of the internet that if you have never connected to your remote host via SSH, you will want to do this first prior to configuring HeidiSQL because the RSA/host key fingerprint has not been cached yet. More information can be found in the PuTTY help file http://the.earth.li/~sgtatham/putty/0.62/htmldoc/Chapter2.html#gs-hostkey The issue lies with plink not being able to prompt the user or program for additional information in this case saying yes or no to the unverified key. I believe there are some issue reported over at http://code.google.com/p/heidisql/ for this. Error without hostkey in cache
|
|
Written by chrisls121
1 month ago 1 posts since Sun, 21 Apr 13 |
Another thing that can trip you up is if you are using a private key protected by a passphrase, be sure to enter the passphrase in the password field under the SSH Tunnel Tab. |
|
Please login to leave a reply, or register at first. |