Question

Unable to connect to remote MySQL Server

I am trying to connect to a remote MySQL server 5.7 installed on Ubuntu 16.04 from my workstation.

Here is the error i am getting (Using Navicat to connect).

https://i.stack.imgur.com/oclnV.png

To make it work I followed the following steps.

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

  2. flush privileges;

  3. sudo /etc/init.d/mysql restart

Some forums recommends commenting line starts with “bind-address” on my.cnf which resides in /etc/mysql/my.cnf. The problem is there is no such line on my installation.

# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

I also tried to open ports on firewall using the following commands.

sudo ufw allow 3306/tcp sudo service ufw restart

But still stuck with the same problem.


Submit an answer


This textbox defaults to using Markdown to format your answer.

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

Sign In or Sign Up to Answer

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.

@ozwiz

Inside /etc/mysql/mysql.conf.d/ there should be a file called mysqld.cnf, if you’ll open that file up:

nano /etc/mysql/mysql.conf.d/mysqld.conf

… and then find this line:

bind-address            = 127.0.0.1

You’ll need to replace 127.0.0.1 with your Droplet’s Public IPv4 IP address, and then restart MySQL. The above line is telling MySQL to only accept local connections, so we need to bind MySQL to a public IP that you can connect to.

The above is beneficial if you need to connect from your local PC/Mac, though if you need to connect from another server in the same data center, you can instead use the Private Network IPv4 Address if you enabled private networking during deployment.

That’s easy, just change a line “bing-address” in the file /etc/mysql/mysql.conf.d/mysqld.cnf and comment that line, save the file and restart Mysql. That is all! good luck!

Thanks it works !

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel