How to Allow MySQL remote connections in Ubuntu Server 18.04

This tutorial explains how to allow remote connections to the MySQL/MariaDB server on Ubuntu 18.04. The default behavior of the Ubuntu MySQL Server blocks all remote connections. Which prevent us from accessing the database server from the outside.

Note that to allow mysql remote connections we need to edit the MySQL main configuration file. If you are using MariaDB Database server, configuration file going to be "/etc/mysql/mariadb.conf.d/50-server.cnf", If you have installed MySQL Database server configuration file is: "/etc/mysql/mysql.conf.d/mysqld.cnf".

Open the /etc/mysql/mariadb.conf.d/50-server.cnf file (or /etc/mysql/mysql.conf.d/mysqld.cnf).

sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

Under the [mysqld] section, locate the line:

bind-address            = 127.0.0.1

And change it to:

bind-address            = 0.0.0.0

Save the configuration file, and restart the MySQL server:

sudo systemctl restart mariadb

sudo systemctl restart mysql

Run the netstat command and make sure that mysql server listen on socket 0 0.0.0.0:3306.

sudo netstat -tulnp | grep mysqld

The output should be similar to the following:

tcp        0 0 0.0.0.0:3306            0.0.0.0:* LISTEN   622/mysqld

How it works..

By default the mysql daemon on Ubuntu 18.04 is only listening for connections on localhost (127.0.0.1), which mean you cannot login to the server from a remote computer. This setting is controlled by the bind-address in the MySQL/MariaDB configuration file. By default it is set to: "bind-address = 127.0.0.1" which prevents other hosts from accessing our mysql server.

To allow remote access, we changed the value of the bind-address to: "0.0.0.0".

bind-address            = 0.0.0.0

By changing value to 0.0.0.0, we instruct MySQL to bind to all available interfaces and by doing that we allow remote connections to the MySQL Server on Ubuntu 18.04.

Open port 3306 from Ubuntu Firewall

UFW firewall is disabled by default in Ubuntu 18.04, so you don't have to worry about opening mysql port 3306 if you didn't enable UFW.

But if have enabled UFW then it will block the mysql remote access, so you need to add firewall rule to open the port 3306.

sudo ufw allow 3306/tcp

Click the following link to learn more about UFW Ubuntu Firewall.

From a another Linux machine, you can run nmap against your server IP to check whether port 3306 is open or not.

nmap 192.168.1.100

Create Remote MySQL user and grant remote access to databases

Now that our MySQL server allows remote connections, we still need to have a mysql user that is allowed to access the server from outside the localhost. To create a mysql user that is allowed to connect from any host, login in the MySQL console and run:

CREATE USER 'username'@'%' IDENTIFIED BY 'new-password';
FLUSH PRIVILEGES;

Then you can grant access to databases using the GRANT ALL command:

GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'%';

If you want to grant access to all databases on the server, run:

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';

If you want to create a user that is only allowed to login from a specific host, replace '%' with host IP or domain name when creating the user.

CREATE USER 'username'@'192.168.1.200' identified by 'new-password';

To test the connection, try to access the MySQL server from a remote computer:

mysql -h 192.168.1.100 -u username -p

Here 192.168.1.100 is the IP address of my Ubuntu Server where MySQL server is running.

Note that, enabling remote connections to MySQL server is not good practice from a security standpoint. So don't expose your database server to outside unless you must, especially in a production environment.