tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TDSQL-C for MySQL
    Documentation
    Download PDF

    Upgrading Database Version from MySQL 5.7 to 8.0 Through DTS

    Last updated: 2024-06-07 14:21:10
    Download PDF

      Background

      The database MySQL 8.0 of TDSQL-C for MySQL combines comprehensive management and control services with the new TXSQL kernel, ensuring faster, more stable enterprise-level services and a wide range of industry scenarios. Currently, the TDSQL-C for MySQL console does not support directly upgrading to MySQL 8.0. To upgrade, you can use the same cloud account to create a cluster with MySQL 8.0 in the source region, and then synchronize the data from the source cluster to the new cluster through Data Transfer Service (DTS). Finally, after confirming the data is accurate, delete the source cluster and switch the business to the new cluster to achieve the database version upgrade.

      Use Cases

      Synchronization Link
      Source Database and Version
      Target Database and Version
      TDSQL-C for MySQL > TDSQL-C for MySQL
      TDSQL-C for MySQL MySQL5.7
      Synchronization between databases under the same cloud primary account
      TDSQL-C for MySQL MySQL8.0

      Notes

      During full data synchronization, DTS consumes certain source instance resources, which may increase the load and pressure of the source instance. If your database configuration is low, we recommend you synchronize the data during off-peak hours.
      To avoid duplicate data, ensure the tables to be synchronized have a primary key or a non-null unique key.
      By default, a lock-free method is used during synchronization. Global locks (FTWRL) are not applied to the source database, only table locks are applied to tables without a primary key, and no locks are applied elsewhere.
      During data synchronization, DTS will write a system database __tencentdb__ in the source database using the account that executes the synchronization task, to record data comparison information during the synchronization process.
      To ensure that data comparison issues can be traced later, the synchronization task will not delete the __tencentdb__ in the source database after completion.
      The system database __tencentdb__ occupies a very small space, roughly one thousandth to one ten-thousandth of the source database storage space (for example, if the source database's storage space is 50 GB, then the space occupied by the system database __tencentdb__ will be approximately 5 MB to 50 MB). It also uses a single-thread, waiting connection mechanism, so it almost has no impact on the performance of the source database, nor does it pre-empt resources.

      Prerequisites

      The following permissions are required for the source database:
      GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, PROCESS, SELECT ON *.* TO 'account'@'%' IDENTIFIED BY 'password';
      GRANT ALL PRIVILEGES ON `__tencentdb__`.* TO 'account'@'%';
      FLUSH PRIVILEGES;
      The following permissions are required for the target database: ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, and UPDATE.

      Use Limits

      Only synchronization of base tables, views, stored procedures, and functions is supported.
      When synchronizing views, stored procedures, and functions, DTS will check whether the DEFINER corresponding to user1 ([DEFINER = user1]) in the source database and the synchronization account user2 are the same. If not, after the synchronization, DTS will modify the SQL SECURITY attribute of user1 in the target database from DEFINER to INVOKER ([INVOKER = user1]), while setting the DEFINER in the target database to the synchronization account user2 ([DEFINER = synchronization account user2]). If the definition of the view in the source database is too complex, the task may fail.
      If the source is a non-GTID instance, DTS does not support source-end HA switching. Once the source instance switches, it may cause an interruption in DTS incremental synchronization.
      Only synchronization of InnoDB, MyISAM, and TokuDB engine databases is supported. Tables using engines other than these three will be skipped by default. Among these, if the source database contains TokuDB engine data in compression mode, the target database must also support the compression mode for synchronization; otherwise, the task will report an error.
      Interrelated data objects need to be synchronized together; otherwise, synchronization will fail. Common associations include: View Reference Table, View Referencing View, and Primary-Foreign Key Association Table.
      During the incremental synchronization process, if the source database generates binlog statements in the STATEMENT format, it will cause the synchronization task to fail.
      Gaps in the GTID of the source database binlog might affect the performance of the synchronization task and lead to task failure.
      Scenarios that include both DML and DDL statements in one transaction are not supported. In such scenarios, an error may occur.
      Geometry-related data types are not supported, and tasks will report an error when encountering such data types.
      The ALTER VIEW statement is not supported; tasks encountering this statement will be skipped and not synchronized.

      Operation Restrictions

      Do not perform the following operations during synchronization, as they will cause the synchronization task to fail.
      Do not modify or delete user information (including username, password, and permissions) and port number in both the source and target databases.
      Do not write data with the binlog format as STATEMENT in the source database.
      Do not clear binlogs on the source database.
      During the incremental synchronization phase, do not delete the system database table __tencentdb__.

      Supported SQL Operations for Synchronization

      Operation Type
      SQL Operation Statements
      DML
      INSERT, UPDATE, and DELETE
      DDL
      CREATE DATABASE, DROP DATABASE, ALTER DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, RENAME TABLE, CREATE VIEW, DROP VIEW, CREATE INDEX, and DROP INDEX
      Note:
      The CREATE TABLE table name AS SELECT statement is not supported.

      Environment Requirements

      Type
      Environment Requirements
      Source Database Requirements
      The networks of the source and target databases must be interconnected.
      Instance Parameter Requirements:
      The row_format of the source database table cannot be set to FIXED.
      Binlog Parameter Requirements:
      The source binlog_format variable must be set to ROW.
      The source binlog_row_image variable must be set to FULL.
      Setting do_db and ignore_db is not allowed.
      We recommend you retain the binlogs of the source database for at least three days; otherwise, the task cannot be resumed from the checkpoint and will fail.
      Foreign Key Dependencies:
      Foreign key dependencies can only be set to two types: NO ACTION or RESTRICT.
      During partial database table synchronization, tables with foreign key dependencies must be completely migrated.
      The environment variable innodb_stats_on_metadata must be set to OFF.
      Target Database Requirements
      The version of the target database must be later than or equal to the version of the source database.
      The target database must have sufficient storage space. If the initialization type is set to "Full Data Initialization", the space of the target database must be at least 1.2 times the size of the source database's tables pending synchronization.
      The target database cannot have tables, views, or other synchronization objects with the same name as those in the source database.
      The max_allowed_packet parameter of the target database must be set to a minimum of 4M.

      Step 1: Creating a Synchronization Task

      1. Access the Data Sync Purchase Page, select the appropriate configuration, and click Buy Now.
      Parameter
      Description
      Service Type
      Select Data Sync.
      Creation Mode
      Select Create new task.
      Billing Mode
      Monthly subscription and Pay as you go billing modes are supported.
      Source Instance Type
      Select TDSQL-C for MySQL. It cannot be modified after purchase.
      Source Instance Region
      Select the region where the source instance is located. It cannot be modified after purchase.
      Target Instance Type
      Select TDSQL-C for MySQL. It cannot be modified after purchase.
      Target Instance Region
      Select the region where the target instance is located. It cannot be modified after purchase.
      Specification
      Select a specification based on your business needs. The higher the specification, the better the performance. For more details, refer to Billing Overview.
      2. After the purchase, return to the data synchronization list to see the newly created data synchronization task. The newly created task must be configured before it can be executed.
      3. In the data synchronization list, click Configure in the Operation column to enter the synchronization task configuration page.
      
      4. On the synchronization task configuration page, configure the source instance, account password, and target instance, account and password. After testing connectivity, click Next.
      
      Configuration Item
      Parameter
      Description
      Task Configuration
      Task Name
      DTS will automatically generate a task name. You can set a name for the task as needed.
      Running Mode
      Both immediate and scheduled execution modes are supported.
      Automatic Retry
      After this feature is enabled, if the migration task is temporarily interrupted due to network anomalies or other reasons, DTS will automatically retry and resume the task within the set time frame, without the need for manual intervention by the user. The supported time range is 5 minutes to 720 minutes.
      Source Database Settings
      Source Instance Type
      The source instance type selected at the time of purchase cannot be modified.
      Source Instance Region
      The source instance region selected at the time of purchase cannot be modified.
      Access Type
      Select TencentDB.
      Instance ID
      Select the ID of the source instance.
      Account
      Enter the account of the source instance. Account permissions must meet the requirements.
      Password
      Enter the password of the source instance account.
      Target Database Settings
      Target Instance Type
      The target instance type selected at the time of purchase cannot be modified.
      Target Instance Region
      The target instance region selected at the time of purchase cannot be modified.
      Access Type
      Select TencentDB.
      Instance ID
      Select the ID of the target instance.
      Account
      Enter the account of the target instance. Account permissions must meet the requirements.
      Password
      Enter the password of the target instance account.
      5. On the Set sync options and objects page, you can set the data initialization options, data synchronization options, and synchronization object options. After completing the settings, click Save and Go Next.
      Note:
      When Initialization Type is set to Full data initialization alone, the system assumes that table structures have already been created in the target database. Therefore, it will not synchronize table structures, nor will it verify if there are tables with duplicate names in the source and target databases. Therefore, if a user also selects Precheck and report error in the presence of If Target Already Exists, this validation and error-reporting feature will not be effective.
      If you plan to use the rename operation on a table during synchronization (for example, renaming table A to table B), then the Sync Object must include the entire database (or the entire instance) where table A is located, not just table A. Otherwise, after the rename operation, data from table B will not be synchronized to the target database.
      
      Configuration Item
      Parameter
      Description
      Data Initialization Option
      Initialization Type
      Structure initialization: During the execution of the synchronization task, the table structure from the source instance will be initialized in the target instance first.
      Full data initialization: During the execution of the synchronization task, data from the source instance will be initialized in the target instance first. In scenarios where only Full data initialization is selected, you need to pre-create the table structure in the target database.
      By default, both are selected, but can be canceled depending on the actual situation.
      If Target Already Exists
      Precheck and report error: If a table with the same name exists, an error is reported and the process is not continued.
      Ignore and execute: Directly append both full and incremental data to the table in the target instance.
      Data Sync Option
      Primary Key Conflict Resolution
      Report: If a primary key conflict is detected during synchronization, an error is reported and the data synchronization task is paused.
      Ignore: If a primary key conflict is detected during synchronization, the primary key record in the target database is retained.
      Overwrite: If a primary key conflict is detected during synchronization, the primary key record in the source database will overwrite that in the target database.
      SQL Type
      Supported operations are Insert, Update, Delete, and DDL. By selecting Custom DDL, you can choose different DDL synchronization policies as needed. For details, refer to Setting SQL Filter Policies.
      Sync Object Option
      Sync Object
      Select specific objects, including base tables, views, stored procedures, and functions.
      Advanced Migration Object
      Synchronization of advanced migration objects is a one-time action, only supporting synchronization of advanced objects that existed in the source database before the task started. After the task has started, newly added advanced objects will not be synchronized to the target database.
      6. On the Verify task page, complete the verification and, after all verification items have passed, click Start Task.
      Note:
      If the verification fails, refer to Solution to Verification Failure to fix the issue and initiate the verification task again.
      A synchronization task may increase the load on both the source and target database instances. Perform the operation during business off-peak hours.
      Do not change the source or target database passwords after the synchronization task has started.
      Do not execute DDL operations on the source database.
      Do not disconnect the network or switch the source database.
      Failed: Indicates that the verification item failed the check, the task is blocked, and you need to fix the issue and run the verification task again.
      Alarm: Indicates that a verification item does not fully meet the requirements but the task can proceed, impacting the business to some extent. You need to decide based on the prompt whether to ignore the warning or fix the issue and continue.
      
      7. Return to the data synchronization task list, and the task has entered the Running status.
      Note:
      In the Operation column, choose More > Stop to close a synchronization task. Ensure data synchronization is complete before closing the task.
      
      8. (Optional) You can click on the task name to enter the task details page and view the initialization state and monitoring data.

      Step 2: Actively Verify Data Through DMC

      After the data synchronization task is created, data synchronization from the source cluster to the new cluster will continue. Considering the need to connect the business to a cluster with database version 8.0 in the future, refrain from writing incremental data to the source database to ensure consistency between the source database and new database.
      1. Log in to the TDSQL-C for MySQL console.
      2. Find the source cluster used for data synchronization in the cluster list, and then in the top right corner of its cluster details page, click Log In.
      3. In the pop-up window, enter your account password to access the source cluster's DMC management page.
      4. Open a new webpage and log in to the TDSQL-C for MySQL console.
      5. Find the target cluster for data synchronization in the cluster list, and then in the top right corner of its cluster details page, click Log In.
      6. In the pop-up window, enter the account and password to access the DMC management page of the target cluster.
      7. On the DMC management page, compare the database tables and other data of the source cluster and the target cluster to ensure that all data has been fully synchronized.
      Source Cluster DMC Management Page
      
      Target Database DMC Management Page
      

      Step 3: Switch the Business to the New Cluster

      After the data synchronization is complete and consistent, and you confirm that the source cluster is no longer needed, you can manually update the business connection to the new cluster's connection address on the actual application side. Then, end the corresponding data synchronization task in the data synchronization list on the DTS side. Finally, delete the source cluster in the TDSQL-C for MySQL console.
      Stopping the synchronization task:
      
      Note:
      Before the service ends, ensure there are no business dependencies on this synchronization service.
      Existing configured synchronization tasks will be stopped.
      The synchronization task will be set to stopped status.
      For monthly subscription data synchronization tasks, stopping them will not automatically trigger a refund. For refunds, click Return/Refund.
      Deleting the source cluster:
      
      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