How to use the MySQL GUI client HeidiSQL with a remote DB server

Most IDEs nowadays work nicely with remote servers. But how about MySQL? phpMyAdmin is ok when you only have to use it once in a while, but a pain in the a… for everyday work.

So I wondered if my favorite MySQL Client HeidiSQL (which has a slightly stupid name, but this shouldn’t keep you off from trying it!) was able to help…

… and it was! You simply have to use the connection type “MySQL (SSH tunnel)” and set up the two tabs in the Connection Manager correctly:

1. Tab "Settings":
- Hostname: 127.0.0.1
- User: [mysql-user]
- Password: [mysql-pwd]
- Port: 3306 (default)

2. Tab "SSH tunnel"
- SSH Host: [server-name]
- Port: 22 (default)
- Username: [ssh-user]
- Password: [ssh-pwd]
- Local port: 3306

(thanks to Thomas Haller for the nice aggregation!)

It simply works like a charm (given a decent internet connection, of course) and gives you all the comfort a lazy Windows user wants (and needs :-).

Another tip from Thomas blog: if you experience problems with the connection, try to delete the path to the plink.exe. This will show the error messages which otherwise would be suppressed.

Helpful MySQL shell tricks

Although I’m Windows user most of the time, I have to take care of some projects on Linux boxes, so it’s good to have at least a little knowledge of what you’re doing there.

The pipe really is a helpful little tool, which allows to do a mysqldump and directly gzip the result:

mysqldump my_database | gzip > my_dump.sql.gz

This surely saves some time during late night deploys!

You can even do a mysqldump from one server to another:

mysqldump my_database | ssh user@12.34.567.89 "cat >> /tmp/my_dump.sql"