Amazon RDS for MySQL LTS version 8.4 is now generally available

Today, Amazon Relational Database Service (Amazon RDS) has announced support for MySQL version 8.4, which is the latest Long-Term Support (LTS) major version from the MySQL community. With that, Amazon RDS now supports MySQL Community Edition versions 8.0 and 8.4. In addition to the two community-supported LTS releases, Amazon RDS also offers MySQL 5.7 under RDS Extended Support, where RDS provides critical patches and bug fixes for the engine. For any of these versions, you can bring your existing MySQL code, applications, and tools to Amazon RDS.

With MySQL 8.4, the MySQL community has introduced, as well as retired, multiple features, which are listed in the MySQL 8.4 reference manual. In this post, we explore some of these features, list known breaking changes, and provide recommendations to ease the migration of your workloads to this version.

Before we dive into RDS for MySQL 8.4, let’s look at the new MySQL versioning model introduced by the community last year. The MySQL community now classifies every engine release either as an Innovation Release, or a Long-Term Support (LTS) Release.

• Innovation releases: These versions are released frequently (typically one release per quarter), and have short support cycles, making them ideal for early and rapid experimentation by developers and DBAs. As of this writing, MySQL community has announced five Innovation Releases – versions 8.1, 8.2, 8.3, 9.0, and 9.1. Due to the short lifecycle of these releases, RDS offers these versions in the Amazon RDS Database Preview Environment.
• Long-Term Support (LTS) releases – In contrast, LTS releases include stable, proven features, including functionality introduced on prior Innovation Releases, and receive five years of community support (RDS offers up to 3 additional years under RDS Extended Support after community stops supporting these versions). The MySQL community expects to add/remove features only in the first version of an LTS release, e.g. 8.4.0. Incremental releases within an LTS Series, such as MySQL 8.4.1, and 8.4.2., prioritize backward compatibility and security. MySQL 8.4 is the first LTS Release from the community, and additional LTS releases are expected approximately once every 2 years. Enterprises that require long term support, and minimal disruption due to breaking changes will prefer LTS Releases over Innovation Releases.

For more information, review MySQL Releases: Innovation and LTS.

Upgrading your Amazon RDS for MySQL 8.0 database to MySQL 8.4

Before you start planning your MySQL upgrade, we strongly recommend that you review the upgrade path guidelines from the MySQL community. Amazon RDS for MySQL supports direct upgrades from MySQL 8.0 to 8.4. Note, if you’re running a minor version lower than 8.0.22, an intermediate upgrade to a higher minor version is required before upgrading to MySQL 8.4. Further, MySQL does not support multi-version upgrades, that is, you cannot skip versions during a major version or LTS upgrade. If your database is running MySQL 5.7, then you will have to first upgrade MySQL 5.7 to MySQL 8.0, and then upgrade MySQL 8.0 to MySQL 8.4.

You have multiple options when you’re upgrading your Amazon RDS for MySQL 8.0 database to MySQL 8.4: use Amazon RDS Blue/Green deployments, read replicas, or, perform an in-place upgrade. Of these, we recommend using Amazon RDS blue/green deployments to minimize risk and downtime during your database upgrade. Checking the compatibility of a new major/LTS version with your existing application plays a crucial role in an upgrade’s overall success. An Amazon RDS Blue/Green deployment creates two database environments: your current production environment (Blue), and a staging environment (Green). The Blue and Green environments remain in sync with each other using native logical replication, so you can safely test your changes on the staging (green) environment, and only promote it to primary when you’re confident of your application’s compatibility with the new version. This approach enables testing database upgrades in a safe staging environment alongside the production setup, and allows you to identify and resolve issues without impacting your production environment.

In the following sections, we discuss notable changes introduced with MySQL 8.4, that may be relevant for your applications.

Changed performance-related parameter defaults

With MySQL 8.4, the community has changed several parameter defaults, that may impact the flushing behavior. Amazon RDS for MySQL uses the same defaults. We recommend thoroughly testing your applications with these new defaults before upgrading. Although these changes are designed to enhance overall performance, some workloads may require additional parameter adjustments. Review each parameter change carefully and consider its impact on your specific use case. The following table shows the difference in default parameter values between MySQL 8.0 and 8.4:

Set default authentication plugin to caching_sha2_password

Starting with MySQL 8.4, Amazon RDS has switched the default authentication plugin to caching_sha2_password. The caching_sha2_password plugin implements SHA-256 password hashing with server-side caching for improved performance.

