Version Upgrade Guide
MySQL Version Migration Guide
Version upgrades are among the highest-risk database operations. This chapter provides a complete guide for upgrading from 5.7 to 8.0 to 8.4, including pre-checks, breaking changes, step-by-step procedures, and rollback plans.
Golden rules: ① Always backup before upgrading ② Validate in test environment first ③ No skipping major versions (5.7 → 8.4 must go through 8.0) ④ Have your rollback plan ready BEFORE starting the upgrade
1. Supported Upgrade Paths
MySQL 5.5 → 5.6 → 5.7 → 8.0 → 8.4 (LTS) ↓ 9.0 (Innovation)
⚠️ NOT supported: 5.7 → 8.4 (must go through 8.0) ⚠️ NOT supported: skipping 2+ major versions
2. Pre-upgrade Checks
-- MySQL Shell (recommended — most comprehensive)
mysqlsh> util.checkForServerUpgrade('root@localhost:3306', {targetVersion: "8.0"})
-- Check tables without primary keys
SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS tc
ON t.TABLE_SCHEMA = tc.TABLE_SCHEMA AND t.TABLE_NAME = tc.TABLE_NAME
AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema','sys')
AND tc.TABLE_NAME IS NULL;
3. MySQL 5.7 → 8.0 Breaking Changes
| Change | 5.7 | 8.0 | Action Required |
|---|---|---|---|
| Default charset | latin1 | utf8mb4 | Check sort order changes |
| Authentication | mysql_native_password | caching_sha2_password | Upgrade JDBC driver to 8.x |
| Query Cache | Available | Removed | Remove from my.cnf |
| GROUP BY behavior | Lenient | Strict ONLY_FULL_GROUP_BY | Fix ambiguous GROUP BY queries |
| innodb_file_format | Exists | Removed | Remove from my.cnf |
| YEAR(2) | Exists | Removed | Change to YEAR(4) |
4. MySQL 8.0 → 8.4 Breaking Changes
mysql_native_passworddisabled by default — migrate users to caching_sha2_password- SLAVE commands deprecated → update scripts to use REPLICA commands
- Several GROUP BY edge cases more strictly enforced
5. Rolling Upgrade (Zero Downtime)
- Full backup + test environment validation with regression tests
- Upgrade replicas one at a time, verify replication health after each
- Promote upgraded replica to primary (via MHA/Orchestrator)
- Upgrade original primary (now replica), verify catch-up
- Monitor closely for 1-2 weeks before decommissioning old servers
6. Rollback Plans
MySQL upgrades cannot be rolled back in-place! Higher-version mysqld modifies ibdata and system table formats that older versions cannot read. Rollback means restoring from backup, not downgrading in place.
- Safest: Keep old-version replica running; if rollback needed, cut traffic back to old replica
- Physical backup: xtrabackup snapshot before upgrade; restore to new server if needed
- For large tables: Use CDC (Canal/Debezium) to sync new primary back to old-version standby during stability period