{"id":2813,"date":"2025-02-10T17:59:17","date_gmt":"2025-02-10T17:59:17","guid":{"rendered":"https:\/\/bynatree.com\/?p=2813"},"modified":"2025-02-10T17:59:17","modified_gmt":"2025-02-10T17:59:17","slug":"new-features-in-mysql-8-4-from-mysql-8-0","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2025\/02\/10\/new-features-in-mysql-8-4-from-mysql-8-0\/","title":{"rendered":"New Features in Mysql 8.4 from Mysql 8.0"},"content":{"rendered":"<blockquote><p><span style=\"font-weight: 400;\"><strong>MySQL 8.0<\/strong>, a reliable base in database management, has been continuously updated with new functionalities and enhancements. This blog explores the performance, security, user-friendly tools, and New features in <strong>MySQL 8.4<\/strong>, providing insights for both experienced database administrators and developers to maximize the capabilities of this transformative database management tool.<\/span><\/p><\/blockquote>\n<p><b>Following are the new features of Mysql 8.4 from Mysql 8.0.<br \/>\n<\/b><b>The default value of an InnoDB system variable has changed: <\/b><span style=\"font-weight: 400;\">The standard value of an InnoDB system variable has changed. MySQL 8.4.0 modified the default settings for a number of server system variables associated with the InnoDB storage engine, as indicated in the table below:<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><b>MySQL native password authentication revisions:<\/b><span style=\"font-weight: 400;\"> MySQL has added a new feature called &#8220;start_server&#8221; which allows users to start a server without the need for the deprecated mysql_native_password authentication plugin.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><b>Hash table optimization for set operations: <\/b><span style=\"font-weight: 400;\">MySQL 8.2 Performance Improvements<\/span><\/p>\n<ul>\n<li><span style=\"font-weight: 400;\"> Introduces new hash table optimization for EXCEPT and INTERSECT statements.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Controlled by the hash_set_operations optimizer switch.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Disables optimization to revert to old temporary table optimization.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Adjusts memory allocation for optimization by changing set_operations_buffer_size server system variable.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Raising buffer size improves execution speeds of some statements using these operations.<\/span><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><b>Information Schema PROCESSLIST Table Usage:<\/b><\/p>\n<p><span style=\"font-weight: 400;\">MySQL Versions and PROCESSLIST Table Usage<\/span><\/p>\n<ul>\n<li><span style=\"font-weight: 400;\"> Information_SCHEMA.PROCESSLIST table deprecated in MySQL 8.0.35 and 8.2.0.<\/span><\/li>\n<li><span style=\"font-weight: 400;\">Two system status variables: Deprecated_use_i_s_processlist_count and Deprecated_use_i_s_processlist_last_timestamp.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Deprecated_use_i_s_processlist_count records query references since server start.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Deprecated_use_i_s_processlist_last_timestamp records latest table consultation.<\/span><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><b>The default value of an InnoDB system variable is changed:<\/b><span style=\"font-weight: 400;\"> MySQL 8.4.0 introduced changes to default values for server system variables related to the InnoDB storage engine, as illustrated in the table.<\/span><\/p>\n<p>&nbsp;<\/p>\n<table class=\" aligncenter\">\n<tbody>\n<tr>\n<td><b>InnoDB System Variable Name<\/b><\/td>\n<td><b>Previous Default Value (MySQL 8.0)<\/b><\/td>\n<td><b>New Default Value (MySQL 8.4)<\/b><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_buffer_pool_in_core_file<\/span><\/td>\n<td><span style=\"font-weight: 400;\">ON<\/span><\/td>\n<td><span style=\"font-weight: 400;\">OFF if MADV_DONTDUMP is supported, otherwise ON<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_change_buffering<\/span><\/td>\n<td><span style=\"font-weight: 400;\">all<\/span><\/td>\n<td><span style=\"font-weight: 400;\">none<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb-dedicated-server<\/span><\/td>\n<td><span style=\"font-weight: 400;\">OFF<\/span><\/td>\n<td><span style=\"font-weight: 400;\">If ON[a], the value of innodb_flush_method is not altered as in MySQL 8.0, however the computation of innodb_redo_log_capacity is switched from memory-based to CPU-based.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_adaptive_hash_index<\/span><\/td>\n<td><span style=\"font-weight: 400;\">ON<\/span><\/td>\n<td><span style=\"font-weight: 400;\">OFF<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_adaptive_hash_index<\/span><\/td>\n<td><span style=\"font-weight: 400;\">ON<\/span><\/td>\n<td><span style=\"font-weight: 400;\">OFF<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_doublewrite_files<\/span><\/td>\n<td><span style=\"font-weight: 400;\">innodb_buffer_pool_instances * 2<\/span><\/td>\n<td><span style=\"font-weight: 400;\">2<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_doublewrite_pages<\/span><\/td>\n<td><span style=\"font-weight: 400;\">innodb_write_io_threads, which meant a default of 4<\/span><\/td>\n<td><span style=\"font-weight: 400;\">128<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_flush_method on Linux<\/span><\/td>\n<td><span style=\"font-weight: 400;\">fsync<\/span><\/td>\n<td><span style=\"font-weight: 400;\">O_DIRECT if supported, otherwise fsync<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">temptable_max_mmap<\/span><\/td>\n<td><span style=\"font-weight: 400;\">1073741824 (1 GiB)<\/span><\/td>\n<td><span style=\"font-weight: 400;\">0, which means OFF<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">temptable_use_mmap[b]<\/span><\/td>\n<td><span style=\"font-weight: 400;\">ON<\/span><\/td>\n<td><span style=\"font-weight: 400;\">OFF<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">temptable_use_mmap[b]<\/span><\/td>\n<td><span style=\"font-weight: 400;\">ON<\/span><\/td>\n<td><span style=\"font-weight: 400;\">OFF<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_buffer_pool_instances<\/span><\/td>\n<td><span style=\"font-weight: 400;\">8 (or 1 if innodb_buffer_pool_size &lt; 1 GiB)<\/span><\/td>\n<td><span style=\"font-weight: 400;\">The calculated value of innodb_buffer_pool_size, based on the input data, is 1 GiB, indicating the minimum value in the range of 1 to 64.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_change_buffering<\/span><\/td>\n<td><span style=\"font-weight: 400;\">all<\/span><\/td>\n<td><span style=\"font-weight: 400;\">none<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_io_capacity<\/span><\/td>\n<td><span style=\"font-weight: 400;\">200<\/span><\/td>\n<td><span style=\"font-weight: 400;\">10000<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_io_capacity_max<\/span><\/td>\n<td><span style=\"font-weight: 400;\">2 * innodb_io_capacity, with a minimum default value of 2000<\/span><\/td>\n<td><span style=\"font-weight: 400;\">2 * innodb_io_capacity<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_log_buffer_size<\/span><\/td>\n<td><span style=\"font-weight: 400;\">16777216 (16 MiB)<\/span><\/td>\n<td><span style=\"font-weight: 400;\">67108864 (64 MiB)<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_numa_interleave<\/span><\/td>\n<td><span style=\"font-weight: 400;\">OFF<\/span><\/td>\n<td><span style=\"font-weight: 400;\">ON<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_page_cleaners<\/span><\/td>\n<td><span style=\"font-weight: 400;\">4<\/span><\/td>\n<td><span style=\"font-weight: 400;\">innodb_buffer_pool_instances<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_parallel_read_threads<\/span><\/td>\n<td><span style=\"font-weight: 400;\">4<\/span><\/td>\n<td><span style=\"font-weight: 400;\">available logical processors \/ 8, with a minimum default value of 4<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_purge_threads<\/span><\/td>\n<td><span style=\"font-weight: 400;\">4<\/span><\/td>\n<td><span style=\"font-weight: 400;\">1 if available logical processors is &lt;= 16, otherwise 4<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_read_io_threads<\/span><\/td>\n<td><span style=\"font-weight: 400;\">4<\/span><\/td>\n<td><span style=\"font-weight: 400;\">available logical processors \/ 2, with a minimum default value of 4<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">innodb_use_fdatasync<\/span><\/td>\n<td><span style=\"font-weight: 400;\">OFF<\/span><\/td>\n<td><span style=\"font-weight: 400;\">ON<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">temptable_max_ram<\/span><\/td>\n<td><span style=\"font-weight: 400;\">1073741824 (1 GiB)<\/span><\/td>\n<td><span style=\"font-weight: 400;\">3% of total memory, with a default value within a range of 1-4 GiB<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">temptable_max_mmap<\/span><\/td>\n<td><span style=\"font-weight: 400;\">1073741824 (1 GiB)<\/span><\/td>\n<td><span style=\"font-weight: 400;\">0, which means OFF<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400;\">temptable_use_mmap[b]<\/span><\/td>\n<td><span style=\"font-weight: 400;\">ON<\/span><\/td>\n<td><span style=\"font-weight: 400;\">OFF<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p><b>MySQL Replication: SOURCE_RETRY_COUNT Change<\/b><span style=\"font-weight: 400;\">: The CHANGE REPLICATION SOURCE TO statement has modified the default value for the SOURCE_RETRY_COUNT option to 10, indicating that the replica waits 60 seconds between reconnection attempts and rejoins at this rate for 10 minutes.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><b>MySQL Replication: Tagged GTIDs:<\/b><span style=\"font-weight: 400;\"> MySQL Replication and Group Replication have improved the structure of global transaction identifiers (GIDs) to identify transaction groups. The updated GTID format is UUID:TAG:NUMBER, with TAG being a string of up to 8 characters. This tag is effective for all transactions initiated in the current session and is applied at commit time for such transactions or Group Replication at certification time. The original UUID:NUMBER format for GTIDs remains supported without changes. A new TRANSACTION_GTID_TAG privilege is introduced, allowing administrators to limit the usage of SET @gtid_next=AUTOMATIC:TAG or UUID:TAG:NUMBER to a specified group of MySQL users or roles.<\/span><\/p>\n<p><b>Replication terminology and compatibility with past versions:\u00a0<\/b><\/p>\n<p><b>MySQL Dump Release Overview<\/b><\/p>\n<ul>\n<li><span style=\"font-weight: 400;\"> Includes the &#8211;output-as-version option for MySQL 8.2 or greater servers.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Allows building a dump compatible with previous MySQL versions.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Query retrieves the server&#8217;s version and applies the latest replication statements and variable names.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Output compatible with MySQL servers running versions 8.0.23 to 8.1.0, inclusive.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Output compatible with MySQL servers operating versions before 8.0.23.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">The MySQL version number used in version-specific comments allows a major version of one or two digits, allowing the overall version to be five or six digits long.\u00a0<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><b>group_replication_set_as_primary() function and DDL statements:<\/b><span style=\"font-weight: 400;\">\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The group_replication_set_as_primary() method waits for active DDL commands, such as ALTER TABLE, to complete before selecting a new primary.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><b>DDL and DCL statement monitoring for group_replication_set_as_primary():<\/b><\/p>\n<p><span style=\"font-weight: 400;\">group_replication_set_as_primary() also waits for the next couple of statements to finish before electing a new primary.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">ALTER DATABASE, ALTER FUNCTION, ALTER INSTANCE, ALTER PROCEDURE, ALTER SERVER, ALTER TABLESPACE, ALTER USER, ALTER VIEW, CREATE DATABASE, CREATE FUNCTION, CREATE PROCEDURE, CREATE ROLE, CREATE SERVER, CREATE SPATIAL REFERENCE SYSTEM, CREATE TABLESPACE, CREATE TRIGGER, CREATE USER, CREATE VIEW, DROP DATABASE, DROP FUNCTION, DROP PROCEDURE, DROP ROLE, DROP SERVER, DROP SPATIAL REFERENCE SYSTEM, DROP TABLESPACE, DROP TRIGGER, DROP USER, DROP VIEW, GRANT, RENAME TABLE, REVOKE etc.<\/span><\/p>\n<p>&nbsp;<\/p>\n<p><b>FLUSH_PRIVILEGES privilege:<\/b><\/p>\n<p><span style=\"font-weight: 400;\">MySQL 8.4.0 Introduces FLUSH PRIVILEGES Privilege<\/span><\/p>\n<ul>\n<li><span style=\"font-weight: 400;\"> Exclusively applicable to FLUSH PRIVILEGES statements.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Supports RELOAD privilege for backward compatibility.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Upgrade checks if users have FLUSH_PRIVILEGES privilege.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Users with RELOAD privilege granted new privilege.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Downgrade to a version without FLUSH_PRIVILEGES privilege prevents execution of FLUSH PRIVILEGES commands.<\/span><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><b>Keyring migration: <\/b><span style=\"font-weight: 400;\">MySQL 8.4.0 Migration\u00a0<\/span><\/p>\n<ul>\n<li><span style=\"font-weight: 400;\"> Supports transition from keyring component to plugin.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Uses &#8211;keyring-migration-from-component server option.<\/span><\/li>\n<li><span style=\"font-weight: 400;\"> Specifies source component and target plugin.<\/span><\/li>\n<\/ul>\n<blockquote><p>MySQL 8.0, a database management system, is continuously improving through continual upgrades, addressing both performance and security concerns. Its user-friendly tools and novel features in versions like <strong>MySQL 8.4<\/strong> ensure that administrators can fully realize its potential. Future releases will explore more aspects, offering even greater advancements in database administration.<\/p>\n<p style=\"text-align: center;\"><strong>Credit:<\/strong> https:\/\/dev.mysql.com\/doc\/refman\/8.4\/en\/mysql-nutshell.html<\/p>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>MySQL 8.0, a reliable base in database management, has been continuously updated with new functionalities and enhancements. This blog explores the performance, security, user-friendly tools, and New features in MySQL 8.4, providing insights for both experienced database administrators and developers to maximize the capabilities of this transformative database management tool. Following are the new features&hellip;<\/p>\n","protected":false},"author":1,"featured_media":2816,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24],"tags":[44,98,127,138,142,155,156,167,170,202,220,238,305,315],"class_list":["post-2813","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-mysql8-4","tag-continuousimprovement","tag-databasemanagement","tag-dba","tag-developers","tag-gtids","tag-hashtableoptimization","tag-innodb","tag-innovation","tag-mysql","tag-mysql8-0","tag-performance","tag-replication","tag-security","category-24","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2813","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2813"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2813\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2816"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2813"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2813"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2813"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}