Error: Host is not allowed to connect to this MySQL server

Problem

When you try to connect to MySQL remotely, you get the following error:

Host <hostname or IP> is not allowed to connect to this MySQL server

In MySQL, hosts are allowed (whitelisted) per user. So this error means the user you’re trying to connect with doesn’t have your remote host whitelisted. You’re likely trying to connect with the ‘root’ user, which by default only allows connections from localhost. Here’s what this looks like in the user configuration:

MySQL Workbench, Users and Privileges - Showing the root user only has localhost whitelisted — *MySQL Workbench – Users and Privileges*

To fix this, add a user that allows your host. To allow any host, use the wildcard symbol %. You can add a user from the command line or with a UI client (such as MySQL Workbench or phpMyAdmin). I’ll show an example below of adding a user that allows any host.

Note: I don’t recommend modifying the ‘root’ user.

Solution – Add a user that allows any host

Let’s say you’re trying to connect to an internal MySQL server being used in a dev environment, so you want to add a user that allows any host and is granted all privileges.

First, get on the server that is running MySQL. Then run the MySQL Command Line Client (mysql.exe).

Add a user with a password and allow all hosts:

mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'password123';Code language: Bash (bash)

Note: % is a wildcard symbol that means all hosts. So @’%’ means you’re allowing this user to connect from any host.

Grant all privileges to the user for all schemas:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;Code language: Bash (bash)

Note: WITH GRANT OPTION enables the user to grant privileges to other users.

Now you should be able to connect from the remote host with the user / password you just created. If you’re still getting the connection error, you may need to flush the cached privileges (I didn’t need to):

mysql> FLUSH PRIVILEGES;

Starting mysql.exe manually

If you’re getting an error like ‘mysql.exe is unknown’ or if you can’t find the MySQL Command Line Client shortcut, then you can start it manually using the full path and passing in some parameters.

Here’s an example of doing this on Windows:

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" "--defaults-file=C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" "-uroot" "-p"Code language: Bash (bash)