mariadb_remote_connections.md

(Main Article)[https://webdock.io/en/docs/how-guides/how-enable-remote-access-your-mariadbmysql-database]

Verify MariaDB Server

Before starting, make sure the MariaDB server is running by using the following command:

ps -ef | grep -i mysql

You should get the following output:

mysql        595       1  0 04:17 ?        00:00:00 /usr/sbin/mysqld
root        1350    1337  0 04:22 pts/0    00:00:00 grep --color=auto -i mysql

By default, the MariaDB server is listening on localhost only for security reasons. You can check it with the following command:

netstat -ant | grep 3306

In the following output, you should see that the MariaDB server is listening on localhost (127.0.0.1):

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

Configure MariaDB

First thing you need to do is to configure the MariaDB server to listen on all IP addresses on the system.

You can do it by editing the MariaDB default configuration file /etc/mysql/my.cnf. You can open this file using your favorite text editor:

nano /etc/mysql/my.cnf

Change the value of the bind-address from 127.0.0.1 to 0.0.0.0. So that MariaDB server accepts connections on all host IPv4 interfaces.

bind-address = 0.0.0.0

Save and close the file when you are finished. Then, restart the MariaDB service to apply the changes:

systemctl restart mariadb

You can now verify the MariaDB listening status with the following command:

netstat -ant | grep 3306

If everything is fine, you should get the following output:

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

Now, the MariaDB server is setup to listen to all IP addresses.

Grant Access to a User from a Remote System

MariaDB [(none)]> CREATE DATABASE wpdb;
MariaDB [(none)]> CREATE USER  'wpuser'@'localhost' IDENTIFIED BY 'password';

MariaDB [(none)]> GRANT ALL ON wpdb.* to 'wpuser'@'208.117.84.50' IDENTIFIED BY 'password' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> EXIT;

If you want to grant remote access on all databases for wpuser, run the following command:

MariaDB [(none)]> GRANT ALL ON *.* to 'wpuser'@'208.117.84.50' IDENTIFIED BY 'password' WITH GRANT OPTION;

If you want to grant access to all remote IP address on wpdb as a wpuser, use % instead of IP address (208.117.84.50) as shown below:

MariaDB [(none)]> GRANT ALL ON wpdb.* to 'wpuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

If you want to grant access to all IP addresses in the subnet 208.117.84.0/24 on wpdb as a wpuser, run the following command:

MariaDB [(none)]> GRANT ALL ON wpdb.* to 'wpuser'@'208.117.84.%' IDENTIFIED BY 'password' WITH GRANT OPTION;

Configure Firewall

You can grant access to the remote system with IP 208.117.84.50 to connect the port 3306 with the following command:

ufw allow from 208.117.84.50 to any port 3306

If you want to grant access from any IP address you would use the following command:

ufw allow 3306

Next, reload the firewall with the following command:

ufw reload

Once you are finished, you can proceed to the next step.

Test Connection from Remote System

mysql -u wpuser -h 45.148.28.101 -p