Configuring Remote Access to MySQL/MariaDB Databases

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


To


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';

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

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

flush privileges;

 

After running the commands above, you should be able to access the server from the client computer with that assigned IP.


To connect to the server from the IP, run the commands below


sudo mysql -uroot -pdatabaseuser_password -h server hostname or IP address


That’s it! You’ve successfully configured a remote access to MySQL/MariaDB database server.


You may want to open Ubuntu Firewall to allow IP address 192.168.1.5 to connect on port 3306.


sudo ufw allow from 192.168.1.5 to any port 3306


Reference:

https://websiteforstudents.com/configure-remote-access-mysql-mariadb-databases/

0 Komentar