tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TencentDB for MySQL
    Documentation
    Download PDF

    Upgrading from MySQL 5.7 to MySQL 8.0

    Last updated: 2023-11-20 15:28:58
    Download PDF
      TencentDB for MySQL supports direct database version upgrade through the console. This document describes how to upgrade the CDB engine from MySQL 5.7 to MySQL 8.0 in the console.

      Overview

      MySQL 8.0 offers significant performance improvements and enhanced features. Its performance under high concurrency and large data volume scenarios is particularly prominent, offering greater system performance and stability.
      The upgrade of the following database engine versions is supported by TencentDB for MySQL:
      From MySQL 5.5 to MySQL 5.6
      From MySQL 5.6 to MySQL 5.7
      From MySQL 5.7 to MySQL 8.0
      Note:
      Single-node instances of the cloud disk edition do not support engine version upgrade.
      The upgrade process has no impact on existing business.

      Considerations

      Feature Limits

      Limit Type
      Description
      Instance Limits
      Only two-node and three-node local disk instances support the upgrade operation from MySQL 5.7 to 8.0.
      If a master instance has multiple read-only instances, the read-only instances will be upgraded together.
      If a master instance has a disaster recovery instance, the disaster recovery instance must first be released before upgrading. After the upgrade is completed, a new disaster recovery instance can be recreated.
      Before initiating the database version upgrade, ensure the instance status is running, and there are no other tasks being executed or to be executed. If there are other tasks, please wait until the task is completed.
      Before upgrading, a compatibility check will be performed on the instance. The check results are valid for 24 hours. If it exceeds this time, you need to recheck it. During the check, if you add a read-only instance or a disaster recovery instance, you also need to re-execute the task.
      Upgrade Limits
      Upgrading across major versions is not supported. For example, MySQL 5.6 cannot be directly upgraded to MySQL 8.0. You need to upgrade it to MySQL 5.7 first, and then upgrade to MySQL 8.0.
      Downgrading after upgrade is not supported.
      Note:
      After the upgrade is completed, backup sets of the old version cannot be used to restore the new version instance. If you need to perform cloning or rollback operations, please select the backup set or point in time generated after the instance upgrade. Backup sets of older versions can be downloaded for local restoration operations.
      
      Database Limits
      After upgrading the database instance to MySQL 8.0, the unsupported settings in the sql_mode parameter will be reset to the default values of MySQL 8.0.
      If a partition table is used in the RocksDB storage engine, upgrading to MySQL 8.0 is not supported. You need to modify it to the InnoDB engine or delete the partition table to upgrade.
      If there are tables, views, stored procedures and triggers that contain MySQL 8.0 reserved keywords in the database instance, the upgrade will fail.
      If stored procedures, triggers, views, or functions in the database contain Changes in MySQL 8.0, the upgrade will fail.
      If you are using the MyISAM or Memory engines, upgrading is not supported. Please convert to the InnoDB engine first.
      Note:
      If your database is already running on the InnoDB engine, but some tables are still using other engines, please use the ALTER TABLE <table name> engine=InnoDB; command to convert those tables to the InnoDB engine before upgrading.
      

      Preparation

      Please be sure to carefully compare the differences between the versions before and after the upgrade. We recommend testing syntax on a new MySQL 8.0 instance-this will help avoid issues with lower-version syntax or features not being supported after the upgrade. For more information, please refer to List of functional differences between MySQL8.0 and MySQL5.7.
      We recommend that you clone the original instance for upgrade testing first, and confirm that all functions are normal before upgrading.
      Before upgrading, please check whether there is a successful record of full data backup in the past week. If necessary, please perform a full data backup.
      During the database upgrade, a brief disconnection of the TencentDB for MySQL service may occur. It is suggested that you carry out the upgrade during off-peak period or ensure your application has an automatic reconnection mechanism.
      Please ensure that free disk space is available before upgrading.
      Before upgrading, review the Keywords and Reserved Words that you should avoid using them when creating custom functions.
      To ensure stability and performance of MySQL in the new version, after the database version has been upgraded, CDB will not support the inspection or modification of MySQL 5.7 parameters that have been deprecated in MySQL 8.0. For more information, please refer to Options and Variables Removed in MySQL 8.0. Before upgrading, it is recommended that you back up modification records of relevant parameters for subsequent operations and audits.

      Directions

      1. Log in to the TencentDB for MySQL console, fin the target instance in the instance list, and select More > Upgrade Version in the Operation column.
      Note:
      You can also click on the Instance ID to go to the instance details page and click Upgrade Version after the database version.
      
      2. In the Database Version Upgrade window, complete the required settings, read and select the Database Version Upgrade Rule, then click on Upgrade Check.
      
      Parameter
      Description
      Delay Threshold for Data Consistency Check
      Enter an integer from 1 to 10 (The new configuration may take longer to take effect if the threshold is lower.)
      Switch Time
      During Maintenance Time: Set the switch time to During maintenance time, so that the switch will be initiated within the next maintenance time after the upgrade. For more information, see Setting Instance Maintenance Time.
      Upon Upgrade Completion: Set the switch time to Upon Upgrade Completion, so that the switch will be initiated immediately after the instance data migration has been completed.
      Database Version
      MySQL 8.0 is selected by default.
      3. During the upgrade check process, the system will start a check task to verify whether the instance to be upgraded meets the criteria of not having any disaster recovery instances and having parameter configurations that meet the specifications. After the check is completed and the upgrade conditions are met, click Upgrade.
      3.1 Verify whether there are any disaster recovery instances under the master instance. If master instance has a disaster recovery instance, the check will fail. You need to release the disaster recovery instance first, and recreate it once the upgrade is completed.
      3.2 The system will automatically verify if the parameter configuration is up to the specified standards. If it doesn't meet the specification requirements, adjustments can be made according to the error message in the check box.
      Note:
      After the upgrade check task is executed successfully, please perform the upgrade within 24 hours. If it times out, check again.
      4. After upgrading, return to the instance list. Select Task List from the left sidebar to track the progress and details of the database version upgrade task for the instance.
      5. Find the Task ID of the recent database version upgrade in the task list, and you can query the task progress. By clicking on Task Details in its Operation column, more upgrade-related information will be displayed.
      6. The database version upgrade is completed when the instance status changes from Changing configuration to Running.
      Note:
      If you select During Maintenance Time as the switch time for the upgrade, the instance status will display Awaiting switch if the next maintenance time has not been reached after the instance upgrade is completed. If an immediate switch is necessary, you can click Switch Now in the Operation column of the instance list. However, be aware that the switch will involve a momentary disconnection lasting for a few seconds. Please ensure that your business is equipped with a reconnection mechanism.

      Appendix: MySQL 8.0 vs. MySQL 5.7

      MySQL 8.0 introduces over 300 new features, including invisible indexes, descending indexes, functional indexes, common table expressions, window functions, instant column addition, implicit primary keys and more. These capabilities make development more adaptable and efficient.
      MySQL 8.0 introduces improvements in performance, implementing features such as Histograms, HASH JOINS, parallel queries, and JSON columns that enhance capability. These additions have served to increase both the query performance and response time of the database, enabling it to process large-scale data and complex queries more swiftly.
      MySQL 8.0 supports the caching_sha2_password authentication plugin, thus enhancing security.
      MySQL 8.0 Optimizer enhances functionality.
      MySQL8.0 enhances replication performance.
      MySQL 8.0 supports the creation of multi-valued indexes and optimization under derived conditions.
      MySQL 8.0 supports the reading of MySQL authorization tables.
      MySQL 8.0 supports resource allocation control.

      FAQs

      Will TencentDB for MySQL automatically back up data before upgrade?

      TencentDB for MySQL adopts the daily real-time dual-server hot backup mechanism, which supports lossless restoration of data from the last 7–1830 days based on data backup and log backup (binlog).

      Can TencentDB for MySQL be downgraded from MySQL 8.0 to MySQL 5.7?

      No. Database engine downgrading is not supported. To use a MySQL 5.7 instance, you need to terminate or return the existing MySQL 8.0 instance, and then purchase a MySQL 5.7 instance.

      Will there be a source-replica delay during upgrade?

      Source instance upgrade requires data comparison and may cause a source-replica delay.

      Will the instance switch after database engine version upgrade affect my TencentDB for MySQL instance?

      The upgrade won't affect your business, but the TencentDB for MySQL instance may be disconnected for a few seconds. We recommend you configure an automatic reconnection feature for your application and conduct the switch during the instance maintenance time.

      How long will it take to upgrade the database engine version of a TencentDB for MySQL instance? How do I check the upgrade progress?

      The time it takes depends on the instance's data volume and the read requests to replicate data. TencentDB for MySQL upgrade involves data migration and generally takes a relatively long time. Your business will not be affected during the upgrade and can be accessed as per usual.
      You can monitor the progress and details of tasks in the task list. Keep an eye on the task execution progress for insight into the upgrade status.

      Why is the instance always in the "Waiting for switch (after upgrade)" status?

      It may be because you select During Maintenance Time for Switch Time, and the switch will be initiated within the next maintenance time after the upgrade. To switch immediately, find the target instance in the instance list and click Switch Now in the Operation column. The switch will cause a momentary disconnection. Make sure that your business has a reconnection mechanism.
      
      
      Contact Us

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

      Contact Us
      Technical Support

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

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy