[Fixed] Host is not allowed to connect to this MySQL server

Application developers may encounter difficulties connecting to a database hosted on a server other than the local server. In this article, we will resolve a common error that occurs when connecting to a MySQL database remotely from outside the network or from another host.

SQLSTATE[HY000] [1130] Host '172.19.0.11' is not allowed to connect to this MySQL server error occurs when the connection request is rejected by the MySQL server. By default, the MySQL server only accepts connections from local hosts and not from other hosts.

To enable the remote connections, we need to do the following steps –

1. Enable remote connections from the config
2. Create a new user and allow it to connect to the database server from the specific host (or all hosts)
3. Flush privileges

Enable Remote connections from MySQL config

Open the MySQL config using your favorite text editor, such as nano. The MySQL file is usually located at /etc/mysql/my.cnf or /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf. The location of the MySQL configuration file depends on the version of MySQL you’re using. Check all of these locations to see if you can find the configuration file. Please join our Discord server and let us know if you haven’t found the config file yet. Perhaps we can assist you.

Once found the config file, open it and comment out the line bind-address = 127.0.0.1.

Just add # before the line to comment it out.

# bind-address = 127.0.0.1

Create new MySQL user

We create a mysql user with the host as ‘localhost’ for local use, but when adding a user for remote connections, we must replace the localhost with the IP address of the remote computer.

Login to MySQL as root –

sudo mysql

Or

mysql -u root -p

Depending on the method you select, you will be prompted to enter your password. If you’re using the second method, enter the MySQL root user’s password, or the sudo password if you’re logging in with sudo.

Once in the MySQL command-line, create a new user –

> CREATE USER 'username'@'ip-address' IDENTIFIED BY 'set-password';

You should see the following message if the new user is created –

Query OK, 0 rows affected (0.02 sec)

We will now give the newly created user permissions to manage a specific database on the server. We can also give this user access to all of the databases on the server, but this is not recommended. I recommend that you create a new database(s) for your application(s) and grant this user permissions to manage the database(s).

> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'ip-address';

Once done, please flush the privileges for the changes to take effect.

> FLUSH PRIVILEGES;

Allow all remote connections

As in the preceding command, I instructed to replace the ip-address with the remote computer’s IP address. Only connections from that remote computer will be permitted. However, we can also use the ‘%’ wildcard to allow all connections, regardless of whether they are from your computer or from that basement guy who needs access to your database for personal reasons. 😉 They will, however, need to enter the correct credentials to access the database.

> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'%';

If your database server is in production, it is highly recommended to not use ‘%’ wildcard.

Allow connections from a range of IP address

If the remote servers are on the same network, their IP addresses can easily be allowed to allow remote connections without the need for multiple MySQL users.

> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'172.19.0.*';

Notice how the last octet of the IP address has been replaced with * in the above command. This allows all servers with that IP address to begin with 172.19.0.

The post [Fixed] Host is not allowed to connect to this MySQL server appeared first on Linux Tutorials, FOSS Reviews, Security News.

Source: Linux Tutorials, FOSS Reviews, Security News