Restoring Database from Physical Backup File

Last updated: 2020-06-03 14:06:29

    Operation Scenarios

    To save storage capacity, physical and logical backups in TencentDB for MySQL will be compressed with qpress and then packed with xbstream offered by Percona.

    The open-source Percona XtraBackup can be used to back up and restore databases. This document describes how to use XtraBackup to restore a logical backup file of TencentDB for MySQL instance to a self-built database on CVM.

    Prerequisites

    • Download and install XtraBackup.
      XtraBackup can be downloaded at Percona's official website. Please select Percona XtraBackup 2.4.6 or higher. For more information on installation, please see Percona XtraBackup 2.4.
    • Supported instance version: TencentDB for MySQL 5.5, 5.6, and 5.7 High-Availability Edition and Finance Edition.

    Directions

    Step 1. Download the backup file

    1. Log in to the TencentDB for MySQL Console. In the instance list, click an instance name or Manage in the "Operation" column to enter the instance management page.
    2. On the instance management page, click Backup and Restore > Data Backup List, select the backup file to download, and then click Download in the "Operation" column.
    3. We recommend that you copy the download address in the pop-up dialog box, log in to a (Linux) CVM instance in the same VPC as the TencentDB instance, and run the wget command to download over the private network for higher speed.
      • You can also click Download to download it directly, which may take longer.
      • wget command format: wget -c 'backup file download address' -O custom filename.xb
      Example:
      wget -c 'https://mysql-database-backup-bj-118.cos.ap-beijing.myqcloud.com/12427%2Fmysql%2F42d-11ea-b887-6c0b82b%2Fdata%2Fautomatic-delete%2F2019-11-28%2Fautomatic%2Fxtrabackup%2Fbk_204_10385%2Fcdb-1pe7bexs_backup_20191128044644.xb?sign=q-sign-algorithm%3Dsha1%26q-ak%3D1%26q-sign-time%3D1574269%3B1575417469%26q-key-time%3D1575374269%3B1517469%26q-header-list%3D%26q-url-param-list%3D%26q-signature%3Dfb8fad13c4ed&response-content-disposition=attachment%3Bfilename%3D%2141731_backup_20191128044644.xb%22&response-content-type=application%2Foctet-stream' -O test0.xb

    Step 2. Unpack the backup file

    Unpack the backup file with xbstream.

    xbstream -x < test0.xb

    Replace test0.xb with your backup file.

    The unpacking result is as shown below:

    Step 3. Decompress the backup file

    1. Download qpress by running the following command:
      wget http://www.quicklz.com/qpress-11-linux-x64.tar

      If an error is displayed during the wget download, you can go to QuickLZ's official website to download qpress locally and upload it to the Linux CVM instance. For more information, please see Upload Files via SCP.

    2. Extract the qpress binary files by running the following command:
      tar -xf qpress-11-linux-x64.tar -C /usr/local/bin
      source /etc/profile
    3. Decompress the backup file with qpress.
      qpress -d cdb-jp0zua5k_backup_20191202182218.sql.qp .

      Find the backup file with .sql.qp extension by decompression time and replace cdb-jp0zua5k_backup_20191202182218 with its filename.

      The decompressing result is as shown below:

    Step 4. Import the backup file into the target database

    Import the .sql file into the target database by running the following command:

    mysql -uroot -P3306 -h127.0.0.1 -p < cdb-jp0zua5k_backup_20191202182218.sql
    • This document takes importing into a local MySQL instance with port 3306 as an example. You can replace it as needed.
    • Replace cdb-jp0zua5k_backup_20191202182218.sql with the .sql file extracted by qpress.

    Was this page helpful?

    Was this page helpful?

    • Not at all
    • Not very helpful
    • Somewhat helpful
    • Very helpful
    • Extremely helpful
    Send Feedback
    Help