All Products
Search
Document Center

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

Last Updated:Nov 03, 2023

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.

    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 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.

  1. Install the Percona repository.

    sudo yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
  2. Enable the Percona repository.

    percona-release enable-only tools release
  3. 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.

    Note

    You 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
Note

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

  1. Download the source code package of MySQL Backup Helper.

    wget https://github.com/aliyun/mysql-backup-helper/archive/refs/heads/master.zip
    Note

    You can download the source code package of MySQL Backup Helper from the mysql-backup-helper page.

  2. Decompress the source code package of MySQL Backup Helper.

    unzip master.zip
  3. 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
  4. 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

  1. 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>

  2. 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
    Note
    • If 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.

  3. 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.

  4. In the left-side navigation pane, click Backups.

  5. On the User Backups tab of the page that appears, click Import Backup.

  6. In the wizard that appears, read the messages that are displayed and click Next until you enter the 3. Import Data step.

  7. 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 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.

  8. Find the full backup file and click Restore in the Actions column.

  9. 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 designed based on the distributed storage architecture of Alibaba Cloud. 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 cores 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 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.

  10. 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.

    Note

    The 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.

  11. 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 to 5 minutes to create the RDS instance. Wait until the instance is created.

Appendix 1: Install Percona XtraBackup in an Ubuntu operating system

  1. Download the latest Percona package.

    wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
  2. Install the downloaded Percona package.

    sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
  3. Enable the Percona repository.

    percona-release enable-only tools release
  4. Update the list of software applications in the local repository.

    apt-get update
  5. 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. 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.

  6. Install qpress.

    sudo apt-get install -y qpress
    Note

    qpress 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.

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

  1. 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
    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.

  2. 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.

  • If the MySQL version does not meet the requirements, you can migrate the data based on the instructions provided in Migrate data from a self-managed MySQL database to an ApsaraDB RDS for MySQL instance.

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.

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.