Tutorial on how to create and use ApsaraDB RDS for MySQL native replication instances - ApsaraDB RDS

This topic describes how to create an ApsaraDB RDS for MySQL native replication instance or upgrade an existing instance to a native replication instance. It also describes the permissions and limits of native replication instances.

Prerequisites

Before you create a new native replication instance or upgrade an existing instance, ensure that the instance meets the following prerequisites. You can view the instance details on the Basic Information page.

Database version: MySQL 5.7 (minor version 20240930 or later) or 8.0 (minor version 20250531 or later)
Product series: Basic Edition
Billing methods: subscription and pay-as-you-go
Note
To use a Serverless native replication instance, you must first create a pay-as-you-go instance, enable native replication, and then change the billing method to Serverless.
Supported regions: China (Shanghai), China (Beijing), China (Shenzhen), China (Guangzhou), and China (Chengdu)
Important
ApsaraDB RDS native replication is currently available only in the regions listed above. Support for other regions is being rolled out. If you need this feature in other regions, please submit a ticket.

Billing

You are charged instance fees when you create a new instance with native replication enabled. Upgrading an existing instance to a native replication instance does not incur extra fees.
If you import full data by uploading a backup of your self-managed database to Object Storage Service (OSS), OSS storage fees are incurred for storing the backup file in OSS.

Native Replication Permissions

Privileged accounts for ApsaraDB RDS for MySQL native replication instances have additional operational permissions that are not available for standard ApsaraDB RDS for MySQL instances.

Native replication command permissions

Native replication instances allow privileged accounts to execute the following MySQL native replication commands.

Configure replication synchronization between the master and slave: CHANGE MASTER TO. For more information, see CHANGE MASTER TO.
Start the replication process: START SLAVE;. For more information, see START SLAVE.
Stop the replication process: STOP SLAVE;. For more information, see STOP SLAVE.
Reset the slave status: RESET SLAVE; to restart the replication process with the master. For more information, see RESET SLAVE.

Parameter modification permissions

Native replication instances allow privileged accounts to execute the following three SQL statements to modify runtime parameters.

Set the read-only mode for the database server.
```
SET GLOBAL read_only = ON/OFF;
```
When set to ON, the server enters read-only mode. When set to OFF, the read-only mode is disabled, and read/write operations are allowed.
Set whether the SQL statements executed in the current session are recorded in the binary log.
```
SET SESSION sql_log_bin = ON/OFF;
```
When set to ON, SQL operations in the session are recorded in the binary log. When set to OFF, SQL statements executed in the session are not recorded in the binary log.
Set the next value for the global transaction identifier (GTID).
```
SET SESSION GTID_NEXT = "your_gtid_value";
```

During maintenance operations such as cross-machine migration or an upgrade, the native replication command permissions and parameter modification permissions are temporarily revoked. This does not affect the instance's replication process. The permissions are restored after the maintenance task is complete.

Notes

ApsaraDB RDS for MySQL native replication instances have the following limits:

Limits

The instance becomes read-only.
Major version upgrades are not supported.
Upgrading the product series is not supported (such as from Basic Edition to High-availability Edition or Cluster Edition).
Switching the VPC is not supported.
Database and table recovery is not supported.
When you upgrade an existing instance to a native replication instance, a transient connection of about 30 seconds may occur. We recommend that you perform this operation during off-peak hours and ensure that your application has a reconnection mechanism.

Permission limits

Privileged account limits
Some of the SUPER permissions for the native replication feature are granted to the privileged account of the ApsaraDB RDS for MySQL instance. Only the privileged account can execute native commands such as CHANGE MASTER and START SLAVE.

Replication permission limits

When the native replication feature applies an external binary log, it first performs a permission check. Any operation that exceeds the following permissions is not applied and breaks the replication.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, 
DROP, RELOAD, PROCESS, REFERENCES, INDEX,
ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE,
REPLICATION CLIENT, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, CREATE USER, 
EVENT, TRIGGER ON *.* TO XXX;

System database limits
All operations on system databases are ignored. System databases include mysql, sys, information_schema, performance_schema, and __recyclebin__.
Note
Operations that indirectly modify system databases, such as CREATE USER and GRANT, are not ignored. However, the SQL statements must comply with the replication permission limits. Operations that exceed the granted permissions, such as GRANT super on *.* to xxx;, will break the replication.
GTID limits
In a MySQL native replication configuration, the GTID mode must be the same on both the master and slave databases. GTID must be enabled or disabled on both. ApsaraDB RDS for MySQL instances do not support disabling GTID mode. Therefore, to establish a replication relationship with an ApsaraDB RDS for MySQL instance as the slave, the external master database must also have GTID mode enabled.
Before you set up the replication relationship, run SHOW VARIABLES LIKE 'gtid_mode'; on the external master database to check if GTID is enabled. If GTID is not enabled, run SET GLOBAL gtid_mode=ON; to enable GTID mode on the external master.

Upgrade to a native replication instance

Enable native replication when creating a new instance

When you create a new ApsaraDB RDS for MySQL instance on the Standard Create page, expand More Configurations at the bottom of the page and turn on the Native Replication switch. Note that the native replication option appears only if the instance meets the prerequisites.

Upgrade an existing instance to a native replication instance

Important

When you upgrade an existing instance to a native replication instance, a transient connection of about 30 seconds may occur. We recommend that you perform this operation during off-peak hours and ensure that your application has a reconnection mechanism.

Go to the RDS Instances list, select a region, and click the ID of the target instance. The target instance must meet the prerequisites.
In the navigation pane on the left, click Native Replication. Then, click Enable Native Replication.
Carefully read the content in the pop-up window and click OK.
Note
If you have not granted the official ApsaraDB RDS service account permissions to access your OSS and to create and attach elastic network interfaces (ENIs), you must first select the corresponding checkboxes to grant these permissions.

