Upgrading MySQL 5.7 to 8.0: Steps to Take and Pitfalls

Why Upgrade from MySQL 5.7 to 8.0

Official support for MySQL 5.7 ended in October 2023: no more security updates are released for that branch. Version 8.0 keeps receiving patches, supports window functions, CTEs (WITH ... AS), invisible indexes, and runs operations on large tables noticeably faster thanks to a reworked query optimizer.

Delaying the upgrade is risky not only because of vulnerabilities: the longer a server stays on 5.7, the more incompatible code piles up, and it all has to be fixed in one pass later.

What Breaks During the Move to 8.0

Some issues appear right after the upgrade, others only show up under load.

• The default authentication plugin is caching_sha2_password instead of mysql_native_password — older PHP drivers and connectors may not be able to read it.
• The default sql_mode enables ONLY_FULL_GROUP_BY — queries with GROUP BY and no aggregate functions start failing with an error.
• The Query Cache has been removed from the server entirely — a configuration with query_cache_size causes a startup error.
• The list of reserved words has grown: column names like RANK or GROUPS now need backtick escaping.

Checking Compatibility Before the Upgrade

A compatibility-check utility is built into the MySQL Shell client and finds most issues without performing a real upgrade.

mysqlsh -- util check-for-server-upgrade { --user=root --password }
mysqlcheck --check-upgrade --all-databases -u root -p

The report splits findings into errors that must be fixed before the upgrade and warnings that can be handled later. Before starting, always take a full backup with mysqldump or another hot-backup tool.

Version Order for the Upgrade

Step-by-Step Upgrade Process

On Ubuntu and Debian, upgrading within the same package manager goes through replacing the repository.

systemctl stop mysql
mysqldump --all-databases --routines --triggers -u root -p > /root/full_backup.sql
apt update
apt install mysql-server=8.0.*
systemctl start mysql
mysql_upgrade -u root -p

The mysql_upgrade command runs automatically at service startup in version 8.0.16 and newer, but calling it manually does no harm and prints an explicit list of updated system tables.

Common Pitfalls After the Upgrade

Most support requests after an upgrade are not about MySQL itself, but about the server-application pairing.

• An application on an old PHP mysqlnd driver fails to connect — the authentication plugin needs to be temporarily switched back to mysql_native_password for that specific user.
• Queries sorting by an alias or with an incomplete GROUP BY return error 1055 — they need to be rewritten for ONLY_FULL_GROUP_BY.
• The default charset changed to utf8mb4 — check the article on utf8mb4 configuration if tables were created without an explicit charset.
• User privileges granted with GRANT ALL ON *.* are worth reviewing again after the upgrade — the role mechanism in 8.0 changed some behavior covered in the article on user management.

Summary: 5.7 to 8.0 Upgrade Checklist

• Take a full database backup before taking any action.
• Run util check-for-server-upgrade and fix all critical findings.
• Check application drivers for compatibility with caching_sha2_password.
• Update the server package and run mysql_upgrade.
• Run application regression tests accounting for ONLY_FULL_GROUP_BY.