My Computer Tips

Home | About | Categories | All Tips & Tutorials

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

ID: 335

Category: MariaDB Database

Added: 14th of January 2023

Views: 1,745

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 percentage symbol means that any host, including remote and local connections can connect to the database

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.

Related Tips & Tutorials

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