Migrate self-managed MySQL 5.7 and 8.0 - ApsaraDB RDS - Alibaba Cloud Documentation Center

Restore a full backup of a self-managed MySQL database from Object Storage Service (OSS) to a new ApsaraDB RDS for MySQL instance by using the RDS console.

Prerequisites

Your self-managed MySQL database must meet cloud migration requirements. For details, see Appendix: limits.
You must have an OSS bucket in the destination region. If you do not have one, see Create a bucket in the console.

Note
The destination region must be the region where you will create the RDS instance.

Migration process

The migration involves the following steps:

Step 1: Install Percona Xtrabackup

Step 2: Install MySQL Backup Helper

Step 3: Back up the self-managed database and migrate it to the cloud

Tutorial environment

This tutorial uses an Alibaba Cloud ECS instance running CentOS Linux release 8.3.2011. If you use a different version, adjust the commands accordingly. To create an ECS instance, see Create an ECS instance.

Step 1: Install Percona XtraBackup

Percona XtraBackup is a tool for creating physical hot backups of MySQL databases. It supports multiple database engines. For Ubuntu, see Appendix 1: Install Percona XtraBackup on Ubuntu.

Select the Percona XtraBackup version matching your MySQL version.

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

Step 2: Install MySQL Backup Helper

Prerequisites

If Golang is not installed, run the following command.
```
sudo yum install -y go
```
If unzip is not installed, run the following command.
```
sudo yum install -y unzip
```

Note

The preceding commands are for CentOS. If you are using Ubuntu, see Appendix 2: Install Golang and Unzip on Ubuntu.

Procedure

Download the MySQL Backup Helper source code package.
```
wget https://github.com/aliyun/mysql-backup-helper/archive/refs/heads/master.zip
```
Note
The latest source code package is available at mysql-backup-helper.
Unzip the MySQL Backup Helper source code package.
```
unzip master.zip
```
Change to the mysql-backup-helper-master directory and compile the main.go file to generate the backup_helper executable.
```
cd mysql-backup-helper-master
go build -a -o backup_helper main.go
```
Change to the oss_stream directory and compile the oss_stream.go file to generate the oss_stream executable.
```
cd oss_stream
go build -a -o oss_stream oss_stream.go
```

Step 3: Back up and upload your database

Use MySQL Backup Helper to verify that your self-managed MySQL database supports backups.

cd ~/mysql-backup-helper-master && ./backup_helper -host <host_address> -port <port> -user <root_username> --password <root_password>

backup_helper

After verification, perform a full backup of the self-managed database and upload the backup file to OSS. If you have not created an OSS bucket, see Prerequisites.

Select the command that corresponds to your MySQL version.

MySQL 5.7

innobackupex --backup --host=<host_address> --port=<port> --user=<root_username> --password=<root_password> --stream=xbstream --compress <temp_backup_directory> | ./mysql-backup-helper-master/oss_stream/oss_stream -accessKeyId <access_key_id> -accessKeySecret <access_key_secret> -bucketName <bucket_name> -endpoint <bucket_endpoint> -objectName <custom_backup_filename>

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 LTAI**************** -accessKeySecret ****** -bucketName test -endpoint oss-********.aliyuncs.com -objectName backup_qp.xb

Backups take longer for larger databases. If your database is large, run the backup in the background by using the nohup command to prevent interruptions from an unexpected logout. The following command is an example:

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 LTAI**************** -accessKeySecret ****** -bucketName test -endpoint oss-ap-southeast-1.aliyuncs.com -objectName backup_qp.xb' &

MySQL 8.0

xtrabackup --backup --host=<host_address> --port=<port> --user=<root_username> --password=<root_password> --stream=xbstream --compress <temp_backup_directory> | ./mysql-backup-helper-master/oss_stream/oss_stream -accessKeyId <access_key_id> -accessKeySecret <access_key_secret> -bucketName <bucket_name> -endpoint <bucket_endpoint> -objectName <custom_backup_filename>

Example:

xtrabackup --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 LTAI**************** -accessKeySecret ******** -bucketName test -endpoint oss-****.aliyuncs.com -objectName backup_qp.xb

nohup sh -c 'xtrabackup --backup --host=127.0.0.1 --port=3306 --user=root --password=Aa123456@ --stream=xbstream  /root/mysql/data | ./mysql-backup-helper-master/oss_stream/oss_stream -accessKeyId LTAI**************** -accessKeySecret ****** -bucketName test -endpoint oss-ap-southeast-1.aliyuncs.com -objectName backup_qp.xb' &

Note

Backup duration depends on the instance workload. For example, heavy write operations that generate many redo logs, or large transactions running on the instance, increase backup time. When the backup is complete, completed OK ! is displayed.
If you cannot currently access OSS, you can back up the database to a local disk and upload the file later. For details, see Appendix 3: Perform a full backup and upload to OSS in separate steps.
After completing this step, you can log in to the OSS console to confirm the upload. If the upload failed, repeat Step 2.

Log in to the RDS instance list, select a region in the upper-left corner of the page, and then click Backups in the left-side navigation pane.
On the User Backups tab, click Import Backup.
In the Import Backup dialog box, read the instructions and click Next until you reach the 3. Import Data tab.
Note
The wizard guides you through the backup import process. For more information, see Perform a full backup and upload to OSS in separate steps.
- Step 1. Back Up Source Database: Perform a full backup of your self-managed database.
- 2. Upload Backup Files to OSS: Upload the full backup file to OSS.

On the Step 3: Import the data tab, configure the following parameters and click OK.

Parameter	Description
MySQL Version	The system automatically displays 5.7/8.0. Note You can import backups only from self-managed databases that run MySQL 5.7 or 8.0.
Region	The region selected in Step 1. This region must match the region of the OSS bucket that contains the backup file.
OSS Bucket	Select the OSS bucket that contains the backup file. For more information about OSS buckets, see Upload files.
OSS File Name	Select the backup file from the OSS bucket. You can search for the file by entering its name in the text box next to OSS File Name. Both fuzzy and exact matching are supported. Note The backup file in OSS must be in the `_QP.XB` format. You can also compress a file in the `_QP.XB` format into a `TAR.GZ` file for storage. For more information about the limitations, see Appendix: Usage limits.
Description	Enter custom remarks for the backup file.
Zone	Select the availability zone for the user backup. After you select one, the system creates a snapshot in that zone, which significantly reduces the backup import time. Note When you restore data from this user backup to a new instance, the new instance will be in this availability zone.

Note

If you have not authorized RDS to access OSS, click Authorize at the bottom of the 3. Import Data page. On the authorization page, click Authorize.
For more information about importing backups, read the instructions on this page.

The system creates a verification task for the backup file on the User Backups page. Wait for the Status of the task to change from Verifying to Completed.

Important
Verification time depends on the source instance state during the backup. For example, heavy write operations that generate many redo logs, or large transactions running on the source instance, increase verification time.
Find the target backup and click Restore in the Actions column for the Backup ID/Name.

Configure the following parameters and click Next: Instance Configurations.

Category	Description
Zone of Primary Node	Select the availability zone for the primary instance. Note This parameter appears only if you did not select an availability zone when importing the user backup.
Storage Type	ESSD PL1: An Enhanced SSD (ESSD) cloud disk that provides PL1 performance. Standard SSD: An elastic block storage device based on a distributed storage architecture. Selecting an SSD cloud disk separates compute from storage. Note For more information, see Storage types.
Instance Type	General-purpose (Entry-level): Provides dedicated memory and I/O resources while sharing CPU and storage resources with other general-purpose instances on the same server. Note Each instance type has a specific number of CPU cores, memory size, maximum number of connections, and maximum IOPS. For more information, see Primary instance types.
Storage Capacity	The total space for data, system files, log files, and transaction files. You can adjust the storage space in 5 GB increments.

Configure the following parameters and click Next: Confirm Order.

Category	Description
Network Type	VPC: An isolated network environment that provides higher security and performance than the classic network. You must specify a VPC and a vSwitch of Primary Node. Note Ensure the VPC is the same one used by the target ECS instance. Otherwise, the RDS and ECS instances cannot communicate over the internal network.
Parameter Template	Specify a parameter template for the instance. You can use system templates or custom templates to preset instance parameters. For more information, see Use a parameter template.
Time Zone	Set the time zone for the instance.
Table Name Case Sensitivity	Specify whether table names in the instance are case-sensitive. If table names in your source database are case-sensitive, select Case-sensitive to facilitate data migration.

Review the Parameter Configuration, select the Quantity, read and accept the terms of service, and then click Pay Now.

Note
Instance creation takes 1 to 5 minutes.

Operations

Set retention period

Imported user backups are retained for 3 days by default. You can change this retention period based on your business needs.

Note

If you no longer need a user backup, you can delete it.

