MySQL 8.4 LTS version

Estimated read time 7 min read

Preface

Oracle released MySQL 8.4 version (LTS) a few days ago, which is the first long-term support version of the innovative version.

About MySQL version release plan

New versions released by the official Oracle MySQL development team will transition to the new MySQL version model. MySQL 8.1.0 is the first innovative version, and 8.0.34+ will only have bug fixes until the 8.0 end of life (EOL, scheduled for April 2026).

The MySQL 8.x version will eventually become LTS, which will provide sufficient time for users to migrate from 8.0.x to the 8.x LTS version.

The impact on us can be summarized as follows:

  1. If users want the latest features, improvements, and bug fixes for MySQL database, please use innovative versions (such as 8.1.x, 8.2.x, 8.3.x, etc.).
  2. If the user MySQL only needs bug fixes, please use the 8.0.x version (eg 8.0.35, 8.0.36, 8.0.37, etc.).
  3. If the user is using the RDS MySQL database service of a domestic cloud vendor, please refer to the relevant announcements of each vendor for specific version support plans.

Let’s get back to business, let’s talk about the technical features of 8.4.

8.4 Function changes

1 MySQL password authentication

Authentication plug-in: By default, mysql_native_passwordthe plug-in is disabled. If you need to enable the plug-in, you need to specify it at startup --mysql-native-password=ON or configure it in the my.cnf file mysql_native_password=ON .

2 Changes in default parameters related to InnoDB

Let’s focus on 3 points.

innodb_io_capacity: The default value has been changed to 10000, which was previously 200. At present, most disks are SSD, and the value of 200 is indeed a bit low.

innodb_buffer_pool_instances: Adjust the value algorithm, take min( innodb_buffer_pool_sizeinnodb_buffer_pool_chunk_size*1/2, logical CPU * 1/4)

innodb_adaptive_hash_index: The default is changed to OFF. It was ON before. AHI is one of the unstable factors of DDL. This time it is finally turned off by default. Turning it on won’t bring much performance improvement.

Other readers can see the official documentation or the screenshots below:

3 Copy related

The default value of the master-slave replication parameter   SOURCE_RETRY_COUNT  is changed to 10, that is, when the master-slave replication relationship is abnormal, the slave database attempts to connect to the master database 10 times. The time of each interval SOURCE_CONNECT_RETRYis determined by , the default is 60s.

In a MySQL master-slave replication configuration, SOURCE_RETRY_COUNTand SOURCE_CONNECT_RETRYdetermines the behavior of the slave server when it attempts to reconnect to the master server during replication.

SOURCE_RETRY_COUNT: The number of retries the slave server attempts to re-access the master database when it encounters an error during the replication process (such as network problems or access conflicts). By default, if the slave server encounters an error while reading the binary log file, it stops replication and waits for the next poll operation (usually a few seconds to tens of seconds, depending on the setting of the REPLICATE_RECONNECT parameter). If set SOURCE_RETRY_COUNT, the slave server will try to reread the location where the error occurred within this number of times, rather than stopping replication immediately.

SOURCE_CONNECT_RETRY: The interval between attempts by the slave server to reconnect to the master server. When the slave server is disconnected from the master server for some reason (such as the master server restarts or the network is interrupted), the slave server will wait for SOURCE_CONNECT_RETRYthe specified number of seconds before trying to re-establish the connection. If the connection fails, the slave server will continue to SOURCE_CONNECT_RETRYtry to reconnect every seconds until successful.

I didn’t pay attention to changing the parameters before, so I’ll make up for it this time.

4 master/slave keyword changes

A few years ago, for the sake of political correctness, the official decision was made to remove the keywords   master and slave . Now it has finally been removed in version 8.4. Users need to make the following replacements:

master  ---> source
slave   ---> replica

Here, the operation and maintenance development students have to work hard to ensure version compatibility. All status parameters and command statements involving these two words must be modified. However, it will be many years before version 8.4 will be applied to the production version. Friends who are accustomed to the command line do not need to rush to modify it.

5   SQL_AFTER_GTIDSCompatible with MTA.

Previously, when replication enabled the parallel replication MTA feature and the user attempted SQL_AFTER_GTIDS, “START REPLICA” would trigger a warning ER_MTS_FEATURE_IS_NOT_SUPPORTEDand the replication would be switched to single-threaded mode. In version 8.4, the “SQL_AFTER_GTIDS” option of the “START REPLICA” statement is compatible with MTA.

6 groups of copy parameters related

group_replication_consistencyThe default value of the system variable EVENTUAL  was changed from BEFORE_ON_PRIMARY_FAILOVER.

group_replication_exit_state_actionThe default value of the system variable is changed to OFFLINE_MODE.

group_replication_set_as_primary()  The function will wait for the ongoing DDL to end while selecting a new primary member.

7 Clone plugin

The cloning function further relaxes the version requirements and allows cloning between different minor versions of the same major version. In other words, only the major and minor version numbers must match, while the previous dot version numbers must also match.

For example, the cloning feature now allows cloning 8.4.0 to 8.4.14 and 8.0.51 to 8.0.37. With 8.0, the previous restrictions still apply to versions prior to 8.0.37, so cloning 8.0.36 etc. to 8.0.42 or vice versa is not allowed. (WL#15989)

8 Histogram automatic update function

MySQL 8.4.0 supports automatic updating of histograms. When enabling this feature for a given histogram, you can enable this feature by including the AUTO UPDATE option for the ANALYZE TABLE statement. ANALYZE TABLE  It will be updated every time it is run against the specified table .

You can use the MANUAL UPDATE option to disable automatic updating of the histogram for a table. If neither option is specified, the default is MANUAL UPDATE (no automatic updates).

9 Thread pool plugin connection information

  1. Add a connection pool-related table   tp_connectionsto record the metadata of each connection pool.
  2. tp_thread_statetp_thread_group_stateadding more fine-grained fields to display the status of the connection pool.

10 abandonedexpire_logs_days

Deprecated parameter expire_logs_daysand use binlog_expire_logs_secondsinstead.

11 Obsolete copy options and variables

Some MySQL replication-related options and variables were deprecated in earlier versions of MySQL and have been removed from MySQL 8.4. Attempting to use these options and variables now will cause the server to throw a syntax error. These options and variables are listed below:

--slave-rows-search-algorithms: When applying update or delete operations to find table rows, the algorithm used by the replication application is now always HASH_SCAN, INDEX_SCAN. Users can no longer configure this.

--log_bin_use_v1_events: This allows source servers running MySQL 5.7 and newer to replicate to earlier versions of MySQL that are no longer supported or maintained.

--relay-log-info-file--relay-log-info-repository--master-info-file--master-info-repository: The use of files as metadata for the sql application thread has been replaced by crash-safe tables.

You May Also Like

More From Author

+ There are no comments

Add yours