All Products
Search
Document Center

Log on to MySQL through DMS and the system prompts "Host'xxx.xxx.xxx.xxx'is not allowed to connect to this MySQL server" error

Last Updated: Feb 25, 2021

Problem description

The error "null, message from server: Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server" is returned when you log on to the database with the specified user name through data management service (DMS).

Cause

The mysql.us er table of the database sets the specified user to log on to MySQL only from this machine, resulting in the specified database user being unable to log on to MySQL remotely.

Solution

Alibaba Cloud reminds you that:

  • Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
  • You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
  • If you have authorized or submitted sensitive information such as the logon account and password in the Alibaba Cloud Management Console, we recommend that you modify such information in a timely manner.
  1. Run the following commands or a client command to log on to the MySQL database:
  2. Create a user that can remotely log on to MySQL. The authorized CIDR block varies with the network environment. See the following content for details:
    • Classic network
      The following SQL statement is used to create a user that can remotely log on to the MySQL database in classic network.
      grant all privileges on *.* to 'dms'@'120.55.177.0/24' identified by '[$Password]';
      grant all privileges on *.* to 'dms'@'121.43.18.0/24' identified by '[$Password]';
      grant all privileges on *.* to 'dms'@'10.153.176.106/24' identified by '[$Password]';
      grant all privileges on *.* to 'dms'@'10.137.42.136/24' identified by '[$Password]';
      Note:
      • [$Password] is the Password you set for the database user.
      • The CIDR block involved in this operation is a private CIDR block of the Alibaba Cloud classic network.


    • Endpoint for access from VPC
      In VPC network environment, execute the following SQL statement to create a user who can remotely log on to the MySQL database:
      grant all privileges on *.* to 'dms'@'100.104.175.0/24' identified by '[$Password]';
      grant all privileges on *.* to 'dms'@'100.104.72.0/24' identified by '[$Password]';
      grant all privileges on *.* to 'dms'@'100.104.5.0/24' identified by '[$Password]';
      grant all privileges on *.* to 'dms'@'100.104.205.0/24' identified by '[$Password]';
      Note:
      • [$Password] is the Password you set for the database user.
      • The CIDR block involved in this operation is a private CIDR block of the Alibaba Cloud VPC.


Scope

  • Data Management
  • ApsaraDB RDS for MySQL