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';

Note

Replace newremoteuser with your desired mysql username, and remote_password with mysql password
% means that this newremoteuser can connect to this database server from the Internet (any host).

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

This bind address configuration will allow everyone on the internet to connect to your database server.

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

Note

Replace newremoteuser to your mysql user that was previously created on step 1.
Replace 47.44.44.444 to your database server's IP public address.

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

Replace 192.168.0.6 with your mysql client's IP Address

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

Replace 192.168.0.6 with your mysql client's IP Address

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

Thanks for reading!