How to Allow Remote MySQL Database Connection

January 22, 2013 202k views
I want to be able to connect to my Digital Ocean MySQL database from my MacBook. I have attempted to follow this tutorial: Pretty much all it's saying is to edit /etc/mysql/my.cnf and change this... bind-address ... to this... bind-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 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!
1 comment
  • 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 =

23 Answers
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 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)
  • Also, if there's a firewall installed, one should open the port on the firewall;


    sudo ufw allow 3306/tcp
    sudo service ufw restart
  • Hey there. Just stopping by to tell you that I've been working hard on this all day, and your comment is the only thing I found that worked.


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 Thank you !!!
    Could you please help because I can'f find such documentation about it...
    I'm still stuck with a

    The SSH Tunnel has unexpectedly closed.

    I did the following:

    ssh -NC me@remote_ip -p my_custom_port -L 9999:localhost:3306

    Then on Sequel:

    - Mysql Host =
    - Username = root (I'll create a dedicated user later :p)
    - Password = my_password
    - Database = database_name
    - Port = 9999
    - SSH Host = localhost

    With best regards,


    I guess I found something...

    debug1: Connecting to [] port 22.

    I have set remote SSH to run on a custom port, not on the default port 22.
    So, within Sequel, how can I set the port to my custom one for the SSH Host entry?
    Or maybe I'm totally wrong...
    Any help is welcome.


    [UPDATE 2]
    I found something (again).
    First I missed some field within Sequel... Now it's ok.

    But when I attempt to "Test connection" I have the following:

    SSH port forwarding failed
    Unable to connect to host because the port connection via SSH was refused.
    Please ensure that your MySQL host is set up to allow TCP/IP connections (no --skip-networking) and is configured to allow connections from the host you are tunnelling via.
    You may also want to check the port is correct and that you have the necessary privileges.
    Checking the error detail will show the SSH debug log which may provide more details.
    MySQL said: Lost connection to MySQL server at 'reading initial communication packet', system error: 0

    It's weird because within my my.cnf I have the following line saying:

    Instead of skip-networking the default is now to listen only on localhost which is more compatible and is not less secure.
    bind-adress =

    (I don't have any "skip-networking" entry ..)

    We are close...


    [UPDATE 3]
    I found!

    I keep all the steps, maybe it will help someone.
    Step by step:


    ssh -NC remote_username@remote_ip [-p my_custom_port] -L 9999:localhost:3306

    [-p mycustomport] only if you set a custom port to access with SSH ; if not, leave empty and default port 22 will be used.

    Sequel Pro:

    - Name = Connection's name (it's up to you)
    - Mysql Host =
    - Username = mysql user (it can be root or a custom user)
    - Password = password for that user
    - Database = database name or "mysql" as default
    - Port = 3306 (here was one of my mistakes)
    - SSH Host = remote ip
    - SSH User = username used to connect with SSH
    - SSH Key = target your file or it will try to find it to it's default location
    - SSH port = by default it's 22 but you could have set a custom one...



  • Awesome post Bruns! Thank you for posting this, saved me a lot of time and effort and no need to go mucking around with the settings. This worked like a charm using sequel pro and my SSH user.


That 2nd "bind-address" was supposed to be bind-address (my local IP)
Awesome, thank you. That helps.

One more follow-up question. If I were to decide to do an SSH tunnel instead, does my.cnf need to be changed to accommodate that? Or is MySQL ready out-of-the-box for that type of connection?
That would be something configured on the firewall side of both your app and DB server and would not require changing the MySQL config, you would still leave it listening on the public IP/port and just restrict access via firewall rules.
I am trying to get my web server to talk to my db1 server through MySQL. However I keep getting a connection refused. The steps I have taken on db1:

1. Made sure my.cnf can bind to all ports by commenting out `bind-address`
2. Granted permissions to the user through the web server in MySQL
3. Made sure port 3306 was open in IPTables

Any ideas on what I might be missing?
raiyu, can you fix it? I'm having the same problem...
Make sure you restart MySQL after editing the config files. Run netstat -plutn to verify that MySQL is listening on
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 = "";
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 = "";' to 'server = "";' (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
Did you flush mysql's privileges after granting permissions?

Yeah, I flush the privileges... Nothing happens.
Now it works, I need to do another iptable command in firewall:
iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
and I uncomment bind-address again, but put the server ip in bind-address, like this:
bind-adress = myServerIp
@jacksonbenete Glad to hear you figured it out! :]
In localhost Place How To Write Ip Cofig?
// ONLINE (My VPS Droplet)
server = "";
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 = "";' to 'server = "";' (withou port number) It takes longer, but got the same error.
How Ti Renew Ip?
@chsmadhu07: Is MySQL installed on the same droplet? If so, you just need to set the server to localhost.
@bruns.nicholas, I owe you a beer.
This worked for me:
Hi guys, I'm trying to do something similar to bruns.nicholas, also from Sequel Pro. I've setup mysql and can connect from Railo admin, so all the details have are correct. I have an ssh key setup and can access the droplet using that just fine. But no combo I'm entering in the Sequel Pro SSH settings is letting me connect "the ssh tunnel has unexpectedly closed". What am I missing? Do I need a new ssh key just for the mysql connection? I'm not really sure what I'm supposed to be entering into SSH Host, SSH User and SSH Password?!
Never mind; about 1 minute after posting the above, I figured out all I was missing was just entering the ip address as the SSH Host as well. That was it; too easy!

No need to change MySQL conf.
I've tried with HeidiSQL 9.2 @ Win7 Ultimate , and working well.


Networktype: MySQL (SSH tunnel)
Hostname / IP :
      Prompt for credentials: unchecked
User: root
Password: [root_password]
Port: 3306
      Compressed client/server protocol: unchecked

SSH Tunnel

plink.exe location: C:\Program Files (x86)\PuTTY\plink.exe
SSH host + port: [your_droplet_ip_address]
Username: root
Password: -leave empty-
plink.exe.timeout: 5
Private key file: PATH_TO\private_key.ppk
Local port: 3306

This worked for me Enable remote access of MySql on Ubuntu Posted by rbgeek on September 23, 2014

  • Also worked for me but you also need to know that in my case '/etc/mysql/my.cnf' was empty and in fact you are looking for '/etc/mysql/mysql.conf.d/mysqld.cnf' so that you can remove the bound IP

    #bind-address =

    Then it will work

Have another answer? Share your knowledge.