Backing up Databases
Download
Focus Mode
Font Size
To prevent data loss or corruption, you can use automatic or manual backup to back up the database.
Backup Overview
Backup Method
TencentDB for MySQL single-node (cloud disk), two-node (local disk), three-node (local disk), and cluster edition (cloud disk) support automatic backup and manual backup for database backup.
Backup Type
TencentDB for MySQL two-node and three-node instances support two backup types:
Physical backup: A full copy of physical data (supported by automatic backup).
Logical backup: Backup of SQL statements (supported by both manual backup and automatic backup).
Note:
Physical backup recovery requires unpacking with xbstream. For detailed operations, see Restoring Databases from Physical Backups.
If the number of tables in a single instance exceeds 300,000, it may cause backup failures, as well as impact database monitoring. It is recommended to reasonably standardize and control the number of tables per single instance to not exceed 300,000.
The backup duration is positively correlated with the data file size and the number of tables in the database. The larger the data file or the more tables there are, the longer the backup duration will be.
Since the data of Memory engine tables is stored in Memory, physical backup cannot be performed on them. To avoid data loss, it is recommended to convert Memory engine tables to InnoDB tables.
If an instance contains a large number of tables without primary keys, it may cause backup failures and affect the high availability of the instance. It is recommended to create primary keys or secondary indexes for tables without primary keys in a timely manner.
There will be a global read lock when you choose Manual Backup > Logical Cold Backup. It is recommended to perform this operation through the console (initiating a logical cold backup task in the console will back up the secondary database and will not affect the primary database).
The advantages of physical backup | The disadvantages of logical backup |
Fast backup speed. Supports streaming backup and compression. High backup success rate. Simple and efficient recovery. Coupled operations relying on backups, such as adding read-only instances or disaster recovery setups, will become faster. The average time taken to complete a physical backup is approximately one-eighth of that for a logical backup. The import speed of physical backups is approximately ten times faster than that of logical backups. | Recovery requires executing SQL and rebuilding indexes, leading to extended recovery times. Backup speed is slow, particularly noticeable with large data volumes. The backup process exerts pressure on the instance and may increase primary-secondary latency. Precision of floating-point numbers may be lost. Backup failures may occur due to various issues (such as incorrect views, and so on). Coupled operations relying on backups, such as adding read-only instances or disaster recovery setups, will become slower. |
TencentDB for MySQL single-node (cloud disk) and cluster edition (cloud disk)support snapshot backup.
Snapshot backup: Backups are created by taking snapshots of storage-layer disks (supported for both automatic and manual backups).
Note:
A single-node (cloud disk) instance adopts a single-node architecture, and backup is initiated on the primary node. It is recommended to perform the backup operation during off-peak hours.
Advantages of snapshot backup | Disadvantages of Snapshot Backup |
Fast backup speed. Relatively small occupied space. | Download is not supported. |
Backup Object
Data backup | Log Backup |
TencentDB for MySQL dual-node and tri-node: Automatic backups support full physical backups. Manual backups support full physical backup, full logical backup, and logical backup of a single database or table. Both automatic and manual backups support compression and download. TencentDB for MySQL single-node (cloud disk) and cluster edition (cloud disk): Automatic backups support full snapshot backups. Manual backups support full snapshot backups. Neither automatic nor manual backups support download. | Backup of database binlog log files is supported for TencentDB for MySQL single-node (cloud disk), two-node, three-node, and cluster edition (cloud disk): Log files occupy instance backup space. Log files support download but do not support compression. The retention period for log files can be set. |
Precautions
Automatic backup for TencentDB for MySQL has supported only physical backup since February 26, 2019. The default automatic backup method is physical backup, and logical backup is no longer available. Existing automatic logical backup instances will be automatically switched to physical backup.
This switch will not affect your business access but may impact your automatic backup usage habits. If you require logical backups, you can generate them through manual backup in the TencentDB for MySQL console or via API calls.
Manual backups default to logical cold backup. If you select logical cold backup for a full backup before configuration adjustment, the adjustment may take longer. To reduce the adjustment time, it is recommended to choose physical cold backup for full backups before configuration changes.
Backup files of instances occupy backup space. Use backup space reasonably, as exceeding the free quota will incur charges. See Backup Space Billing Overview.
It is recommended to perform backups during off-peak hours.
To prevent the required backup files from being deleted after the retention period is exceeded, promptly download the necessary backup files to your local device.
Avoid DDL operations during backup to prevent backup failures caused by table locking.
MySQL read-only instances do not support database backups.
If an instance is in an automatic backup cycle when cross-AZ migration is completed, the system generates an additional backup. For example, an automatic backup operation on August 5 is scheduled for an instance, but the instance is undergoing cross-AZ migration on that day. In this case, the system triggers another backup operation when migration is completed, in addition to the scheduled automatic backup operation.
Automatically back up MySQL data.
Automatically back up MySQL data.
Setting Automatic Backup
1. Log in to the TencentDB for MySQL console. On the instance list page, click the target Instance ID to go to the management page. Then choose Backup and Restoration > Auto-Backup Settings.
2. In the backup settings dialog that appears, select the backup parameters and click OK. The parameters are described as follows:
Note:
Rollback Feature relies on data backups within the backup cycle and retention period + log backups (binlog). Shortening the backup frequency or retention period will reduce the time range available for instance data rollback. Please consider this when configuring backups.
For example, if the backup cycle is set to Monday and Thursday with a 7-day retention period, you can roll back to any point in time within 7 days (actual storage duration of valid data and log backups).
Automatic backups cannot be manually deleted. You can set the backup retention period, after which backups are automatically deleted.
Increasing the retention period for data backups and log backups may result in additional billing costs for backup storage.
Reducing the retention period for log backups may affect the data rollback cycle of the instance.
The recoverable time for backups is calculated from the oldest valid physical backup or full logical backup relative to the current time. We recommend that you configure an appropriate backup retention period based on your actual business needs.
In the auto backup settings, the data backup settings support enabling periodic retention. Settings without periodic retention enabled are referred to as regular backup settings. The following describes the parameters in non-archive backup and archive backup respectively.
Non-archive backup settings
Parameter | Description |
Backup Start Time | Default time: The backup start time range automatically assigned by the system (00:00 to 12:00). Custom: Custom backup start time range (such as 02:00 to 06:00) is supported. It is recommended to set it to off-peak hours.* Note: The backup start time is only the time when the backup task enters the scheduling queue. For example, if you select to enable backup between 02:00 and 06:00, the system will initiate the backup task at a certain time point within this range. The specific initiation time point depends on the backend backup policy and backup system status. To ensure that the backup task does not affect the instance running, it may be delayed due to queuing. The time consumed by the backup task is proportional to data volume, with the longest time not exceeding 24 hours. |
Data Backup Retention Period | TencentDB for MySQL two-node, three-node, and cluster edition data backup files can be retained for 7 to 1830 days, with a default period of 7 days. The backup set will be automatically deleted upon expiration. The data backup files for MySQL single-node (cloud disk) can be retained for 7 to 30 days. The default value is 7 days, and the backup set will be automatically deleted upon expiration. |
Backup Cycle | Configuration Rules: Set by week: By default, all 7 days from Monday to Sunday are selected. Custom backup time selection is supported. To ensure your data security, please configure backups to occur at least twice a week. When the setting is made by month: To ensure your data security, the interval between any two adjacent backup dates in a month must not exceed 2 days. For example, if a backup is scheduled on the 1st, the next backup date cannot skip the 2nd, 3rd, or 4th to select the 5th. Note: When Monthly setting is selected, to avoid consecutive days without backup, the following date arrangements cannot be skipped: 27th/28th/1st, 28th/29th/1st, 29th/30th/1st, 28th/1st/2nd, 29th/1st/2nd, or 30th/1st/2nd. |
Transition-to-Cold Storage (optional) | Select the corresponding data backup cold storage policy and specify the number of days: Specify the Standard Storage Days: This sets the number of days after which a data backup file transitions to Standard Storage. Specify the Archive Storage Days: This sets the number of days after which a data backup file transitions to Archive Storage. For detailed explanations and policies about backup archiving to cold storage, see Configure Backup Archiving to Cold Storage. Note: The Archive Storage feature is currently unavailable. Stay tuned for updates. Note: Single-node (cloud disk) and cluster edition instances do not support configuring transition-to-cold storage. |
Log Backup Retention Period | TencentDB for MySQL two-node, three-node, and cluster edition log backup files can be retained for 7 to 3650 days, with a default period of 7 days. The backup set will be automatically deleted upon expiration. The log backup files for MySQL single-node (cloud disk) can be retained for 7 to 30 days. The default value is 7 days, and the backup set will be automatically deleted upon expiration. |
Transition-to-Cold Storage (optional) | Select the corresponding binlog backup cold storage policy and specify the number of days: Specify the Standard Storage Days: This sets the number of days after which a binlog file transitions to Standard Storage. Specify the Archive Storage Days: This sets the number of days after which a binlog file transitions to Archive Storage. For detailed explanations and policies about backup archiving to cold storage, see Configure Backup Archiving to Cold Storage. Note: The Archive Storage feature is currently unavailable. Stay tuned for updates. Note: Single-node (cloud disk) and cluster edition instances do not support configuring transition-to-cold storage. |
*The available backup start times may be limited by system scheduling resources. If certain time periods are not selectable, the backup queue for that time period is full. It is recommended to select an idle time period to complete the settings.
Archive backup settings
Archive backup settings
Note:
Single-node (cloud disk) and cluster edition instances do not support the scheduled backup setting feature.
The retention period for scheduled backups must be longer than that set for regular backups.
Parameter | Description |
Backup Start Time | Default time: The backup start time range automatically assigned by the system (00:00 to 12:00). Custom: Custom backup start time range (such as 02:00 to 06:00) is supported. It is recommended to set it to off-peak hours. Note: The backup start time is only the time when the backup task enters the scheduling queue. For example, if you select to enable backup between 02:00 and 06:00, the system will initiate the backup task at a certain time point within this range. The specific initiation time point depends on the backend backup policy and backup system status. To ensure that the backup task does not affect the instance running, it may be delayed due to queuing. The time consumed by the backup task is proportional to data volume, with the longest time not exceeding 24 hours. |
Data Backup Retention Period | MySQL two-node and three-node data backup files can be retained for 7 to 1830 days. The default value is 7 days, and the backup set will be automatically deleted upon expiration. |
Backup Cycle | Configuration Rules: Set by week: By default, all 7 days from Monday to Sunday are selected. Custom backup time selection is supported. To ensure your data security, please configure backups to occur at least twice a week. When the setting is made by month: To ensure your data security, the interval between any two adjacent backup dates in a month must not exceed 2 days. For example, if a backup is scheduled on the 1st, the next backup date cannot skip the 2nd, 3rd, or 4th to select the 5th. Note: When Monthly setting is selected, to avoid consecutive days without backup, the following date arrangements cannot be skipped: 27th/28th/1st, 28th/29th/1st, 29th/30th/1st, 28th/1st/2nd, 29th/1st/2nd, or 30th/1st/2nd. |
Archive Backup Retention Period | Data backup files can be retained for 90 days - 3650 days. The default value is 1080 days. Backup sets will be automatically deleted upon exceeding the retention period. |
Archive Backup Retention Policy | Supports configuring the number of retained backups on a monthly, quarterly, or yearly basis. |
Start Date | The execution time for regularly retained backups. |
Transition-to-Cold Storage (optional) | Select the corresponding data backup cold storage policy and specify the number of days: Specify the Standard Storage Days: This sets the number of days after which a data backup file transitions to Standard Storage. Specify the Archive Storage Days: This sets the number of days after which a data backup file transitions to Archive Storage. For detailed explanations and policies about backup archiving to cold storage, see Configure Backup Archiving to Cold Storage. Note: The Archive Storage feature is currently unavailable. Stay tuned for updates. |
Log Backup Retention Period | Log backup files can be retained for 7 days - 3650 days. The default value is 7 days. Backup sets will be automatically deleted upon expiration. |
Transition-to-Cold Storage (optional) | Select the corresponding binlog backup cold storage policy and specify the number of days: Specify the Standard Storage Days: This sets the number of days after which a binlog file transitions to Standard Storage. Specify the Archive Storage Days: This sets the number of days after which a binlog file transitions to Archive Storage. For detailed explanations and policies about backup archiving to cold storage, see Configure Backup Archiving to Cold Storage. Note: The Archive Storage feature is currently unavailable. Stay tuned for updates. |
View Retention Plan
Note:
Single-node (cloud disk) and cloud disk-based instances do not currently support the retention plan feature.
After the scheduled backup retention policy is selected in the backup settings, you can click View Retention Plan to preview it.
Blue dates indicate the dates for regular backups.
Red dates indicate the dates for scheduled backups.
You can click Regular Backups or Scheduled Backups to hide the corresponding date color coding for easier preview.
The backup plan preview currently shows the backup retention status for the next year, for reference only.
Manually back up MySQL data.
Manually back up MySQL data.
The manual backup feature allows you to self-initiate backup tasks.
Note:
MySQL two-node and three-node instances support full physical backup, full logical backup, and logical backup of a single database or table for manual backups.
MySQL manual backups for two-node and three-node instances can be manually deleted from the backup list to free up backup space, preventing space wastage and occupation. Unless manually deleted, backups will be retained until the database instance is decommissioned.
MySQL single-node (cloud disk) instances support full snapshot backups for manual backups.
MySQL single-node (cloud disk) instances do not support deletion of manual backups.
A manual backup cannot be initiated when an instance is executing a daily automatic backup task.
Manual backups enter the backup queue with the highest priority. The actual backup start time is determined by the task start time field in the backup list.
1. Log in to the TencentDB for MySQL console, go to the instance list, click an Instance ID to go to the management page, and choose Backup and Restoration > Manual Backup.
2. In the pop-up backup settings dialog box, select the backup method and objects, fill in the remarks name, and click OK.
Note:
If it is a single database backup or single table backup under logical backup, in the Select Database/Table section on the left, select the databases or tables to be backed up and add them to the list on the right. If you do not have any databases or tables yet, create databases or tables first.
Manual backups default to logical cold backup. If you select logical cold backup for a full backup before configuration adjustment, the adjustment may take longer. To reduce the adjustment time, it is recommended to choose physical cold backup for full backups before configuration changes.
1. Log in to the TencentDB for MySQL console, go to the instance list, click the Target Instance ID to go to the management page, and choose Backup and Restoration > Manual Backup.
2. Enter the remarks name and click OK.
Backup Data Protection
Tamper-proof
The backup system of TencentDB for MySQL adopts a dual-mode approach to store backup data: full physical backups, full logical backups, and log backups are stored in COS, while full snapshot backups are stored in the cloud disk snapshot service. Both methods feature WORM (write once read many) immutable characteristics.
Protection against malicious/accidental deletion
User-initiated deletion: Users are allowed to delete manual backup data (manual logical backups and manual physical backups), but are not allowed to delete automatic backup data (relevant documentation can be found in Deleting Backups).
Automatic Backup Expiration Deletion: Automatic backup data can be deleted (automatically deleted upon expiration), but this also prevents automatic backups from being disabled. The minimum backup retention period is 7 days, and backups must occur at least twice per week (for details, see Automatically back up MySQL data). Therefore, full data and log data generated by automatic backups cannot be completely deleted.
FAQs
1. Can Backups Beyond the Backup Retention Period Still Be Downloaded or Restored?
Expired backup sets will be automatically deleted and cannot be downloaded for restoration.
It is recommended that you set the backup retention period appropriately based on your needs, or download backup files to your local device from the TencentDB for MySQL console. (Note that backup files for single-node cloud disk instances are currently not supported for download.)
You can also manually back up instance data in the console. Manual backups will be saved indefinitely.
Note:
Manual backups also consume backup space. Please use the backup space appropriately to avoid incurring additional costs.
2. Can Backups Be Manually Deleted?
Automatic backups cannot be manually deleted. You can set the backup retention period, and they will be automatically deleted when it expires.
Two-node and three-node instance manual backups can be manually deleted in the backup list of the TencentDB for MySQL console. They will be retained indefinitely if not manually deleted. Manual backups for single-node cloud disk instances are not supported for deletion at this time.
3. Can Data and Log Backups Be Disabled?
Cannot be disabled. However, you can reduce backup frequency and delete unused manual backup data via the TencentDB for MySQL console to decrease backup space usage (manual backups for single-node cloud disk instances are currently not supported for deletion).
4. How to Reduce Backup Space Overhead?
Delete unused manual backup data (Manual backups can be deleted on the instance management page > Backup & Restore page in the TencentDB for MySQL console. Note that manual backups for single-node cloud disk instances are not supported for deletion at this time).
Reduce the automatic backup frequency for non-core business data (you can adjust the backup cycle and backup retention period in the console, with a minimum of two backups per week).
Note:
Restore feature is based on data backups within the backup cycle and retention days + log backups (binlog). Reducing the automatic backup frequency and retention days will affect the time range for instance data restoration. Please evaluate your backup configuration accordingly.
Shorten the retention time for data backups and log backups of non-core businesses (a backup retention period of 7 days is sufficient for most scenarios).
Configure the transition-to-cold storage policy to transition backup files and reduce the storage costs. For more information, see Configuring Transition-to-Cold Storage.
Business Scenario | Backup Retention Period |
Core businesses | It is recommended to set the backup retention period to 7 to 3650 days. Enable regular backups to achieve long-term preservation of backups. |
Non-core and non-data businesses | Recommended: 7 days |
Archiving businesses | It is recommended to set the backup retention period to 7 days. Manually back up data according to actual business needs and delete them promptly after use. |
Testing businesses | It is recommended to set the backup retention period to 7 days. Manually back up data according to actual business needs and delete them promptly after use. |
Help and Support
Was this page helpful?
You can also Contact sales or Submit a Ticket for help.
Help us improve! Rate your documentation experience in 5 mins.
Feedback