Category: MariaDB Database

Added: 14th of January 2023

Viewed: 859 times

Related Tips & Tutorials

➔ Unable to connect to MariaDB remotely. Socket fail to connect to host on IP address, port

Unable to connect to MariaDB remotely, Host ip_address is not allowed to connect to MariaDB server

If you have created a new user in MariaDB and cannot login remotely via database management tools such as DBeaver, you are the sure the password is correct, your next step is to make sure that the host value for the specified user is not set to localhost

Login to your remote server, then login to MariaDB as root.

mysql -u root -p


At the MariaDB prompt, enter the following query to show all records from the mysql.user table
SELECT Host, User FROM mysql.user;


As you can see User mycomptips_guest is set as localhost for Host. This means that this user can only access MariaDB from the same machine.



To allow mycomptips_guest to connect remotely, we need to change the host value to %

The first thing to do is delete mycomptips_guest from the table mysql.user
DROP USER 'mycomptips_guest'@'localhost';


Then add the user again
CREATE USER 'mycomptips_guest'@'%' IDENTIFIED BY 'password';


Grant all Privileges to mycomptips_guest by entering the following
GRANT ALL PRIVILEGES ON *.* TO 'mycomptips_guest'@'%';


Query the mysql.user table again
SELECT Host, User FROM mysql.user;


As you can see the host value has now been updated for user mycomptips_guest


Exit MariaDB by entering the following command
exit


Then restart MariaDB
restart mariadb


You should now be able to login remotely.