MySQL Upgrade Guide: Principles, 8.0 Changes, Pre‑Upgrade Checks, and Step‑by‑Step In‑Place & Logical Migration

Upgrade Version Selection Principles and Recommendations

The upgrade path supports moving from MySQL 5.7 to 8.0, but only between GA releases. Cross‑major upgrades such as 5.6 → 8.0 are not supported. It is recommended to first upgrade to the latest minor version of the current series (e.g., 5.7.35) before jumping to 8.0. Within a major series, GA minor versions can be upgraded directly (e.g., 8.0.9 → 8.0.26).

Key Changes in MySQL 8.0

Data Dictionary: The global data dictionary is now stored in transactional tables. Settings like innodb_read_only affect DDL operations. mysqldump and mysqlpump no longer export information_schema or data‑dictionary tables; use --routines and --events for stored procedures and events, and ensure the user has global SELECT privileges.

Authentication Plugin: Default authentication changes from mysql_native_password to caching_sha2_password. Clients must support the new plugin.

Configuration Changes:

Only InnoDB and NDB engines support partitioned tables.

Deprecated error codes are listed at MySQL Nutshell .

Default character set changes from latin1 to utf8mb4 with collation utf8mb4_0900_ai_ci, which may cause implicit conversion issues.

From 8.0.11, lower_case_table_names mismatches between server and data dictionary cause errors (example error shown below).

[ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('0') and data dictionary ('1').
[ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
[ERROR] [MY-010119] [Server] Aborting

Server Changes:

From 8.0.11, several account‑management features are removed (e.g., GRANT modifications, NO_AUTO_CREATE_USER, PASSWORD(), old_passwords).

Deprecated SQL modes (DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS) are no longer supported.

Spatial data types now require an SRID attribute; some non‑ ST_ spatial functions are removed.

InnoDB Changes:

System tables renamed: innodb_sys_% → innodb_%.

Zlib library upgraded to 1.2.11. innodb_directories must contain paths for file‑per‑table and general tablespace files.

Undo logs moved out of the system tablespace; two undo tablespaces are created by default. When upgrading from 5.7, set innodb_fast_shutdown=0.

From 8.0.17, tablespace file paths cannot contain circular references (e.g., /../). Example error and check query are provided.

mysql> CREATE TABLESPACE ts11 ADD DATAFILE '/data/mysql/log/test/../ts11.ibd';
ERROR 3121 (HY000): The ADD DATAFILE filepath cannot contain circular directory references.
mysql> CREATE TABLESPACE ts11 ADD DATAFILE '/data/mysql/log/ts11.ibd';
Query OK, 0 rows affected (10.02 sec)

SQL Changes: GROUP BY no longer supports ASC or DESC modifiers.

Keyword list updated – see MySQL Keywords .

Test optimizer hints after upgrade; some hints may be deprecated.

Upgrade Process Overview

The upgrade consists of two main parts: data‑dictionary upgrade and service upgrade. The data‑dictionary upgrade updates the MySQL schema’s internal tables and system schemas (performance_schema, information_schema, sys). It can be controlled with --upgrade=NONE (MySQL 8.0.16+) or --no-dd-upgrade (pre‑8.0.16). The service upgrade handles all non‑dictionary tables, user schemas, and the sys schema.

Before MySQL 8.0.16, mysql_upgrade must be run after the server starts; from 8.0.16 onward the server performs the upgrade automatically based on the --upgrade option.

Pre‑Upgrade Checks

Use MySQL Shell’s util.checkForServerUpgrade to detect incompatibilities. Example output shows errors such as a MyISAM partitioned table that must be converted to InnoDB.

Manual checks include:

Detect outdated data types/functions (e.g., old temporal types).

Find orphan .frm files.

Verify triggers have valid definers.

Identify non‑InnoDB/NDB partitioned tables:

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

Check for usage of new reserved words.

Ensure no tables in the mysql schema share names with new data‑dictionary tables (SQL provided).

Validate foreign‑key constraint names are ≤ 64 characters.

Confirm sql_mode does not contain deprecated modes.

Verify column names in views are ≤ 64 characters.

Check ENUM/SET column element length limits.

Ensure no partitioned tables exist in shared tablespaces before 8.0.13.

Disallow GROUP BY ... ASC/DESC syntax.

Review removed features and variables (links provided).

When lower_case_table_names=1, ensure all object names are lowercase (SQL examples given).

If pre‑upgrade checks reveal errors, resolve them before proceeding; otherwise the upgrade may abort and roll back.

In‑Place Upgrade (Binary/Package Replacement)

Perform all pre‑upgrade checks and fix issues.

If XA transactions are used, recover and commit/rollback pending XA transactions.

For versions < 5.7.11 with encrypted tablespaces, rotate the master key: ALTER INSTANCE ROTATE INNODB MASTER KEY; Set innodb_fast_shutdown to 0 or 1.

Stop the current MySQL instance.

Replace the binary or package with the target version.

Start the new MySQL on the existing data directory. If encrypted InnoDB tablespaces exist, load the keyring plugin with --early-plugin-load. If startup fails, delete the redo log, start the old 5.7 instance, set innodb_fast_shutdown=0, then restart with 8.0.

If the target version is < 8.0.16, run mysql_upgrade after startup and restart.

Sample commands:

# Check current version
mysql> SELECT @@global.version;
# Run upgrade check
util.checkForServerUpgrade('[email protected]:3307', {"password":"XXXXX", "targetVersion":"8.0.26", "configPath":"/etc/my3307.cnf"});
# Set fast shutdown
mysql> SET GLOBAL innodb_fast_shutdown=0;
# Shutdown
mysql> SHUTDOWN;
# Start new version with force upgrade
./mysqld_safe --defaults-file=/etc/my3307.cnf --user=mysql --upgrade=FORCE &

Logical Upgrade (Dump & Import)

Export all data from the old version:

mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > data-for-upgrade.sql

Stop the old MySQL instance.

Install MySQL 8.0 and initialize it (retrieve the temporary root password from the error log).

Start MySQL 8.0, reset the root password.

Import the dump: mysql -u root -p --force < data-for-upgrade.sql (Disable GTID if system tables are included.)

Perform remaining upgrade steps. For versions > 8.0.16, restart with --upgrade=FORCE; for earlier versions, run mysql_upgrade then restart.

# After 8.0.16
mysqladmin -u root -p shutdown
mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir --upgrade=FORCE &

# Before 8.0.16
mysql_upgrade -u root -p
mysqladmin -u root -p shutdown
mysqld_safe --user=mysql --datadir=/path/to/8.0-datadir &

# Clean up obsolete tables
DROP TABLE mysql.event;
DROP TABLE mysql.proc;

The article concludes with author information and links to related reading on database topics.