What's New In MySQL 8.4 LTS Mydbops MyWebinar Edition 36

What's New in MySQL 8.4? Mydbops MyWebinar Edition 36 - Vinoth Kanna, Founding Partner, Mydbops

Join us as we explore the latest advancements in MySQL 8.4 and discover how these updates can enhance your database management.

Key highlights:
* GTID Tags for improved replication
* Automatic histogram updates for query optimization
* Clone Plugin for faster replication
* Backward-compatible backups with mysqldump

Mydbops Managed Services specializes in taking the pain out of database management while optimizing performance. Since 2015, we have been providing top-notch support and assistance for the top three open-source databases: MySQL, MongoDB, and PostgreSQL.

Our team offers a wide range of services, including assistance, support, consulting, 24/7 operations, and expertise in all relevant technologies. We help organizations improve their database's performance, scalability, efficiency, and availability.

Contact us: [email protected]

1. What's New In MySQL 8.4 LTS ?
Long Term Support, Features Explained


Vinoth Kanna RS
Founding Partner, Mydbops LLP
Mydbops MyWebinar 36

2. About Me
Vinoth Kanna R S
❏Making Database Management Simple at Scale
❏Performance Tuning Ninja
❏AWS RDS Cloud Expert
❏Tech Speaker on Open Source Events
❏Databases for living (12 years)

3. Mydbops Services
Focus on MySQL, MongoDB, PostgreSQL, TiDB, Cassandra
Consulting
Services
Consulting
Services
Managed
Services
24*7
DBA Team
Targeted
Engagement

4. ❏MySQL 8.4 LTS

❏Versioning Model

❏What’s New ?

❏Cloud Support

❏Key Takeaways
Agenda

5. MySQL 8.4 LTS

6. ❏General Availability, April 2024
❏Supported for 8 Years, April 2032
❏New LTS release will happen every 2 years
❏Applies to all the products in the MySQL Ecosystem
❏MySQL Server, MySQL Shell
❏MySQL Router, MySQL Kubernetes Operator
❏NDB Cluster
❏Next LTS will be 9.7.0, Expected on April 2026
MySQL 8.4 LTS (Long Term Support)

7. ❏Innovation Releases
❏Quarterly
❏9.0.0, 9.1.0, 9.2.0 …
❏Breaking changes
❏Feature addition
❏Feature removal
❏No patch versions
❏Supported till next quarterly release
Versioning Model

8. ❏LTS Release Promises
❏Stability
❏Quarterly releases
❏8.4.0, 8.4.1, 8.4.2 …
❏Bundles the features from the
innovation releases of that series
❏No feature addition or removal
after the initial release
❏Security patches and Bug fixes for 8 years since GA
Versioning Model

9. ❏Past, Present and Future
Versioning Model
MySQL LTS
Version
Current
Version
Initial GA
Release
End Of lifeComment
8.0 8.0.39 April 2018April 2026Bug fixes only 8.0.35
8.4 8.4.2 April 2024April 2032Bug fixes and security patches
9.7 April 2026
(Expected)

10. What’s New In MySQL 8.4 LTS ?

11. ❏In place downgrades are now possible
Between any patch version inside the same LTS series (Between 8.4.X to 8.4.X)
1. Inplace Upgrades and Downgrades
From To Type In place Support
8.4.0 LTS 8.4.2 LTS Upgrade Yes
8.4.2 LTS 8.4.0 LTS Downgrade Yes
❏Added on 8.0 LTS series from 8.0.35+

12. ❏In Place Upgrade: From 8.4.0 To 8.4.2
1. Inplace Upgrades and Downgrades

13. ❏In Place Downgrade: From 8.4.2 To 8.4.0
1. Inplace Upgrades and Downgrades

14. ❏Most used feature in MySQL 8 to add replica or secondary node to a cluster
❏On older versions, the clone plugin will work only if the (Major, Major and Patch Versions)
between source and destination match.
2. Clone Plugin
Source Destination Clone Operation
8.0.32 8.0.32 Pass
8.0.34 8.0.35 Fail

15. ❏Now clone plugin will work if major version and minor version
matches regardless of point releases in the same series.



❏Version running 8.4.0 can be cloned from 8.4.2 and vice-versa
❏Added on 8.0 LTS series from 8.0.37+
2. Clone Plugin
Source Destination Clone Operation
8.4.0 8.4.2 Pass
8.4.0 9.0.0 Fail

16. ❏Members in a group can run any patch release of
the given LTS series
❏For example, a member of a group running
MySQL 8.4.2 can be downgraded to MySQL 8.4.0
❏This enables seamless rolling upgrades and
downgrades
❏Added on 8.0 LTS series from 8.0.35+
3. Group Replication Compatibility

17. ❏Introduced in innovation release 8.2.0
❏Incorporated in MySQL Router 8.4 LTS
❏MySQL Router 8.4 officially supports MySQL
server versions 8.0, 8.4
❏Traffic can be distributed without app changes
❏Connection Pool + R/W Split for traffic scaleout
4. MySQL Router - Transparent R/W Split
https://www.mydbops.com/blog/mysql-transparent-read-write-splitting-with-mysql-router-82/

18. ❏Histogram refreshed on execution of Analyze
table command and persistent stats auto recalc
❏Keeps histogram updated periodically
❏Eases histogram management
5. Auto Histogram Update For Tables
To Know About Histogram: https://www.mydbops.com/blog/histogram-in-mysql-80/

19. 5. Auto Histogram Update For Tables
❏Syntax:
ANALYZE TABLE tbl_name UPDATE HISTOGRAM
ON col_name [, col_name] …
[WITH N BUCKETS] AUTO UPDATE

20. 5. Auto Histogram Update For Tables
❏Syntax:
ANALYZE TABLE tbl_name UPDATE HISTOGRAM
ON col_name [, col_name] …
[WITH N BUCKETS] AUTO UPDATE

21. ❏Introduces ability to add a tag (8-33 characters) in the GTID and
branch out a set of transactions
❏Differentiate writes from varying sources
❏Allows us ability to skip the transactions tagged on the
replica’s without missing the rest of the writes
❏Provides procedural way to handle without turning off binlog
❏Add insights of actions performed through tags
6. Tagged GTID

22. ❏Test Case: Maintain 1 month of data on the table ‘prod.trace_log’ on the InnoDB Cluster
and retain the data infinitely over the Read replica.
❏To achieve this we need to skip only the deletes on the table.
❏No easy way to achieve traditionally
❏With tagged GTID, We will tag the deletes as ‘delete_trlogs’ and skip those tags in
replica
❏Since the GTID is idempotent in nature, It will not reapply the events
❏Using this method any transaction can be isolated and ran like filtered transaction
replication
6. Tagged GTID

23. ❏Step1: Validate records in Primary server
6. Tagged GTID

24. ❏Step2: Perform the following on the Replica
❏set global gtid_purged =
‘<group_name>: <tag_name>: <1-max_value>’
If innodb cluster, Use group name
If regular replication, Use source_uuid
❏Eg: tag_name=’delete_trlogs’, group_name=
‘00024603-1111-1111-1111-111111111111’
6. Tagged GTID

25. ❏Step3: Perform the deletes on the source
❏set gtid_next =
‘automatic:<tag_name>’
❏Perform delete and validate
6. Tagged GTID

26. ❏New variable output-as-version is introduced
❏Allows to dump in the format that is needed in the destination,
useful for restoration of backup in lower versions.
❏Avoid restoration failures during restores.
7. mysqldump
Possible Options Behaviour
SERVER Compatible with current version
BEFORE_8_2_0 Compatible with versions >= 8.0.23 and <= 8.1.0
BEFORE_8_0_23 Compatible with versions < 8.0.23

27. 8. Dedicated Server
❏When started with --dedicated-server option, Innodb performs auto configuration for the
following variables.
❏Innodb_buffer_pool_size