Log on to the RDS Instance List. In the upper-left corner of the page, select a region. In the left-side navigation pane, click Backups.
Click Set Retention Period in the Expiration Time column of the target Backup ID/Name.
In the dialog box, select a preset retention period from the drop-down list, or select the Custom Retention Period checkbox and enter a value.

Note
Expiration Time shows the backup's expiration time. If the expiration time is after 2099, it is displayed as Permanent.
Click OK.

Add tags

You can add tags to your imported backups for easier management.

Log on to the RDS Instance List. In the upper-left corner of the page, select a region. In the left-side navigation pane, click Backups.
Click +Add in the Tags column for the target Backup ID/Name.
Click Create a label, enter a Key and a Value, and click OK to the right of the text box. Then, click OK in the lower-right corner of the dialog box.

Note
If you have already created tags, you can click Select the label to add an existing tag.
To change an existing tag, hover over it, click edit in the pop-up, and then repeat Step 3.

View log details

If the source database is modified during a self-managed database backup, the backup file contains log information that can be used to restore incremental data.

Log on to the RDS Instance List. In the upper-left corner of the page, select a region. In the left-side navigation pane, click Backups.
Click View Details in the Actions column of the target Backup ID.
In the dialog box, you can view the log details.
Note
The log information includes the following fields:
- Master_Log_File:: The name of the log file where the incremental data begins.
- Master_Log_Position:: The starting position of the incremental data within the log file.

Delete user backup

Delete user backups that you no longer need to save costs.

Log on to the RDS Instance List. In the upper-left corner of the page, select a region. In the left-side navigation pane, click Backups.
Click Delete in the Actions column of the target Backup ID/Name.
In the dialog box, click OK.

Other operations

Filter Columns: Customize which columns appear in the backup list.
1. Log on to the RDS Instance List. In the upper-left corner of the page, select a region. In the left-side navigation pane, click Backups.
2. On the right side of the page, click the icon. In the pop-up window, select a column and click the or icon to show or hide the column.
  
  Note
  Columns in the left pane are hidden. Columns in the right pane are displayed.
3. Click OK.
Export Instance List: Export a list of all current user backups to a CSV file.
1. Log on to the RDS Instance List. In the upper-left corner of the page, select a region. In the left-side navigation pane, click Backups.
2. On the right side of the page, click the icon to export the resource list.
Refresh: Refreshes the user backup list.
1. Log on to the RDS Instance List. In the upper-left corner of the page, select a region. In the left-side navigation pane, click Backups.
2. On the right side of the page, click the icon to refresh the user backup list.

Appendix 1: Xtrabackup on Ubuntu

Select the XtraBackup version that matches your MySQL version.

MySQL 5.7

Install XtraBackup.

# 1. Download the Percona repository package.
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb

# 2. Install the repository package.
sudo dpkg -i percona-release_latest.generic_all.deb

# 3. Update the package lists.
sudo apt-get update

# 4. Install Percona XtraBackup.
sudo apt-get install -y percona-xtrabackup-24

Install qpress.
```
sudo apt-get install -y qpress
```
Note
XtraBackup requires the qpress decompression tool. The Ubuntu package for XtraBackup does not include qpress, so you must install it separately.

Note

If you encounter an error like The following packages have unmet dependencies, run apt-get -f install to resolve the dependencies, then try again.

MySQL 8.0

Install XtraBackup.

# 1. Download the Percona repository package (if not done in a previous step).
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb

# 2. Install the repository package.
sudo dpkg -i percona-release_latest.generic_all.deb

# 3. Update the package lists.
sudo apt-get update

# 4. Install Percona XtraBackup.
sudo apt-get install -y percona-xtrabackup-80

Install qpress.
```
sudo apt-get install -y qpress
```
Note
XtraBackup requires the qpress decompression tool. The Ubuntu package for XtraBackup does not include qpress, so you must install it separately.

Note

If you encounter an error like The following packages have unmet dependencies, run apt-get -f install to resolve the dependencies, then try again.

Appendix 2: Golang and unzip on Ubuntu

To install Golang:

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

To install unzip:
```
sudo apt-get -y install unzip
```

Appendix 3: Back up and upload to OSS separately

Create a full backup of your self-managed database locally.

Select the command based on your MySQL version.

MySQL 5.7

innobackupex --backup --host=<your_host_address> --port=<your_port_number> --user=<your_root_account> --password=<your_root_password> --stream=xbstream --compress <temporary_backup_directory> > /<backup_path>/<backup_file_name>_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

MySQL 8.0

xtrabackup --backup --host=<your_host_address> --port=<your_port_number> --user=<your_root_account> --password=<your_root_password> --stream=xbstream --compress <temporary_backup_directory> > /<backup_path>/<backup_file_name>_qp.xb

Example:

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

Use OSS_Stream to upload the backup file to OSS.

cat /<backup_path>/<backup_file_name>_qp.xb | ./mysql-backup-helper-master/oss_stream/oss_stream -accessKeyId LTAI**************** -accessKeySecret ****** -bucketName test -endpoint oss-********.aliyuncs.com -objectName backup_qp.xb

Example:

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

Appendix 4: Limitations

Item	Description
MySQL version limitations	You can migrate only the following versions of self-managed databases. MySQL 5.7 (minor versions 5.7.32 and earlier) MySQL 8.0 (minor versions 8.0.18 and earlier) Note The version of your self-managed database must match the version of the destination RDS instance. For example, you can restore backup data from a MySQL 5.7 database only to an ApsaraDB for RDS for MySQL 5.7 instance. If your database version does not meet these requirements, see Migrate a self-managed MySQL database to an ApsaraDB for RDS for MySQL instance.
Self-managed MySQL limitations	The self-managed database must store its data in the `datadir` directory that is configured in MySQL. You can run the `mysqladmin -u<your_root_account> -p<your_root_password> variables \| grep datadir` command to check the `datadir` directory of your MySQL database. The `innodb_data_file_path` parameter must be set to the default value, `ibdata1`.
Backup limitations	Backup files do not include incremental data that is generated after the backup is complete. To back up a self-managed MySQL 5.7 database, you must use Percona XtraBackup 2.4. When you use Percona XtraBackup to back up a self-managed database, you cannot specify the `--tables`, `--tables-exclude`, `--tables-file`, `--databases`, or `--databases-file` option. The migration task cannot read encrypted files in an OSS Bucket. Therefore, when you create the OSS Bucket, you must set Encryption Method to None. Differential backups and log backups are not supported. A full backup file name cannot contain special characters. Otherwise, the migration fails. After you grant the RDS service account access to OSS, the system automatically creates a role named AliyunRDSImportRole in the Role Management section of RAM. Do not modify or delete this role. Otherwise, the cloud migration task fails because it cannot download the backup file. Do not delete the backup file from OSS before the restore task is complete. Otherwise, the restore fails. The backup file in OSS must be in the `_QP.XB` format, or a `_QP.XB` file compressed into the `TAR.GZ` format. Note If a backup file does not meet the format requirements, for example, a backup file in the `.xbstream` format, we recommend that you restore the file to a local database and convert it to the `_QP.XB` format before migrating. Alternatively, you can use another migration method. For more information, see Migrate a self-managed MySQL database to an ApsaraDB for RDS for MySQL instance.
OSS limitations	When you use the CLI to perform multipart uploads, a single file cannot exceed 16 TB in size. For more information, see OSS limits. The OSS Bucket that stores the backup file and the destination RDS instance must be in the same region.
Restore limitations	To prevent accidental data overwrites, you can restore data only to a new instance. You cannot restore a database whose size exceeds the maximum storage space supported by ApsaraDB for RDS for MySQL. For more information about specifications, see Primary instance types. When you import a backup, the system creates a temporary instance and imports the backup data to it. During the restore, data is extracted from the temporary instance. By default, the temporary instance is provisioned with storage space that is five times the backup data size. If the import fails due to insufficient storage space, you can adjust the storage space. The restore does not import user accounts, user-defined functions, or stored procedures. You must manually add them to the destination RDS instance after the restore is complete. The restore does not import time zone information. You must manually configure the time zone on the destination RDS instance after the restore is complete. You can restore a backup file only to a pay-as-you-go ApsaraDB for RDS for MySQL 5.7 or 8.0 Basic Edition instance that uses an SSD cloud disk. Note You can perform the following operations after the migration is complete: Upgrade the major engine version Upgrade an instance from Basic Edition to High-availability Edition Change specifications Change the billing method of an instance from pay-as-you-go to subscription
Replication limitations	Only replication based on global transaction identifiers (GTIDs) is supported. Therefore, you must enable GTID-based replication on your self-managed database by setting both gtid_mode and enforce_gtid_consistency to `ON`. By default, backup verification records and their generated snapshots are retained for 7 days before automatic deletion. To prevent the self-managed database logs from being purged and the backup verification snapshots from being deleted, establish the replication relationship as soon as possible after the instance restore is complete.