This guide demonstrates how to enable remote connections on an Ubuntu 18.04 or higher MySQL/MariaDB server. Ubuntu MySQL Server, by default, disables all remote connections. This prevents us from accessing the database server from outside.
You must modify the MySQL main configuration file in order to enable remote connections for MySQL on the Ubuntu server.
Therefore, if you have installed MySQL, the configuration file for the server is located in /etc/mysql/mysql.conf.d/mysqld.cnf. However, the configuration file will be /etc/mysql/mariadb.conf.d/50-server.cnf if you are using the MariaDB database server.
1. You must first open the file mysqld.cnf in /etc/mysql/mysql.conf.d (if you use MariaDB, install it using the specified file path; otherwise, use the older MariaDB-provided location):
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
2. Find the following line under the [mysqld] section:
bind-address = 127.0.0.1
3. Change this line to
bind-address = 0.0.0.0
By setting the value to 0.0.0.0, we tell MySQL to bind to every accessible interface, enabling remote connections to the MySQL Server on Ubuntu 18.04.
4. Then, save the modification to the file and restart MariaDB or MySQL.
MySQL: sudo systemctl restart mysql
MariaDB: sudo systemctl restart mariadb
Whether UFW is activated on the server
You must add a firewall rule to your firewall to open port 3306 if your VPS has the UFW firewall activated, because doing so will prevent MySQL remote access.
sudo ufw allow 3306/tcp
Remember that it is not a good idea from a security standpoint to enable remote connections to the MySQL server. Therefore, especially in a production environment, don't expose your database server to the outside unless you absolutely must.
I hope this article helps to enable MySQL and MariaDB remote connections in Ubuntu Server.