How to Allow Remote MySQL Database Connection

January 22, 2013 370.4k views
clifton
By:
clifton
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 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!
1 comment
  • sergeytsaplin October 15, 2014

    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

Log In to Comment
24 Answers
moisey MOD January 22, 2013
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)
Reply
bruns.nicholas January 31, 2014
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!.
Reply
  • jrean October 10, 2014

    @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 = 127.0.0.1
- Username = root (I'll create a dedicated user later :p)
- Password = my_password
- Database = database_name
- Port = 9999
- SSH Host = localhost

    With best regards,

    J.

    [UPDATE]
    I guess I found something...

    debug1: Connecting to 127.0.0.1 [127.0.0.1] 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.

    J.

    [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 127.0.0.1 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 = 127.0.0.1

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

    We are close...

    J.

    [UPDATE 3]
    I found!

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

    Terminal

    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 = 127.0.0.1
- 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 id_rsa.pub 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...

    Regards,

    J.

  • americanninja May 13, 2015

    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.

    Sweet!!!

  • inspired01 July 16, 2017

    @bruns.nicholas : Thanks for pointing this out. Had tried a great many suggested ways to get access to mysql remotely, and nothing worked. Doing it via SSH was instant and super easy. Cheers.

jacksonbenete May 28, 2013
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
Reply
  • peterQ November 5, 2016

    I'm a newbie and this gave me the idea to change my user's hostname to '%'. thanks.

clifton January 22, 2013
That 2nd "bind-address" was supposed to be bind-address (my local IP)
Reply
clifton January 22, 2013
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?
Reply
moisey MOD January 22, 2013
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.
Reply
mikelbring April 19, 2013
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?
Reply
ads5.mrt May 14, 2013
raiyu, can you fix it? I'm having the same problem...
Reply
kamaln7 MOD May 15, 2013
Make sure you restart MySQL after editing the config files. Run netstat -plutn to verify that MySQL is listening on 0.0.0.0:3306.
Reply
kamaln7 MOD May 28, 2013
Did you flush mysql's privileges after granting permissions?

FLUSH PRIVILEGES;
Reply
jacksonbenete May 28, 2013
Yeah, I flush the privileges... Nothing happens.
Reply
jacksonbenete May 28, 2013
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
Reply
kamaln7 MOD May 29, 2013
@jacksonbenete Glad to hear you figured it out! :]
Reply
chsmadhu07 December 13, 2013
In localhost Place How To Write Ip Cofig?
Reply
chsmadhu07 December 13, 2013
// 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.
How Ti Renew Ip?
Reply
kamaln7 MOD December 13, 2013
@chsmadhu07: Is MySQL installed on the same droplet? If so, you just need to set the server to localhost.
Reply
ericprieto April 10, 2014
@bruns.nicholas, I owe you a beer.
Reply
brendonwbrown June 5, 2014
This worked for me:
http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html
Reply
samueldaams June 6, 2014
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?!
Reply
samueldaams June 6, 2014
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!
Reply
iamkingsleyf January 5, 2015

trying to work with heidisql its not working and workbench keep freezing https://www.digitalocean.com/community/questions/unable-to-connect-to-mysql-server-using-heidisql

Reply
gonevps June 8, 2015

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

Settings

Networktype: MySQL (SSH tunnel)
Hostname / IP : 127.0.0.1
      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
Reply
id6298 May 19, 2016

This worked for me https://rbgeek.wordpress.com/2014/09/23/enable-remote-access-of-mysql-on-ubuntu/ Enable remote access of MySql on Ubuntu Posted by rbgeek on September 23, 2014

Reply
  • simonilett September 23, 2016

    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 = 127.0.0.1

    Then it will work

    • srgovin February 18, 2017

      this works for me as well by commenting bind IP from the file /etc/mysql/mysql.conf.d/mysqld.cnf

adismith011 May 1, 2018

*Edify your MySQL "Losing Connection" Error with MySQL Remote Support *
At whatever point you are using MySQL Database by then damn without question you need to confront a kind of specific issue which ousts you to work with your MySQL Database. In any case, now you don't need to go up against this issue over and over in light of the route that at Cognegic we give MySQL Server 5.0 Support and in addition offer to wrap up the straightforwardness and execution of your MySQL Database. Our exceedingly committed masters increase the database execution and application transparency. So rapidly contact to MySQL Enterprise Backup and light up these issues inside surveyed day and age.
For More Info: https://cognegicsystems.com/

Contact Number: 1-800-450-8670
Email Address- info@cognegicsystems.com
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

Reply
Have another answer? Share your knowledge.