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