What’s New in MySQL 8.4 LTS: Key Enhancements Explained

MySQL has been evolving rapidly, and with the recent release of MySQL 8.4, there’s a lot to unpack. While MySQL 8.4 is officially dubbed a Long-Term Support (LTS) release, many features introduced between 8.0 and 8.4 have now matured into defaults or become deprecated/removed.

This blog provides a comprehensive, side-by-side comparison of the key differences between MySQL 8.0 and 8.4. Whether you’re planning an upgrade or just curious about the evolution of MySQL, this guide simplifies the technicalities to help you make informed decisions.

MySQL 8.4: Long-Term Support (LTS) release

Release date: April 2024

LTS designation: MySQL 8.4.0 is designated as a Long Term Support (LTS) release.  

Support duration:

• Premier Support: 5 years
• Extended Support: 3 additional years
• Total Extended Support End: Approximately April 2032.
• The LTS release is the final minor version within its major release series.
• 8.4 is the 8.x LTS; the next major release will be MySQL 9.0.

1. Default authentication method

• MySQL 8.0: Uses caching_sha2_password as the default plugin instead of mysql_native_password.
• MySQL 8.4: Continues with caching_sha2_password but goes further by disabling mysql_native_password by default.

Why it matters: mysql_native_password is deprecated in 8.0.34 and will be removed entirely in MySQL 9.0.0. If you still rely on it, it’s time to switch.

2. Replication terminology

• MySQL 8.0: Uses older terms like MASTER/SLAVE.
• MySQL 8.4: Uses inclusive terms like SOURCE/REPLICA.

Why it matters: This aligns with modern standards and inclusivity efforts while also making replication roles clearer.

3. Foreign key constraints

• MySQL 8.0: Allows indexes on the parent table (not necessarily unique) for foreign keys.
• MySQL 8.4: Requires a unique key on the referenced columns in the parent table.

Why it matters: This enforces stricter relational integrity. The older behavior is deprecated and controlled by restrict_fk_on_non_standard_key (default ON). Adjusting your schema accordingly is recommended.

4. AUTO_INCREMENT with FLOAT or DOUBLE

• MySQL 8.0: Deprecated with warnings.
• MySQL 8.4: Completely removed. Using it causes an error.

Why it matters:  You must remove AUTO_INCREMENT from FLOAT/DOUBLE columns or convert them to integer types before upgrading.

5. FLUSH HOSTS statement

• MySQL 8.0: Still available.
• MySQL 8.4: Removed. Use TRUNCATE TABLE performance_schema.host_cache instead.

Why it matters: The new method offers more control and observability.

6. SET_USER_ID privilege changes

• MySQL 8.0: Uses SET_USER_ID.
• MySQL 8.4: Introduces SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER for finer privilege control.

Why it matters: This improves security and control over object ownership.

7. innodb_buffer_pool_in_core_file

• 8.0 Default: ON
• 8.4 Default: OFF

Why it matters: Turning this off reduces core file sizes by excluding large InnoDB buffer pool data.

8. innodb_change_buffering

• 8.0 Default: all
• 8.4 Default: none

Why it matters: No buffering of index changes can lead to faster writes, especially on SSDs.

9. innodb_adaptive_hash_index

• 8.0 Default: ON
• 8.4 Default: OFF

Why it matters: Depending on your workload, it may be beneficial to enable or disable AHI dynamically to enhance query performance. Since AHI does not provide benefits for all workload types, it’s recommended to perform benchmarking with both settings enabled and disabled using realistic workload scenarios to evaluate its impact.

10. innodb_doublewrite_pages

• 8.0 Default: Depends on innodb_write_io_threads
• 8.4 Default: Fixed at 128

Why it matters: This provides predictable and consistent doublewrite behavior.

11. innodb_flush_method

• 8.0 Default: fsync
• 8.4 Default: O_DIRECT (if supported)

Why it matters: Reduces OS caching, potentially improving performance.

12. innodb_io_capacity

• 8.0 Default: 200
• 8.4 Default: 10000

Why it matters: Reflects modern hardware capabilities like SSDs and RAID.

13. innodb_log_buffer_size

• 8.0 Default: 16 MB
• 8.4 Default: 64 MB

Why it matters: Larger buffers reduce the frequency of log writes, improving performance.

14. temptable_max_ram

• 8.0 Default: 1 GB
• 8.4 Default: 3% of total memory (within 1-4 GB)

Why it matters: Dynamically adjusts based on server memory, balancing performance and stability.

15. Automatic histogram updates

• 8.0: Manual
• 8.4: Supports ANALYZE TABLE AUTO UPDATE

Why it matters: Keeps statistics up-to-date automatically, improving query optimization.

16. SOURCE_RETRY_COUNT (Replication)

• 8.0 Default: 60
• 8.4 Default: 10

Why it matters: Reduces failover/reconnect wait time.

17. GTID tags

• 8.0: Basic
• 8.4: Allows tagging groups of transactions

Why it matters: Improves tracking and auditing of replication transactions.

18. Innodb_purge_threads

• 8.0: 4
• 8.4: The default value is 1 when the number of available logical processors is 16 or fewer; otherwise, the default is 4.

Why it matters: Automatically optimizes purge thread usage based on CPU availability, helping balance purge performance and overall system load.

Final thoughts

MySQL 8.4 is a significant step forward, not just in features but also in usability, performance, and security. While many of these changes enhance the database’s behavior by default, they also require careful planning, especially during upgrades.

Test thoroughly and consider enabling or disabling features based on your workload and infrastructure. With MySQL 9.0 on the horizon, transitioning your systems to MySQL 8.4 standards will put you ahead of the curve.

To thoroughly test your MySQL upgrade path, you can take advantage of two powerful tools: pt-upgrade and checkForServerUpgrade. These tools help identify potential issues, such as query plan changes or deprecated features that could affect performance or functionality after an upgrade. For a detailed walkthrough on how to use them effectively, refer to this helpful blog post: Two Extremely Useful Tools (pt-upgrade and checkForServerUpgrade) for MySQL Upgrade Testing.