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.

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

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

2. Upload the qpress RPM file to the ECS.
3. Install qpress on the ECS.

   rpm -ivh qpress-11-1.el7.x86_64.rpm

4. Download XtraBackup from the website, for example, percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm.
   

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

5. Upload XtraBackup to the ECS.
6. Install XtraBackup on the ECS.

   rpm -ivh percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm --nodeps --force

7. On the ECS, decompress the full backup file that has been downloaded.
   1. Create a temporary directory backupdir.

      mkdir backupdir

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

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

9. Back up data.
   1. Stop MySQL database services.

      service mysql stop

      

      For MySQL 5.7, run the following command to stop MySQL database services:

      /bin/systemctl stop mysqld.service

   2. Back up the original database directory.

      mv /var/lib/mysql/data /var/lib/mysql/data_bak

   3. Create a new database directory and change the permissions.

      mkdir /var/lib/mysql/data

      chown mysql:mysql /var/lib/mysql/data

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

    

    Clear the content in the var/lib/mysql/data directory in advance.

11. Start the database.

    service mysql start

    

    For MySQL 5.7, run the following command to start the database:

    /bin/systemctl start mysqld.service

12. Log in to the database and view the restoration result.

    mysql -u -root

    show databases

    Figure 1 Viewing the restoration result