Restoring Data to an On-Premises MySQL Database
This section describes how to restore a downloaded RDS for MySQL full backup to an on-premises MySQL database.
If you want to migrate all data from your RDS for MySQL DB instance to an on-premises MySQL database, you can download a full backup in .qp format and restore it to the on-premises database following the steps described in this section.
- Step 1: Download a Full Backup of Your RDS for MySQL DB Instance
- Step 2: Install qpress and XtraBackup on the On-Premises MySQL Database
- Step 3: Restore the Backup to the On-Premises MySQL Database
Operation Process
- Download the target full backup of your RDS for MySQL DB instance.
- Upload the full backup to the on-premises MySQL database.
- Use the qpress tool to decompress the full backup.
- Use the XtraBackup tool to restore the full backup to the data directory of the on-premises database and save the original data of the database to the data_back file.
- Restart the on-premises database.
Constraints
- This section only covers restoring a full backup of an RDS for MySQL 5.6, 5.7 or 8.0 DB instance to an on-premises database of the corresponding version. Incremental backup restoration is not included.
- The minor version of the on-premises MySQL database must be the same as that of your RDS for MySQL DB instance.
To view the MySQL kernel version, run mysql -V or mysqld --version.
- The backup can be restored only to an on-premises database running Linux.
- Since the open-source backup tool supports only the x86 package, you cannot restore a backup to a database built on an Arm-based ECS by following the operations described in this section. To restore data to a database built on an Arm-based ECS, you can use Data Replication Service (DRS) or data export and import. For details, see Migration Solution Overview.
- The following software is required to restore a full backup to an on-premises database:
- MySQL database
- qpress
- Percona XtraBackup
- Use the tools of the corresponding version. Otherwise, the restoration will fail.
Table 1 Version mapping Database
qpress
Percona XtraBackup
MySQL 8.0
qpress 7
XtraBackup 8.0.0 or later
MySQL 5.7 and MySQL 5.6
qpress 7
XtraBackup 2.4.9 or later
- During the restoration, do not run other workloads on the on-premises database.
Step 1: Download a Full Backup of Your RDS for MySQL DB Instance
RDS for MySQL DB instances automatically perform full backups at the time you specified. You can also create manual backups for your DB instance. The generated .qp files can be downloaded and restored to an on-premises database.
- Click the DB instance name on the RDS console, choose Backups & Restorations > Full Backups, locate the target full backup, and click Download in the Operation column to download the backup.
Figure 1 Downloading a full backup
- Use a file transfer tool (such as WinSCP) to upload the full backup file to the Linux device where the on-premises MySQL database is located.
Step 2: Install qpress and XtraBackup on the On-Premises MySQL Database
Method 1: Manual Installation
- Download qpress and XtraBackup of a correct version. You can also download them according to Table 2. After the download is complete, upload the installation package to the Linux device where the on-premises MySQL database is located.
- qpress: https://repo.percona.com/yum/release/
- Percona XtraBackup:
- For MySQL 5.6 and 5.7, download XtraBackup 2.4.9 or later.
- For MySQL 8.0, download XtraBackup 8.0 or later.
Table 2 Download example Tool
Example
MySQL 5.6
mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz
MySQL 5.7
mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
MySQL 8.0
mysql-8.0.26-linux-glibc2.12-x86_64.tar
qpress
Percona XtraBackup
XtraBackup 2.4.9 (MySQL 5.6 and 5.7)
XtraBackup 8.0 (MySQL 8.0)
- Install the qpress rpm package. Enterprise Linux 7 (including CentOS 7, RHEL 7, Rocky Linux 7, and AlmaLinux 7) is used as an example.
- Decompress the XtraBackup package and change the name to xtrabackup.
tar -zxvf percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz
mv percona-xtrabackup-2.4.9-Linux-x86_64 xtrabackup
- Add xtrabackup to environment variables.
echo "export PATH=$PATH:/usr/local/xtrabackup/bin" >> /etc/profile mv xtrabackup/ /usr/local/ source /etc/profile
Method 2: Installation Using wget
- Install the qpress rpm package.
wget https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm
rpm -ivh qpress-11-1.el7.x86_64.rpm
- Install Percona XtraBackup.
- For MySQL 5.6 and 5.7, Percona XtraBackup 2.4.9 is used as an example.
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
rpm -ivh percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm --nodeps --force
- For MySQL 8.0, Percona XtraBackup 8.0 is used as an example.
wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.32-26/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.32-26.1.el7.x86_64.rpm
rpm -ivh percona-xtrabackup-80-8.0.32-26.1.el7.x86_64.rpm --nodeps --force
- For MySQL 5.6 and 5.7, Percona XtraBackup 2.4.9 is used as an example.
Step 3: Restore the Backup to the On-Premises MySQL Database
- Create a temporary directory backupdir.
mkdir backupdir
- Decompress the full backup.
Before decompressing the full backup to the temporary directory backupdir, ensure that the temporary directory is empty to prevent restoration exceptions.
- Delete the .qp file.
find ./backupdir/ -name '*.qp' | xargs rm -f
- Apply redo logs.
- Back up data.
- Restore data to the on-premises database and change the directory permissions.
Before performing this step, clear the data directory of the on-premises database. For details, see 5.b.
- MySQL 5.6 and 5.7:
innobackupex --defaults-file=/etc/my.cnf --copy-back ./backupdir
chown -R mysql:mysql /usr/local/mysql/data
- MySQL 8.0:
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=./backupdir
chown -R mysql:mysql /usr/local/mysql/data
- The relative path (./backupdir) in the commands can be replaced with an absolute path.
- --defaults-file is followed by the location (for example, /etc/my.cnf) of the MySQL configuration file. You can specify the location based on the site requirements.
- MySQL 5.6 and 5.7:
- Start the MySQL database.
For MySQL 5.7, run the following command:
/bin/systemctl start mysqld.service
- Log in to the database and view the restoration result.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot