MySQL 5.7 to 8.0 Upgrade Guide: Requirements, Steps, and Precautions

Preface

This article is part of the "MySQL Column" series produced by the Aikexing operations team, sharing frontline operational experience, covering MySQL features, optimization cases, architecture, HA, monitoring, and fault analysis.

Introduction

During a recent project we performed a logical upgrade from MySQL 5.7.21 to MySQL 8.0.13 using production‑level configuration files. The initialization command completed instantly, but the data directory remained empty and no error was reported. Investigation revealed that the NO_AUTO_CREATE_USER sql_mode, deprecated in MySQL 8.0.13, was still present in the configuration, causing the failure.

Upgrade Requirements

MySQL 5.7 must be at least version 5.7.9 GA or higher before upgrading to MySQL 8.0.

Cross‑version upgrades are not supported.

Upgrade Content

Upgrade the version of the data dictionary tables.

Upgrade the MySQL server version, including the system schema and other schema objects.

Upgrade Steps

Upgrade the data dictionary – this is performed automatically during server startup.

Upgrade MySQL system tables (remaining non‑dictionary tables) and the performance_schema , information_schema , and sys schemas. For versions prior to MySQL 8.0.16, run mysql_upgrade manually; from 8.0.16 onward the server performs this automatically.

Upgrade Precautions

The default authentication plugin in MySQL 8.0 is caching_sha2_password , while MySQL 5.7 uses mysql_native_password . Clients that do not support the newer plugin may fail to connect.

Storage engines now provide their own partition handlers; generic partition support has been removed. Only InnoDB and NDB support native partitioning in MySQL 8.0, so tables using other engines must be converted or have partitions removed.

MySQL 8.0 changes the default character set to utf8mb4 . To avoid mismatched character sets between existing objects and new ones, configure the server to use the legacy character set and collation if needed.

The lower_case_table_names value used at startup must match the value used during initialization; otherwise identifier sorting and comparison may become inconsistent.

Do not include the deprecated NO_AUTO_CREATE_USER in sql_mode when initializing MySQL 8.0, as it will cause startup failure.

From MySQL 5.7.24/8.0.13 onward, mysqldump no longer includes NO_AUTO_CREATE_USER in its output; older dump files must be edited to remove this option.

MySQL 8.0.11 removed several compatibility SQL modes (e.g., DB2, MAXDB, MSSQL, etc.). Using these modes in replication scenarios can cause replication errors.

When performing an in‑place upgrade on MySQL 8.0.3 or later, the BACKUP_ADMIN privilege is automatically granted to users with RELOAD privilege.

Reference Documentation

https://dev.mysql.com/doc/refman/8.0/en/upgrade-binary-package.html