How to Upgrade from MySQL 8.0 to 8.4 LTS?

https://dev.mysql.com/doc/refman/8.4/en/images/mysql-lts-innovation-versioning-graph.png

Upgrading from MySQL 8.0 to 8.4 LTS gives teams a stronger foundation for daily database management. Many users worry about keeping their data safe and ensuring a smooth transition. Planning carefully helps avoid problems and keeps systems reliable.

• Teams see better security and improved performance after the upgrade.

• Usability becomes easier, which saves time and effort.
  Choosing the right platform and following best practices during the MySQL Update provides long-term stability.

Upgrade Path Overview

Supported Versions

Before starting a MySQL Update, teams should check if their current version supports a direct upgrade to MySQL 8.4 LTS. The official upgrade path allows users to move from an LTS or Bugfix series to the next LTS series. The table below shows the recommended upgrade paths and supported methods:

The following table lists which source versions support a direct upgrade to MySQL 8.4 LTS:

• Direct upgrades from MySQL 8.0 to 8.4 are supported.

• If the current version is lower than 8.0.22, an intermediate upgrade is necessary.

• Multi-version upgrades are not supported. Teams cannot skip versions.

Compatibility Notes

Administrators should review key compatibility changes before starting the upgrade. These changes can affect authentication, replication, and table creation. Important updates include:

• The mysql_native_password plugin is now disabled by default. The new default is caching_sha2_password.

• GTID now supports GTID TAGS, which improves transaction identification.

• The terminology of MASTER/SLAVE has been removed. This change affects commands and status variables.

• Using AUTO_INCREMENT with FLOAT or DOUBLE is no longer allowed. MySQL will return errors if teams try to create tables with this combination.

• Automatic updates to histograms are now enabled with the AUTO UPDATE option during ANALYZE TABLE statements.

• New privileges exist for the FLUSH PRIVILEGES statement and OPTIMIZE LOCAL TABLE.

• Several InnoDB variables have new default values.

• The mysqldump tool now supports the --output-as-version option for logical backups from previous versions.

• Shutdown and startup messages have improved.

• The FLUSH HOSTS statement has been removed. Teams must use TRUNCATE TABLE performance_schema.host_cache instead.

Tip: Review the full list of changes in the official documentation before starting a MySQL Update. This step helps prevent unexpected issues during the process.

MySQL Update Steps

Image Source: unsplash

Pre-Update Checks

Before starting a MySQL Update, administrators should complete several important checks to ensure a smooth process:

1. Confirm Version: Run SELECT VERSION(); to check the current MySQL version. The version must be at least 8.0.21 to proceed directly to 8.4 LTS.

2. Review Changes: Examine the release notes for MySQL 8.4. Look for removed settings, new reserved words, and other changes that could affect applications.

3. Backup & Precheck: Make a full backup of the database. Test the restore process to confirm data safety.

4. Run Upgrade Checker: Use the MySQL Shell Upgrade Checker Utility (util.checkForServerUpgrade()) to identify potential issues that might block the MySQL Update.

Note: Teams should also review supported platforms and GCC version requirements. Some operating systems or compiler versions may not support MySQL 8.4 LTS.

Several tools help verify system compatibility before a MySQL Update:

• pt-upgrade: Tests application queries for compatibility issues.

• MySQL Shell Upgrade Checker Utility: Generates a report with warnings, errors, and notices to prepare for the upgrade.

Backup Data

Backing up data is a critical step in any MySQL Update. Teams should understand the different backup types and follow best practices to protect their data:

• Full, Incremental, and Differential backups each serve different purposes. A full backup must exist and be tested before the upgrade.

• Regularly test backups to confirm their integrity.

• Store backups offsite, such as in the cloud or on an external file server, to guard against data loss from hardware failures or disasters.

• Keep 1-7 days of backups locally for quick recovery, depending on business needs.

• Prioritize which backup files to upload to the cloud based on recovery time objectives.

Tip: Always copy backup files to another location. Having backups in a different location protects against host or data center failures.

Run Upgrade Commands

After completing pre-update checks and backups, teams can begin the MySQL Update process:

1. Stop the MySQL server.

2. Install MySQL 8.4 LTS using the package manager or official installer for the operating system.

3. Start the MySQL server with the new version.

4. Run the MySQL Shell 9.4.0 and execute util.checkForServerUpgrade() to identify any remaining compatibility issues.

5. Apply any required schema or configuration changes highlighted by the upgrade checker.

6. Run mysql_upgrade to update system tables and objects.

Alert: The MySQL Update process may require downtime. Plan the upgrade during a maintenance window to minimize impact.

Verify Upgrade

Verification ensures the MySQL Update succeeded and the database operates as expected. Teams should use several methods to validate the upgrade:

Common verification errors may occur after a MySQL Update. For example, the error “Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted” can appear when viewing or running stored procedures, inserting into tables with triggers, or executing SELECT statements with misspelled function names. To resolve this, teams should dump and restore stored procedures after the upgrade. Running mysql_upgrade may also help, but it does not always fix the issue.

Reminder: Always monitor logs and error counters after a MySQL Update. Early detection of issues helps maintain database stability.

Troubleshooting

Common Issues

