:Migrate data from an ApsaraDB RDS for MySQL Enterprise Edition instance to a PolarDB-X 2.0 Standard Edition instance

Introduction

To migrate data from an ApsaraDB RDS for MySQL Enterprise Edition instance to a PolarDB-X 2.0 Standard Edition instance, create a destination PolarDB-X 2.0 Standard Edition instance and then synchronize data from the source ApsaraDB RDS for MySQL Enterprise Edition instance to the destination PolarDB-X 2.0 Standard Edition instance. After the migration, the destination PolarDB-X 2.0 Standard Edition instance contains the same account information, databases, IP address whitelists, and required parameter settings as the source ApsaraDB RDS for MySQL Enterprise Edition instance.

To synchronize data over DTS, create a DTS data synchronization task to synchronize the database and table schemas and full data of the source ApsaraDB RDS for MySQL Enterprise Edition instance to the destination PolarDB-X 2.0 Standard Edition instance and then continue to synchronize incremental data. For more information, see What is Data Transmission Service?.

To migrate data from an ApsaraDB RDS for MySQL Enterprise Edition instance, make sure that the instance meets the following requirements for the database engine and storage types.

Benefits

The migration feature provides the following benefits:

• The endpoints can be switched between the source and destination instances. This way, you can switch over your business from the source instance to the destination PolarDB-X 2.0 Standard Edition instance without the need to modify the connection configuration of your application.

• The data migration feature is free of charge.

• No data is lost during migration.

• Incremental data migration is supported.

• Hot migration is supported. Only one transient connection occurs during data migration. The transient connection occurs when you switch over your business from the source ApsaraDB RDS for MySQL Enterprise Edition instance to the destination PolarDB-X 2.0 Standard Edition instance.

• You can manually switch the endpoints between the source and destination instances. Do not perform write operations during the endpoint switch process. You can control the switch duration based on your business requirements. In most cases, the switch is complete within 10 minutes.

• If migration fails, you can roll back the migration. The rollback can be completed within 10 minutes.

Prerequisites

• The source ApsaraDB RDS for MySQL Enterprise Edition instance uses the InnoDB or X-Engine storage engine.

• A privileged account is created if Database Proxy (Safe Mode) is enabled for the source ApsaraDB RDS for MySQL Enterprise Edition instance. For information about how to create a privileged account, see Create an account. You can also switch the source instance to high-performance mode. For more information, see [Product changes/Feature changes] The network connection mode of an ApsaraDB RDS instance is upgraded.

Limits

• You can migrate data from an ApsaraDB RDS for MySQL Enterprise Edition instance only to a PolarDB-X 2.0 Standard Edition instance that runs the same or a higher database engine version.

• IPv6 addresses are not supported in switchover with endpoints.

• When you synchronize data from the source instance to the destination instance by using a DTS data synchronization task, take note of the following limits:

  • Cross-region data synchronization is not supported.

  • Do not modify the parameters of the source ApsaraDB RDS for MySQL Enterprise Edition instance during migration.

  • Only databases, tables, views, stored procedures, and functions can be migrated.

    Note

    The account information, IP address whitelists, and required parameters of the source ApsaraDB RDS for MySQL Enterprise Edition instance are migrated in the PolarDB-X console instead of by using DTS.

  • The following table describes the limits on the source ApsaraDB RDS for MySQL Enterprise Edition instance.

    Category

    Description

    Limits on the source database

    The tables to be synchronized must have PRIMARY KEY or UNIQUE constraints, and all fields must be unique. Otherwise, the destination database may contain duplicate data records. If you select tables as the objects to be synchronized and you need to edit the tables (such as renaming tables or columns) in the destination database, up to 1,000 tables can be synchronized in a single data synchronization task. If you run a task to synchronize more than 1,000 tables, an error is returned. In this case, we recommend that you split the tables and configure multiple tasks to synchronize the tables, or configure a task to synchronize the entire database. The following requirements for binary logs must be met: The binary logging feature is enabled and the binlog_row_image parameter is set to full. For more information about how to enable the binary logging feature, see Modify instance parameters. Otherwise, an error is reported during the precheck and data synchronization tasks cannot start. For an incremental data synchronization task, the binary logs of the source database must be retained for at least 24 hours. For a full and incremental data synchronization task, the binary logs of the source database must be retained for at least seven days. After the full data synchronization is complete, you can set the retention period to more than 24 hours. Otherwise, DTS may fail to obtain the binary logs and the task may fail. In extreme circumstances, data inconsistency or loss may occur. Make sure that the retention period of binary logs is configured based on the specified requirements. Issues that occur due to non-compliance with the requirements are not covered under the DTS SLA guarantees. For more information about how to manage the binary logs of the source ApsaraDB RDS for MySQL Enterprise Edition instance, see Manage binary log files.

  • The following table describes the limits on SQL statements.

    Operation type	SQL statement
    DML	INSERT, UPDATE, and DELETE
    DDL	ALTER TABLE and ALTER VIEW CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, and CREATE VIEW DROP INDEX and DROP TABLE RENAME TABLE TRUNCATE TABLE

  • The following table describes the other limits.

    Category

    Description

    Other limits

    Before you synchronize data, evaluate the impact of data synchronization on the performance of the source and destination databases. We recommend that you synchronize data during off-peak hours. During initial full data synchronization, DTS uses read and write resources of the source and destination databases. This may increase the loads on the database servers. During full data synchronization, concurrent INSERT operations cause fragmentation in the tables of the destination database. After full data synchronization is complete, the size of the tablespace used by the destination database is larger than that used by the source database. During data synchronization, data inconsistency between the source and destination databases occurs if data from other sources is written to the destination database. For example, if you use Data Management (DMS) to execute online DDL statements during data synchronization, data loss may occur in the destination database. By default, DTS disables FOREIGN KEY constraints for the destination database in a data synchronization task. In this case, specific operations such as the cascade and delete operations of the source database are not synchronized to the destination database.

Usage notes

The endpoints of the source ApsaraDB RDS for MySQL Enterprise Edition instance and the destination PolarDB-X 2.0 Standard Edition instance must have the same SSL configuration.

• If you select the Switchover with Endpoints option and SSL is enabled for the endpoints of the source ApsaraDB RDS for MySQL Enterprise Edition instance, make sure that SSL is enabled for the endpoints of the destination PolarDB-X 2.0 Standard Edition instance.

• If SSL is disabled for the endpoints of the source ApsaraDB RDS for MySQL Enterprise Edition instance, make sure that SSL is disabled for the endpoints of the PolarDB-X 2.0 Standard Edition instance.

Billing rules

Data synchronization during migration is free of charge. You are charged only for the destination PolarDB-X 2.0 Standard Edition instance.

Backup policies

The backup schedule of the destination PolarDB-X 2.0 Standard Edition instance is the same as that of the source ApsaraDB RDS for MySQL Enterprise Edition instance.

After migration is complete, you can modify the backup policies of the PolarDB-X 2.0 Standard Edition instance.

Switchover with endpoints

If you select the Switchover with Endpoints option, the endpoints of the source ApsaraDB RDS for MySQL Enterprise Edition instance and the destination PolarDB-X 2.0 Standard Edition instance are automatically switched during migration. After the switchover, you can connect to the destination PolarDB-X 2.0 Standard Edition instance without the need to modify the configuration of your application. The following figure shows the exchange of the endpoints between the source ApsaraDB RDS for MySQL Enterprise Edition instance and the destination PolarDB-X 2.0 Standard Edition instance after you select the Switchover with Endpoints option.

To use the switchover with endpoints feature, take note of the following points:

• The switchover with endpoints feature switches only endpoints between the source ApsaraDB RDS for MySQL Enterprise Edition instance and the destination PolarDB-X 2.0 Standard Edition instance. The vSwitches and VIPs are not switched.

• Endpoints can be switched only if the source ApsaraDB RDS for MySQL Enterprise Edition instance and the destination PolarDB-X 2.0 Standard Edition instance have the endpoints. By default, only the primary private endpoints are switched.

• Endpoints that use IPv6 addresses are not switched.

• The switchover with endpoints feature does not switch ports between the source ApsaraDB RDS for MySQL Enterprise Edition instance and the destination PolarDB-X 2.0 Standard Edition instance. Make sure that the two instances use the same connection port. By default, port 3306 is used. For information about how to change the port, see View and manage instance endpoints and ports.

• After the endpoints are switched, the original endpoint entries may remain in the Domain Name System (DNS) cache before the entries expire. During this period, you may fail to connect to the PolarDB-X 2.0 Standard Edition instance, or the instance may allow only read operations. To resolve the issue, we recommend that you refresh the DNS cache.

  Note

  To resolve the issue, we recommend that you refresh the DNS cache based on the specific operating system and version of your server. To refresh the DNS cache, perform the following steps. In this example, Alibaba Cloud Linux 2/3 is used.

  1. Check whether the systemd-resolved service is running. If the service is running, Active: active (running) is displayed.

     sudo systemctl status systemd-resolved

  2. Refresh the DNS cache of the systemd-resolved service.

     sudo systemd-resolve --flush-caches

• If you want to access the PolarDB-X 2.0 Standard Edition instance by using DMS after you switch the endpoints, log on to the instance by using the latest version of DMS and the ID of the PolarDB-X 2.0 Standard Edition instance.