MySQL 8.0 to 8.4 LTS Upgrade Guide — Part 1: Pre-Upgrade Preparation

MySQL 8.0 reached End of Life (EOL) on April 6, 2026. After that date, Oracle no longer provides standard support, security updates, or bug fixes. Upgrading to MySQL 8.4 LTS ensures continued long-term support, security patches, and access to performance improvements.

This guide is intended for Database Administrators (DBAs) performing the upgrade on self-managed MySQL or Percona Server installations. It covers both Oracle MySQL Community/Enterprise and Percona Server for MySQL. Where package commands differ between the two, both variants are shown explicitly.

Key Differences: MySQL 8.0 vs 8.4 LTS

Before starting any upgrade work, understand the scope of what's changing. The following table summarizes the most impactful differences between MySQL 8.0 and 8.4 LTS.

Backup Strategy

Pre-upgrade preparation is the most critical phase. A well-prepared upgrade dramatically reduces risk and downtime. It all starts with backups.

A reliable, tested backup is your only safety net. Without it, a failed upgrade could result in catastrophic, unrecoverable data loss.

Logical Backup Options

• mysqldump: Full logical dump including routines, triggers, and events
• mydumper / myloader: Multi-threaded logical backup and restore for large datasets
• MySQL Shell Dump Utility (util.dumpInstance): Modern parallel dump with compression

Physical Backup Options

• Percona XtraBackup: Hot, non-blocking backup for InnoDB (use version matching your MySQL)
• LVM Snapshots: Instant filesystem-level snapshot (requires clean InnoDB shutdown or flush with lock)
• File system copy (tar/gzip): Cold backup after clean shutdown with innodb_fast_shutdown=0

Sample mysqldump Command

mysqldump -u root -p --all-databases --single-transaction --routines \
  --triggers --events --set-gtid-purged=OFF > full_backup_pre_upgrade.sql

Sample XtraBackup Commands

xtrabackup --backup --target-dir=/backup/full_pre_upgrade \
  --user=root --password=<password>

xtrabackup --prepare --target-dir=/backup/full_pre_upgrade

Disk Space Planning

A major version upgrade requires significant free disk space. Plan for the following:

Table Integrity Check

Identify and repair any corrupted tables before upgrading. Upgrading with corrupted data can cause the new version to fail to start or cause silent data corruption.

Run Table Checks

mysqlcheck -u root -p --all-databases --check

Auto-Repair Corrupted Tables (only after confirming backup)

mysqlcheck -u root -p --all-databases --auto-repair

Check for Orphaned Tablespace Files

-- Check for orphaned tablespace files:
SELECT * FROM information_schema.INNODB_TABLESPACES
WHERE NAME NOT IN (SELECT CONCAT(TABLE_SCHEMA,'/',TABLE_NAME)
  FROM information_schema.TABLES WHERE ENGINE='InnoDB');

MySQL Shell Upgrade Checker Utility

The MySQL Shell Upgrade Checker is the single most important pre-upgrade tool. It analyzes your current instance against 24+ compatibility metrics and reports errors, warnings, and notices.

Run the Upgrade Checker (targeting 8.4.8)

mysqlsh -- util checkForServerUpgrade root@localhost:3306 \
  --target-version=8.4.8 \
  --config-path=/etc/mysql/mysql.conf.d/mysqld.cnf

Alternative JavaScript Syntax within MySQL Shell

util.checkForServerUpgrade("root@localhost:3306",
  {targetVersion: "8.4.8",
   configPath: "/etc/mysql/mysql.conf.d/mysqld.cnf"})

The Upgrade Checker examines:

• Usage of removed or deprecated system variables
• Usage of new reserved keywords as identifiers (table/column names)
• Deprecated utf8 (utf8mb3) character set usage
• Tables with temporal columns in pre-5.6.4 format
• Partitioned tables with unsupported engines
• Foreign key constraint naming conflicts
• Column definitions exceeding maximum lengths
• Zero date/datetime values (0000-00-00) violating strict mode
• Authentication plugin compatibility
• Orphan data dictionary entries and inconsistencies

Removed and Changed Configuration Parameters

MySQL 8.4 removes several parameters that were deprecated in 8.0. If any of these remain in your my.cnf, the server will refuse to start.

Parameters Removed in MySQL 8.4 (must remove from my.cnf)

Parameters with Changed Default Values in 8.4

sql_mode Changes

MySQL 8.4 enforces stricter sql_mode defaults. This is one of the most common sources of silent breakage where queries that worked in 8.0 fail or return different results in 8.4.

Key sql_mode Changes

• ONLY_FULL_GROUP_BY is enforced by default: SELECT columns not in GROUP BY or aggregate functions will error
• NO_ZERO_IN_DATE and NO_ZERO_DATE: Dates like '0000-00-00' or '2024-00-15' are rejected
• Implicit GROUP BY sorting removed: Queries relying on GROUP BY producing sorted output must add explicit ORDER BY clauses

Check Your Current sql_mode

SELECT @@GLOBAL.sql_mode;

Find Tables with Zero-Date Values

-- Example for a specific table:
SELECT COUNT(*) FROM your_table
WHERE date_column = '0000-00-00' OR date_column = '0000-00-00 00:00:00';

Authentication Plugin Migration

Identify Affected Users

SELECT user, host, plugin, password_expired
FROM mysql.user WHERE plugin = 'mysql_native_password';

Migrate Users to caching_sha2_password

ALTER USER 'username'@'host' IDENTIFIED WITH caching_sha2_password BY 'new_password';

Verify that all application connection libraries and drivers support caching_sha2_password. Older MySQL connectors (pre-8.0) may not support the new authentication plugin. Common connectors that need updating:

Transitional Option: Temporarily Re-enabling mysql_native_password

If an immediate migration of all users and applications to caching_sha2_password isn't feasible, MySQL 8.4 provides a transitional option. Add the following to your my.cnf:

[mysqld]
mysql_native_password = ON

This loads the plugin at startup so existing accounts using mysql_native_password can still authenticate. It does not make it the default for new accounts. This is strictly a temporary measure to allow a phased migration.

Recommended Migration Sequence

1. Add mysql_native_password = ON to my.cnf for the 8.4 upgrade
2. Upgrade MySQL from 8.0.37 to 8.4
3. Verify all applications and users can connect successfully
4. Migrate user accounts to caching_sha2_password in batches:

ALTER USER 'app_user'@'%' IDENTIFIED WITH caching_sha2_password BY 'password';

5. After all accounts are migrated, remove mysql_native_password = ON from my.cnf
6. Restart MySQL and verify all connections still work

ProxySQL Considerations

If your environment uses ProxySQL for connection pooling, load balancing, or query routing, the authentication plugin change requires special attention.

Key Considerations

• ProxySQL handles frontend (app-to-ProxySQL) and backend (ProxySQL-to-MySQL) authentication separately. Both paths must support caching_sha2_password.
• caching_sha2_password requires either TLS or RSA public key exchange for the initial handshake. Without TLS between ProxySQL and MySQL, the first authentication will fail.
• Configure GET_SOURCE_PUBLIC_KEY=1 in ProxySQL mysql_servers, or enable TLS between ProxySQL and MySQL backends.

Recommended Upgrade Order for ProxySQL Environments

1. Upgrade ProxySQL to version 2.5.4+ (no disruption to existing 8.0 traffic)
2. Configure TLS or RSA key exchange between ProxySQL and MySQL backends
3. Test caching_sha2_password connectivity through ProxySQL in staging
4. Proceed with MySQL 8.0.37 to 8.4 upgrade (with mysql_native_password=ON as transitional)
5. Migrate user accounts to caching_sha2_password in batches, validating through ProxySQL
6. Remove mysql_native_password = ON from my.cnf once fully migrated

Schema Compatibility Checks

AUTO_INCREMENT on FLOAT/DOUBLE Columns

MySQL 8.4 removes AUTO_INCREMENT support on FLOAT or DOUBLE columns. Identify and alter affected tables:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM information_schema.COLUMNS
WHERE EXTRA LIKE '%auto_increment%'
  AND DATA_TYPE IN ('float', 'double');

Fix by altering the column to INT or BIGINT:

ALTER TABLE table_name MODIFY COLUMN id BIGINT AUTO_INCREMENT;

New Reserved Keywords

MySQL 8.4 adds reserved keywords including MANUAL, QUALIFY, INTERSECT, and others. If any identifiers use these keywords, they must be backtick-quoted:

-- This will FAIL in 8.4:
CREATE TABLE manual (id INT);

-- Fix:
CREATE TABLE `manual` (id INT);

Foreign Key Validation

MySQL 8.4 enforces stricter foreign key validation. The Upgrade Checker identifies problematic FK constraints. Common issues include mismatched column types between parent and child tables, and FK names that conflict with reserved identifiers.

Character Set Considerations (utf8mb3 to utf8mb4)

While not a hard blocker, utf8 (utf8mb3) is deprecated. Converting to utf8mb4 changes byte widths from 3 to 4 bytes per character, which may violate index length limits (767 bytes for compact/redundant, 3072 for dynamic/compressed). Always check index sizes before converting:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME
FROM information_schema.COLUMNS
WHERE CHARACTER_SET_NAME IN ('utf8', 'utf8mb3');

SQL Syntax and Terminology Changes

MySQL 8.4 fully removes deprecated replication-related SQL syntax. In 8.0, these produced warnings. In 8.4, they produce ERRORS. All scripts, monitoring tools, and automation must be updated.

Part 1 Quick Checklist

Use this checklist to verify you've completed all pre-upgrade preparation steps before moving on to testing.

• Take full logical backup (mysqldump/mydumper) and verify restore
• Take full physical backup (XtraBackup/LVM snapshot) and verify restore
• Verify sufficient disk space (2.5x datadir free)
• Run mysqlcheck --all-databases --check for corruption
• Run MySQL Shell Upgrade Checker targeting 8.4.8
• Resolve all Upgrade Checker ERRORS
• Address all Upgrade Checker WARNINGS
• Identify and migrate mysql_native_password users (or plan transitional)
• Identify and fix AUTO_INCREMENT on FLOAT/DOUBLE columns
• Audit my.cnf: remove all deprecated/removed parameters
• Set explicit values for parameters with changed defaults
• Review and fix sql_mode-dependent queries (GROUP BY, zero dates)
• Audit all scripts/code for MASTER/SLAVE syntax
• Upgrade ProxySQL to 2.5.4+ (if applicable)
• Test auth chain through ProxySQL in staging (if applicable)
• Verify all tool versions are 8.4-compatible (PMM, XtraBackup, etc.)