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!

brunsnicholas · 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

alexdo · Answer

Hi, all We have an existing tutorial on how to allow remote access to MySQL which you can check here: https://www.digitalocean.com/community/tutorials/how-to-allow-remote-access-to-mysql Hope that this helps!Regards,Alex

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today

Report this

How to Allow Remote MySQL Database Connection

Report this

How to Allow Remote MySQL Database Connection