Step 2: Set up data replication from a self-managed database to ApsaraDB RDS for MySQL

Install XtraBackup and perform a backup on the self-managed database

Install XtraBackup:

Install on CentOS

MySQL 5.7

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.29/binary/redhat/8/x86_64/percona-xtrabackup-24-2.4.29-1.el8.x86_64.rpm
yum localinstall percona-xtrabackup-24-2.4.29-1.el8.x86_64.rpm

MySQL 8.0

wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-31/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.35-31.1.el8.x86_64.rpm
yum localinstall percona-xtrabackup-80-8.0.35-31.1.el8.x86_64.rpm

Install on Ubuntu

Install XtraBackup

MySQL 5.7

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.29/binary/redhat/8/x86_64/percona-xtrabackup-24-2.4.29-1.el8.x86_64.rpm
yum localinstall percona-xtrabackup-24-2.4.29-1.el8.x86_64.rpm

MySQL 8.0

wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-31/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.35-31.1.el8.x86_64.rpm
yum localinstall percona-xtrabackup-80-8.0.35-31.1.el8.x86_64.rpm

Install qpress: Because XtraBackup installed on Ubuntu does not include qpress, you must install it separately.
```
sudo apt-get install -y qpress
```

Perform a backup:

The following three compression methods are based on the XtraBackup command and are suitable for instances that primarily use the InnoDB engine. If your database contains MyISAM tables, use the innobackupex command.

Method 1: Default qpress compression

xtrabackup --backup \
  --host=127.0.0.1 \
  --port=3306 \
  --user=<user_of_self-managed_MySQL> \
  --password=<password> \
  --stream=xbstream \
  --compress > ./<backup_file_name_such_as_backup_1206.xb>

Method 2: QuickLZ compression

This method requires you to use XtraBackup version 8.0.34-29 or earlier. For more information, see the official Percona XtraBackup tutorial.

xtrabackup --backup \
  --host=127.0.0.1 \
  --port=3306 \
  --user=<user_of_self-managed_MySQL> \
  --password=<password> \
  --stream=xbstream \
  --compress > ./<backup_file_name_such_as_backup_1206_qp.xb>

Method 3: External ZSTD compression

xtrabackup --backup \
  --host=127.0.0.1 \
  --port=3306 \
  --user=<user_of_self-managed_MySQL> \
  --password=<password> \
  --stream=xbstream \
  | zstd -q - > ./<backup_file_name_such_as_backup_1206.xb.zstd>

Import full data and automatically set up data replication

Note

Before you import data, configure the network settings to ensure network connectivity between the self-managed MySQL database and the ApsaraDB RDS for MySQL instance.

Method 1: Import a backup file from OSS

Upload the self-managed database backup to OSS
You can use different tools to upload backup files, such as ossutil or an OSS software development kit (SDK). This topic uses ossutil as an example.
Important
Ensure that the target OSS Bucket is in the same region as the RDS MySQL instance. Otherwise, RDS cannot retrieve the OSS backup file during the upload.
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 data to OSS.
```
ossutil -e <OSS_Endpoint> -i <your_AccessKeyId> -k <your_AccessKeySecret> cp <backup_file_name> oss://<Bucket_name>/
```

Import the backup file from OSS to RDS and automatically set up the replication link

Go to the RDS Instances list, select a region, and click the ID of the target instance.
In the navigation pane on the left, click Native Replication.

On the Native Replication page, click Import Full Data, configure the parameters, and click OK.

Configuration Category	Parameter	Description
Backup Upload Method (Required)	MySQL Version	The system automatically displays 5.7 or 8.0. No configuration is required.
	Method	Select Download From OSS.
	OSS Bucket	Select the OSS Bucket where the backup file of the self-managed database is stored. For more information about OSS Buckets, see Upload files.
	OSS File Name	Select the backup file for your self-built database from the OSS bucket. If the backup file is stored in a subdirectory of the OSS bucket, you must manually enter the full path to search. The following three backup file formats are supported: An `xbstream` file directly generated by XtraBackup. A `_qp.xb` file generated using the built-in quicklz compression of XtraBackup. An `xbstream` file compressed with zstd (with the `.xb.zst` suffix).
We recommend enabling automatic replication setup. The system will automatically set up the data replication link based on the following configuration. If you do not enable automatic replication setup, you must manually set up the replication link after importing the full data.
Automatic Replication Link Configuration (Optional)	Automatic Replication Setup	Turn on the switch to automatically set up the replication relationship from the self-managed MySQL database to the ApsaraDB RDS for MySQL instance.
	Replication Source IP	Enter the IP address of the source self-managed database.
	Replication Source Port	Enter the port number of the source self-managed database.
	Replication Source Account	Enter the account of the source self-managed database. The source account must have the `REPLICATION CLIENT` and `REPLICATION SLAVE` permissions.
	Replication Account Password	Enter the password for the account of the source self-managed database.

Method 2: Transfer the self-managed database backup to RDS

Install the backup-helper tool on the self-managed database and establish a backup transfer process

# Install the backup-helper tool
wget -O backup-helper https://mysql-backup-helper.oss-cn-beijing.aliyuncs.com/v1.0.0-alpha/backup-helper && chmod +x backup-helper

# Establish a backup transfer process using the tool (Make sure MySQL is running in the environment and the corresponding version of XtraBackup is installed based on the self-managed database version)
./backup-helper --backup --mode=stream --host=<MySQL_IP> --port=<MySQL_port> --user=<MySQL_account> --password=<MySQL_password>

Import data into RDS

Log on to the ApsaraDB RDS console, select a region at the top of the page, and click the instance ID.
In the navigation pane on the left, click Native Replication.