Saturday, June 19, 2021

Configuring Remote Access to MySQL/MariaDB Databases

Mahesh Palamuttath June 19, 2021

By default, MySQL or MariaDB only listens for connections from the localhost. All remote access to the server is denied by default. To enable remote access, run the commands below to open MySQL/MariaDB configuration file.

sudo nano /etc/mysql/mysql.conf.d/mysql.cnf

on MariaDB server, the file may live below (Debian 10)

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

Then make the below change below from:

bind-address = 127.0.0.1

bind-address = 0.0.0.0

After making the change above, save the file and run the commands below to restart the server.

sudo systemctl restart mysql.service

sudo systemctl restart mariadb.service

To verify that the change happens, run the commands below

sudo netstat -anp | grep 3306

and you should find the result that looks like the one below

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3213/mysqld

Now the server is setup to listen to all IP addresses but individual IP needs to be explicitly configure to connect to a database.

To enable a client to connect to a database, you must grant access to the remote server.

For example, if you wish for a client computer with IP address 192.168.1.5 to connect to a database called demodatabase as user user, then run the commands below after logging onto the database server.

sudo mysql -uroot -p

for local user

use mysql;

SELECT User,Host,plugin FROM mysql.user; (check status)

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin123';

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

UPDATE user SET plugin='mysql_native_password' WHERE User='admin';

flush privileges;

for remote user

use mysql;

SELECT User,Host,plugin FROM mysql.user; (check status)

CREATE USER 'admin'@'%' IDENTIFIED BY 'admin123';