Help Center> Data Replication Service> User Guide (Paris Region) > FAQs> Real-Time Migration> What Are the Precautions for Migrating Data from an Earlier Version MySQL to MySQL 8.0?
Updated on 2023-01-05 GMT+08:00
View PDF

What Are the Precautions for Migrating Data from an Earlier Version MySQL to MySQL 8.0?

Based on MySQL 5.7, some new features have been added to MySQL 8.0. There are performance differences between the two versions. Before migration, you need to analyze compatibility and provide a corresponding solution. The following shows the analysis:

  • Compatibility analysis

    MySQL 8.0 and MySQL 5.7 Community Edition are analyzed as follows:

    1. Compatibility does not affect migration, but the solutions are different.

      Compatibility

      Check Item

      Function

      Status

      Solution

      Data types or functions

      ENCODE()

      Encryption

      Deleted

      Replaced by AES_ENCRYPT()

      DECODE()

      Decryption

      Deleted

      Replaced by AES_DECRYPT()

      ENCRYPT()

      Encryption

      Deleted

      Replaced by SHA2()

      DES_ENCRYPT()

      Encryption

      Deleted

      Replaced by AES_ENCRYPT()

      DES_DECRYPT()

      Decryption

      Deleted

      Replaced by AES_DECRYPT()

      JSON_APPEND()

      Adds JSON elements.

      Deleted

      Replaced by JSON_ARRAY_APPEND()

      PASSWORD()

      Changes a user password.

      Deleted

      ALTER USER user IDENTIFIED BY 'auth_string';

      JSON_MERGE()

      Merges multiple JSONs.

      Discarded

      Replaced by JSON_MERGE_PERSERVE()

      SQL MODE

      NO_AUTO_CREATE_USER, DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS

      -

      Deleted

      -

      Foreign key constraint length

      The constraint name cannot be greater than 64 characters.

      -

      -

      SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1), INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1) FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

      Use the ALTER TABLE statement to adjust the length.

      Features

      Use the GRANT statement to create users.

      -

      Deleted

      CREATE USER

      Use the GRANT statement to modify user information.

      -

      Deleted

      ALTER USER

      IDENTIFIED BY PASSWORD 'auth_string'

      Sets new passwords

      Deleted

      IDENTIFIED WITH auth_plugin AS 'auth_string'

      \N in a SQL statement

      NULL

      Deleted

      Replaced by NULL

      PROCEDURE ANALYSE() syntax

      Specifies the recommended field type is provided after the MySQL field value is analyzed.

      Deleted

      -

      Spatial functions

      -

      -

      -

      mysql_install_db

      Initialization

      Deleted

      mysqld --initialize or --initialize-insecure
    2. The following items affect the migration. You need to check in advance.

      Compatibility

      Check Item

      Function

      Status

      Solution

      Original Usage

      Reserving keywords

      cume_dist, dense_rank, empty, except, first_value, grouping, groups, json_table, lag, last_value, lateral, lead, nth_value, ntile, of, over, percent_rank, rank, recursive,row_number, system, window

      -

      Added

      SET sql_mode = 'ANSI_QUOTES'

      Name: database, table, index, column, alias, view, stored procedure, partition, and tablespace

      Character set

      UTF8MB3

      -

      Discarded

      Replaced by UTF8MB4.

      -

      Partition table name

      Partition tables of storage engines that do not support local partitions are not allowed.

      -

      Deleted

      SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb', 'ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';

      You can use either of the following methods:

      (1) ALTER TABLE table_name ENGINE=INNODB;

      (2) ALTER TABLE table_name REMOVE PARTITIONING;

      MyISAM is not supported.

      Syntax

      group by... asc/desc

      Ascending/Descending

      Deleted

      Replaced by the ORDER By clause.

      View and function

      Name length

      The view name cannot be greater than 64 characters.

      -

      -

      ALTER

      The value can contain a maximum of 255 characters.

      The enum or set element contains a maximum of 255 characters.

      -

      -

      Handled by users.

      The value can contain a maximum of 64 KB.

      Upper and lower case letters

      lower_case_table_names

      Specifies whether to set the MySQL table name case sensitive.

      -

      If this parameter is set to 1 during the upgrade, ensure that the schema and table names are in lowercase.

      SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != LOWER(TABLE_NAME) AND TABLE_TYPE = 'BASE TABLE';

      SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME != LOWER(SCHEMA_NAME);

      -

      Triggers

      Check whether there is an empty definition or invalid creation context.

      -

      -

      Use the SHOW TRIGGERS statement to check the character_set_client, collation_connection, and Database Collation attributes.

      -
  • Change the default value of the system variable.

    The analysis of default values of MySQL 5.7 and MySQL 8.0 Community Edition shows that default values do not affect the migration but affect services after the migration.

    No.

    Parameter/Option

    Community

    Function

    Remarks

    Original Default Value

    New Default Value

    Server

    1

    character_set_server

    latin1

    utf8mb4

    -

    Be consistent with the origin default value.

    2

    collation_server

    latin1_swedish_ci

    utf8mb4_0900_ai_ci

    -

    Be consistent with the origin default value.

    3

    explicit_defaults_for_timestamp

    OFF

    ON

    Specifies whether to update the timestamp column when a row is updated.

    Be consistent with the origin default value.

    4

    optimizer_trace_max_mem_size

    16KB

    1MB

    -

    Be consistent with the origin default value.

    5

    validate_password_check_user_name

    OFF

    ON

    -

    Be consistent with the origin default value.

    6

    back_log

    -1 (autosize) changed from : back_log = 50 + (max_connections / 5)

    -1 (autosize) changed to : back_log = max_connections

    Specifies the number of requests that can be stored in the stack in a short period before the MySQL database stops responding to new requests.

    Be consistent with the origin default value.

    7

    max_allowed_packet

    4194304 (4MB)

    67108864 (64MB)

    Limits the size of data packets received by the server

    Use the default value.

    8

    max_error_count

    64

    1024

    Controls the number of alarms to be displayed.

    Be consistent with the origin default value.

    9

    event_scheduler

    OFF

    ON

    -

    Be consistent with the origin default value.

    10

    table_open_cache

    2000

    4000

    -

    Be consistent with the origin default value.

    11

    log_error_verbosity

    3 (Notes)

    2 (Warning)

    -

    Use the default value.

    INNODB

    1

    innodb_undo_tablespaces

    0

    2

    -

    Use the default value.

    2

    innodb_undo_log_truncate

    OFF

    ON

    -

    Use the default value.

    3

    innodb_flush_method

    NULL

    fsync (Unix),

    unbuffered (Windows)

    Controls the enabling and writing modes of InnoDB data files and redo logs.

    Use the default value O_DIRECT for SQL.

    4

    innodb_autoinc_lock_mode

    1 (consecutive)

    2 (interleaved)

    Controls the behavior of related locks when data is inserted into a table with the auto_increment column.

    Be consistent with the origin default value.

    5

    innodb_flush_neighbors

    1 (enable)

    0 (disable)

    Checks whether other dirty pages in the same range are refreshed when refreshing the page from the buffer pool.

    Be consistent with the origin default value.

    6

    innodb_max_dirty_pages_pct_lwm

    0 (%)

    10 (%)

    Affects the InnoDB dirty page refreshing operation.

    Use the default value.

    7

    innodb_max_dirty_pages_pct

    75 (%)

    90 (%)

    Affects the InnoDB dirty page refreshing operation.

    Use the default value.

    PERFORMANCE SCHEMA

    Enabled globally.

    -

    -

    -

    Be consistent with the origin default value.

    REPLICATION

    1

    log_bin

    OFF

    ON

    -

    Enabled by default

    2

    server_id

    0

    1

    -

    If the value is 0, change it to 1.

    3

    log-slave-updates

    OFF

    ON

    -

    Enabled by default.

    4

    expire_log_days

    0

    30

    -

    Use the default value.

    5

    master-info-repository

    FILE

    TABLE

    -

    Use the default value TABLE.

    6

    relay-log-info-repository

    FILE

    TABLE

    -

    Use the default value TABLE.

    7

    transaction-write-set-extraction

    OFF

    XXHASH64

    -

    Use the default value.

    8

    slave_rows_search_algorithms

    INDEX_SCAN, TABLE_SCAN

    INDEX_SCAN, HASH_SCAN

    -

    Use the default value.
  • Remove system variables.

    The analysis of MySQL 5.7 and 8.0 Community Edition shows that removing system variables does not affect migration.

    System variables

    innodb_locks_unsafe_for_binlog

    log_builtin_as_identified_by_password

    old_passwords

    query_cache_limit

    query_cache_min_res_unit

    query_cache_size

    query_cache_type

    query_cache_wlock_invalidate

    ndb_cache_check_time

    ignore_db_dirs

    tx_isolation

    tx_read_only

    sync_frm

    secure_auth

    multi_range_count

    log_error_verbosity

    sql_log_bin

    metadata_locks_cache_size

    metadata_locks_hash_instances

    date_format

    datetime_format

    time_format

    max_tmp_tables

    ignore_builtin_innodb

    innodb_support_xa

    innodb_undo_logs

    innodb_undo_tablespaces

    internal_tmp_disk_storage_engine
Parent topic: Real-Time Migration