Enable Remote Access MariaDB

Sometimes we only use MySQL for localhost, which means that your application and MySQL databases are on one server for simplicity.

And this guide will help you to enable remote access MySQL, so in case you want to build a dedicated database server and make it another server (for example web server) to connect your database server.

Step 1: Create a Remote MySQL User

We need to create a remote mysql user to allow connect it to our database server, open mysql root session with:

mysql -u root

and execute the following query:

CREATE USER 'newremoteuser'@'%' IDENTIFIED BY 'remote_password';

Step 2: Remote Access MariaDB

After we installed the latest MariaDB server (I've posted this tutorial here), open the configuration of your mariadb (my version MariaDB 10.6) with your favorite editor:

nano /etc/mysql/mariadb.conf.d/50-server.cnf

Scroll until you find bind-address, replace it with:

bind-address            = 0.0.0.0

save it with CTRL+X and Y.

Restart your mysql / mariadb server with the following command:

service mysql restart

Step 3: Check Remote Access

To start to check remote access of your database server is successfully configured or not, first log in to another server (for example web server). Install mariadb-client if you don't install it before, use the following command in the ssh session:

mysql -u newremoteuser -p -h 47.44.44.44 -P 3306

Optional: Check Firewall

If you are using default firewall Uncomplicated Firewall (UFW), use the following command to allow IP to connect remote mysql your database server:

sudo ufw allow from 192.168.0.6 to any port 3306

If you are using Config Server Firewall (CSF), make sure to add port 3306 on an incoming connection (TCP_IN)  (UDP_IN) to allow everyone on the Internet to connect remotely mysql your database server.

Or, you can add whitelisted IP on /etc/csf/csf.allow file:

tcp|in|d=3306|s=192.168.0.6
udp|in|d=3306|s=192.168.0.6

Restart CSF with csf -r command to apply the configuration.

Thanks for reading!