Backing up Databases

Last updated: 2020-11-17 17:02:42

    To avoid data loss or corruption, you can back up a database automatically or manually.

    Backup Overview

    Backup modes

    TencentDB for MySQL High-Availability Edition and Finance Edition support the automatic backup and manual backup of databases.

    Backup types

    TencentDB for MySQL High-Availability Edition and Finance Edition support two backup types:

    • Physical backup, which replicates full physical data (available for both automatic backup and manual backup).
    • Logical backup, which backs up SQL statements (only available for manual backup).

      Note:

      • To restore a database from a physical backup, xbstream is required to decompress backup files first. For more information, see Restoring Databases from Physical Backups.
      • If the number of tables in a single instance exceeds one million, backup may fail and database monitoring may be affected. Please make sure the number of tables in a single instance is no more than one million.
      • As the data of tables created by the MEMORY storage engine is stored in the memory, physical backups cannot be created for such tables. To avoid data loss, we recommend that you convert them to InnoDB tables.
      • If there are a high number of tables in an instance with no primary key, backup may fail and instance high availability may be affected. Please create primary keys or secondary indexes for such tables.
    Physical Backup Advantages Logical Backup Disadvantages
  • High backup speed.
  • Streaming backup and compression are supported.
  • High success rate.
  • Simple and efficient restoration.
  • Faster backup-based coupling operations such as adding real-only and disaster recovery instances.
  • 1/8 of average time needed for creating a logical backup.
  • Ten times faster than logical backups during import.
  • Long time needed to restore as it takes time to run SQL statements and build indexes.
  • Low backup speed, especially when there are massive amounts of data.
  • Possible increase in source-replica delay due to the pressure on instances during backup.
  • Possible loss of precision information of floating points.
  • Potential backup failures due to wrong views and other problems.
  • Slower backup-based coupling operations such as adding read-only and disaster recovery instances.
  • Backup objects

    Data Backup Log Backup
    TencentDB for MySQL High-Availability Edition and Finance Edition:
  • Automatic backup supports full physical backup.
  • Manual backup supports full physical backup, full logical backup, and single-database/table logical backup.
  • Both automatic and manual backups can be compressed and downloaded.
  • TencentDB for MySQL High-Availability Edition and Finance Edition support binlog backup:
  • Log files occupy the instance's backup space.
  • Log files can be downloaded but cannot be compressed.
  • Retention periods can be set for log files.
  • Notes

    • Since February 26, 2019, the automatic backup feature of TencentDB for MySQL only supports physical backup (default type) and no longer provides logical backup. Existing automatic logical backups will be switched to physical backups automatically.
      This will not affect your business access, but may impact your automatic backup habit. If you need logical backups, you can use the manual backup feature in the TencentDB for MySQL console or call the CreateBackup API to generate logical backups.
    • Both logical and physical backup files will be compressed, so some files may be unusable after being downloaded. In that case, you can use the table backup feature in manual logical backup. For more information, see Backing up MySQL Data Manually.
    • Instance backup files occupy backup space. We recommend that you plan the usage of backup space appropriately. Usage of backup space that exceeds the free tier will incur fees. For more information, please see Backup Space Billing.
    • We recommend that you back up your data during off-peak hours.
    • We recommend that you download the backup files locally before they are deleted after the retention period ends.
    • Do not perform DDL operations during the backup process to avoid backup failure due to table locking.
    • TencentDB for MySQL Basic Edition instances cannot be backed up.

    Backing up MySQL Data Automatically

    1. Log in to the TencentDB for MySQL console, click an instance ID/name on the instance list page to access the instance’s management page, and select Backup and Restore > Auto Backup Settings.
    2. Select backup parameters in the pop-up window (details are shown as below) and click OK:

      Note:

      • The rollback feature relies on the backup cycle and retention days of data backups and log backups (binlog). Rollback will be affected if you reduce the automatic backup frequency and retention period. Please select the parameters as needed.
        For example, if the backup cycle is set as Monday and Thursday and the retention period is set as seven days, you can roll a database back to any point of time in the past seven days (which is the actual retention days of data backups and log backups).
      • Automatic backups cannot be deleted manually. You can set the retention period for automatic backups, and the backups will be deleted automatically when they expire.
      Parameter Description
      Backup cycle To ensure data security, please back up your data at least twice a week. All seven days of the week will be selected by default.
      Backup start time
      • The default backup start time is automatically assigned by the system.
      • You can set a start time as needed. We recommend that you set it to off-peak hours. This is just the start time of the backup process and does not indicate the end time.
        For example, if the backup start time is set to 02:00-06:00, the system will initiate a backup at a point in time during 02:00-06:00, which depends on the backend backup policy and backup system conditions.
      Data backup retention time Data backup files can be retained for 7 (default value) to 732 days.
      Log backup retention time Log backup files can be retained for 7 (default value) to 732 days. The number of days set for log backup retention must be smaller than that for data backup retention.

    Backing up MySQL Data Manually

    The manual backup feature allows you to initiate a backup task manually.

    Note:

    • Manual backup supports full physical backup, full logical backup, and single-database/table logical backup.
    • Manual backups can be manually deleted from the backup list in the console. You can delete manual backups that are no longer in use to free up space. Manual backups can be retained permanently as long as they are not deleted.
    • When the instance is performing daily automatic backup, no manual backup tasks can be initiated.
    1. On the instance list page, click an instance ID/name to access the instance’s management page, and select Backup and Restore > Manual Backup.
    2. Select the backup mode and object in the pop-up window and click OK.

      Note:

      For single-database/table logical backup, select the database or table to be backed up in Select database & table in the left column and add the selected item to the right column. If you don't have a database, please create a database/table first.

    FAQs

    1. Can I download or restore backup files that exceed the retention period?

    Expired backup sets will be deleted automatically and cannot be downloaded or restored.

    • We recommend that you configure a backup retention period based on business needs or download the backup files locally via the TencentDB for MySQL console.
    • You can also manually back up instance data in the console. Manual backups will be retained permanently.

      Note:

      Manual backups will also take up the backup space. We recommend that you plan the usage of the backup space appropriately to reduce costs.

    2. Can I delete backups manually?

    • Automatic backups cannot be deleted manually. You can set the retention period for automatic backups, and the backups will be deleted automatically when they expire.
    • Manual backups can be manually deleted from the backup list in the TencentDB for MySQL console. Manual backups can be retained permanently as long as they are not deleted.

    3. Can I disable data and log backups?

    No. However, you can reduce the backup frequency and delete manual backups no longer used via the TencentDB for MySQL console to lower the capacity usage.

    4. How can I reduce the backup capacity costs?

    • Delete manual backups that are no longer used (you can log in to the TencentDB for MySQL console, click an instance ID/name to access the instance’s management page, and delete manual backups on the "Backup and Restore" tab).
    • Reduce the frequency of automatic data backup for non-core businesses (you can adjust the backup cycle and retention period in the console, and the frequency should be at least twice a week).

      Note:

      The rollback feature relies on the backup cycle and retention days of data backups and log backups (binlog). Rollback will be affected if you reduce the automatic backup frequency and retention period. Please select the parameters as needed.

    • Reduce the retention period of data and log backups for non-core businesses (a 7-day retention period can meet the requirements of most scenarios).
    Business Scenario Recommended Backup Retention Period
    Core businesses 7-732 days
    Non-core and non-data businesses 7 days
    Archive businesses 7 days. We recommend that you manually back up data based on your business needs and delete the backups promptly after use
    Testing businesses 7 days. We recommend that you manually back up data based on your actual business needs and delete the backups promptly after use

    Was this page helpful?

    Was this page helpful?

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