Help Center/ Relational Database Service/ User Guide/ Working with RDS for MySQL/ Data Restorations/ Restoring Data to an On-Premises MySQL Database
Updated on 2024-10-14 GMT+08:00
View PDF
Share

Restoring Data to an On-Premises MySQL Database

This section describes how to restore a downloaded RDS for MySQL full backup to an on-premises MySQL database.

If you want to migrate all data from your RDS for MySQL DB instance to an on-premises MySQL database, you can download a full backup in .qp format and restore it to the on-premises database following the steps described in this section.

Operation Process

  1. Download the target full backup of your RDS for MySQL DB instance.
  2. Upload the full backup to the on-premises MySQL database.
  3. Use the qpress tool to decompress the full backup.
  4. Use the XtraBackup tool to restore the full backup to the data directory of the on-premises database and save the original data of the database to the data_back file.
  5. Restart the on-premises database.

Constraints

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

    To view the MySQL kernel version, run mysql -V or mysqld --version.

  • The backup can be restored only to an on-premises database running Linux.
  • Since the open-source backup tool supports only the x86 package, you cannot restore a backup to a database built on an Arm-based ECS by following the operations described in this section. To restore data to a database built on an Arm-based ECS, you can use Data Replication Service (DRS) or data export and import. For details, see Migration Solution Overview.
  • The following software is required to restore a full backup to an on-premises database:
    • MySQL database
    • qpress
    • Percona XtraBackup
    • Use the tools of the corresponding version. Otherwise, the restoration will fail.
      Table 1 Version mapping

      Database

      qpress

      Percona XtraBackup

      MySQL 8.0

      qpress 7

      XtraBackup 8.0.0 or later

      MySQL 5.7 and MySQL 5.6

      qpress 7

      XtraBackup 2.4.9 or later
  • During the restoration, do not run other workloads on the on-premises database.

Step 1: Download a Full Backup of Your RDS for MySQL DB Instance

RDS for MySQL DB instances automatically perform full backups at the time you specified. You can also create manual backups for your DB instance. The generated .qp files can be downloaded and restored to an on-premises database.

  1. Click the DB instance name on the RDS console, choose Backups & Restorations > Full Backups, locate the target full backup, and click Download in the Operation column to download the backup.
    Figure 1 Downloading a full backup
  2. Use a file transfer tool (such as WinSCP) to upload the full backup file to the Linux device where the on-premises MySQL database is located.

Step 2: Install qpress and XtraBackup on the On-Premises MySQL Database

Method 1: Manual Installation

  1. Download qpress and XtraBackup of a correct version. You can also download them according to Table 2. After the download is complete, upload the installation package to the Linux device where the on-premises MySQL database is located.
    Table 2 Download example

    Tool

    Example

    MySQL 5.6

    mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz

    MySQL 5.7

    mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

    MySQL 8.0

    mysql-8.0.26-linux-glibc2.12-x86_64.tar

    qpress

    qpress-11-1.el7.x86_64.rpm

    Percona XtraBackup

    XtraBackup 2.4.9 (MySQL 5.6 and 5.7)

    XtraBackup 8.0 (MySQL 8.0)
  2. Install the qpress rpm package. Enterprise Linux 7 (including CentOS 7, RHEL 7, Rocky Linux 7, and AlmaLinux 7) is used as an example.

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

  3. Decompress the XtraBackup package and change the name to xtrabackup.

    tar -zxvf percona-xtrabackup-2.4.9-Linux-x86_64.tar.gz

    mv percona-xtrabackup-2.4.9-Linux-x86_64 xtrabackup

  4. Add xtrabackup to environment variables. 
    echo  "export  PATH=$PATH:/usr/local/xtrabackup/bin"  >> /etc/profile
mv xtrabackup/ /usr/local/
source  /etc/profile

Method 2: Installation Using wget

  1. Install the qpress rpm package.

    wget https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm

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

  2. Install Percona XtraBackup.

Step 3: Restore the Backup to the On-Premises MySQL Database

  1. Create a temporary directory backupdir.

    mkdir backupdir

  2. Decompress the full backup.

    Before decompressing the full backup to the temporary directory backupdir, ensure that the temporary directory is empty to prevent restoration exceptions.

    • MySQL 5.6 and 5.7:

      xbstream -x -p 4 < ./full_backup.qp -C ./backupdir/

      innobackupex --parallel 4 --decompress ./backupdir

    • MySQL 8.0:

      xbstream -x -p 4 < ./full_backup.qp -C ./backupdir/

      xtrabackup --parallel 4 --decompress --target-dir=./backupdir

  3. Delete the .qp file.

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

  4. Apply redo logs.
    • MySQL 5.6 and 5.7:

      innobackupex --apply-log ./backupdir

    • MySQL 8.0:

      xtrabackup --prepare --target-dir=./backupdir

  5. Back up data.
    1. Stop the MySQL database service.

      service mysql stop

      For MySQL 5.7, run the following command:

      /bin/systemctl stop mysqld.service

    2. Back up the original database directory.

      mv /usr/local/mysql/data /usr/local/mysql/data_bak

      mkdir /usr/local/mysql/data

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

      chown mysql:mysql /usr/local/mysql/data

  6. Restore data to the on-premises database and change the directory permissions.

    Before performing this step, clear the data directory of the on-premises database. For details, see 5.b.

    • MySQL 5.6 and 5.7:

      innobackupex --defaults-file=/etc/my.cnf --copy-back ./backupdir

      chown -R mysql:mysql /usr/local/mysql/data

    • MySQL 8.0:

      xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=./backupdir

      chown -R mysql:mysql /usr/local/mysql/data

    • The relative path (./backupdir) in the commands can be replaced with an absolute path.
    • --defaults-file is followed by the location (for example, /etc/my.cnf) of the MySQL configuration file. You can specify the location based on the site requirements.
  7. Start the MySQL database.

    service mysql start

    For MySQL 5.7, run the following command:

    /bin/systemctl start mysqld.service

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

    show databases

Parent Topic: Data Restorations