How to Migrate from MySQL 5.7 to 8.0 (Step-by-Step)

MySQL 5.7 reached end of life on October 31, 2023. Oracle stopped publishing security patches for it. Managed providers (RDS, Cloud SQL, Aurora) have moved 5.7 into extended support, added fees, or scheduled upgrade pressure since then; self-hosted installations are on borrowed time. The migration to 8.0 is mostly straightforward but has three sharp edges that bite people: the default authentication plugin changed to caching_sha2_password and breaks every client that doesn't speak it, removed and reserved keywords cause stored procedures and views to fail validation, and the new data dictionary means there is no painless rollback once mysql_upgrade has rewritten system tables. Below is the full procedure I use, the verification commands at each step, and what to do when the auth plugin breaks PHP / Node.js / Python clients.

How do I migrate MySQL from 5.7 to 8.0?

The migration is a five-step procedure. First, take a full logical backup with mysqldump --all-databases --single-transaction --master-data=2. Second, run the MySQL Shell upgrade checker (mysqlsh -- util check-for-server-upgrade) against 5.7 to catch incompatibilities (reserved-word conflicts, deprecated character sets, partitioned tables on non-InnoDB engines); mysqlcheck --check-upgrade is a lighter complementary scan. Third, stop the 5.7 service, install the 8.0 binaries on the same data directory (in-place upgrade), and start MySQL 8.0; the server runs mysql_upgrade automatically on first start since 8.0.16 and rewrites the system schema. Fourth, switch the default authentication plugin back to mysql_native_password if you have legacy clients, or update every client driver to one that speaks caching_sha2_password. Fifth, validate with a smoke-test query against each application before you delete the backup. Rollback after mysql_upgrade runs is not possible without restoring from the dump.

Jump to:

• Why migrate (and the EOL clock)
• Pre-flight: backup everything
• Pre-flight: run the upgrade checker
• The in-place upgrade itself
• The authentication-plugin trap
• Verify the upgrade succeeded
• Removed and reserved keywords to watch
• Rollback options
• What to do next
• FAQ

Why migrate (and the EOL clock)

MySQL 5.7 reached end of life on October 31, 2023. After that date, Oracle no longer publishes security patches, bug fixes, or even build artifacts on the public download mirrors. The CVE backlog for 5.7 since EOL is non-trivial and growing. If you run 5.7 in production today, you're carrying an audit risk that will only get worse.

MySQL 8.0 itself reached the end of Extended Support in April 2026 and is now under Sustaining Support: Oracle no longer ships new bug fixes or security patches for it either. MySQL 8.4 LTS is the branch that carries Premier and Extended Support through April 2032. So treat 5.7 → 8.0 as the first leg, not the destination: plan the 8.0 to 8.4 LTS upgrade right after. The 5.7 → 8.0 jump is the bigger, scarier one; 8.0 → 8.4 is a much smaller surface.

Pre-flight: backup everything

Before touching anything, take a full logical backup. The --single-transaction flag uses a consistent snapshot at the InnoDB level so the dump completes without locking tables on a live server:

mysqldump \
    --all-databases \
    --single-transaction \
    --master-data=2 \
    --routines \
    --triggers \
    --events \
    --hex-blob \
    | gzip > mysql-5.7-pre-upgrade-$(date +%Y%m%d-%H%M).sql.gz

--single-transaction only works for InnoDB tables. If you still have MyISAM tables, convert them to InnoDB before migrating (here is why).

