Connecting to MySQL from external sources + IPTables

By default, MySQL only allows local connections. This is due to security, and for the most part works just fine for most people. Ideally you can use things like PHPMyAdmin for things like this, or even command line.

Occasionally people need to connect from externally, either from a web front end, or some other PC, and this also opens things up for anyone else to connect and potentially exploit weak users/passwords, so this is how we do it.

First things you need to think about before opening up MySQL to the world is how secure are the passwords? Make sure they are all good, regardless of the hostname.
Secondly, block all access to hosts that do not need to connect, if you can whitelist the IP (if you are on static IP) you are connecting from, this is the ideal scenario. You can do this with IPTables

This is assuming that 116.251.196.255 is your whitelist IP (it’s not, change that)

iptables -A INPUT -p tcp -s 116.251.196.255 --dport 3306 -j ACCEPT
iptables -A INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

You will need to save those iptables rules , so they apply after a reboot.

Another alternative is to set up a VPN, and connect to it over private IPs (eg 10.0.0.36 or something), this works better if you are on a dynamic IP. OpenVPN is good for this type of thing.

A quick and easy VPN you can use is SSH – you can do that like this

ssh -fNg -L 3307:127.0.0.1:3306 myuser@remotehost.com

This redirects port 3307 on localhost to port 3306 on the remote host. Once that is done you can connect mysql to localhost

mysql -h 127.0.0.1 -P 3307 -u dbuser -p db

Once you have set up the security, you can then edit the my.cnf and change the line that says bind-address so its bound to 0.0.0.0. In the case of using a VPN you can bind it to the private IP (eg 10.0.0.42 )

MySQL users also need the host set, so remember to connect to a ‘user’@’%’ rather than ‘user’@’localhost’ , you may need to adapt a user or add a new one.

If you have any issues with this, just pop in a ticket and we can help out.