Wednesday, May 21, 2008

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.)

12 comments:

oops-a-daisy said...

thanks a lot..... that did save me a bit of headache..

Anonymous said...

Thanks. This was quick and easy and fixed me up. The MySQL official docs are so dense I always appreciate someone saving me the headache.

Ndiritu said...

Very helpful post - its taken away hours of fruitless husstling.

Vicious129 said...

This has been very helpful. Thanks!!

Uli said...

Ditto - great work!

JMTyler said...

I'm sorry, I have to say, I did not find this particularly useful. Even though I did not know how to setup MySQL for network access before, the process is clearly very simple (it's only one small change in the config file) and I don't think it would have taken much more effort to include how to setup network users as well. Knowing how to allow network access is for all intents and purposes useless without network users. If this hadn't been the first Google result, I could have found any number of other articles on setting up MySQL for network access that include setting up users. Now I have to go back and use those articles anyway, and for that reason, this was frankly a waste of my time.

Thanks for the article, though. I come off as negative but you did write it well and made the process very simple. I just wish you'd included users.

Anonymous said...

Thank you! This was all that I needed to get over the last hurdle.

Don't worry about that other person - they need to figure out that it takes time to write this stuff and if people only were allowed to post when they had answered all of the questions out there then people wouldn't bother.

Thanks again!

Mr. eXoDia said...

Thanks a lot, at first I couldn't manage to do this, lots of thanks!

Manoj said...

Thnaks a lot

Nate said...
This comment has been removed by the author.
Nate said...

Thank you!

pavankumar vibhutimath said...

Thanks a lot i was struggling for past 2 days.. finally resolved ...

Thanks a lot