Setting up MariaDB for Remote Client Access

Some MariaDB packages bind MariaDB to 127.0.0.1 (the loopback IP address) by default as a security measure using the bind-address configuration directive, in that case, one can't connect to the MariaDB server from other hosts or from the same host over TCP/IP on a different interface than the loopback (127.0.0.1).

The list of users existing remote users can be accessed with the following SQL statement on the mysql.user table:

SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';

+-----------+-----------+ | User | Host | +-----------+-----------+ | Guillaume | % | | root | 127.0.0.1 | | root | ::1 | +-----------+-----------+ 4 rows in set (0.00 sec)

We will create a "root" user that can connect from anywhere with the local area network (LAN), which has addresses in the subnet 192.168.1.0/24. This is an improvement because opening a MariaDB server up to the Internet and granting access to all hosts is bad practice.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;

(% is a wildcard)