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 qpress-11-linux.x64.tar from the website and upload it to the ECS.

   tar -xvf qpress-11-linux-x64.tar

   mv qpress /usr/bin/

2. Download the XtraBackup software and upload it to the ECS for installation.
   

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

   Download XtraBackup from the website and upload it to the ECS. Then, run the following command to install 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

3. 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
   3. Delete full backup files ending in ".qp":

      find ./backupdir/ -name '*.qp' | xargs rm -f

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

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

6. Copy the full backup file and change 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

7. Start the database.

   service mysql start

   

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

   /bin/systemctl start mysqld.service

8. Run the following commands to log in to the database and view the restoration result:

   mysql -u root

   show databases

   Figure 1 Viewing the restoration result