Identifying the Connection Issue

When setting up a fresh MySQL instance on a test server, connection attempts may fail with the following error:

ERROR 1130 (HY000): Host 'node-01' is not allowed to connect to this MySQL server

This indicates that the server's host-based access control does not permit connections from the specific hostname. Since changing the system hostname is not an option, the database permissions must be altered. To do this, the server needs to be restarted with privilege validation disabled.

Stopping the Database Service

First, halt the running MySQL service to modify the startup parameters:

sudo systemctl stop mysqld

Configuring the Skip-Grant Option

Open the MySQL configuration file, typically found at /etc/my.cnf. Append the skip-grant-tables directive within the [mysqld] section to bypass authentication.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
skip-grant-tables

Restarting the Service

Apply the configuration changes by starting the MySQL daemon:

sudo systemctl start mysqld

Resetting Access Permissions

Connect to the database server. With grant tables disabled, you can log in without a password. Proceed to update the user permissions using one of the two methods below.

Direct Table Modification

Access the mysql system database and update the user table to allow connections from any remote host:

USE mysql;
UPDATE user SET host = '%' WHERE user = 'root' AND host = 'localhost';
SELECT host, user FROM user;

Using the GRANT Command

Alternatively, grant privileges to a specific user. Ensure you replace the placeholder username and password with secure values:

GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'%' IDENTIFIED BY 'P@ssw0rd123!' WITH GRANT OPTION;
FLUSH PRIVILEGES;