Executive Summary and Analysis

It covers thoroughly covers all steps needed before upgrading MySQL from 8.0 to 8.4. It stresses that MySQL 8.0 reached End-of-Life (EOL) on April 6, 2026, after which it no longer receives fixes or security updates. Upgrading to 8.4 LTS is necessary for continued support, but it is a major version jump with many breaking changes (in authentication, SQL syntax, configuration parameters, and data handling). As a result, thorough testing and preparation are mandatory.

Key pre-upgrade tasks highlighted in the guide include:

• Backups: Always take both a full logical backup and a full physical backup before upgrading. The guide provides example commands (mysqldump and xtrabackup) and emphasizes verifying your backups by performing a test restore on a separate server. A tested backup is your only safety net; without it, an upgrade failure could cause catastrophic data loss.
• Disk Space: Allocate plenty of free disk space. A major upgrade will need space for backups and temporary data (e.g. dumping data dictionary). Recommandation having at least 2.5× the current data directory size free to be safe.
• Table Integrity Checks: Run mysqlcheck --all-databases --check to find and repair any corrupted tables before upgrading. (Do repairs only after confirming your backup!) The guide warns that upgrading corrupted data can cause the new version to fail to start or produce silent data corruption.
• Upgrade Checker: Use the MySQL Shell Upgrade Checker (mysqlsh ... util checkForServerUpgrade) targeting the planned 8.4 version (e.g. 8.4.8) to scan for compatibility issues. This tool examines 24+ metrics (deprecated variables, new reserved words, UTF8 vs UTF8MB4, zero dates, auth plugin issues, etc.). All ERRORS reported by the checker must be resolved before proceeding, and you should address warnings as well. (Ignoring them risks upgrade failures or outages.)
• Configuration Parameters: Audit your my.cnf for removed or changed parameters. MySQL 8.4 removes several deprecated settings (e.g. default_authentication_plugin, expire_logs_days, old_alter_table, etc.), and any of these left in my.cnf will prevent the server from starting. You must remove them in advance. Also note parameters whose defaults have changed (like innodb_flush_method now defaults to O_DIRECT instead of fsync). The guide advises explicitly setting such parameters if you rely on the old behavior to avoid unexpected performance shifts.
• SQL Mode Changes: MySQL 8.4 enforces stricter SQL modes. For example, ONLY_FULL_GROUP_BY and NO_ZERO_DATE become default, so queries that grouped without full columns or that used “0000-00-00” dates may now fail. The guide shows how to check for zero-date values and warns you to fix them or explicitly set sql_mode in my.cnf to preserve old behavior (though that is technical debt).
• Authentication Migration: The mysql_native_password authentication plugin is removed in MySQL 8.4. Any accounts still using it will fail to authenticate after upgrade. The guide instructs you to identify such users (SELECT user, host, plugin FROM mysql.user WHERE plugin = 'mysql_native_password';) and migrate them to caching_sha2_password (using ALTER USER ... IDENTIFIED WITH caching_sha2_password). It also provides a transitional option: you can temporarily enable the old plugin on 8.4 by adding mysql_native_password = ON under [mysqld] in my.cnf, allowing old accounts to connect while you gradually migrate them. However, this is a short-term workaround – the guide warns that mysql_native_password is deprecated and should be fully removed (e.g. within ~30 days) after upgrade.
• Connectors and ProxySQL: Verify that all application connectors and tools support caching_sha2_password. The blog lists minimum versions (e.g. Connector/J 8.0.12+, Python 8.0.11+, PHP7.4+, Go 1.4+, Node mysql2 1.6+). If you use ProxySQL, upgrade it to version 2.5.4 or later (required for caching_sha2_password on the MySQL backend). Also configure GET_SOURCE_PUBLIC_KEY=1 or enable TLS so that the initial handshake (which may need encryption) succeeds. The guide gives a recommended order for ProxySQL environments (first upgrade ProxySQL, configure keys, test auth, then upgrade MySQL with the transitional option, then migrate accounts).
• Schema & Syntax Adjustments: Identify and fix any schema issues that 8.4 will enforce. For example, MySQL 8.4 no longer allows AUTO_INCREMENT on FLOAT or DOUBLE columns, so such tables must be altered to use an integer type instead. New reserved keywords (e.g. MANUAL, QUALIFY, INTERSECT) may conflict with your table or column names; these identifiers must be quoted with backticks. Foreign key constraints are validated more strictly in 8.4, so mismatches (like type differences) should be corrected. The guide also warns that many old replication commands (e.g. SHOW SLAVE STATUS, CHANGE MASTER TO, etc.) have been replaced by new terms (SHOW REPLICA STATUS, CHANGE REPLICATION SOURCE TO, etc.), and any scripts or monitoring queries using the old syntax will error out.
• Checklist: The blog ends with a quick checklist of all steps for easy verification (backups, disk space, table checks, upgrade checker, auth migration, config cleanup, etc.). Our final checklist will synthesize these points in simpler form.

Overall, the guide is a thorough roadmap: it tells DBAs to prepare backups, clean/configure the environment, and resolve all compatibility issues before touching production. Skipping any of these pre-upgrade steps (for example, leaving a deprecated parameter in my.cnf or ignoring an upgrade-checker error) can cause the upgrade to fail, leave MySQL unstartable, or lead to data loss and extended downtime.

Assumptions:

• The guide’s examples assume a Linux environment with MySQL config at /etc/mysql/... and a data directory at /var/lib/mysql; actual paths may vary.
• Exact MySQL package names or install commands (Community vs. Percona) are not specified here.
• Commands like mysqlsh, mysqlcheck, mysqldump, and mysqlbackup or xtrabackup should be run with appropriate usernames, passwords, and file paths (placeholders are shown in the source).
• The target version is shown as 8.4.8; substitute your exact target release as needed.
• Connector versions listed are minimums; ensure you upgrade any clients or ORMs accordingly.

Upgrading from MySQL 8.0 to 8.4 LTS: Pre-Upgrade Preparation Guide

Note: MySQL 8.0 is End-of-Life as of April 6, 2026. Follow these steps carefully to prepare for a major-version upgrade to MySQL 8.4 LTS. This guide assumes you are a DBA managing self-hosted MySQL (Oracle or Percona). Adjust commands for your OS and environment.

1. Backup Your Data

Before anything, take both a full logical backup and a full physical backup, and verify them by restoring to a test server. Example commands:

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

mysqlbackup --user=root --password \
  --backup-dir=/backup/full_pre_upgrade \
  backup

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

Verify the backups by restoring them to a separate server. An untested backup is not a backup.

2. Plan Disk Space

Ensure you have ample free disk space. As a rule of thumb, have at least 2.5× your current datadir size available before upgrading. This covers backups, temporary files, and binary log retention. For example, a 1TB database should have ~2.5TB free.

3. Check Table Integrity

Run a full table check to find corruption:

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

If any tables are corrupted, repair them (only after ensuring backups are safe):

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

Note: Repairing large tables can be time-consuming and may lock tables; schedule it in maintenance windows. Also look for orphaned InnoDB tablespace files (advanced step):

SELECT * FROM information_schema.INNODB_TABLESPACES
WHERE NAME NOT IN (
  SELECT CONCAT(TABLE_SCHEMA,'/',TABLE_NAME)
  FROM information_schema.TABLES
  WHERE ENGINE='InnoDB'
);

4. Run the MySQL Shell Upgrade Checker

Use MySQL Shell’s checkForServerUpgrade utility against the target version (e.g. 8.4.8). This tool scans for incompatible settings, syntax, and data issues. Example:

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

It will report errors, warnings, and notices (e.g. removed variables, new reserved words, zero dates, auth plugin issues). Fix all ERRORS before upgrading, and address warnings as needed.

5. Clean Up Configuration Parameters

MySQL 8.4 removes some old parameters. Open your my.cnf and remove any of these deprecated settings (otherwise MySQL won’t start):

• default_authentication_plugin
• master_info_repository
• relay_log_info_repository
• binlog_transaction_dependency_tracking
• avoid_temporal_upgrade
• show_old_temporals
• log_bin_use_v1_row_events
• expire_logs_days (use binlog_expire_logs_seconds instead)
• old_alter_table

Also explicitly set any parameters whose default changed, if you depend on the old value. For example, if you want the old behavior:

• innodb_flush_method=fsync (instead of default O_DIRECT)

6. Adjust SQL Modes and Data

MySQL 8.4 enforces stricter SQL modes by default. Notably, ONLY_FULL_GROUP_BY and NO_ZERO_DATE are enabled. Find and fix any legacy zero dates or queries relying on implicit GROUP BY sorting. For example:

SELECT COUNT(*) FROM your_table
WHERE date_column = '0000-00-00' OR date_column = '0000-00-00 00:00:00';

Either remove/replace zero-date values, or explicitly set the old SQL mode (not recommended long-term). Also ensure any GROUP BY queries include all non-aggregated columns or use explicit ORDER BY.

7. Migrate Authentication Plugins

The old mysql_native_password plugin is removed in 8.4. Identify any user accounts still using it:

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

For each, alter the account to use caching_sha2_password (set a password):

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

Verify that your application libraries/drivers support caching_sha2_password (e.g. Connector/J ≥8.0.12, Python ≥8.0.11, PHP 7.4+, Go ≥1.4, Node.js mysql2 ≥1.6). If you cannot migrate all at once, you can temporarily re-enable the old plugin on the 8.4 server by adding this to my.cnf (under [mysqld]):

[mysqld]
mysql_native_password = ON

This allows existing mysql_native_password users to connect until you convert them. But it’s only a short-term workaround: remove that line and finalize migration soon after upgrade, as the old plugin is deprecated.

8. Prepare ProxySQL (if applicable)

If you use ProxySQL, upgrade it to version 2.5.4 or later before the MySQL upgrade. Configure TLS or the GET_SOURCE_PUBLIC_KEY=1 setting in ProxySQL so that the back-end connection can handle caching_sha2_password authentication. Test the full App→ProxySQL→MySQL authentication chain in a staging environment first, since failures here are a common issue.

9. Fix Schema and Syntax

• AUTO_INCREMENT on FLOAT/DOUBLE: MySQL 8.4 disallows this. Find affected columns:
  SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS WHERE EXTRA LIKE '%auto_increment%' AND DATA_TYPE IN ('float', 'double');
  Then alter them to an integer type (e.g. BIGINT):
  ALTER TABLE mytable MODIFY id BIGINT AUTO_INCREMENT;
• New Reserved Keywords: If you have tables or columns named MANUAL, QUALIFY, INTERSECT, etc., wrap them in backticks. For example, instead of CREATE TABLE manual (id INT), do CREATE TABLE `manual` (id INT).
• Foreign Key Validation: 8.4 is stricter about foreign keys. The upgrade checker will flag mismatches; fix any parent/child column type inconsistencies or naming conflicts【11†L411-L417】.
• Deprecated Replication Syntax: Update any scripts or tools that use old MASTER/SLAVE commands. For example, use SHOW REPLICA STATUS instead of SHOW SLAVE STATUS, CHANGE REPLICATION SOURCE TO instead of CHANGE MASTER TO, etc.【11†L431-L440】. These old commands will error out in 8.4.

10. Final Checks & Checklist

Before proceeding to the actual upgrade, verify you have done all the above steps. Key items:

• Logical and physical backups taken and tested.
• Enough disk space (≥2.5× datadir) free for upgrade.
• All tables checked and repaired.
• Upgrade Checker run and all reported errors fixed.
• Removed all deprecated parameters from my.cnf.
• Migrated or planned migration of all mysql_native_password users (or enabled transitional plugin).
• ProxySQL upgraded and auth chain tested (if used).
• Affected schemas fixed (AUTO_INCREMENT, keywords, etc.) and SQL modes addressed.

With these preparations complete, you can move on to testing and executing the upgrade.

Best Practices and Tips

• Always test *every* step in a non-production environment first.
• Document your current configuration and changes you make, so you can rollback if needed.
• Communicate with application teams about downtime and any connection changes required.

Best Way — Easy Checklist

1. Confirm EOL and Plan Upgrade: Note MySQL 8.0 EOL (Apr 6, 2026). Plan upgrade to 8.4 LTS.
2. Take & Test Backups:

• Run mysqldump --all-databases and a full physical backup (e.g. XtraBackup).
• Restore them on a test server to verify.

1. Ensure Disk Space: Check disk free space; make sure at least 2.5× your datadir size is available.
2. Check Tables:

• Run mysqlcheck -u root -p --all-databases --check.
• If issues, repair (--auto-repair) during maintenance window.

1. Run Upgrade Checker:

• Execute MySQL Shell’s util.checkForServerUpgrade targeting your 8.4 version.
• Fix all reported errors/warnings before proceeding.

1. Clean Up my.cnf:

• Remove any parameters removed in 8.4 (e.g., default_authentication_plugin, expire_logs_days, etc.).
• Explicitly set any changed defaults if needed.

1. Adjust SQL Modes/Data:

• Check and handle zero dates (e.g. SELECT COUNT(*) ... '0000-00-00').
• Add ORDER BY to queries if relying on implicit GROUP BY sorting.

1. Migrate Authentication:

• Find users with mysql_native_password. (SELECT user, host FROM mysql.user ...)
• For each, run: ALTER USER 'user'@'host' IDENTIFIED WITH caching_sha2_password BY 'pwd';.
• (If needed, enable mysql_native_password = ON in 8.4 my.cnf temporarily, but remove after migration.)

1. Upgrade ProxySQL (if used):

• Upgrade ProxySQL to ≥2.5.4.
• Configure TLS or GET_SOURCE_PUBLIC_KEY=1 for backend connections.
• Test connections using the new auth plugin.

1. Fix Schema Issues:
   • Change any AUTO_INCREMENT on FLOAT/DOUBLE to INT.
   • Quote any new keywords (e.g. table named manual → `manual`).
   • Correct foreign key type mismatches.
2. Update Scripts/Tools:
   • Search code for old replication terms (SLAVE/MASTER) and update to REPLICA/SOURCE syntax.
   • Ensure all management tools (PMM, XtraBackup, MySQL Shell, etc.) are compatible with 8.4.
3. Review Checklist: Go through your own pre-upgrade list before downtime. Ensure backups, checks, migrations, and updates are all verified.

Comparison of Key Pre-Upgrade Tasks

Task	Risk if Skipped	Time/Complexity
Full logical & physical backups	Data loss if upgrade fails	Low (few hours) – straightforward tools (mysqldump, Xtrabackup)
Disk space planning	Upgrade aborts, disk full errors	Low (minutes) – check free space, adjust storage
Table integrity check	Upgrade can fail or corrupt data silently	Low to Medium (minutes to hours, depending on DB size)
Run MySQL Shell Upgrade Checker	Unidentified incompatibilities (fails later)	Medium (1–2 hours) – technical familiarity with MySQL Shell
Remove deprecated parameters in my.cnf	Server won’t start after upgrade	Low (30–60 min) – just edit config file
Explicitly set changed defaults	Unexpected performance regressions	Low (10–30 min) – minor config edits
SQL mode data cleanup	Queries that worked may suddenly fail	Medium (1–2 hours) – query review/alter data
Migrate authentication to caching_sha2_password	Accounts can’t log in to new server	Medium (1–2 hours) – ensure drivers support it
ProxySQL upgrade & config	Connection errors through ProxySQL	Medium to High (hours) – needs testing and possible TLS setup
Schema fixes (AUTO_INC, keywords)	DDL statements will error	Low to Medium (hours) – run queries to find issues and alter tables
Update scripts (SQL syntax)	Monitoring/tools break with errors	Low (1–2 hours) – search/replace in code

Pre-Upgrade FAQ / Troubleshooting

• Q: What if the MySQL server fails to start after upgrade?
  A: Check my.cnf for any removed parameters (e.g. default_authentication_plugin, old_alter_table) – remove them. Also review error logs for missing plugins or syntax errors in config.
• Q: I see “authentication plugin caching_sha2_password not supported” error. What happened?
  A: Some applications (or ProxySQL) may not support the new auth plugin. Make sure you upgraded those clients/ProxySQL to versions that support caching_sha2_password or enabled the transitional plugin option temporarily.
• Q: Why are some SELECT queries failing after upgrade?
  A: MySQL 8.4 enforces stricter SQL mode. Check if your queries relied on GROUP BY without full columns or on ‘0000-00-00’ dates. You may need to rewrite queries or adjust SQL modes/data.
• Q: How do I handle a table with FLOAT column using AUTO_INCREMENT?
  A: 8.4 disallows AUTO_INCREMENT on FLOAT/DOUBLE columns. You must ALTER the column to an integer type (e.g. BIGINT).
• Q: Is it safe to skip the MySQL Shell upgrade checker?
  A: No. Skipping it risks missing critical compatibility issues (like removed variables or bad column types) that will cause the upgrade to fail or MySQL to crash. Always run it and fix errors.