SSH to remote mysql

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,

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

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?

Try [remote server IP] instead of 127.0.0.1 in Settings tab - works for me.

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?

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

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?

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.

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

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

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/

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

/* Connecting to SSHSERVER via MySQL (SSH tunnel), username MySQLUser, using password: Yes ... */
/* Attempt to create plink.exe process, waiting 4s for response ... */
/* SQL Error (2003): Can't connect to MySQL server on 'localhost' (10061) */
/* Closing plink.exe process #9540 ... */

1. If first connect, Connect to remote server via PuTTY accept hostkey otherwise move to step 2.
2. Configure HeidiSQL
Settings Tab:
Network Type:  MySQL (SSH Tunnel)
Hostname / IP: localhost (Or address to MySQL server)
Username:      MySQL username
Password:      MySQL Password
Port:          MySQL Port (default 3306)
SSH Tunnel Tab:
plink.exe loc: C:\Program Files (x86)\PuTTY\plink.exe (remove " (x86)" if not 64bit
SSH host+port: Remote host / Port (not MySQL host)
Username:      SSH Username
Password:      SSH Password
Local Port:    3307 (any unused port on local workstation)
3. Save and connect!

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.