Create a database account for a self-managed MySQL database instance and grant privileges to the account

Background

The self-managed MySQL database instance can be a self-managed database in a virtual private cloud (VPC) or a self-managed database with a public IP address. In this example, a database account is created for a self-managed MySQL database instance. Specify this account for the Username field when you create a self-managed MySQL data source.

Create a database account for a self-managed MySQL database instance

When you create a MySQL data source, you can select an existing user as the data migration user. However, we recommend that you create a dedicated database user for your data migration task and grant the required privileges to the user.

1. Log on to the MySQL database.

2. Use the CREATE USER statement to create a user.

   CREATE USER '<user_name>'@'<host_name>' IDENTIFIED BY '<user_password>';

   Parameter	Description
   user_name	The user to be created.
   host_name	The host from which the user logs on to the database. To allow the user to log on to the database from any host, use a percent sign (%).
   user_password	The password of the user.

   For example, to create a user named test, allow the user to log on to the MySQL database from any host, and set the logon password to password, execute the following statement:

   CREATE USER 'test'@'%' IDENTIFIED BY 'password';

User privileges required when a MySQL database serves as the source data source

• The database user must have the read privilege on the database from which data is migrated. If the version of the MySQL database is 8.0, the user must also have the SHOW VIEW privilege.

  GRANT SELECT ON <database_name>.* TO '<user_name>';

• To implement incremental synchronization from the MySQL database, the database user must have the REPLICATION CLIENT, REPLICATION SLAVE, and SELECT *.* privileges.

  GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO '<user_name>' [WITH GRANT OPTION];GRANT SELECT ON *.* TO '<user_name>';

  Note

  • In the incremental synchronization phase, if the database user does not have the read privilege on all tables in the source data source, the task may be interrupted.

  • The WITH GRANT OPTION parameter is optional.

User privileges required when a MySQL database serves as the target data source

To implement incremental synchronization to the MySQL database, the database user must have the CREATE, CREATE VIEW, INSERT, UPDATE, and DELETE privileges.

GRANT <privilege_type> ON <database_name>.<table_name> TO '<user_name>'@'<host_name>' [WITH GRANT OPTION];