Help Center> Relational Database Service> Best Practices> MySQL> Downloading a Backup File and Restoring Data from It
View PDF

Downloading a Backup File and Restoring Data from It

You can download a backup file and restore data from it.

Backup data cannot be restored to local databases that run the Windows operating system.

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 program and upload it to the ECS for installation.

    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.

    Download XtraBackup from the website and upload it to the ECS. The following uses percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm as an example.

    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.

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

  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. Log in to the database and view the restoration result.

    mysql -u -root

    show databases

    Figure 1 Viewing the restoration result

Parent topic: MySQL