❏innodb_redo_log_capacity
VCPU’s/2 GB, Max Limit: 16 GB
❏Auto configured variable in 8.0+,
innodb_flush_method is removed in the current release

28. 9. Better Defaults

29. 9. Better Defaults

30. ❏group_replication_consistency = ‘BEFORE_ON_PRIMARY_FAILOVER’, Previously EVENTUAL
If the Primary crashes or fails for any reason, Ensures backlog is completely applied before the
promotion of new primary and avoid stale reads.
❏group_replication_exit_state_action = ‘OFFLINE’, Previously READ_ONLY
❏Useful feature if the GR on any of the node fails, Application connections are forcefully
disconnected, This will avoid stale reads if any application is directly connected to secondaries.
❏If connected via Load balancers like ProxySQL, Router, Load balancer itself will stop routing on
failures, This mechanism in MySQL acts like a secondary layer of protection.
9. Better Defaults

31. 10. Application Impacting Changes
❏Reserved keywords added:
manual, parallel, qualify, tablesample
❏Reserved keywords removed:
master_bind, master_ssl_verify_server_cert
❏Native password authentication disabled by default, Recommend to switch to caching_sha2
Backward Compatibility:
mysql_native_password = ON

32. 10. Application Impacting Changes
❏MySQL 8.4, mandates the referring column to be Unique key for Foreign key reference
Backward Compatibility:
restrict_fk_on_non_standard_key = OFF
❏Client comments preserved
In MySQL 8.0, the stripping of comments from the mysql client was the default behavior.
Starting MySQL 8.4, the default was changed to preserve such comments.
It is best practices for database developers to add comments for UDF’s and SP’s
https://www.skeema.io/blog/2024/05/14/mysql84-surprises/

33. ❏Changes in replication management
Depreciated keywords, Master, Slave are removed and replaced by Source, Replica
❏Listed below are the removed SQL statements, Which is related to Source server
11. Monitoring and Automation Impacting Changes

34. ❏Listed below are the removed SQL statements, relating to Replicas
11. Monitoring and Automation Impacting Changes
❏mysqlpump is removed, Use mysqldump or shell utilities instead

35. Cloud Support

36. ❏Availability with major cloud providers
Cloud Support - OCI
Cloud ProviderSupported version
OCI (Heatwave)8.0.35 LTS +

8.4.0 LTS +

Innovation Releases 9.0.0 +

37. ❏Availability with major cloud providers
Cloud Support - AWS
Cloud ProviderSupported version 8.4 LTS Availability
AWS (RDS) 5.7, 8.0 8.4 is available in
Database preview
❏ Amazon RDS Database Preview Environment, allowing you to evaluate the latest Long-Term
Support Release on Amazon RDS for MySQL.
❏Instances can be retained for a maximum period of 60 days and are automatically deleted
after the retention period.

38. ❏Availability with major cloud providers
Cloud Support - Azure
Cloud Provider Supported versions8.4 LTS Availability
Azure (Flexible Server)5.7, 8.0 Yet to be announced

39. ❏Availability with major cloud providers
Cloud Support - GCP
Cloud ProviderSupported version8.4 LTS Availability
GCP (Cloud SQL)5.6, 5.7, 8.0 Yet to be announced

40. Key Takeaways

41. ❏MySQL 8.4 LTS features
❏Fully featured, Natural version of 8.0 series
❏Flexibility in scaling
❏Improved stability and predictability in the releases
❏Improved consistency and fault tolerance in InnoDB cluster
❏Better default parameters tuning for performance
❏Critical issues led to recall of the following versions, no longer available to download
MySQL 8.0.38, MySQL 8.4.1, and MySQL 9.0.0, Released on July 2024
The bug fixes MySQL 8.0.39, MySQL 8.4.2, MySQL 9.0.1 on the same month
Key Takeaways

42. Consulting
Services
Connect with us !
Reach us at : [email protected]

43. Thank You