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.
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
- 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.
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:
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 |
|
Backup |
|
OSS |
|
Restoration |
|
Replication |
|