The content of this page has been automatically translated by AI. If you encounter any problems while reading, you can view the corresponding content in Chinese.
Help & Documentation>TencentDB for MySQL>Operation Guide>Backup and rollback>Restoring Database from Backup File>Restoring a Database Using Logical Backup

Restoring a Database Using Logical Backup

Last updated: 2026-04-29 14:21:22

On this page:

Scenarios

Note:
To save storage space, physical backup and logical backup files of TencentDB for MySQL are first compressed using qpress, then packaged with xbstream (xbstream is a packaging/unpackaging tool from Percona) for compression and packaging.
TencentDB for MySQL supports the logical backup method. Users can generate logical backup files by manually backing up via the console and download to obtain logical backup files for the entire instance or partial databases/tables. This document describes how to use logical backup files for manual restoration.
This document describes a recovery method that is applicable only to Linux platforms and is not currently supported on Windows platforms.
For data recovery on Windows, see Command Line Tool Migration.
Supported instance editions: MySQL Two-node, Three-node.

Operation Steps

Step 1: Download Backup Files

1. Log in to the MySQL console, in the instance list, click Instance ID or Operation > Manage, and go to the instance management page.
2. On the instance management page, choose Backup and Restoration > Data Backup List, choose the backup to download, and click Download in the Operation column.
3. In the pop-up dialog box, it is recommended that you copy the download address and log in to the CVM (Linux system) in the VPC where the cloud database resides, then use the wget command for high-speed download over the private network for higher efficiency.
Note:
You can also choose Download to download directly, but it is more time-consuming.
Command format for wget: wget -c 'backup file download URL' -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

Use xbstream to unpack backup files.
Note:
For the download address of the xbstream tool, see Percona XtraBackup official website. The major version of Percona XtraBackup should be compatible with your TencentDB for MySQL version. For example, MySQL 5.7 corresponds to Percona XtraBackup 2.4, and MySQL 8.0 corresponds to Percona XtraBackup 8.0.
For MySQL 5.6 and MySQL 5.7 databases, it is recommended to use Percona-XtraBackup-2.4.26 or later. For installation instructions, see Percona XtraBackup 2.4.
When the database version is MySQL 8.0, it is recommended to use Percona-XtraBackup-8.0.30-23 or later. For installation instructions, see Install Percona XtraBackup 8.0 overview.
xbstream -x < test0.xb
Note:
Replace test0.xb with your backup file.
The unpacking result is as shown in the figure below:


Step 3: Unpack the Backup Files

1. Download the qpress tool via 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 the wget download reports an error, you can click download qpress tool to download it locally, then upload the qpress tool to Linux CVM. See upload files to Linux CVM via SCP.
2. Use the following command to extract the qpress binary file.
tar -xf qpress-11-linux-x64.tar -C /usr/local/bin
source /etc/profile
3. Use qpress to extract the backup files.
qpress -d cdb-jp0***5k_backup_20191202182218.sql.qp .
Note:
Please locate the backup file with the .sql.qp suffix based on the decompression time, and replace cdb-jp0***5k_backup_20191202182218 with that file name.
The decompression result is as shown in the figure below:


Step 4: Import Backup to Target Database

Note:
If permission issues occur during import (SET GLOBAL is prohibited), you can comment out the following in the SQL file: SET GLOBAL INNODB_STATS_AUTO_RECALC=ON */;, or directly delete that SET GLOBAL statement.

Because the stability and security of TencentDB for MySQL are considered, it restricts super, shutdown, and file privileges. When executing SET statements on TencentDB for MySQL, you may encounter the following error:
#1227-Access denied;you need(at least one of)the SUPER privilege (s) for this operation
Run the following command to import the sql file to the target database:
mysql -uroot -P3306 -h127.0.0.1 -p < cdb-jp0***5k_backup_20191202182218.sql
Note:
This document uses the example of importing a MySQL instance running on local port 3306. You can replace it according to your actual situation.
Replace cdb-jp0***5k_backup_20191202182218.sql with the actual sql file extracted using qpress.
中国站 - English