Friday, 20 May 2011

Connect remotely to MySQL

I find it very useful to be able to remotely connect to my MySQL servers. You don’t really need this as you could get away with the command line through SSH but it’s much easier and faster to access your database using Workbench from your laptop. You could also need this if you have applications that run on different machines and need access to that server.

There are 3 things you have to do in order to remotely connect. Create a user that is allowed to connect remotely, make MySQL accept connections from other IPs and make sure your firewall doesn’t block your IP, port, etc.

Create a MySQL user allowed to connect remotely
mysql> GRANT ALL PRIVILEGES ON *.* TO 'sys'@'%' IDENTIFIED BY 'superSecretToBeChanged'  WITH GRANT OPTION;
User “sys” will now have access to all databases from all over the world. Make sure you don’t have it’s password hardcoded anywhere. This may be too much for your security.
mysql> GRANT ALL PRIVILEGES ON myDatabase.* TO 'sys'@'%' IDENTIFIED BY 'superSecretToBeChanged'  WITH GRANT OPTION;
Now “sys” will have access only to “myDatabase” from all over the world. You could change the “%” with your IP and that would be much safer but when your laptop acquires a new IP your connection won’t work.
mysql> GRANT ALL PRIVILEGES ON myDatabase.* TO 'sys'@'192.168.123.456' IDENTIFIED BY 'superSecretToBeChanged'  WITH GRANT OPTION;
Please replace the IP with yours.
You could also grant less privileges to improve your security.
mysql> FLUSH PRIVILEGES;

If you have problems connecting it would be a good idea to start with the first option (granting all privileges to all databases from all IPs) and then improve the security. If this is not a production server that you expect to be attacked the first option would probably be safe enough anyway. Remember not to have the password hardcoded.

Allow MySQL connections through Firewall
If your connection attempt fails with “Can’t connect to MySQL server on ‘your server IP’ (10060)”, you probably have your port / IP blocked by the Firewall
iptables -I RH-Firewall-1-INPUT -m tcp -p tcp --dport 3306 -j ACCEPT
service iptables save
Please note that you should use the -I option (insert) not -A (append).
If you edit your “/etc/sysconfig/iptables” file you would use -A, of course. But there is no need to do that when you have the clean way above.

This iptables rule is just for Red Hat / CentOS. If you have a different Linux distribution you would have to use a slightly different rule. On some distributions you could get away even faster. Ubuntu would settle with: sudo ufw allow mysql :)

If you still get Error 10060 (and if this is not yet a production server) you could stop your Firewall and see if you can connect or at least get a different error.
service iptables stop
If you do the restart your Firewall, service iptables restart (or start instead of restart because you just stopped it earlier) and check your settings again.
Check that you have the line “iptables -A RH-Firewall-1-INPUT -m tcp -p tcp --dport 3306 -j ACCEPT” in your “/etc/sysconfig/iptables” file and check that MySQL is actually running on port 3306.

Accept MySQL remote connections
If your connection attempt fails with “Can’t connect to MySQL server on ‘your server IP’ (10061)”, MySQL is probably not accepting remote connections.
Edit /etc/my.cnf and comment these two lines, if they exist. If they don’t exist, don’t worry, it’s even better :)
#skip-networking
#bind-address=127.0.0.1
Restart MySQL so the changes can take effect.
service mysqld restart

After you can connect remotely you can decide if you want to have some more complex iptables rules, grant less privileges, etc.

No comments:

Post a Comment