Migrate the data of a self-managed MySQL 5.7 or MySQL 8.0 instance to an ApsaraDB RDS for MySQL instance - ApsaraDB RDS

This topic describes how to migrate the data of a self-managed MySQL instance to an ApsaraDB RDS for MySQL instance that runs the same MySQL version as the self-managed MySQL instance. You can perform a full backup on the self-managed MySQL instance, upload the full backup file to an Object Storage Service (OSS) bucket, import the full backup file from the OSS bucket into a temporary RDS instance, and then restore the data from the full backup file to the destination RDS instance.

Prerequisites

An Alibaba Cloud account is created.
The self-managed instance meets the migration conditions. For more information, see Appendix 5: Limits.
An OSS bucket is created in the region where the destination RDS instance resides. For more information, see Create buckets.
Note The OSS bucket that you create must reside in the same region as the destination RDS instance.

Migration process

The migration process consists of the following steps:

Step 1: Install Percona XtraBackup

Step 2: Install MySQL Backup Helper

Step 3: Back up the self-managed MySQL instance and migrate the backup data to the RDS instance

Environment

In this topic, the self-managed MySQL instance is deployed on an Elastic Compute Service (ECS) instance. The image that is used to create the self-managed MySQL instance runs CentOS Linux V8.3.2011. For more information, see Create an instance by using the wizard.

Billing

After you import a full backup file into an RDS instance, you are not charged for the storage of the file within 24 hours. If the file is stored for longer than 24 hours, you are charged storage fees. For more information, see Storage fees.

Note The preceding billing rules take effect on July 15, 2021.

Step 1: Install Percona XtraBackup

Percona XtraBackup is developed by Percona to help you back up MySQL databases without impacting uptime. Percona XtraBackup is compatible with various storage engines. If you use an Ubuntu operating system, see Appendix 1: Install Percona XtraBackup in an Ubuntu operating system.

Install the Percona repository.

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Enable the Percona repository.

percona-release enable-only tools release

Install Percona XtraBackup 2.4 or Percona XtraBackup 8.0.
```
yum install -y percona-xtrabackup-24  # Install Percona XtraBackup 2.4. 
yum install -y percona-xtrabackup-80 # Install Percona XtraBackup 8.0. 
```
Note You must select the version of Percona XtraBackup based on the MySQL version.
- MySQL 5.7: Install Percona XtraBackup 2.4.
- MySQL 8.0: Install Percona XtraBackup 8.0.

Step 2: Install MySQL Backup Helper

Prerequisites

The Go programming language is installed. If Go is not installed, run the following command in the CLI to install Go:
```
yum install -y go
```
The UnZip utility is installed. If UnZip is not installed, run the following command in the CLI to install UnZip:
```
yum install -y unzip
```

Note The preceding commands are supported only for CentOS. If you use an Ubuntu operating system, see Appendix 2: Install Go and UnZip in an Ubuntu operating system.

Download the MySQL Backup Helper source package.
```
wget https://github.com/aliyun/mysql-backup-helper/archive/refs/heads/master.zip
```
Note You can download the MySQL Backup Helper source package from the mysql-backup-helper page.
Decompress the MySQL Backup Helper source package.
```
unzip master.zip
```
Go to the mysql-backup-helper-master folder and compile the main.go file into an executable file named backup_helper.
```
cd mysql-backup-helper-master
go build -a -o backup_helper main.go
```
Go to the oss_stream folder and compile the oss_stream.go file into an executable file named oss_stream.
```
cd oss_stream
go build -a -o oss_stream oss_stream.go
```

Step 3: Back up the self-managed MySQL instance and migrate the backup data to the RDS instance

Use MySQL Backup Helper to check whether the self-managed MySQL instance supports backups.

cd ~/mysql-backup-helper-master && ./backup-helper -host <The IP address of the host in which the self-managed MySQL instance resides> -port <The port number that is used to connect to the self-managed MySQL instance> -user <The username of the root account that is used to connect to the self-managed MySQL instance> --password <The password of the root account that is used to connect to the self-managed MySQL instance>

After the self-managed MySQL instance passes the check, perform a full backup on the instance and upload the full backup file to your OSS bucket. If no OSS bucket is created, create one. For more information, see the "Prerequisites" section of this topic.
```
innobackupex --backup --host=<The IP address of the host in which the self-managed MySQL instance resides> --port=<The port number that is used to connect to the self-managed MySQL instance> --user=<The username of the root account that is used to connect to the self-managed MySQL instance> --password=<The password of the root account that is used to connect to the self-managed MySQL instance> --stream=xbstream --compress <The temporary directory that is used to store the full backup file> | ./mysql-backup-helper-master/oss_stream/oss_stream -accessKeyId <The AccessKey ID of your Alibaba Cloud account> -accessKeySecret <The AccessKey secret of your Alibaba Cloud account> -bucketName <The name of your OSS bucket> -endpoint <The endpoint that is used to connect to your OSS bucket> -objectName <The name of the full backup file after the file is uploaded as an object to your OSS bucket>
```
Example:
```
innobackupex --backup --host=127.0.0.1 --port=3306 --user=root --password=Aa123456@ --stream=xbstream --compress /root/mysql/data | ./mysql-backup-helper-master/oss_stream/oss_stream -accessKeyId LTAI5tCqY18jvvKk******** -accessKeySecret 4A5Q7ZVzcYnWMQPysXFxld******** -bucketName test -endpoint oss-********.aliyuncs.com -objectName backup_qp.xb
```
Note
- If the full backup is performed on a self-managed MySQL 8.0 instance, you must replace innobackupex in the preceding command with xtrabackup.
- The status of the self-managed MySQL instance during the full backup affects the time that is required to complete the full backup. For example, if a large number of redo log records are generated from a large number of write operations or large transactions are run during the full backup, the time that is required increases. When the full backup is complete, the system displays the "completed OK !" message.
- The amount of data in the self-managed MySQL instance also affects the time that is required to complete the full backup. A larger amount of data requires a longer period of time. If the self-managed MySQL instance has a large amount of data, we recommend that you run the nohup command to perform the full backup in the background. This way, you can prevent interruptions to the full backup in the event of unexpected logoffs. Sample statement:
  nohup sh -c 'innobackupex --backup --host=127.0.0.1 --port=3306 --user=root --password=Aa123456@ --stream=xbstream --compress /root/mysql/data | ./mysql-backup-helper-master/oss_stream/oss_stream -accessKeyId LTAI5tCqY18jvvKk******** -accessKeySecret 4A5Q7ZVzcYnWMQPysXFxld******** -bucketName test -endpoint oss-ap-southeast-1.aliyuncs.com -objectName backup_qp.xb' &
- If your OSS bucket is temporarily inaccessible, we recommend that you save the full backup file to your computer. When your OSS bucket is restored to normal, you can upload the full backup file to your OSS bucket. For more information, see Appendix 3: Perform a full backup, save the full backup file to your computer, and then upload the full backup file to your OSS bucket.
- After you upload the full backup file to your OSS bucket, you can log on to the OSS console to check whether the upload is successful. If the upload failed, you can repeat this step.
Log on to the ApsaraDB RDS console. In the top navigation bar, select the region of the RDS instance to which you want to restore the data of the self-managed MySQL instance.
In the left-side navigation pane, click Backups.
On the tab that appears, click Import Backup. In the wizard that appears, read the messages that are displayed and click Next until you enter the 3. Import Data step.
Select the name of your OSS bucket from the OSS Bucket drop-down list. In the File Name section, select the full backup file that you want to import. Select the zone to which you want to import the full backup file from the Zone drop-down list. Then, click OK.
Note
- If ApsaraDB RDS is not authorized to access OSS resources, click Authorize. In the lower-left corner of the page that appears, click Confirm Authorization Policy.
- ApsaraDB RDS creates a task to check the backup file. You can view the status of the task on the User Backups page. When Status of the task changes from Verifying to Completed, the task is complete. The time that is required to complete the task varies based on the status of the self-managed MySQL instance during the full backup. For example, if a large number of redo log records are generated from a large number of write operations or large transactions are run during the full backup, the time that is required to complete the task increases.
Find the full backup file. Then, click Restore in the Actions column to the right of the Backup ID/Name column.

Configure the following parameters and click Next: Instance Configuration.


Parameter	Description
Zone of Primary Node	The zone to which the primary RDS instance belongs. Note If you did not select a zone for the OSS bucket that stores the full backup file when you import the file, this parameter is displayed. If you selected a zone for the OSS bucket that stores the full backup file when you import the file, this parameter is not displayed.
Storage type	ESSD PL1: an enhanced SSD (ESSD) of performance level 1 (PL1). Standard SSD: A standard SSD is an elastic block storage device that is developed based on the distributed storage architecture. You can store data on standard SSDs to separate computing from storage. Note For more information, see Storage types.
Instance Type	General-purpose (Entry-level): A general-purpose RDS instance exclusively occupies the allocated memory and I/O resources. However, it shares CPU and storage resources with the other general-purpose RDS instances that are deployed on the same host. Note Each instance type supports a specific number of CPU cores, memory capacity, maximum number of connections, and maximum IOPS. For more information, see Primary ApsaraDB RDS instance types.
Capacity	The maximum amount of storage that is provisioned to store data files, system files, binary log files, and transaction files in the new RDS instance. You can adjust the storage capacity at a step size of 5 GB.

