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
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:
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
1$ /etc/init.d/mysql restart
Comments
Hi Andy,
Unless CentOS is different this line:
$ /etc/init.d/mysql restart
...should be...
$ /etc/init.d/mysqld restart
Unless CentOS is different this line:
$ /etc/init.d/mysql restart
...should be...
$ /etc/init.d/mysqld restart
Im on Ubuntu and that works fine?
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.
@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?