tencent cloud

Feedback

Restoring Database from Logical Backup

Last updated: 2022-10-20 17:15:50

    Overview

    Note:

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

    TencentDB for MySQL supports logical backup as described in Backing up Databases. You can manually generate and download logical backup for the entire instance or specified databases/tables. This document describes how to manually restore data from logical backup files.

    Directions

    Step 1. Download the backup file

    1. Log in to the TencentDB for MySQL console. In the instance list, click an instance ID or Manage in the Operation column to enter the instance management page.
    2. On the Backup and Restoration > Data Backup List tab, locate the backup file to be downloaded and click Download in the Operation column.
    3. Copy the download address in the pop-up dialog box, log in to the Linux CVM in the same VPC as the TencentDB instance as instructed in Customizing Linux CVM Configurations, and run wget to download the file over the high-speed private network.
      Note:

      • You can also click Download to download it directly. However, this may take longer.
      • wget command format: wget -c 'backup file download address' -O custom filename.xb
      Below is a sample:
      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.

    Note:

    xbstream can be downloaded at Percona's official website. Select Percona XtraBackup v2.4.6 or later. For more information on installation, see Installing Percona XtraBackup on Red Hat Enterprise Linux and CentOS.

    xbstream -x < test0.xb
    
    Note:

    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 -d --user-agent="Mozilla/5.0 (Windows NT x.y; rv:10.0) Gecko/20100101 Firefox/10.0" https://docs-tencentdb-1256569818.cos.ap-guangzhou.myqcloud.com/qpress-11-linux-x64.tar
      
    Note:

    If an error is displayed during the wget download, you can click here to download qpress locally and upload it to the Linux CVM instance. For more information, see Uploading Files from Linux or MacOS to Linux CVM via SCP.

    1. Extract the qpress binary files by running the following command:

      tar -xf qpress-11-linux-x64.tar -C /usr/local/bin
      source /etc/profile
      
    2. Decompress the backup file with qpress.

      qpress -d cdb-jp0zua5k_backup_20191202182218.sql.qp .
      
    Note:

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

    • 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 actually extracted by qpress.
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support