How to Access a MariaDB Database From Another Host?

The previous article already explained how to access a MariaDB database from localhost. This article will explain how to access the MariaDB database from another host.

Problem

How to access a MariaDB database from another host?

Solution

To access the MariaDB database, you can follow the format below:

mariadb -h your_server_ip -u username -P port_number -p

By default, the MariaDB database uses port 3306, so if your MariaDB database uses port 3306, then there is no need to write the port when executing the command to access the MariaDB database. I have a MariaDB database server with an IP of 192.168.56.101 and an Ubuntu server with an IP of 192.168.56.11. I want to access the MariaDB database via the Ubuntu server using the user john and run the command:

mariadb -h 192.168.56.101 -u john -p

But I got an error like below:

ERROR 2002 (HY000): Can’t connect to server on ‘192.168.56.101’ (115)

Error when accessing the MariaDB from another host

Below are the steps to access the MariaDB database:

1. Open the port

Open port 3306 on both servers. If you use RockyLinux/AlmaLinux/RHEL for your servers, use the command below:

firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

But if you use Ubuntu/Debian for your server, type the command below:

sudo ufw allow 3306

2. Check the MariaDB version

You should know that to access the MariaDB database, you need a mariadb-client package whose version is the same as the MariaDB database version. If the mariadb-client version is different from the mariadb database version, then there will usually be an error. To install the mariadb-client package, you can go to this page.

3. Grant access

On the database server, run a query with the format below:

GRANT option ON db_name.* TO username@"ip_address" IDENTIFIED BY "password";

For example, if user john wants to access the entire MariaDB database from the host with IP 192.168.56.11, then use the query below:

GRANT ALL ON *.* TO john@"192.168.56.11" IDENTIFIED BY "123456";

Warning

If you want user john to be able to access only the Zabbix database, use the query below:

GRANT ALL ON zabbix.* TO john@"192.168.56.101" IDENTIFIED BY "qwerty";

And use the query below if you want user john to be able to view only the Zabbix database:

GRANT SELECT ON zabbix.* TO john@"192.168.56.101" IDENTIFIED BY "qwerty";

4. Configure the file

Go to the file /etc/mysql/mariadb.conf.d/50-server.cnf and change the bind-address item to be as below:

bind-address             = 0.0.0.0

Warning

You can also change the bind-address item in the /etc/mysql/my.cnf file by adding the following script to the file:

[mysqld]
bind-address = 0.0.0.0

5. Restart MariaDB

After that, restart the MariaDB service using the command below:

sudo systemctl restart mariadb

Then, try to access it again, and you should be able to access the database like in the picture below:

Succeed to access MariaDB from another host

Note

If you want your MariaDB database to only be accessed from a certain IP, for example, IP 192.168.56.11, then use the command below if your distro uses Ubuntu/Debian:

sudo ufw allow from 192.168.56.11 to any port 3306

But, if you use RockyLinux/AlmaLinux/RHEL, type the below command to open port 3306 only from IP 192.168.56.11

firewall-cmd --zone=public --add-rich-rule 'rule family=ipv4 source address=192.168.56.11 port port=3306 protocol=tcp accept'
firewall-cmd --reload
firewall-cmd --list-rich-rules

References

mariadb.com
tencentcloud.com
webdock.io