Data Transmission Service:Migrate database accounts

Background information

After you synchronize or migrate data from the source database to the destination database, you may need to manually create the accounts of the source database and grant permissions to the accounts in the destination database. To complete this process, you must have professional knowledge on databases. This process is time-consuming and prone to omission. As a result, your business may be affected.

Prerequisites

• The accounts of the source and destination databases used in the DTS task are granted the required permissions. For more information, see the Permissions required for database accounts section of this topic.

• Schema synchronization or migration is selected as the type of the synchronization or migration. That is, in the Configure Objects and Advanced Settings step of the Create Task wizard, Schema Synchronization is selected for the Synchronization Types parameter, or Schema Migration is selected for the Migration Types parameter.

Usage notes

• You can migrate database accounts only between MySQL databases, from MySQL databases to PolarDB for MySQL clusters, or between PolarDB for MySQL clusters.

  Note

  • The MySQL databases can be ApsaraDB RDS for MySQL instances or self-managed MySQL databases.

  • If the destination database runs on an ApsaraDB RDS for MySQL 5.6 instance, the accounts of the source database cannot be migrated to the destination database.

  • If the source database runs MySQL 8.0, the accounts with dynamic permissions cannot be migrated.

• The system accounts, such as root, mysql.infoschema, mysql.session, and mysql.sys, and built-in accounts of the source database cannot be migrated to the destination database. If you select system accounts or built-in accounts as the database accounts to be migrated, the error message Access denied for user 'XXXX' is returned for the DTS task.

• Accounts that already exist in the destination database cannot be migrated. If a DTS task is restarted or multiple DTS tasks are running, accounts to be migrated may exist in the destination database. On the User_Privilege tab for the Schema Migration module of your DTS task, the accounts that exist in the destination database are listed as migrated accounts. The annotation user already exists appears in the statements for creating the accounts in the destination database.

• Unauthorized accounts cannot be migrated.

• The password of an account cannot be reset when you migrate the account.

• If the account to be migrated is a privileged account, the account becomes a standard account that is not authorized to manage databases after it is migrated to the destination database.

Permissions required for database accounts

Before you migrate the accounts of the source database, make sure that the source and destination database accounts that are used when you configure the DTS task have the following permissions. Privileged accounts meet the requirements.

grant SELECT on mysql.user to user@'%';
grant SELECT on mysql.db to user@'%';
grant SELECT on mysql.columns_priv to user@'%';
grant SELECT on mysql.tables_priv to user@'%';

grant SELECT on mysql.user to user@'%';
grant CREATE USER on *.* to user@'%';
grant GRANT OPTION on *.* to user@'%';

Procedure

1. In the Advanced Settings step, set Whether to Migrate Accounts to Yes.

2. Select the accounts that you want to migrate based on your business requirements.

   Note

   The account name is in the <username>@<host> format. The <host> variable specifies the host that can be logged on to by using the specified account.

3. Configure other parameters that are required for the DTS task. For more information, see Overview of data synchronization scenarios or Overview of data migration scenarios.

FAQ

• Why am I unable to select an account?

  The account may be a system account, or an account with the same name already exists in the destination database.

• Why is the account list empty?

  The accounts that you specify may not have the required permissions. Make sure that the accounts of the source and destination databases used in the DTS task are granted the required permissions.