An authentication plugin determines the method used to verify a user’s identity during their attempt to connect to a MySQL server. It defines the protocol and algorithm employed to validate credentials and authorize access to the database system. The previous default authentication plugin, mysql_native_password, was deprecated by the community starting with MySQL 8.0.34, and has been removed entirely in MySQL 9.0 and subsequent Innovation Releases. We encourage you to use the new default authentication plugin, that is, caching_sha2_password, when creating new users.

When connecting to an RDS for MySQL DB instance with a user account created using the caching_sha2_password plugin, you must use SSL. Command line clients for MySQL 5.7, and higher versions have SSL enabled by default. If an unencrypted connection is used, the connection should support password exchange using an RSA key pair to maintain security.

The following example shows how to connect to the database using RSA key pair exchange with SSL disabled.

mysql -h<rds_endpoint> -u<user_name> -p<password> --ssl-mode=DISABLED --get-server-public-key

Note, MySQL client versions prior to 5.7 do not support caching_sha2_password authentication plugin. To ensure compatibility, it’s important to upgrade your MySQL client to the latest version.

If you upgraded your existing Amazon RDS instances from MySQL 8.0 to MySQL 8.4, your existing database users including the primary user will continue to use mysql_native_password. However, all new users will be created with the caching_sha2_password authentication plugin. We encourage you to migrate the existing users created with mysql_native_password to use caching_sha2_password plugin. You can modify an existing user created with mysql_native_password to caching_sha2_password by using the following ALTER USER command:

ALTER USER 'username'@'hostname' IDENTIFIED WITH caching_sha2_password BY 'password';

You can use the following query to list all users with mysql_native_password authentication plugin.

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

Make sure that your client driver is compatible with the caching_sha2_password authentication plugin before migrating the user.

Restricted use of non-unique keys or partial keys as foreign keys

The MySQL community has deprecated the ability to use non-unique or partial keys as foreign keys while creating or altering a table, and will remove it in a future version. To manage this transition, MySQL 8.4 includes a new parameter, called restrict_fk_on_non_standard_key, which is set to ON by default. This setting prevents the use of non-unique or partial keys as foreign keys. You can revert to the previous behavior by setting it to OFF. However, we recommend following best practices, and modifying your schema to only use unique keys as foreign keys.

Introduced automatic histogram updates

MySQL 8.4 has introduced a feature, called automatic histogram updates, designed to enhance the efficiency and accuracy of query optimization. With previous versions, updating histograms required a manual ANALYZE TABLE..UPDATE HISTOGRAM.. command to update the histogram. MySQL 8.4 introduces automatic updates, which are invoked when certain thresholds are met. In order to enable automatic update on a specific Histogram, run ANALYZE TABLE..UPDATE HISTOGRAM.. with the AUTO UPDATE option.

mysql > analyze table table1 update histogram on col2 with 10 buckets AUTO UPDATE;
*************************** 1. row ***************************
   Table: testdb.table1
      Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'col2'.
1 row in set (0.01 sec)

To verify if auto-update of histograms is enabled, you can query the HISTOGRAM column in the information_schema.column_statistics table. Look for the “auto-update”: true attribute in the JSON output.

mysql> select * from information_schema.column_statistics G
*************************** 1. row ***************************
SCHEMA_NAME: testdb
 TABLE_NAME: table1
