Data Transmission Service:Migrate database accounts

Background

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.

Supported databases

Usage notes

• If the destination database runs on a MySQL 5.6 instance or a PolarDB for MySQL cluster, 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.

• If the destination database is MySQL instance or PolarDB for MySQL cluster, the account migration feature is not supported for the accounts of the source database that have RELOAD, CREATE TABLESPACE, FILE, or ALL PRIVILEGES permission.

• 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.

• The account of the destination Alibaba Cloud database instance that is accessed by using only localhost or 127.0.0.1 does not support data migration, so we recommend not migrating the account.

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.