MySQL is often configured to be accessible from the local host only. Recently I moved all my websites from a CentOS box to a Debian one and needed to be able to connect to the MySQL server on the new box from the old one, but was not able to. This post looks at how to enable MySQL to be accessible from remote servers.
The MySQL configuration file is at /etc/mysql/my.cnf on Debian and similar distros and /etc/my.cnf on CentOS, Red Hat Enterprise Linux and similar. It contains a bind-address setting which is often set as follows by default:
bind-address = 127.0.0.1
This binds the MySQL deamon to 127.0.0.1 which is the local machine and means it will not be able to be accessed from other machines, whether or not the firewall and/or privilege grant options allow it.
To allow it to be accessible from any host, comment the line out as shown in the following example and then restart MySQL:
#bind-address = 127.0.0.1
Note that you could set the IP address to that of the server but it will mean all connections to the MySQL server must be via that IP address as it is not currently possible to bind MySQL to more than one IP address. It’s simply easier to comment the line out.
You’ll still need to enable access to the MySQL server through the firewall from the remote server(s) and add/modify the appropriate permissions to the privileges tables and then you’ll be good to go.