Many teams encounter problems during the upgrade from MySQL 8.0 to 8.4 LTS. The most frequently reported issues include:

• Difficulties with package management on Ubuntu 24 LTS.

• The MySQL repository remains on the old version, such as “focal,” even after attempts to upgrade.

• The option for MySQL 8.4 does not appear in the repository list.

These problems often slow down the upgrade process. Administrators should check repository settings and confirm that the system points to the correct MySQL version.

If the upgrade fails, rerunning the process with the same target version can resolve inconsistencies among database instances.
Some users also face network-related issues, such as insufficient IP addresses. Editing the subnet mask or restoring from a backup to a new database system with a larger IP range can help.

Data Migration

Migrating data between MySQL 8.0 and 8.4 LTS requires careful planning. Teams should follow these best practices to avoid data loss:

1. Confirm the current MySQL version using SELECT VERSION();. The version must be at least 8.0.21.

2. Make a full backup of the database before starting the upgrade. Each backup type offers different advantages.

3. Test the restore process on another server to ensure data recovery works.

4. Run the Upgrade Checker Utility to find issues that could block the upgrade.

5. Validate data after the upgrade. Check for missing or extra records and confirm that data types match expectations.

Teams who test their backups and validate data after migration reduce the risk of losing important information.

Platform Support

MySQL 8.4 LTS supports many operating systems and platforms. The official documentation lists all supported platforms.

• Administrators should always check the MySQL support page for the latest updates.

• Compatibility with the operating system and compiler version is essential for a successful upgrade.

Keeping systems updated and reviewing platform requirements helps teams avoid unexpected problems during the upgrade process.

Configuration & Best Practices

Config Updates

Teams should review configuration files before starting a MySQL Update. MySQL 8.4 LTS removes or changes several server startup options. Administrators must update settings to avoid startup errors. The following table highlights important configuration changes:

Several deprecated options from MySQL 8.0 no longer work in 8.4 LTS. Teams should remove or replace these settings:

• PASSWORD() function is gone. Use CREATE USER and ALTER USER instead.

• tx_isolation variable is removed. Switch to transaction_isolation.

• Expire_logs_days is replaced by binlog_expire_logs_seconds.

Administrators should always check configuration files for unsupported options before restarting the server.

Recommended Settings

MySQL 8.4 LTS introduces new default values for buffer and cache settings. These changes help improve performance and reliability. The table below compares key parameters between MySQL 8.0 and 8.4 LTS:

For best results, teams should:

• Adjust TCP buffer sizes for better network performance.

• Increase TCP backlog to handle more incoming connections.

• Disable unnecessary network services to reduce security risks.

• Set query_cache_size to 10%–20% of available memory.

• Enable query_cache_type for effective caching.

Administrators should also:

1. Index columns used often in WHERE clauses and JOINs.

2. Avoid too many indexes, which slow down writes.

3. Use composite indexes for multi-column queries, matching the order in the WHERE clause.

Maintenance

Regular maintenance keeps MySQL 8.4 LTS running smoothly. Teams should:

• Monitor logs for errors and warnings.

• Test backups and restore procedures monthly.

• Review index usage and remove unused indexes.

• Update configuration files after each MySQL Update.

• Schedule downtime for major upgrades and test changes in a staging environment first.

Consistent maintenance and careful configuration help teams avoid downtime and data loss. Administrators who follow these best practices ensure long-term stability and performance.

Upgrading MySQL requires careful planning and attention to detail. Teams should follow these steps:

1. Run the upgrade checker and review warnings.

2. Update the my.cnf file, removing deprecated settings.

3. Change user authentication to caching_sha2_password.

4. Convert character sets as needed.

5. Apply all configuration changes for MySQL 8.4.

Creating and testing backups protects data from unexpected issues. Teams should verify upgrades in a test environment before moving to production. The official MySQL documentation offers the latest procedures. Following best practices ensures long-term support, stability, and access to new features.

FAQ

What steps should teams take before upgrading MySQL?

Teams should check the current MySQL version, review release notes, and create a full backup. Running the upgrade checker helps find issues. Testing the restore process confirms data safety.

Tip: Always test backups on a separate server before starting the upgrade.

How can administrators fix repository issues during the upgrade?

Administrators should update repository sources to point to the correct MySQL version. They can run:

1
2

sudo apt-get update
sudo apt-get install mysql-server

Checking the official documentation helps resolve version mismatches.

Does MySQL 8.4 LTS require new hardware?

Most existing hardware supports MySQL 8.4 LTS. Teams should check operating system and compiler compatibility. Upgrading memory or storage may improve performance, but it is not required for most installations.

What should teams do if stored procedures break after upgrading?

Teams should dump stored procedures before the upgrade. After upgrading, they should restore procedures and run mysql_upgrade. This process fixes common errors with procedure tables.

How often should teams test backups after upgrading?

Teams should test backups monthly. Regular testing ensures data can be restored quickly. Scheduling backup tests helps maintain database reliability.

Note: Testing backups prevents data loss and supports disaster recovery plans.

Recommended reading

• SQL Optimization Course
• SQL optimization knowledge
• Database industry knowledge

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.

How to use SQLFlash in a database?

• SQLFlash + MySQL

• SQLFlash + PostgreSQL