Configure the following parameters and click Next: Confirm Order.


Parameter	Description
Network Type	The network type of the RDS instance. Select VPC. A virtual private cloud (VPC) is an isolated network that provides higher security and better performance than the classic network. If you select the VPC network type, you must also specify the VPC and vSwitch of Primary Node parameters. Note The RDS instance and the Elastic Compute Service (ECS) instance that you want to connect must reside in the same VPC. Otherwise, the RDS instance and the ECS instance cannot communicate over an internal network.
Parameter Template	The parameter template that is used by the RDS instance. You can select a system parameter template or a custom parameter template. For more information, see Use a parameter template to configure the parameters of ApsaraDB RDS for MySQL instances.
Time Zone	The time zone of the RDS instance.
Table Name Case Sensitivity	Specifies whether table names in the RDS instance are case-sensitive. If table names in the self-managed MySQL instance are case-sensitive, we recommend that you select Case-sensitive to facilitate data migration between the RDS instance and the self-managed MySQL instance.

Confirm the configuration of the RDS instance in the Parameters section, configure the Purchase Plan parameter, read and select Terms of Service, and then click Pay Now to complete the payment.
Note ApsaraDB RDS requires 1 minute to 5 minutes to create the RDS instance. Wait until the instance is created.

Appendix 1: Install Percona XtraBackup in an Ubuntu operating system

Download the latest Percona package.

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb

Install the downloaded Percona package.

sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb

Enable the Percona repository.

percona-release enable-only tools release

Update the list of software applications in the local repository.
```
apt-get update
```
Install Percona XtraBackup 2.4 or Percona XtraBackup 8.0.
```
sudo apt-get install -y percona-xtrabackup-24 #Install Percona XtraBackup 2.4. 
sudo apt-get install -y percona-xtrabackup-80 #Install Percona XtraBackup 8.0. 
```
Note You must select the version of Percona XtraBackup based on the MySQL version.
- MySQL 5.7: Install Percona XtraBackup 2.4.
- MySQL 8.0: Install Percona XtraBackup 8.0.
Install the qpress tool.
```
sudo apt-get install -y qpress
```
Note The qpress tool is used to unzip the backup files that are generated by Percona XtraBackup. If you are using an Ubuntu operating system, Percona XtraBackup is not integrated with the qpress tool and you must install this tool.

Note If a message similar to "The following packages have unmet dependencies" is displayed when you perform any of the preceding steps, run the apt-get -f install command to install the required dependency packages. Then, perform the step again.

Appendix 2: Install Go and UnZip in an Ubuntu operating system

Install the Go programming language.

sudo apt-get install -y software-properties-common
sudo add-apt-repository ppa:longsleep/golang-backports
sudo apt-get update
sudo apt-get install -y golang-go

Install the UnZip utility.
```
sudo apt-get -y install unzip
```

Appendix 3: Perform a full backup, save the full backup file to your computer, and then upload the full backup file to your OSS bucket

Perform a full backup on the self-managed MySQL instance and save the full backup file to your computer.

innobackupex --backup --host=<The IP address of the host in which the self-managed MySQL instance resides> --port=<The port number that is used to connect to the self-managed MySQL instance> --user=<The username of the root account that is used to connect to the self-managed MySQL instance> --password=<The password of the root account that is used to connect to the self-managed MySQL instance> --stream=xbstream --compress <The temporary directory that is used to store the full backup file> > /<The directory that is used to store the full backup file>/<The name of the full backup file>_qp.xb

Example:

innobackupex --backup --host=127.0.0.1 --port=3306 --user=root --password=Aa123456@ --stream=xbstream --compress /root/mysql/data > /root/backup_qp.xb

Note If the full backup is performed on a self-managed MySQL 8.0 instance, you must replace innobackupex in the preceding command with xtrabackup.

Upload the full backup file to your OSS bucket by using OSS_Stream.

cat /<The directory that is used to store the full backup file>/<The name of the full backup file>_qp.xb | ./mysql-backup-helper-master/oss_stream/oss_stream -accessKeyId LTAI5tCqY18jvvKk******** -accessKeySecret 4A5Q7ZVzcYnWMQPysXFxld******** -bucketName test -endpoint oss-********.aliyuncs.com -objectName backup_qp.xb

Examples:

cat /root/backup_qp.xb | ./mysql-backup-helper-master/oss_stream/oss_stream -accessKeyId LTAI5tCqY18jvvKk******** -accessKeySecret 4A5Q7ZVzcYnWMQPysXFxld******** -bucketName test -endpoint oss-********.aliyuncs.com -objectName backup_qp.xb

