Help Center > > User Guide> Working with RDS for MySQL> Backups and Restorations> Restoring Data from MySQL Backup Files

Restoring Data from MySQL Backup Files

Updated at: Mar 17, 2020 GMT+08:00

Scenarios

You can download backup files by referring to Downloading a Full Backup File and restore data from them. This section uses CentOS 7.4 64bit and MySQL 5.6 as examples to describe how to restore data.

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.

    Ensure that the XtraBackup version is 2.4.9 or later. Otherwise, an error will be reported in subsequent steps.

    Download percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm from the website and upload it to the ECS.

    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 < ./mysql_xtrabackup_ file.qp -C ./backupdir/

      innobackupex --parallel 4 --decompress ./backupdir

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

  4. Apply the log.

    innobackupex --apply-log ./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 the directory permissions.

    innobackupex --defaults-file=/etc/my.cnf --copy-back ./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

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel