How to Allow Remote MySQL Database Connection

Question

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!

bruns.nicholas · Answer

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

bruns.nicholas · Answer

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

bruns.nicholas · Answer

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

jacksonbenete · Answer

Hello beautiful people,

Well, I have a desktop application and I need to access my vps database to manipulate the data.

What I already did:
Commented bind-address in /etc/mysql/my.cnf
Grant permissions to myuser@‘%’
(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?)
Restart mysql (service mysql restart)
Did netstat -plutn to see and mysql are listening in port 3306

So, I copy the IP to my string connection in the application, set the username, password and database name, and It can’t connect.

If I use these values (to connect to localhost) my app works fine:

// LOCALHOST (Wamp in my personal computer)
server = “localhost”;
database = “cSharpTeste”;
uid = “root”;
password = “”;

But If I try those values to an online connection (the configuration I need)

// ONLINE (My VPS Droplet)
server = “198.211.112.xxx:3306 ”;
database = “cSharpTeste”;
uid = “testUser”;
password = “testPassword”;

I got an error:
“Unable to connect to nay of the specified MySQL hosts. (Error Number: 1042)”

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.

I really need to put this to work, I need that to continue my job, any help will be appreciated. :D

jacksonbenete · Answer

Hello beautiful people,

Well, I have a desktop application and I need to access my vps database to manipulate the data.

What I already did:
Commented bind-address in /etc/mysql/my.cnf
Grant permissions to myuser@‘%’
(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?)
Restart mysql (service mysql restart)
Did netstat -plutn to see and mysql are listening in port 3306

So, I copy the IP to my string connection in the application, set the username, password and database name, and It can’t connect.

If I use these values (to connect to localhost) my app works fine:

// LOCALHOST (Wamp in my personal computer)
server = “localhost”;
database = “cSharpTeste”;
uid = “root”;
password = “”;

But If I try those values to an online connection (the configuration I need)

// ONLINE (My VPS Droplet)
server = “198.211.112.xxx:3306 ”;
database = “cSharpTeste”;
uid = “testUser”;
password = “testPassword”;

I got an error:
“Unable to connect to nay of the specified MySQL hosts. (Error Number: 1042)”

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.

I really need to put this to work, I need that to continue my job, any help will be appreciated. :D

ads5.mrt · Answer

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

Moisey Uretsky · Answer

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.

After that add a user to access your database such as:

mysql> GRANT ALL ON database_name.* TO user@xx .xxx.xx.xx IDENTIFIED BY ‘your_password’;

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:
‘192.168.0.%’ as a dynamic C-class or
‘%’ if you want to be able to connect from anywhere (this is less secure)

Moisey Uretsky · Answer

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.

After that add a user to access your database such as:

mysql> GRANT ALL ON database_name.* TO user@xx .xxx.xx.xx IDENTIFIED BY ‘your_password’;

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:
‘192.168.0.%’ as a dynamic C-class or
‘%’ if you want to be able to connect from anywhere (this is less secure)

Moisey Uretsky · Answer

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.

After that add a user to access your database such as:

mysql> GRANT ALL ON database_name.* TO user@xx .xxx.xx.xx IDENTIFIED BY ‘your_password’;

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:
‘192.168.0.%’ as a dynamic C-class or
‘%’ if you want to be able to connect from anywhere (this is less secure)

Moisey Uretsky · Answer

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.

After that add a user to access your database such as:

mysql> GRANT ALL ON database_name.* TO user@xx .xxx.xx.xx IDENTIFIED BY ‘your_password’;

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:
‘192.168.0.%’ as a dynamic C-class or
‘%’ if you want to be able to connect from anywhere (this is less secure)

Question

How to Allow Remote MySQL Database Connection