Spending quite of hours after struggling with this. I finally found the correct method to connect to remote mysql hosts using an excellent GUI for mysql. I use HeidiSQL all the time for my local development and also the database maintenance on remote servers.
Lately have been working with Amazon AWS. Their EC2 instances are totally awesome. Apart from little configuration problems I am lovin it.
So here are the quick steps to be followed.
- Use puttygen to convert your .pem file which you get from amazon server. It will provide you with a .ppk file. This ppk file is used by HeidiSQL for connecting to remote servers using SSH Tunnel.
- Now from Session Manager in HeidiSQL select MySQL (SSH Tunnel) for the network type.
- Always use Hostname/IP localhost or 127.0.0.1 irrespective of the name/IP of the remote host. We will come to this in the next step.
- Fill in your MySQL username and passwords.
Now in the SSH Tunnel Tab
- Browse for the location of the plink.
- Hostname/IP of your remote host this time.
- Port 22 will work in almost all cases, of course, if you have not tweaked (or messed?) the configuration in your remote host.
- Here in the username field fill in your SSH username. Mostly in case of EC2 ubuntu instances its “ubuntu” itself by default.
- You can safely leave the password field blank as in next step we will be using the ppk file (remember we generated it a while ago!) for authentication.
- TaDa! Done.
There might be some errors regarding connectivity and/or your configuration of the MySQL Server.
Most of the connectivity issues can be easily resolved by extending the plink.exe timeout. You can increse it easily from HeidiSQL Sesison Manager Settings right there on the SSH Tunnel Tab. Default value is 4 seconds. I found 10 seconds to be a fair deal as my connection gets crazy sometimes.
Another problem which may occur is the nasty error
reading initial communication packet, system error: 0
This error occurs mostly when MySQL drops the communication packet silently. There is a simple workaround for this.
You would need to login to your server via SSH and edit following file:
Add following line:
bind_address = 0.0.0.0
This ensures that mysql responds to all the IPs.