Last week one of my mate’s private vServer was hacked, hijacked and used for DoC attacks which prompted his provider (Strato) to disable the machine and recommended a reinstallation of the OS. That was not as bad as it sounds, as all data on the server were on backup and the OS was outdated to boot (OpenSuse 9!).

The vServer was reinstalled with Ubuntu 10.04 LTS and Plesk 10. After all data were back in place, all sites back up and running, all that was missing was the remote connection to the MySQL server. My mate uses Navicat (as do I, btw) to remotely manage his databases. So a new user was added to MySQL with remote access rights, the Navicat settings were altered accordingly and… a connection could not be established. D’Oh!

What went wrong? Well, I had a look at the issue. First off, I checked the [mysqld] settings in the MySQL configuration (aka my.cnf).

user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
bind-address    = 0.0.0.0
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking

That looked all good. The standard port was set to 3306, the bind-address was set to listen to all incoming traffic (although a security related “no-no” in my opinion), and no trace of the “skip-networking” directive that might have negated the bind-address settings.

Just to be on the safe side I restarted the mysql server (service restart mysql), but Navicat would still not connect.

Next stop was to have a look at the output generated by iptables -L and there I found this:

DROP       tcp  --  anywhere             anywhere            tcp dpt:mysql

Hmm. It seems that tcp connections to MySQL are dropped for some reason. Probably not a bad idea to keep that setting (and set my.cnf’s bind-address to 127.0.0.1) just to avoid new system intrusions via the MySQL route.

Of course that meant that the Navicat connection problem remained… that is, as far as a direct connection to the MySQL server is concerned. However, Navicat permits to connect to MySQL via SSH tunneling*. And using that option works like a treat minus the security risks of an open MySQL server.

* A bit confusing is that you have to manually set the MySQL Server address to “localhost” in Navicat’s main connection property window. I would have thought that the software would choose this setting automatically, if SSH tunneling is activated. Well, just a minor nuisance.