Operation Scenarios

Note：

To save storage capacity, both physical and logical backups in TencentDB for MySQL will be compressed and packaged. Specifically, a backup file is compressed with qpress first and then packed with xbstream offered by Percona.

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

• XtraBackup only supports Linux but not Windows.
• For more information on how to restore data on Windows, please see Offline Migration of Data.

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 how to install the tool, please see Percona XtraBackup 2.4.
• Supported instance editions: TencentDB for MySQL High-Availability Edition and Finance Edition.
• Instances with data encryption enabled cannot be restored from a physical backup.

Directions

Note：

This document takes a CVM instance running CentOS and a MySQL v5.7 instance as an example.

Step 1. Download the backup file

You can download data backups and log backups of TencentDB for MySQL instances in the console.

Note：

Each IP can have up to 10 download links by default, with a download speed limit of 20–30 Mbps each.

1. Log in to the 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, select Backup and Restore > Data Backup List, locate the desired backup, and 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 for download over the private network at a higher speed.

   Note：

   • You can also select Download to download it directly, which takes more time though.
   • wget command format: wget -c 'backup file download address' -O custom filename.xb

   Below is a sample:

   wget -c 'https://mysql-database-backup-sh-1218.cos.ap-nanjing.myqcloud.com/12427%2Fmysql%2F0674-ffba-11e9-b592-70bd%2Fdata%2Fautomatic-delete%2F2019-12-03%2Fautomatic%2Fxtrabackup%2Fbk_61_156758150%2Fcdb-293fl9ya_backup_20191203000202.xb?sign=q-sign-algorithm%3Dsha1%26q-ak%3DAKzxfbLJ1%26q-sign-time%3D1575374119%3B1575417319%26q-key-time%3D1575374119%3B1575417319%26q-header-list%3D%26q-url-param-list%3D%26q-signature%3Dba959757&response-content-disposition=attachment%3Bfilename%3D%22yuan177685_backup_20191203000202.xb%22&response-content-type=application%2Foctet-stream' -O ~/test.xb

Step 2. Restore data

2.1 Unpack the backup file

Run the xbstream command to unpack the backup file to the target directory.

xbstream -x -C /data < ~/test.xb

Note：

• /data is used as an example in this document. You can replace it with a real path according to the actual situation.
• Replace ~/test.xb with your backup file.

The unpacking result is as shown below:

2.2 Decompress the backup file

1. Download qpress by running the following command.

   wget http://www.quicklz.com/qpress-11-linux-x64.tar

   Note：

   If an error is displayed for the wget download operation, you can go to QuickLZ's official website to download the qpress locally, and then upload it to the Linux CVM instance. For more information, please see Uploading Files to Linux CVM with 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. Then, decompress all .qp files in the target directory by running the following command:

   xtrabackup --decompress --target-dir=/data

   Note：

   • /data is the target directory where the backup file was previously stored. You can replace it with a real path according to the actual situation.
   • The --remove-original options is supported only in Percona Xtrabackup 2.4.6 and higher.
   • xtrabackup won't delete the original files during decompression by default. If you want to delete them upon the completion of decompression, add the --remove-original parameter to the above command.

   

2.3 Prepare the backup file

After a backup file is decompressed, perform the "apply log" operation by running the following command.

xtrabackup --prepare  --target-dir=/data

If the execution result contains the following output, it means that the preparation succeeded.

2.4 Modify the configuration file

1. Run the following command to open the backup-my.cnf file.

   vi /data/backup-my.cnf

   Note：

   The target directory /data is used as an example in this document. You can replace it with a real path according to the actual situation.

2. Given the existing version issues, the following parameters need to be commented in the extracted file backup-my.cnf.
   • innodb_checksum_algorithm
   • innodb_log_checksum_algorithm
   • innodb_fast_checksum
   • innodb_page_size
   • innodb_log_block_size
   • redo_log_version

2.5 Modify file attributes

Modify file attributes and check whether files are owned by a TencentDB for MySQL user.

chown -R mysql:mysql /data

Step 3. Start the mysqld process and log in for verification

1. Start the mysqld process.

   mysqld_safe --defaults-file=/data/backup-my.cnf --user=mysql --datadir=/data &

2. Log in to the MySQL client for verification.

   mysql  -uroot

   

Backup FAQs

See Common Issues and Failure Reasons.