Restoring from Backup Files to a Self-Built MySQL Database¶
Scenarios¶
You can download backup files by referring to Downloading a Backup File and restore data from them.
Important
Backup data cannot be restored to local databases that run the Windows operating system.
Only x86 packages of the open-source backup tool XtraBackup are supported. Therefore, you are advised to restore backup data to self-built databases on Arm-based ECSs by migrating data with DRS or exporting and importing data. For details, see Data Migration.
Prerequisites¶
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.
During the restoration, do not run other workloads on the on-premises database.
During data restoration, run the following command to view the restoration process:
ps -ef | grep mysql
Procedure¶
Download the qpress RPM file qpress-11-1.el7.x86_64.rpm. Enterprise Linux 7 (CentOS 7, RHEL 7, Rocky Linux 7, and AlmaLinux 7) is used as an example.
For details about RPM files of other OSs, see https://repo.percona.com/yum/release/.
Upload the qpress RPM file to the ECS.
Install qpress on the ECS.
rpm -ivh qpress-11-1.el7.x86_64.rpm
Download XtraBackup from the website, for example, percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm.
Important
For MySQL 5.6 and 5.7, download XtraBackup 2.4.9 or later versions.
For MySQL 8.0, download XtraBackup 8.0 or later versions.
Upload XtraBackup to the ECS.
Install XtraBackup on the ECS.
rpm -ivh percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm --nodeps --force
On the ECS, decompress the full backup file that has been downloaded.
Create a temporary directory backupdir.
mkdir backupdir
Decompress the package.
xbstream -x -p 4 < ./Full backup file.qp -C ./backupdir/
For MySQL 5.6 and 5.7, run innobackupex --parallel 4 --decompress ./backupdir.
For MySQL 8.0, run xtrabackup --parallel 4 --decompress --target-dir=./backupdir.
find ./backupdir/ -name
'*.qp'| xargs rm -f
Apply the log.
For MySQL 5.6 and 5.7, run innobackupex --apply-log ./backupdir.
For MySQL 8.0, run xtrabackup --prepare --target-dir=./backupdir.
Back up data.
Stop MySQL database services.
service mysql stop
Note
For MySQL 5.7, run the following command to stop MySQL database services:
/bin/systemctl stop mysqld.service
Back up the original database directory.
mv /var/lib/mysql/data /var/lib/mysql/data_bak
Create a new database directory and change the permissions.
mkdir /var/lib/mysql/data
chown mysql:mysql /var/lib/mysql/data
Copy the full backup file and modify the directory permissions.
For MySQL 5.6 and 5.7, run innobackupex --defaults-file=/etc/my.cnf --copy-back ./backupdir.
For MySQL 8.0, run xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=./backupdir.
chown -R mysql:mysql /var/lib/mysql/data
Note
Clear the content in the var/lib/mysql/data directory in advance.
Start the database.
service mysql start
Note
For MySQL 5.7, run the following command to start the database:
/bin/systemctl start mysqld.service
Log in to the database and view the restoration result.
mysql -u -root
show databases
Figure 1 Viewing the restoration result¶