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 in which the destination RDS instance resides. For more information about how to create a bucket, see Create buckets.
NoteThe 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 rules
After a user backup file is imported to an ApsaraDB RDS instance, the file is stored free of charge within 24 hours. If the file is stored for more than 24 hours, you are charged storage fees. For more information, see Storage fees.
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. For more information about how to install Percona XtraBackup on Ubuntu, see Appendix 1: Install XtraBackup on Ubuntu.
Install the Percona repository.
sudo 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.
sudo yum install -y percona-xtrabackup-24 #Install Percona XtraBackup 2.4. sudo yum install -y percona-xtrabackup-80 #Install Percona XtraBackup 8.0.
NoteYou must select the version of Percona XtraBackup based on the MySQL version.
If MySQL 5.7 is used, you need to install Percona XtraBackup 2.4.
If MySQL 8.0 is used, you need to 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:
sudo yum install -y go
The Unzip utility is installed. If Unzip is not installed, run the following command in the CLI to install Unzip:
sudo yum install -y unzip
The preceding commands are supported only for CentOS. If you use Ubuntu, see Appendix 2: Install Go and Unzip in an Ubuntu operating system.
Download the source code package of MySQL Backup Helper.
wget https://github.com/aliyun/mysql-backup-helper/archive/refs/heads/master.zip
NoteYou can download the source code package of MySQL Backup Helper from the mysql-backup-helper page.
Decompress the source code package of MySQL Backup Helper.
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>
If the self-managed MySQL instance supports backups, perform a full backup on the self-managed MySQL 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 instanc> --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>
Examples:
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
NoteIf the full backup is performed on a self-managed instance that runs MySQL 8.0, 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 runs as expected, 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 to which you want to restore the data of the self-managed MySQL instance.
In the left-side navigation pane, click Backups.
On the User Backups tab of the page 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.
NoteIf 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 tab. 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 and click Restore in the Actions 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.
NoteIf 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 designed based on the distributed storage architecture of Alibaba Cloud. You can store data on standard SSDs to separate computing from storage.
NoteFor 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 cores and storage resources with the other general-purpose RDS instances that are deployed on the same host.
NoteEach 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 storage capacity that is provisioned to store data files, system files, log files, and transaction files in the RDS instance. You can adjust the storage capacity at a step size of 5 GB.
Configure the following parameters and click Next: Instance Configuration.
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 configure the VPC and vSwitch of Primary Node parameters.
NoteThe RDS instance and the 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.
NoteApsaraDB RDS requires 1 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.
NoteYou must select the version of Percona XtraBackup based on the MySQL version. The following list describes the version mappings:
If MySQL 5.7 is used, you need to install Percona XtraBackup 2.4.
If MySQL 8.0 is used, you need to install Percona XtraBackup 8.0.
Install qpress.
sudo apt-get install -y qpress
Noteqpress 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 qpress and you must install qpress.
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
Examples:
innobackupex --backup --host=127.0.0.1 --port=3306 --user=root --password=Aa123456@ --stream=xbstream --compress /root/mysql/data > /root/backup_qp.xb
NoteIf 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:
Note
|
Self-managed MySQL instance |
|
Backup |
|
OSS |
|
Restoration |
|
Replication |
|