Question

How to Allow Remote MySQL Database Connection

  • Posted January 22, 2013

I want to be able to connect to my Digital Ocean MySQL database from my MacBook. I have attempted to follow this tutorial: http://kahigiso.com/archives/351

Pretty much all it’s saying is to edit /etc/mysql/my.cnf and change this…

bind-address 127.0.0.1 … to this… bind-address <my local IP Address>

Unfortunately when I do this, and attempt to restart mysql with:

sudo /etc/init.d/mysql restart

I get an error:

mysql stop/waiting start: Job failed to start

MySql won’t restart until I change the IP back to 127.0.0.1

Any idea how to allow a remote connection? I obviously want to be able to connect to this same database from my app on the server, I just want remote database access from my computer as well.

Thanks!

Subscribe
Share

It’s not sucure way. You can connect to MySQL through ssh tunnel - it’s more secure. Or if it doesn’t matter you have to wtite bind-address = <vps-ip-address> or bind-address = 0.0.0.0

This comment has been deleted


Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

I’ll put my 2 cents in here and per Clifton’s follow-up. You can connect remotely with no adjustment to your MySQL settings if you use SSL connections with your MySQL client. I use Sequel Pro (on OSX) and you simply enter your SSH credentials (allows you to designate an SSH Key file) and you enter the MySQL credentials as usual. Not only easy, no change to settings, but you don’t have to open more ports, etc so it’s more secure to boot!.

I’ll put my 2 cents in here and per Clifton’s follow-up. You can connect remotely with no adjustment to your MySQL settings if you use SSL connections with your MySQL client. I use Sequel Pro (on OSX) and you simply enter your SSH credentials (allows you to designate an SSH Key file) and you enter the MySQL credentials as usual. Not only easy, no change to settings, but you don’t have to open more ports, etc so it’s more secure to boot!.

I’ll put my 2 cents in here and per Clifton’s follow-up. You can connect remotely with no adjustment to your MySQL settings if you use SSL connections with your MySQL client. I use Sequel Pro (on OSX) and you simply enter your SSH credentials (allows you to designate an SSH Key file) and you enter the MySQL credentials as usual. Not only easy, no change to settings, but you don’t have to open more ports, etc so it’s more secure to boot!.

Hello beautiful people, <br> <br>Well, I have a desktop application and I need to access my vps database to manipulate the data. <br> <br>What I already did: <br>Commented bind-address in /etc/mysql/my.cnf <br>Grant permissions to myuser@‘%’ <br>(allow all ips because anyone with any ip who are using the applications would be allowed to connect, I think need to be that way for a desktop application, isn’t?) <br>Restart mysql (service mysql restart) <br>Did netstat -plutn to see and mysql are listening in port 3306 <br> <br>So, I copy the IP to my string connection in the application, set the username, password and database name, and It can’t connect. <br> <br>If I use these values (to connect to localhost) my app works fine: <br> <br>// LOCALHOST (Wamp in my personal computer) <br>server = “localhost”; <br>database = “cSharpTeste”; <br>uid = “root”; <br>password = “”; <br> <br>But If I try those values to an online connection (the configuration I need) <br> <br>// ONLINE (My VPS Droplet) <br>server = “198.211.112.xxx:3306”; <br>database = “cSharpTeste”; <br>uid = “testUser”; <br>password = “testPassword”; <br> <br>I got an error: <br>“Unable to connect to nay of the specified MySQL hosts. (Error Number: 1042)” <br> <br>If I change ‘server = “198.211.112.xxx:3306”;’ to ‘server = “198.211.112.xxx”;’ (withou port number) It takes longer, but got the same error. <br> <br>I really need to put this to work, I need that to continue my job, any help will be appreciated. :D

Hello beautiful people, <br> <br>Well, I have a desktop application and I need to access my vps database to manipulate the data. <br> <br>What I already did: <br>Commented bind-address in /etc/mysql/my.cnf <br>Grant permissions to myuser@‘%’ <br>(allow all ips because anyone with any ip who are using the applications would be allowed to connect, I think need to be that way for a desktop application, isn’t?) <br>Restart mysql (service mysql restart) <br>Did netstat -plutn to see and mysql are listening in port 3306 <br> <br>So, I copy the IP to my string connection in the application, set the username, password and database name, and It can’t connect. <br> <br>If I use these values (to connect to localhost) my app works fine: <br> <br>// LOCALHOST (Wamp in my personal computer) <br>server = “localhost”; <br>database = “cSharpTeste”; <br>uid = “root”; <br>password = “”; <br> <br>But If I try those values to an online connection (the configuration I need) <br> <br>// ONLINE (My VPS Droplet) <br>server = “198.211.112.xxx:3306”; <br>database = “cSharpTeste”; <br>uid = “testUser”; <br>password = “testPassword”; <br> <br>I got an error: <br>“Unable to connect to nay of the specified MySQL hosts. (Error Number: 1042)” <br> <br>If I change ‘server = “198.211.112.xxx:3306”;’ to ‘server = “198.211.112.xxx”;’ (withou port number) It takes longer, but got the same error. <br> <br>I really need to put this to work, I need that to continue my job, any help will be appreciated. :D

raiyu, can you fix it? I’m having the same problem…

You should actually comment out that line entirely, then it will listen on all IPs and ports which you need because you will be connecting remotely to it over public IPv4. <br> <br>After that add a user to access your database such as: <br> <br>mysql> GRANT ALL ON database_name.* TO user@xx.xxx.xx.xx IDENTIFIED BY ‘your_password’; <br> <br>Replace xx.xx.xx.xx with your local IP address of your laptop/desktop or if it is dynamic you can add them either by: <br>‘192.168.0.%’ as a dynamic C-class or <br>‘%’ if you want to be able to connect from anywhere (this is less secure)

You should actually comment out that line entirely, then it will listen on all IPs and ports which you need because you will be connecting remotely to it over public IPv4. <br> <br>After that add a user to access your database such as: <br> <br>mysql> GRANT ALL ON database_name.* TO user@xx.xxx.xx.xx IDENTIFIED BY ‘your_password’; <br> <br>Replace xx.xx.xx.xx with your local IP address of your laptop/desktop or if it is dynamic you can add them either by: <br>‘192.168.0.%’ as a dynamic C-class or <br>‘%’ if you want to be able to connect from anywhere (this is less secure)

You should actually comment out that line entirely, then it will listen on all IPs and ports which you need because you will be connecting remotely to it over public IPv4. <br> <br>After that add a user to access your database such as: <br> <br>mysql> GRANT ALL ON database_name.* TO user@xx.xxx.xx.xx IDENTIFIED BY ‘your_password’; <br> <br>Replace xx.xx.xx.xx with your local IP address of your laptop/desktop or if it is dynamic you can add them either by: <br>‘192.168.0.%’ as a dynamic C-class or <br>‘%’ if you want to be able to connect from anywhere (this is less secure)

You should actually comment out that line entirely, then it will listen on all IPs and ports which you need because you will be connecting remotely to it over public IPv4. <br> <br>After that add a user to access your database such as: <br> <br>mysql> GRANT ALL ON database_name.* TO user@xx.xxx.xx.xx IDENTIFIED BY ‘your_password’; <br> <br>Replace xx.xx.xx.xx with your local IP address of your laptop/desktop or if it is dynamic you can add them either by: <br>‘192.168.0.%’ as a dynamic C-class or <br>‘%’ if you want to be able to connect from anywhere (this is less secure)