Can't connect to MySQL server

Can’t Connect to MySQL Server Remotely on Ubuntu

Last updated on

MySQL by default will not allow incoming remote connections for security reasons. In this article we will edit the MySQL config file mysqld.cnf to allow remote connections.

Testing a Remote MySQL Connection

To test a remote MySQL connection in Linux terminal, simply replace username and hostname_or_ip with your own.

mysql -u username -h hostname_or_ip -p

Or via Command Line in Windows:

telnet 192.16.8.1.22 3306

If you are getting an error when trying to connect to your remote MySQL server

ERROR 2003 (HY000): Can't connect to MySQL server on '192.16.8.1.22' (111)

or in Windows Telnet

Connecting To 192.16.8.1.22...Could not open connection to the host, on port 3306: Connect failed

1. Edit MySQL config

You may need to comment out bind-address in the MySQL config file mysqld.cnf.

Open mysqld.cnf with nano editor.

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

Press CTRL + W and search for bind-address

/etc/mysql/mysql.conf.d/mysqld.cnf
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1

The above line is telling MySQL to only accept local connections (127.0.0.1 / localhost).

If you know the IP that you are trying to remotely connect from, you should enter it here to restrict remote connections to that IP.

If you have to allow all IP addresses to connect, you can comment out the line by adding # before it so it looks like:

/etc/mysql/mysql.conf.d/mysqld.cnf
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address = 127.0.0.1

Note: It is not good security practice to leave your MySQL server accessible to any IP, so consider this a temporary measure.

Restart the MySQL service for changes to take effect.

sudo service mysql restart

2. Check Firewall

If you still can’t connect, check if there is a firewall configured on your server. The most common firewall for Ubuntu server is ufw.

Check if ufw is enabled:

sudo ufw status

If it’s enabled, you should see some rules:

To                         Action      From
--                         ------      ----
OpenSSH                    ALLOW       Anywhere
Apache Full                ALLOW       Anywhere
OpenSSH (v6)               ALLOW       Anywhere (v6)
Apache Full (v6)           ALLOW       Anywhere (v6)

If MySQL is not listed, add a rule for it.

sudo ufw allow mysql

There should be a rule for MySQL:

To                         Action      From
--                         ------      ----
OpenSSH                    ALLOW       Anywhere
Apache Full                ALLOW       Anywhere
3306                       ALLOW       Anywhere
OpenSSH (v6)               ALLOW       Anywhere (v6)
Apache Full (v6)           ALLOW       Anywhere (v6)
3306 (v6)                  ALLOW       Anywhere (v6)

Restart the ufw service:

sudo service ufw restart

Let me know in the comments if this helped. Follow me on Twitter, Facebook and YouTube.

p.s. I increased my AdSense revenue by 68% using AI 🤖. Read my Ezoic review to find out how.

1 Star2 Stars3 Stars4 Stars5 Stars 4.77 (13 votes)

Leave a Reply

Your email address will not be published. Required fields are marked *

We use Markdown to style comments, like on Github and Reddit.
To do a line break, type two spaces after the sentence.
You can add inline code by wrapping it in backticks: `code here`

    To do an entire block of code  
    type four spaces before the line
    and it will appear in a block like this.
    <-- four empty spaces

8 replies

Implemented the above steps and the problem was resolved immediately. Thank you so much for the guidance.