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:
- 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
- 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.
-
- Compatibility does not affect migration, but the solutions are different.
- 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
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot
