Imagine: Friday evening, you decide to upgrade MySQL from 5.7 to 8.0. You run mysql_upgrade – the application crashes. Clients can't authenticate. Old PHP libraries don't support the new authentication plugin. Rollback? Only from a backup – if you have one. Sound familiar? Over the years, we have completed more than 50 major MySQL upgrades with zero downtime and guaranteed rollback. But the issue isn't just authentication: outdated SQL queries, reserved words, changed configuration can cause cascading errors. On one project, a 200 GB database crashed on startup with MySQL 8.0 due to incompatible data types. Our approach includes a full compatibility audit, strategy selection, and migration execution. According to Oracle, each major MySQL version has a limited support lifetime – don't postpone preparation.
What Changes Between Major Versions?
MySQL 5.7 → 8.0:
- Default authentication plugin: mysql_native_password → caching_sha2_password. Old client libraries (PHP PDO before 7.4, some versions of Python mysqlclient) do not support caching_sha2_password.
- utf8 in MySQL 8.0 is still utf8mb3 (3 bytes). For full Unicode, utf8mb4 is needed. If the database was in utf8, collations need to be checked.
- Reserved words expanded – rank, groups, value, system became reserved in 8.0. If the schema has such table or column names without backticks, queries will fail.
- GROUP BY ... ASC/DESC removed (deprecated in 5.7, removed in 8.0).
- ZEROFILL and FLOAT(M,D) deprecated in 8.0, removed in 8.4.
Full list of reserved words in MySQL 8.0
- RANK
- GROUPS
- VALUE
- SYSTEM
MySQL 8.0 → 8.4:
- mysql_native_password plugin disabled by default.
- Several system variables removed or renamed.
Why Is Compatibility Check Important?
Before upgrading, we always run mysqlcheck and MySQL Shell Upgrade Checker – a tool recommended by Oracle to detect issues before migration. As noted in MySQL documentation: "Before upgrading, always run MySQL Shell Upgrade Checker".
mysqlcheck --all-databases --user=root --password --check-upgrade
mysqlsh root@localhost -e "util.checkForServerUpgrade('root@localhost:3306', {targetVersion: '8.0'})"
Shell Upgrade Checker finds:
- Tables with incompatible data types
- Usage of removed/changed SQL functions
- Configuration parameters that changed
- Problematic names (reserved words)
- Tables in obsolete ROW_FORMAT
Typical output for a problematic database:
1) Usage of old temporal type - table mydb.events: column event_time is using old temporal type
2) Usage of reserved keywords - table mydb.rank: 'rank' is a reserved keyword in MySQL 8.0
3) Tables with non-native partitioning - table mydb.logs uses MySQL 5.1 style partitioning (upgrade required)Each issue must be fixed before the upgrade, otherwise mysql_upgrade or the server itself will refuse to start.
Major MySQL Version Upgrade: Which Strategy to Choose?
In-place upgrade – suitable for non-critical environments, small databases. Stop MySQL 5.7, install MySQL 8.0 on top, start. It is 5 times faster than dump/restore, but riskier – no rollback.
Logical migration via dump – reliable, but requires downtime proportional to database size. For large databases (500+ GB), downtime can reach a day, so replication is preferred.
Replication for zero-downtime – spin up MySQL 8.0 as a replica to the existing MySQL 5.7. MySQL 8.0 can be a replica of 5.7 (the reverse is not possible). Synchronize data via replication, switch the application to the new server, then drop the old one. This method reduces downtime to seconds – 100 times better than in-place.
# Example: setting up replication on MySQL 8.0
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='10.0.0.1',
SOURCE_USER='repl',
SOURCE_PASSWORD='password',
SOURCE_AUTO_POSITION=1;
START REPLICA;This strategy requires MySQL 5.7 to have GTID enabled. If not, use SOURCE_LOG_FILE/POS.
| Strategy | Downtime | Risks | Complexity |
|---|---|---|---|
| In-place | Minimal | High (no rollback) | Low |
| Dump/restore | Depends on volume | Low (backup exists) | Medium |
| Replication | Seconds | Medium (requires GTID) | High |
What to Check After the Upgrade?
Ensure all tables are converted: run SELECT table_schema, table_name, row_format FROM information_schema.tables WHERE engine='InnoDB' AND row_format NOT IN ('Dynamic', 'Compressed') AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys');
Check authentication plugins: SELECT user, host, plugin FROM mysql.user WHERE plugin = 'mysql_native_password';
Update users to caching_sha2_password (if clients support): ALTER USER 'app'@'%' IDENTIFIED WITH caching_sha2_password BY 'password'; Or leave mysql_native_password for legacy clients.
Which Configuration Parameters Need Updating?
Some parameters that worked in 5.7 cause errors or are ignored in 8.0. Remove from my.cnf:
-
innodb_log_checksums(always on) -
innodb_locks_unsafe_for_binlog(removed) -
query_cache_size,query_cache_type(query cache removed in 8.0) -
expire_logs_days→ replaced bybinlog_expire_logs_seconds
MySQL 8.0 will not start if my.cnf contains unknown variables. Shell Upgrade Checker finds them in advance.
How We Perform the Migration
Our process is based on years of experience – over 50 MySQL migration projects. Typical steps:
- Compatibility audit: run Upgrade Checker, check schema, configuration, client libraries.
- Strategy selection: determine whether in-place, dump/restore, or replication fits your SLA.
- Test environment creation: spin up a copy of the database, perform a test migration, check for errors.
- Rollback preparation: configure backup, save configuration.
- Production migration: execute according to plan, monitor metrics.
- Post-migration testing: check performance, functionality, errors.
- Documentation: record changes, update runbook, train your team.
What's Included in the Service – MySQL Major Upgrade
- Compatibility audit and report
- Upgrade strategy selection
- Test environment creation and test migration
- Rollback plan development
- Production migration execution
- Post-migration testing and optimization
- Documentation of changes and runbook update
- One-week support after the upgrade
Timelines and Cost
| Approach | Typical Timeline |
|---|---|
| In-place upgrade, single server | 1 day |
| Dump/restore, single server | 1-2 days |
| Zero-downtime via replication | 2-3 days |
| Master + replicas with traffic switch | 3-5 days |
Cost is calculated individually after the audit of current version, database size, and downtime requirements. License savings when moving from MySQL 5.7 to 8.0 can reach 30%. The cost of an hour of downtime for an average business is around $600. Investment in the upgrade pays off through reduced risks and licensing costs. For a preliminary estimate, leave a request.
Emergency Rollback
Even with careful preparation, unexpected issues can arise. We always plan a rollback before starting: restore from backup or switch to the old server via replication. On a test copy, 99% of errors are caught, but we are ready for an emergency rollback.
Contact us for a consultation – we'll assess your project within a day. Receive a commercial proposal with a detailed plan.