Allowing remote access to a MariaDB server on CentOS

Unless you are running your MariaDB database server to drive some local web applications on the same server hardware, most working environments would be pretty useless if remote access to a database server were forbidden. In many IT surroundings, you will find high-available, centralized dedicated database servers optimized in hardware (for example, huge amounts of RAM) and hosting multiple databases allowing hundreds of parallel connections from the outside to the server. Here in this process, we will show you how to make remote connections to the server possible.

To Start With: What Do You Need?

To complete this process, you will require a working installation of the CentOS 7 operating system with root privileges. It is expected that a MariaDB server is already installed and running and you have read and applied the Managing a MariaDB database process for an understanding of permissions and how to test (local) database connections.

The Process

In our example, we want to access a MariaDB database server with the IP address 192.168.1.12 from a client computer in the same network, with the IP address 192.168.1.33. Please change appropriately to fit your needs:

1. To begin, log in as root on your MariaDB database server and open the firewall for the incoming MariaDB connections:
   firewall-cmd --permanent --add-service=mysql && firewall-cmd --reload
2. Afterwards, we need to create a user account which can connect to our MariaDB server remotely (as we have prevented root from doing this in a further step for security reasons), login your database server using the MariaDB command line interface mysql as user root and type the following MariaDB statement (replacing the XXXX with a password of your choice, also feel free to adjust the username and remote IP of the client who wants to connect to the server—in our case the client has the IP 192.168.1.33—accordingly):
   GRANT SELECT ON mysql.user TO 'johndoe'@'192.168.1.33' IDENTIFIED BY
'XXXX';
FLUSH PRIVILEGES;EXIT;
3. Now we can test the connection from our client computer with the IP address of 192.168.1.33 in our network. This computer needs the MariaDB shell installed (on a CentOS 7 client, install the package mariadb) and needs to be able to ping the server running the MariaDB service (in our example, the IP 192.168.1.12). You can test connecting to the server by using the following command (on success, this will print out the content of the mysql user table):
   echo "select user from mysql.user" | mysql -u johndoe -p mysql -h
192.168.1.12

How Does It Work?

We started our journey by opening the standard MariaDB firewall port 3306 using the firewalld predefined MariaDB service, which is disabled by default on CentOS 7. After this, we configured which IP addresses were allowed to access our database server, which is done on a database level using the MariaDB shell. In our example, we used the GRANT SELECT command to allow the user johndoe at the client IP address 192.168.1.33 and with the password in quotes 'XXXX' to access the database with the name mysql and the table user to make SELECT queries only. Remember, here you can also apply wildcards in the <hostname> field using the % sign (which means any characters). For example, for defining any possible hostname combination in a Class C network, you can use the % sign like so 192.168.1.%. Granting access to the mysql.user database and table was just for testing purposes only and you should remove the user johndoe from this access permission whenever you have finished your tests, using: REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'johndoe'@'192.168.1.33';. If you want you can also delete the user DROP USER 'johndoe'@'192.168.1.33'; because we don’t need it anymore.