Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Situation Awareness
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive
On this page

Show all

Help Center/ Data Replication Service/ User Guide (Ankara Region)/ FAQs/ Real-Time Migration/ What Are the Precautions for Migrating Data from an Earlier Version MySQL to MySQL 8.0?

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

Updated on 2024-04-11 GMT+08:00

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

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback