Help Center> Relational Database Service> User Guide> Working with RDS for MySQL> Backups and Restorations> Restoring from Backup Files to a Self-Built MySQL Database
View PDF

Restoring from Backup Files to a Self-Built MySQL Database

Scenarios

You can download backup files by referring to Downloading a Full Backup File and restore data from them.

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
    3. Delete full backup files suffixed with ".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

Parent topic: Backups and Restorations