Allowing network connections to MySQL in OS X Leopard Server
For the past couple of days I’ve been trying to set up one of our XServe running Leopard Server at work for web hosting. I was able to configure the Web, AFP, and SMB properly. The problem I was having was configuring MySQL. It was easy to enable but trying to connect to it from other computers in the same domain was the problem. There’s a checkbox with “Allow network connections” in the Server Admin section of MySQL. So I checked it, save, and restarted it. I still wasn’t able to connect. I checked for firewall settings. I pinged the host and got a response. I couldn’t figure out what was wrong. So I googled it.
The common answer I found was to edit the /etc/mysql/my.cnf file. Well it’s not there but I did find a /etc/my.cnf file. So I tried editing that file and restarting. It didn’t work.
Then I remembered that the php.ini file that Leopard Server is using isn’t in the default location /etc/php.ini, but it ran the /private/etc/php.ini. So I looked in there and found another my.cnf file. I edited the file by adding # in front of skip-networking entry. I restarted it and it finally worked.
So here’s what you need to do:
- Go into Server Admin and check the Allow network connections and save
- Comment out the skip-networking entry from /private/etc/my.cnf
- Add the IP address of the computer you will be accessing MySQL remotely
- Give it the privileges it needs
- Restart MySQL
That should allow you to connect to MySQL running on Leopard Server remotely.
Win – I just set up a new Xserve a couple of days ago and I experienced similar issues. I didn’t need to edit the my.cnf file as you did, but I did see mention of that through my troubleshooting search. It seems Apple broke it’s own rules about where the config files are supposed to live, huh?At any rate, our WordPress blog and other databases are working like a champ now. It seemed more like a permissions issue to me, but I am not sure exactly what I did to get it working, it just seemed to fix itself!It’s comforting somehow to see others facing the same problems. I work as an Apple Consultant and am a member of the ACN, so it’s really helpful to find these little tidbits of troubleshooting info from time to time.
Thanks for the comment Avi. I agree, they broke the rules of the config file location. I wish they had better documentation about it. What was odd to me was even if I checked the “Allow network connections” setting, it didn’t change the config file to reflect the new option. I had to manually edit the cnf file. I even posted in Apple’s discussions board and no one was able to help me out. Glad we both figured out our little dilemma.
Just FYI, there is no difference between /private/etc and /etc. /etc is a symlink to /private/etc.
thanks for the info evgen but i kept editing files in the /etc directory and was not making any difference until i went in the /private/etc directory. which led me to believe that they are 2 different places.
Hi! I was surfing and found your blog post… nice! I love your blog. 🙂 Cheers! Sandra. R.
Hey guys,I picked up 2 Xserve G5’s running Server 10.5 and wanted to use 1 as a webserver and 1 as a dedicated mysql. I am installing joomla 1.6 but i am having a heck of a problem connecting to the machine i am using as a dedicated mysql server during joomla installation. Has anyone ran into any issues doing this? Thanks
I understand all of this except, how do you add the IP of the computer you’ll be using remotely?
hi AlanB,once you remove or comment out the skip-networking line in the my.cnf file, mysql server will start to accept connections outside of the local network – as long as there is no firewall in the way. the rest is done within mysql where you create a user and host (IP of the remote computer) and give it the permissions it needs. i hope this helps.i have changed this method of remote connection to my mysql server and started to use SSH. it’s more secure.