Appendix 5: Limits


Item	Description
MySQL version	The self-managed MySQL instance must run one of the following MySQL versions: MySQL 5.7.32 or earlier MySQL 8.0.18 or earlier Note The self-managed MySQL instance must run the same MySQL version as the destination RDS instance. For example, the backup data of a self-managed MySQL instance that runs MySQL 5.7 can be restored only to an RDS instance that runs MySQL 5.7.
Self-managed MySQL instance	The data of the self-managed MySQL instance must be stored in the datadir directory on the host in which the instance resides. You can run the following command in the CLI to access the datadir directory: `mysqladmin -u<The username of the root account that is used to connect to the self-managed MySQL instance> -p<The password of the root account that is used to connect to the self-managed MySQL instance> variables \| grep datadir`. You must set the `innodb_data_file_path` parameter to the default value `ibdata1`.
Backup	After the full backup of the self-managed MySQL instance is complete, the incremental data that is generated in the self-managed instance is not included in the full backup file. If the self-managed MySQL instance runs MySQL 5.7, you must use Percona XtraBackup 2.4 to back up the instance. When you use Percona XtraBackup to back up the self-managed MySQL instance, you cannot configure the `--tables`, `--tables-exclude`, `--tables-file`, `--databases`, or `--databases-file` option. You cannot read encrypted objects from OSS buckets. Therefore, you must set the Encryption Method parameter to None when you create an OSS bucket. Differential backup files and log backup files are not supported. The name of the full backup file cannot contain special characters. If the name of the full backup file contains special characters, the file cannot be imported into the destination RDS instance. After you authorize the service account of ApsaraDB RDS to access OSS buckets, a RAM role named AliyunRDSImportRole is created in Resource Access Management (RAM). Do not modify or delete this RAM role. If you modify or delete this RAM role, ApsaraDB RDS cannot download objects from OSS buckets. Before the migration is complete, do not delete the full backup file from the OSS bucket. If you delete the full backup file before the migration is complete, the migration fails. The name of the full backup file that is stored as an object in your OSS bucket must be suffixed by _QP.XB or TAR.GZ.
OSS	If you use the CLI to upload the full backup file as a set of parts to your OSS bucket, you must make sure that the size of the file does not exceed 16 TB. For more information, see Limits. Your OSS bucket must reside in the same region as the destination RDS instance.
Restoration	You can migrate the data of the self-managed MySQL instance only to a new RDS instance. This way, you can prevent data overwrites in an existing RDS instance due to unintended operations. You cannot migrate the data of the self-managed MySQL instance to an RDS instance whose storage capacity is less than the amount of data in the self-managed MySQL instance. For more information, see Primary ApsaraDB RDS instance types. When you import the full backup file from your OSS bucket into ApsaraDB RDS, ApsaraDB RDS creates a temporary RDS instance, imports the full backup file into the temporary RDS instance, and then restores the data from the temporary RDS instance to the destination RDS instance. The default storage capacity of the temporary RDS instance is five times the size of the full backup file. If the available storage on the temporary RDS instance is insufficient after the full backup file is imported, you can increase the storage capacity of the temporary RDS instance. The accounts, custom functions, and stored procedures of the self-managed MySQL instance cannot be migrated to the destination RDS instance. You must record the accounts, custom functions, and stored procedures. After the migration is complete, you must manually add the accounts, custom functions, and stored procedures to the destination RDS instance. The time zone information of the self-managed MySQL instance cannot be migrated to the destination RDS instance. You must record the time zone information. After the migration is complete, you must manually configure the time zone of the destination RDS instance. The destination RDS instance must be a pay-as-you-go instance that runs MySQL 5.7 or MySQL 8.0 on RDS Basic Edition with standard SSDs. Note After the migration is complete, you can perform the following operations: Upgrade the major engine version of an ApsaraDB RDS for MySQL instance Upgrade an ApsaraDB RDS for MySQL instance from Basic Edition to High-availability Edition Change the specifications of an ApsaraDB RDS for MySQL instance Change the billing method of an ApsaraDB RDS for MySQL instance from pay-as-you-go to subscription
Replication	Data can be replicated based only on global transaction identifiers (GTIDs). Therefore, you must enable GTID-based replication and set the gtid_mode parameter and the enforce_gtid_consistency parameter to `ON` in the self-managed MySQL instance. The default retention period of backup validation records is seven days. ApsaraDB RDS automatically deletes the backup validation records that are generated seven days ago and the snapshots of these records. Therefore, after the migration is complete, we recommend that you replicate the incremental data of the self-managed MySQL instance to the destination RDS instance at your earliest opportunity.