Migrate database accounts passwords and permissions - Data Transmission Service

When you migrate or synchronize data to a destination database, you typically need to recreate all source database accounts manually—a time-consuming process that requires deep database knowledge and is prone to omission. Data Transmission Service (DTS) automates this by migrating source database accounts, including their passwords and privileges, to the destination database as part of the migration or synchronization task.

Prerequisites

Before you begin, make sure that:

The source and destination database accounts used in the DTS task have the required privileges. See Required privileges for the exact grant commands.
Schema Migration or Schema Synchronization is selected as the migration or synchronization type. 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.

Supported databases

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

Source database	Destination database
MySQL	MySQL, PolarDB for MySQL
PolarDB for MySQL	MySQL, PolarDB for MySQL

Required privileges

Before account migration can run, grant the following privileges to the DTS task accounts for both the source and destination databases.

Source database

Run the following commands using a privileged account. Replace user with the account you use to configure the DTS task.

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

Run the following commands using a privileged account. Replace user with the account you use to configure the DTS task.

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

Enable account migration

In the Advanced Settings step of the Create Task wizard, set Whether to Migrate Accounts to Yes.
Select the accounts to migrate.
Account names follow the format <username>@<host>, where <host> is the host from which the account can connect.
Complete the remaining task configuration. For details, see Overview of data synchronization scenarios or Overview of data migration scenarios.

Migration results

After the task runs, each account has one of the following outcomes:

Migrated: The account and all its privileges were fully migrated to the destination database.
Not migrated: The account was skipped. To view details, go to the User_Privilege tab in the Schema Migration module of your DTS task. Accounts that already exist in the destination database are listed as migrated accounts with the annotation user already exists. For all conditions that prevent migration, see Limitations.

Limitations

The following conditions prevent account migration or affect migration behavior. Check the conditions that apply to your database version and account types before running the task.

Destination database version

If the destination database is a MySQL 5.6 instance or a PolarDB for MySQL cluster, account migration is not supported.

MySQL 8.0 source restrictions

If the source database is MySQL 8.0, accounts with dynamic privileges cannot be migrated.

Privilege restrictions

Accounts with RELOAD, CREATE TABLESPACE, FILE, or ALL PRIVILEGES cannot be migrated to a MySQL or PolarDB for MySQL destination database.

Account type restrictions

System accounts (root, mysql.infoschema, mysql.session, mysql.sys) and built-in accounts cannot be migrated. Selecting these accounts causes the DTS task to return Access denied for user 'XXXX'.
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.
Unauthorized accounts cannot be migrated.

Behavior after migration

Account passwords cannot be reset during migration.
Privileged accounts become standard accounts in the destination database and lose database management permissions.
We recommend not migrating accounts of the destination Alibaba Cloud database instance that are accessed using only localhost or 127.0.0.1, as these accounts do not support data migration.

FAQ

Why can't I select an account?

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

Why is the account list empty?

The DTS task accounts do not have the required privileges on the source or destination database. Verify the privileges using the grant commands in Required privileges.