# Upgrading MySQL 5.7 to 8.0: Steps to Take and Pitfalls

> How to upgrade MySQL 5.7 to version 8.0 without losing data: compatibility checks, command order, and common upgrade 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](https://www.zevshost.net/en/kb/mysqldump-backup-restore/) or another hot-backup tool.

## Version Order for the Upgrade

| Current Version | Upgrade Target | Comment |
| --- | --- | --- |
| 5.7.x, any minor | straight to 8.0.x | a direct upgrade is officially supported |
| 5.6 and older | first to 5.7, then to 8.0 | skipping version 5.7 is not supported |
| 8.0.x older minor | 8.0.x latest minor | an upgrade within the same major version is the simplest |

## 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](https://www.zevshost.net/en/kb/mysql-charset-utf8mb4-nastroika/) 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](https://www.zevshost.net/en/kb/mysql-user-privileges/).

## 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.

## Frequently asked questions

### Can you roll back to 5.7 if something goes wrong after upgrading to 8.0?

There is no simple rollback by downgrading the MySQL package — the 8.0 system table format is incompatible with the older server, and trying to start 5.7 on top of already-upgraded data usually fails. The only reliable way back is restoring the full backup taken before the upgrade, so skipping that checklist step is not an option.

### Do old queries have to be fully rewritten for ONLY\_FULL\_GROUP\_BY, or can that restriction be turned off temporarily?

Rewriting every query right away is not strictly required — ONLY\_FULL\_GROUP\_BY can be removed from sql\_mode temporarily at the server or session level so old queries keep running unchanged. That is a workable stopgap for a transition period, not a permanent fix: turning the check off also hides genuinely incorrect queries that are worth cleaning up sooner or later.

### Does upgrading the server break existing MySQL user passwords?

The passwords themselves are not lost or reset during the upgrade, but the way they get verified changes along with the new default authentication plugin, which is why some client libraries stop understanding the server's response. The problem sits in the handshake protocol, not the password — for existing users the plugin can be switched back without touching it.

### Does the site need to go offline during the MySQL upgrade, or can it be done without downtime?

A brief service interruption during the process is unavoidable, since the server package itself gets replaced and the service restarts, and a fully uninterrupted upgrade with no connection break is not achievable on a single server. Downtime can be shrunk to a minimum by preparing every step in advance and running the package swap and mysql\_upgrade in one quick pass.

---

Source: https://www.zevshost.net/en/kb/mysql-obnovlenie-57-na-80/