Configuring MySQL for Network access

The typical default install of MySQL server only permits connections from localhost (127.0.0.1); this is presumably for reasons of security. While this is certainly secure, in some cases it is undesirable. This post explains how to permit network access to a MySQL server from remote clients.

Locate the my.cnf file, which is the master configuration file for MySQL server. (On a Ubuntu system this file may be located in /etc/mysql.)

Open this file in your favorite editor and look for the following entry:

bind-address = 127.0.0.1

This limits the MySQL server to listening to connections on the localhost address, as explained earlier.

To instead make the MySQL server listen on all interfaces, edit this entry to the following:

bind-address = 0.0.0.0

Save the file, then restart the MySQL server:

sudo /etc/init.d/mysql restart

Your MySQL server should now be network accessible. To verify that it's listening on all interfaces, issue the following command:

netstat -anp | grep 3306

If you see the following, then your configuration is complete:

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN     -

You'll want to be certain your database users are permitted to connect via the network. This I'll leave to you to work out, though I will recommend the MySQL Admin tool.

(Conversely this tool can also be used to enable networking of MySQL, though this setting is somewhat buried within the myriad of options, and is more easily accomplished via the method described above.)