How to Allow MySQL Remote Access in Ubuntu Server

In this tutorial, we are going to learn how to allow remote access to the MySQL server in Ubuntu Server. For the tutorial, I am using Ubuntu Server 20.04, But you can use this on any previous version of Ubuntu Linux.

Enable MySQL Server Remote Connection in Ubuntu

By default MySQL Server on Ubuntu runs on the local interface, which means remote access to the MySQL Server is not allowed. To enable remote connections to the MySQL Server, we need to change the value of the bind-address in the MySQL Configuration File.

First, Open the /etc/mysql/mysql.conf.d/mysqld.cnf file (/etc/mysql/my.cnf in Ubuntu 14.04 and earlier versions).

vim /etc/mysql/mysql.conf.d/mysqld.cnf

Under the [mysqld] Locate the Line,

bind-address            = 127.0.0.1

And change it to,

bind-address            = 0.0.0.0

Then, Restart the Ubuntu MySQL Server:

systemctl restart mysql.service

Now Ubuntu Server will allow remote access to the MySQL Server, But still, you need to configure MySQL users to allow access from any host.

For example, when you create a MySQL user, you should allow access from any host.

CREATE USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

Or allow from specific IP Address,

CREATE USER 'username'@'192.168.1.100' IDENTIFIED WITH mysql_native_password BY 'password';

Troubleshoot Ubuntu MySQL Remote Access

To make sure that the MySQL server listens on all interfaces, run the netstat command as follows.

netstat -tulnp | grep mysql

The output should show that MySQL Server is running on the socket 0 0.0.0.0:3306 instead of 127.0.0.1:3306.

You can also try to telnet to the MySQL port 3306 from a remote host. For example, if the IP Address of your Ubuntu Server is 192.168.1.10, Then from the remote host execute,

telnet 192.168.1.10 3306

You can also run the nmap command from a remote computer to check whether MySQL port 3306 is open to the remote host.

nmap 192.168.1.10

The output should list MySQL port 3306, and the STATE should be open. If the MySQL port 3306 is not open, Then there is a firewall that blocks the port 3306.

Summary: MySQL Remote Access Ubuntu Server 20.04.

In this tutorial, we learned how to enable Remote Access to MySQL Server in Ubuntu 20.04.

To allow MySQL remote Access in Ubuntu 20.04, we change the value of the bind-address to 0.0.0.0 in the /etc/mysql/mysql.conf.d/mysqld.cnf file.