Unable to register remote MySQL/MariaDB server in Plesk: Host is not allowed to connect to this MySQL server

Symptoms

• Upon the attempt to register a database server in Plesk > Tools & Settings > Database Servers, the following error is displayed:

  Error: The test connection to the database server has failed because of network problems:

  Host '203.0.113.2' is not allowed to connect to this MySQL server

  ---

  Error: The test connection to the database server has failed because of network problems:

  Unknown error 1130

• Only local users are registered in the database server:

  mysql> SELECT User, Host FROM mysql.user;
  +------------------+-----------+
  | User             | Host      |
  +------------------+-----------+
  | mysql.infoschema | localhost |
  | mysql.session    | localhost |
  | mysql.sys        | localhost |
  | root             | localhost |
  +------------------+-----------+
  4 rows in set (0.00 sec)

Cause

'root' user is not allowed to access the MySQL/MariaDB database server remotely.

Resolution

Log in to the MySQL/MariaDB on the remote database server and create a user who is allowed to access database server remotely from 203.0.113.2:

mysql> CREATE USER 'root'@'203.0.113.2' IDENTIFIED BY 'root_password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'203.0.113.2' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

Or a database user who can access the server from any host:

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root_password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;