Host 'localhost' is not allowed to connect to this MySQL server
If you are having this error trying to connect to a local MYSQL server (the same can happen to MariaDB):
ERROR 1130 Host 'localhost' is not allowed to connect to this MySQL server
Or
Access denied for user 'root'@''localhost'
No worries, it’s a common error in MySQL. It might be due to the crash of the user table in mysql database. Here is the fix:
1. Access to the MySQL configuration file and add the --skip-grant-tables option.
You should find the setting in the MySQL configuration file (my.ini on Windows, my.cnf on Unix/Linux) for the [mysqld] tag.
2. Restart the server
3. Then repair the user table:
CHECK TABLE user;
REPAIR TABLE user;
You can also access the user table and manually update the Host value for the root user. But note that using the --skip-grant-tables option, you disable all MySQL security so the GRANT commands are not allowed.
4. Remove --skip-grant-tables and restart the mysql server.
Now it should work properly.
In another case, you are trying to connect to a remote MySQL Server and running into this error:
Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server
Even though you are using the root account that logged in successfully to the localhost just now. That’s weird, isn’t it?
Well, the cause might be the default configuration your MySQL database is currently using. This configuration allows connections only from the 'root' user when coming from 'localhost' and not other IP address ranges.
A quick solution is to create a new user with admin access:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Then you should be able to connect to the MySQL Server with the new username/password. You might need to restart the MySQL Server before making a connection again.
However, it is not recommended as this creates a security vulnerability. So if you can, try to specify exactly the privileges you need and limit the accessibility of the user.
A preferred fix for this issue would be as follow:
1. First, check to confirm that the root user only has permission to connect in localhost:
SELECT user, host FROM user;
2. Then run this query:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'ip' IDENTIFIED BY 'password' WITH GRANT OPTION;
3. Then flush privileges to apple changes:
FLUSH PRIVILEGES;
Now you should be able to connect to the remote IP using the root user account.
Need a good MySQL GUI? TablePlus provides a native client that allows you to access and manage MySQL and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download for iOS
