Andy Jarrett: Getting remote access to MySQL

Loading Twitter

Getting remote access to MySQL

After setting up my server yesterday I had issue with getting remote access to MySQL it was like it wasn't even running. After some digging around and annoying a linux user (always make sure you know a *nix admin guy, and buy him a scotch or two to say thanks) I found out is was running and it just wasn't bound to the machine's IP address but instead to localhost. For security reasons remote access disabled, but sometimes you need it for one reason or another.

Login via SSH
Edit the my.cnf. On Ubuntu you can do this

view plain copy to clipboard print about
$ sudo nano /etc/my.cnf 1$ sudo nano /etc/my.cnf
Once file open look for bind-address=127.0.0.1 and change it for your host machine IP address. So if you host machine is 10.0.1.1 your configuration file would look like:

view plain copy to clipboard print about
1# Instead of skip-networking the default is now to listen only on
2# localhost which is more compatible and is not less secure.
3bind-address = 10.0.1.1
Save, close and restart your mysql service to take change in effect

view plain copy to clipboard print about
$ /etc/init.d/mysql restart 1$ /etc/init.d/mysql restart

Comments (4) | Print | Send | 1645 Views

If you like what you see on the website and/or this post has helped you out in some way please consider donating to help keep me in beer vodka. The donations are made through Paypal, which accepts almost any credit card or eCheck.

(Comment Moderation is enabled. Your comment will not appear until approved.)

[Add Comment] [Subscribe to Comments]

Posted By James Buckingham @ 4/21/09 7:03 AM

Hi Andy,

Unless CentOS is different this line:
$ /etc/init.d/mysql restart

...should be...

$ /etc/init.d/mysqld restart

Posted By Andy Jarrett @ 4/21/09 7:11 AM

Im on Ubuntu and that works fine?

Posted By Aaron West @ 4/22/09 7:49 PM

Andy, what if there is no "bind-address" in my.cnf? I have a Linux VPS that I have never tried to connect to remotely (for MySQL), but I just tried it in Sequel Pro and got a "host denied connection" error. It might be that my password was wrong but I checked the my.cnf file and it doesn't have a bind-address entry. Can you just add it ? This is for MySQL 4.x.

Posted By Andy Jarrett @ 4/23/09 9:37 AM

@Aaron you should be able to just add it. If not you could start it up from the command line adding the bind address. I think it would be something like mysqld --bind-address="{ip address here}" just to test?

[Add Comment]