Allow Remote MySQL (MariaDB) in Ubuntu 18.04

allow remote mysql.md

Check Firewall

Firstly, if ufw firewall is enabled, ensure you have a rule for MySQL sudo ufw allow mysql

Restart the service
sudo service ufw restart

Check MySQL config

You may need to comment out bind-address in the MySQL config file mysqld.cnf

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

Find the line bind-address (CTRL + W to search) Comment out this line by adding # before it. sudo service mysql restart

Login to mysql using root from SSH mysql -uroot -p

Execute this statement

CREATE USER 'root'@'%' IDENTIFIED BY 'root_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

Cek authentication_String

Jalankan SQL Select di database mysq

select host, user, authentication_String from user;
SELECT user, authentication_string, plugin, host FROM mysql.user;

jika authentication_String kosong atau invalid, update berikut :

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;

Jika host belum ada %, tambahkan usernya:

CREATE USER 'root'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Cek lagi valuenya