--master-data=2 writes the binary-log coordinates as a comment in the dump, useful for re-attaching replicas. --routines --triggers --events makes sure stored procedures, triggers, and scheduled events end up in the dump (they're skipped by default).

A logical backup is verbose but portable. For multi-terabyte databases, consider Percona XtraBackup for a physical backup; the rest of this guide assumes a logical dump because it works for every storage backend, and because it's the one rollback path that actually works post-upgrade.

Pre-flight: run the upgrade checker

The primary compatibility scanner is the MySQL Shell upgrade checker. It explicitly flags reserved-keyword collisions, removed features, and other 8.0 upgrade issues, and modifies nothing:

mysqlsh -- util check-for-server-upgrade '{"user":"root","host":"localhost","password":"YOUR_PASSWORD"}' \
    --target-version=8.0.42 \
    --output-format=JSON

mysqlcheck --check-upgrade --all-databases is a lighter complementary scan: it mainly flags tables that need a rebuild or repair, not the broader schema-level incompatibilities. Run it too, but don't treat it as the main compatibility check:

mysqlcheck --check-upgrade --all-databases

The checker emits findings in three buckets:

The most common errors I see in real codebases:

• Reserved word collisions: 8.0 reserves GROUPS, ROLES, LATERAL, WINDOW, RANK, DENSE_RANK, RECURSIVE, and others. Any unquoted use as a column or table name fails.
• Partitioned tables on non-InnoDB engines: 8.0 removes native MyISAM partitioning. Convert to InnoDB or remove partitioning.
• utf8 vs utf8mb4: utf8 is still an alias for utf8mb3 in 8.0 but is deprecated and will be removed. Plan a utf8 to utf8mb4 migration on the same maintenance window if you haven't already.
• ZEROFILL on numeric columns: deprecated in 5.7, still works in 8.0 but emits warnings.

Fix all errors, decide what to do with each warning, then re-run the checker until it's clean.

The in-place upgrade itself

On a self-hosted box with Debian / Ubuntu (the procedure is similar for RHEL / Rocky / Alma — substitute dnf for apt):

# 1. Stop MySQL 5.7
sudo systemctl stop mysql

# 2. Replace the apt source to 8.0
sudo apt remove mysql-server-5.7 mysql-client-5.7
sudo apt install -y wget lsb-release gnupg
wget https://dev.mysql.com/get/mysql-apt-config_0.8.29-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.29-1_all.deb
# (select MySQL 8.0 in the config dialog)
sudo apt update

# 3. Install the 8.0 server (keeps the existing /var/lib/mysql data directory)
sudo apt install -y mysql-server

# 4. Start it
sudo systemctl start mysql

On first start, MySQL 8.0 detects the older data directory and runs the upgrade automatically since mysql_upgrade was integrated into the server itself in 8.0.16. There is no longer a separate mysql_upgrade binary to run; on older 8.0.x point releases it's still there as a no-op for compatibility.

Watch the error log during first startup. If anything goes wrong it ends up here:

sudo tail -f /var/log/mysql/error.log

The upgrade rewrites the system schema (the data dictionary moves from .frm files to a transactional table in InnoDB) and rebuilds the performance schema. On a few-gigabyte database this is seconds; on hundreds of gigabytes with many tables it can take ten minutes or more.

The authentication-plugin trap

This is the single biggest production incident waiting to happen. MySQL 8.0 changes the default authentication plugin from mysql_native_password to caching_sha2_password. Existing user accounts keep their old plugin, but any new account created on 8.0 (and any account whose password you change) ends up on the new plugin. Older client libraries that don't speak it fail with:

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded

Two ways to handle it. Option A (preferred for new deployments): update every client to a driver that speaks the new plugin. The list as of 2026:

Option B (legacy-client compatibility): keep the default at mysql_native_password server-side, so newly-created users continue to land on the old plugin. Set in /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
default_authentication_plugin=mysql_native_password

Restart MySQL. Note that default_authentication_plugin is removed in MySQL 8.4. In 8.4 the mysql_native_password plugin ships disabled by default, so it is not simply controlled by authentication_policy. You load it explicitly under [mysqld]:

[mysqld]
mysql_native_password=ON

Once the plugin is loaded, authentication_policy controls whether it is the default for newly-created accounts. And mysql_native_password is removed entirely in MySQL 9.0, so treat all of this as a short-term compatibility bridge: plan to switch every client to a driver that speaks caching_sha2_password before the next major upgrade.

For an existing user already on the new plugin who needs to revert:

ALTER USER 'app_user'@'%'
    IDENTIFIED WITH mysql_native_password
    BY 'their_password';

Verify the upgrade succeeded

After the upgrade completes, verify:

-- Server version
SELECT VERSION();
-- → 8.0.42 (or whichever 8.0.x you installed)

-- Data dictionary status (no orphaned tables)
SELECT * FROM information_schema.INNODB_TABLESPACES
WHERE NAME LIKE 'mysql/%' LIMIT 5;

-- User plugin distribution
SELECT plugin, COUNT(*) FROM mysql.user GROUP BY plugin;

-- Storage engine sanity
SELECT engine, COUNT(*) FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
GROUP BY engine;

Then run application smoke tests. The pattern I use: a small health-check.sh that runs one representative query against each of the top 5-10 tables and times it. Compare timings to a pre-upgrade baseline.

For a full cheat sheet of inspection commands for the post-upgrade verification phase, that article is the reference.

Removed and reserved keywords to watch

8.0 removed several keywords and reserved several new ones. The fast way to find offenders is the upgrade checker, but the high-impact ones in real codebases:

Stored procedures, functions, triggers, and views that reference reserved words as unquoted identifiers will fail validation during the upgrade. Fix in the dump, restore, or fix in-place after the upgrade with ALTER.

Rollback options

Once MySQL 8.0 has started against your 5.7 data directory and rewritten the system schema, you cannot roll back to 5.7 by reinstalling the 5.7 binaries. The data dictionary is incompatible.

Your rollback is the logical backup you took in step one:

# 1. Stop 8.0, uninstall the 8.0 binaries, install 5.7 again
sudo systemctl stop mysql
sudo apt remove --purge mysql-server-8.0
# (re-add the 5.7 apt source — Oracle has retired the public 5.7 mirrors,
#  so use the Percona Server 5.7 packages or a snapshot of an old archive.mirror)
sudo apt install -y mysql-server-5.7
sudo systemctl stop mysql

# 2. Wipe the 8.0-rewritten data directory
sudo rm -rf /var/lib/mysql

# 3. Re-init 5.7 and restore the backup
sudo mysqld --initialize-insecure --user=mysql
sudo systemctl start mysql
gunzip < mysql-5.7-pre-upgrade-*.sql.gz | mysql -u root

A logical restore on a multi-hundred-GB database takes hours. This is the reason the pre-flight checker matters: the only fast rollback is to not need one.

For the related "I locked myself out during the upgrade" scenario, how to reset a forgotten MySQL root password covers that.

What to do next

After 5.7 → 8.0 succeeds, the natural follow-ups:

• How to Upgrade MySQL 8.0 to 8.4 LTS — the next upgrade you'll do.
• utf8 to utf8mb4 Migration in MySQL — fix the deprecated character set while you have a maintenance window.
• MyISAM to InnoDB Conversion — required if you still have MyISAM tables.
• How to Export All MySQL Databases with mysqldump — for the regular post-migration backup cadence.
• MySQL Cheat Sheet — verification queries and connection management.

External references:

• MySQL 8.0 Reference Manual: Upgrading MySQL
• Checking table incompatibilities
• Percona: MySQL 5.7 EOL and support options

FAQ