COLUMN_NAME: col2
  HISTOGRAM: {"buckets": [], "data-type": "int", "auto-update": true, "null-values": 0.0, "collation-id": 8, "last-updated": "2024-11-12 01:01:10.234206", "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 10}
1 row in set (0.00 sec)

Specifically, for tables using the InnoDB engine, the innodb_stats_auto_recalc parameter invokes an automatic histogram update when more than 10% of a table’s rows have been modified. This threshold makes sure that statistics stay relevant without constant manual refreshes, which can help improve query performance, especially on complex queries.

Automatic histogram recalculations are asynchronous, so they may not occur immediately after significant data changes. In most cases, the delay is minimal, typically a few seconds, but it allows MySQL to balance system performance with the need for up-to-date statistics. Automatic histogram updates in MySQL 8.4 streamline query optimization and reduce maintenance overhead, allowing for more consistent and efficient database performance.

For tables with frequent or substantial data changes, you can rely on the automatic feature, or adjust the recalculation threshold as needed. For more information, review Configuring Persistent Optimizer Statistics Parameters. Alternatively, specifying the MANUAL UPDATE option for tables that require precise control over histogram updates offers flexibility in high-load environments, reducing the potential performance impact of frequent recalculations.

Updated the Group Replication plugin

MySQL 8.4 introduces several updates to the Group Replication plugin, which is typically used to implement continuous availability, or fault tolerance in distributed systems. Some of the key changes include:

• Default system variable changes – The default value for group_replication_consistency is now set to BEFORE_ON_PRIMARY_FAILOVER, replacing the previous default of EVENTUAL. This provides stricter transaction consistency during failover events. Additionally, the group_replication_exit_state_action now defaults to OFFLINE_MODE, shifting from READ_ONLY, which alters how nodes handle failure situations.
• Support for cross-version membership – Within the 8.4 series, servers with different 8.4 versions can still join the same replication group, making version management more flexible.
• Introduction of Group Replication Status Variables – Group Replication has introduced a set of new status variables to provide detailed insights into its operations. These variables offer information on various aspects, such as consensus proposals, certification garbage collection, message transmission, transaction consistency, and more. These status variables have member scope, reflecting local observations, and are reset on group bootstrap, member joining, automatic rejoin, or server restart. For more information, please refer to the MySQL reference manual.

These updates make MySQL Group Replication more robust, offering better consistency guarantees and flexibility in version management.

Removed non-inclusive terms from syntax, parameter groups, and stored procedures

The MySQL community has actively been retiring all non-inclusive language. Starting with MySQL 8.4, the community has discontinued support for deprecated replication-related SQL statements. Attempting to use these statements now results in a syntax error. For example, SHOW MASTER STATUS is now SHOW BINARY LOG STATUS, and SHOW SLAVE STATUS is now SHOW REPLICA STATUS. Review and update any stored procedures that contain these deprecated terms, because it may cause your upgrade to fail. You can refer to the MySQL reference manual for a comprehensive list of replication statement syntax that have changed.

Similarly, Amazon RDS has transitioned to use inclusive language starting with MySQL 8.4. For example, the term slave has been replaced with the term replica in parameter names. This change extends to all configuration parameters and system variables. For example: slave_parallel_workers is now replica_parallel_workers. We recommend that you update existing automation scripts, monitoring tools, and custom configurations with the new parameter names. You can learn more about this transition in the RDS user guide.

When you upgrade an Amazon RDS for MySQL database to MySQL 8.4 LTS, the major version upgrade process automatically updates all RDS stored procedures with inclusive terminology. The following table illustrates the new and existing procedure names.

Both sets of stored procedures are supported in Amazon RDS for MySQL 8.4, to ensure backward compatibility. However, we encourage you to adopt the new, inclusive naming conventions. The older procedures will be removed in future releases of Amazon RDS for MySQL, so transitioning to the new names will help ensure long-term compatibility, and align with industry best practices for inclusive language.

For all user-created stored procedures, we strongly recommend that you manually review, and update any custom stored procedures that contain unsupported terms. Failure to do so may cause issued during your major version upgrades . The upgrade precheck process will attempt to identify all affected procedures, but the list may not be exhaustive.

Removed binlog_transaction_dependency_tracking

Starting with MySQL 8.4, the community has removed the binlog_transaction_dependency_tracking variable. MySQL now uses WRITESETS to generate dependency information for the binary log, when multithreaded replicas are in use. This behavior is equivalent to setting binlog_transaction_dependency_tracking to WRITESET in earlier MySQL versions. This change is aimed at improving optimize performance for multithreaded replication scenarios.

So far in this post, we have discussed features added or removed by the MySQL community with 8.4 LTS release. In the following sections, we explore enhancements specific to Amazon RDS for MySQL 8.4.

Replaced OpenSSL with AWS-LC as cryptographic library provider

Starting with version 8.4, Amazon RDS for MySQL is integrated with AWS Libcrypto (AWS-LC) FIPS module (Certificate #4816). Amazon RDS for MySQL 8.4 supports TLS 1.2 and TLS 1.3, and supports the following cipher suites:

        TLS 1.3 Protocol
        - TLS_AES_128_GCM_SHA256
        - TLS_AES_256_GCM_SHA384
        - TLS_CHACHA20_POLY1305_SHA256
        
        TLS 1.2 Protocol
        - ECDHE-ECDSA-AES128-GCM-SHA256
        - ECDHE-ECDSA-AES256-GCM-SHA384
        - ECDHE-RSA-AES128-GCM-SHA256
        - ECDHE-RSA-AES256-GCM-SHA384
        - ECDHE-ECDSA-CHACHA20-POLY1305
        - ECDHE-RSA-CHACHA20-POLY1305

We advise verifying the compatibility of your client applications with these supported TLS versions and cipher suites. This is crucial to provide seamless connectivity and maintain the enhanced security provided by the new cryptography library.

Set InnoDB_dedicated_server as enabled by default

Starting with MySQL 8.4, Amazon RDS enables the innodb_dedicated_server parameter by default, which allows the database engine to automatically calculate optimal values for innodb_buffer_pool_size and innodb_redo_log_capacity based on the underlying instance. This could provide a performance boost for certain workloads. In our testing, we observed up to 3X improvement in DML throughput when innodb_dedicated_server was enabled by default, compared to the configurations where this parameter is disabled.

The innodb_redo_log_capacity value is calculated based on the number of vCPUs. The formula is (nVCPUs/2) GB, with a maximum dynamic default value of 16 GB. On the other hand, innodb_buffer_pool_size is calculated based on the DB instance class memory, as listed in the following table.

Although innodb_dedicated_server automatically calculates the sizes for the InnoDB buffer pool and redo logs, you still have the option to override innodb_redo_log_capacity and innodb_buffer_pool_size with custom values in the parameter groups. If you do choose custom values, we recommended that you keep innodb_dedicated_server enabled. This way, if the parameters are reset in the parameter group, they will revert to the values calculated by innodb_dedicated_server, instead of defaulting to potentially unsuitable values of 100 MB for innodb_redo_log_capacity and 128 MB for innodb_buffer_pool_size. For additional details, refer to Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server.

Removed Memcached plugin from option groups

The MySQL community deprecated Memcached plugin with version 8.0.22, and has removed it entirely starting with MySQL 8.4. Consequently, RDS for MySQL option groups no longer support MEMCACHED as an option. As an alternative, we recommend using a caching service external to Amazon RDS for MySQL such as Amazon ElastiCache.

Switched binlog_format default to ROW

The default value for the binlog_format variable has been changed to ROW, which provides more accurate and efficient data replication, and is a pre-requisite for setting up Amazon RDS zero-ETL integrations with Amazon Redshift.

With binlog_format set to ROW, when replaying binary logs on a read replica, MySQL must scan the entire table for each row update, if the table lacks a primary key. This can lead to unexpected latency, especially during multi-row update operations. To mitigate this issue, we strongly recommend reviewing your database schema and making sure all tables have appropriate primary keys defined.

Note, for RDS Multi-AZ deployments with two readable standbys, binlog_format has been removed from the cluster parameter groups. Instead, we now use the default ‘binlog_format‘ setting, which is ‘ROW’ .

Changed InnoDB_purge_threads default to scale with number of vCPUs

Purge is a housekeeping operation in a MySQL database. The InnoDB storage engine relies on it to clean up undo logs and delete-marked table records that are no longer needed for multiversion concurrency control (MVCC) or rollback operations. On Amazon RDS for MySQL 8.4, InnoDB purge threads now scale dynamically with the underlying instance’s vCPU count, to prevent accumulation of history list length. The innodb_purge_threads parameter defaults to LEAST({DBInstanceVCPU/2}, 4) on RDS for MySQL 8.4, instead of being set to a fixed value of 1, as in prior versions. To learn more about fine-tuning InnoDB purge threads for optimal performance with your specific application workloads, review the comprehensive blog post dedicated to this topic.

Conclusion

In this post, we discussed MySQL community’s new versioning model, MySQL 8.4 LTS enhancements, and changes introduced with Amazon RDS for MySQL 8.4. We also outlined how to upgrade to Amazon RDS for MySQL 8.4 with minimal downtime, using Amazon RDS Blue/Green deployments. We encourage you to try Amazon RDS for MySQL 8.4 LTS release’s new features, and performance improvements with your workload.

About the authors

Mershad Irani is a Database Engineer at Amazon Web Services. He is a Subject Matter Expert on Amazon RDS for MySQL and Amazon Aurora MySQL. He collaborates closely with the Amazon RDS product teams, contributing his expertise to the development and enhancement of innovative products by identifying areas for improvement and helping to shape the future direction of the Amazon RDS ecosystem.

Vijay Karumajji is a Principal Database Solutions Architect with Amazon Web Services. He works with our customers to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using AWS.

Source: Read More