Data Management "not allowed to connect to this MySQL server" error occurs when adding a user-created MySQL database to DMS -

Disclaimer: This topic may contain information about third-party products. The information is for reference only. Alibaba Cloud does not make a guarantee in any form of the performance and reliability of the third-party products, and potential impacts of operations on these products.

Problem description

The following error occurs when you Data Management add a user-created MySQL database to DMS.

null,message from server:"Host 'x.x.x.x' is not allowed to connect to this MySQL server"

Causes

The IP address of Data Management DMS is not in the authorized user table of the user-created MySQL database.

Solution

Take note of the following items:

Before you perform high-risk operations such as modifying the configurations or data of Alibaba Cloud instances, we recommend that you check the disaster recovery and fault tolerance capabilities of the instance to ensure data security.

Before you modify the specifications or data of an Alibaba Cloud instance, such as an Elastic Compute Service (ECS) instance or an ApsaraDB RDS instance, we recommend that you create snapshots or enable backups for the instance. For example, you can enable log backups for an ApsaraDB RDS instance.

If you have granted specific users the permissions on sensitive information, such as usernames and passwords, or submitted sensitive information in the Alibaba Cloud Management Console, we recommend that you modify the sensitive information at the earliest opportunity.

You need to authorize the IP address of the Data Management DMS in the user-created MySQL database first, and then try to log on again. Regarding authorization, there are two options:

Allows a single IP address to access
and authorizes logon users in the user-created MySQL database. Allows denied IP addresses to access the user-created MySQL. To grant the permissions, perform the following steps:
Note: Data Management the external IP address of DMS to be changed regularly. This method is valid only for the IP address of a single DMS. If you need to log on to a user-created MySQL database by using DMS for a long time, see the next solution.
1. Record the IP address in the error message of the Data Management DMS.
2. Log on to the user-created MySQL database and execute the following SQL statement to authorize the denied IP addresses.
```
GRANT ALL PRIVILEGES ON *.* TO '[$DB_User]'@'[$Host]' IDENTIFIED BY '[$DB_Pass]' WITH GRANT OPTION;
```
  Note:
  - [$DB_User]: the logon user of the user-created MySQL database.
  - [$Host]: the IP address in the error message.
  - [$DB_Pass]: the password used to log on to the user-created MySQL database.
  - This command is for reference only. The specific permissions, database names, usernames, and IP addresses are subject to the actual situation.
3. Execute the following SQL statement to refresh permissions.
```
FLUSH PREVILEGES;
```
4. Add a user-created MySQL database in Data Management DMS and log on to the database.
Allows all IP addresses to access
and authorizes logon users in the user-created MySQL database. Allows all remote IP addresses to access user-created MySQL. To grant the permissions, perform the following steps:
Note: This solution allows all IP addresses to log on to a user-created MySQL database through a specified user. This poses security risks. Proceed with caution.
1. Log on to the user-created MySQL database and execute the following SQL statement to grant access permissions on all endpoints to a MySQL user.
```
GRANT ALL PRIVILEGES ON *.* TO '[$DB_User]'@'%' IDENTIFIED BY '[$DB_Pass]' WITH GRANT OPTION;
```
2. Execute the following SQL statement to refresh permissions.
```
FLUSH PREVILEGES;
```
3. Add a user-created MySQL database in Data Management DMS and log on to the database.

Ideal For

Data Management (DMS)