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

When you restore data from backup files to self-built MySQL databases, ensure that the target MySQL version is later than or equal to the original MySQL version.

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