Use the native replication feature to create an ApsaraDB RDS instance as a secondary database

Prerequisites

• An RDS instance that runs MySQL 5.7 on RDS Basic Edition is created and the native replication feature is enabled for the RDS instance. For more information, see Enable the native replication feature.

• A privileged account is created for the RDS instance. For more information, see Create an account.

• The virtual private cloud (VPC) in which the primary database resides is connected to the VPC in which the native replication instance resides. For more information, see Configure network settings.

Procedure overview

You need to perform the following steps to create a secondary RDS for MySQL database:

1. Perform a full backup on the source database.

2. Import the full backup file to the native replication instance.

3. Run MySQL commands to create a replication task.

Solution 1: Use Percona XtraBackup to perform streaming backups, use OSS to store data, and then rebuild the native replication instance

Benefits

Native replication instances support Percona XtraBackup-based physical backups and the following features:

• Global transaction identifiers (GTIDs) in backup files can be automatically identified to automate point alignment and create replication tasks.

• You can upload backup files to Object Storage Service (OSS) buckets and enable server encryption to ensure data security.

• You can use a selected backup set to rebuild a native replication instance and resolve complex replication interruptions in an efficient manner.

Billing rules

• If you upload backup files to OSS buckets, you are charged OSS storage fees. For more information, see OSS billable items.

• When you create an RDS instance, you are charged based on the instance type and storage capacity that you use. For more information, see ApsaraDB RDS billable items.

Procedure

1. Use Percona XtraBackup to perform a streaming backup on the source database.

   The native replication feature is supported only for RDS instances that run MySQL 5.7. MySQL 5.7 supports Percona XtraBackup 2.4.

   If you want to compress backup files, you can use QuickLZ. You can run the xtrabackup --compress command to enable backup compression.

   1. Install Percona XtraBackup.

      yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
      # Check whether Percona XtraBackup is installed.
      yum list | grep percona
      percona-release enable-only tools release
      yum install -y percona-xtrabackup-24

   2. Perform a backup.

      The xtrabackup command is supported for the InnoDB storage engine. If the source database uses the MyISAM storage engine, use the innobackupex command.

      xtrabackup --backup \
        --host=127.0.0.1 \
        --port=3306 \
        -- user=<Username of the account of the self-managed MySQL database> \
        --password=<Password of the account> \
        --stream=xbstream \
        --compress > ./<Name of the backup file, such as backup_1206.xb>

2. Upload the backup file to your OSS bucket.

   You can use different tools to upload backup files, such as ossutil and OSS SDKs. In this example, ossutil is used.

   1. Install ossutil.

      yum install -y unzip
      sudo -v ; curl https://gosspublic.alicdn.com/ossutil/install.sh | sudo bash
      ossutil config

   2. Upload the backup file to your OSS bucket.

      ossutil -e <Endpoint of the OSS bucket> -i <AccessKey ID> -k <AccessKey Secret> cp <Backup file name> oss://<Bucket_name>/

3. Rebuild an RDS instance.

   1. Log on to the ApsaraDB RDS console and go to the Instances page. Find the RDS instance that you want to manage and click the instance ID. In the upper-right corner of the Basic Information page of the instance, click Rebuild Instance.

   2. On the page that appears, select a bucket and an object and click OK.

4. Create a native replication task.

   1. Create a replication account in the self-managed MySQL database.

      CREATE USER 'replica'@'%' IDENTIFIED BY 'Test123!';
      GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replica'@'%';
      FLUSH PRIVILEGES;

   2. Connect to the RDS instance. For more information, see Use a client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

      mysql -hEndpoint -PPort number -uUsername -pPassword

      Important

      You must use the privileged account of the RDS instance to connect to the RDS instance.

   3. Execute the following statements on the RDS instance to create a native replication task:

      # Create a native replication task.
      CHANGE MASTER TO MASTER_HOST = '<IP address of the host on which the self-managed MySQL database resides>', MASTER_USER = '<Username of the replication account of the self-managed MySQL database>',MASTER_PASSWORD ='<Password of the replication account of the self-managed MySQL database>', MASTER_PORT = 3306,MASTER_AUTO_POSITION = 1;
      # Start replication.
      START SLAVE;

   4. Debug the native replication task.

      Execute the following statement to check the replication status:

      SHOW SLAVE STATUS\G

      If a replication error occurs, troubleshoot the error based on the error message.

      If the replication is interrupted, go to the Error Logs tab of the Logs page to view the error details. For more information about the replication interruption errors and handling methods, see Configure network settings.

Solution 2: Use mysqldump to perform a logical backup, use DMS to import data, and then execute statements in DMS

Benefits

This solution is easy to use and can be completed in the corresponding consoles.

Billing rules

When you create an RDS instance, you are charged based on the instance type and storage capacity that you use. For more information, see ApsaraDB RDS billable items.

Procedure

1. Use mysqldump to perform a logical backup and obtain the SQL file.

   1. Set the SET GTID_PURGED parameter to off. ApsaraDB RDS does not allow you to modify the SET GTID_PURGED parameter. If you enable this parameter, an SQL replay error occurs.

   2. If you use mysqldump to create a secondary database replication task, you can add the --master-data=2 option to automatically generate a comment that contains the CHANGE MASTER statement. This eliminates the need to manually enter binary log information.

      mysqldump --all-databases \
          --single-transaction \
          --order-by-primary \
          --set-gtid-purged=off \
          --master-data=2 \
          -u local_user \
          -p local_password \
          -h 127.0.0.1
          -P 3306 > data.sql

2. Use Data Management (DMS) to import the logical backup file.

   1. Use DMS and the privileged account to log on to your RDS instance. For more information, see Use DMS to log on to an ApsaraDB RDS for MySQL instance.

   2. In the upper-left corner of the DMS console, move the pointer over the icon and choose All Features > Database Development > Data Change > Data Import.

      Note

      If you use the DMS console in normal mode, choose Database Development > Data Change > Data Import in the top navigation bar.

   3. In the Apply section of the Data Change Tickets page, select Large Data Import for Data Change Type in Application. Then, select the required RDS instance and upload the logical backup SQL file. For more information about how to configure other parameters, see Import data.

3. Execute the following statements in DMS to create a replication task.

   Important

   If you use DMS to import the logical backup file, independent binary logs and GTIDs are generated for the secondary database. If you switch the secondary database to the primary database, the excess GTIDs may be replicated to other databases. This may interrupt the replication. To prevent this issue, you can use one of the following methods:

   • Before you create a replication task, disable the native replication feature and then enable the feature again. This process triggers a RESET MASTER operation.

   • Insert empty transactions into other databases in the replication topology to overwrite redundant GTIDs and serve as placeholders.

   # Create a native replication task.
   CHANGE MASTER TO MASTER_HOST = '<IP address of the host on which the self-managed MySQL database resides>', MASTER_USER = '<Username of the replication account of the self-managed MySQL database>',MASTER_PASSWORD ='<Password of the replication account of the self-managed MySQL database>', MASTER_PORT = 3306,MASTER_LOG_FILE = '<SQL file name>',MASTER_LOG_POS = 190;
   # Start replication.
   START SLAVE;
   # View the replication status.
   SHOW SLAVE STATUS\G