Use DTS to migrate database accounts in the source database - Data Transmission Service

Data Transmission Service (DTS) allows you to migrate database accounts. When you configure a data synchronization or migration task, you can enable the account migration feature. This helps you migrate the accounts of the source database and their passwords and permissions to the destination database.

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 the source and destination database accounts used in a DTS task section of this topic.
Schema synchronization or migration is selected as the type of the synchronization or migration. That is to say, 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 synchronize or migrate database accounts only between MySQL databases, such as ApsaraDB RDS for MySQL instances and self-managed MySQL databases.
Note
If the destination database runs on an ApsaraDB RDS for MySQL V5.6 instance, you cannot migrate the accounts of the source database to the destination database.
The system accounts of the source database, such as root, mysql.infoschema, mysql.session, and mysql.sys, cannot be migrated to the destination database.
You cannot migrate the accounts that already exist in the destination database.
Unauthorized accounts cannot be migrated.
You cannot reset the password of an account when you migrate the account.
If the account to be migrated is a privileged account, the account will become a standard account without authorized databases after it is migrated to the destination database.

Permissions required for the source and destination database accounts used in a DTS task

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.

Account

Permission

Authorization method

Source database account

The SELECT permission on the mysql.user, mysql.db, mysql.columns_priv, and mysql.tables_priv metadata tables.

Use the privileged account to run the following commands to grant permissions to the source and destination databases.

Source database

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@'%';

Destination database

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

Note

Replace user in the commands with the source or destination account that you use to configure a DTS task.

Destination database account

The CREATE USER permission, the GRANT OPTION permission, and the SELECT permission on the mysql.user metadata table.

Procedure

In the Advanced Settings step, set the Whether to Migrate Accounts parameter to Yes and 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 by using a specific account.

For more information about subsequent operations, see Synchronize data between ApsaraDB RDS for MySQL instances and Migrate data between ApsaraDB RDS for MySQL instances.