By default, remote access to the MariaDB database service is disabled for security reasons. In this guide we will look at setting up remote access to a database by IP address.

Let's assume that we have 2 servers, on 1 there is a certain system (site/crm), and on the other we will store a database and request data by specifying the login, password and IP address of the server.

1. Preparing for setup

1.1 Let's check the configuration file

Let's make sure that the connection option is not commented out and looks like this:

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

...
bind-address = 0.0.0.0
...

To apply the settings, restart the service

systemctl restart mariadb

1.2 Check port availability

netstat -ant | grep 3306

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

2. Setup

mysql -u root -p

2.1 Create a database and user

CREATE DATABASE base1;
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';

2.2 Let’s allow the connection by specifying the IP of the server from which we will connect

GRANT ALL ON base1.* to 'user1'@'xx.ip.xx.ip' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

These details can be used to connect:

• base1 - Database.
• user1 - User.
• password - Password.
• xx.ip.xx.ip - IP address of the server from which we will connect.

2.3 Other examples

2.3.1 Allow access to all databases from the specified ip

GRANT ALL ON *.* to 'user1'@'xx.ip.xx.ip' IDENTIFIED BY 'password' WITH GRANT OPTION;

2.3.2 Allow connections from any IP addresses to base1

GRANT ALL ON base1.* to 'user1'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

2.3.3 Allow connections only from a specific subnet to base1

GRANT ALL ON base1.* to 'user1'@'xx.ip.xx.%' IDENTIFIED BY 'password' WITH GRANT OPTION;

3. Setting up an iptables firewall

3.1 Let's only allow a specific ip

iptables -A INPUT -p tcp -s xx.ip.xx.ip --dport 3306 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp --sport 3306 -m conntrack --ctstate ESTABLISHED -j ACCEPT

3.2 Let's allow for all ips

iptables -A INPUT -p tcp -m tcp --dport 3306 -m conntrack --ctstate ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp --sport 3306 -m conntrack --ctstate ESTABLISHED -j ACCEPT

3.3 Allow for a specific subnet

iptables -A INPUT -p tcp -s xx.ip.xx.0/24 --dport 3306 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp --sport 3306 -m conntrack --ctstate ESTABLISHED -j ACCEPT

4. Let's check the connection from server 1

4.1 Install the mariadb client

apt install mariadb-client

4.2 Connecting to a configured server

mysql -u wpuser -h ip.xx.ip.xx -p

Enter password:

MariaDB [(none)]> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| base1 |
+--------------------+
2 rows in set (0.10 sec)

Ready.