[Ubuntu] How to allow MySQL/MariaDB remote connections in Ubuntu Server?

Ievaz

This tutorial shows how to allow remote connections to the MySQL/MariaDB server on Ubuntu 18.04. By default, Ubuntu MySQL Server blocks all remote connections. Which prevents us from accessing the database server from the outside.

To enable remote connection of MySQL in Ubuntu server, you will need to edit the MySQL main configuration file.

So if you have installed MySQL Database server configuration file is: /etc/mysql/mysql.conf.d/mysqld.cnf. However, if you are using MariaDB Database server, configuration file going to be /etc/mysql/mariadb.conf.d/50-server.cnf.

Steps:

1. Firstly, you need to open /etc/mysql/mysql.conf.d/mysqld.cnf file (if you use MariaDB, installed of the provided file path, use earlier provided path for MariaDB):

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

2. Under the [mysqld] section, locate the line:

bind-address = 127.0.0.1

3. Then change this line to:

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.

4. After that, save the change in the file and restart MySQL or MariaDB:

For MySQL: sudo systemctl restart mysql
For MariaDB: sudo systemctl restart mariadb

5. To make sure that MySQL server listens on socket 0 0.0.0.0:3306, you should run this command:

For MySQL: sudo netstat -tulnp | grep mysqld
ForMariaDB: sudo netstat -tulnp | grep mariadb

You should see the output something like that i you have installed MariaDB on your VPS:

If UFW is enabled on the server

If on your VPS is enabled UFW firewall, then it will block the MySQL remote access, so you need to add a firewall rule on your firewall to open the port 3306.

sudo ufw allow 3306/tcp

---

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