MySQL Innovation and Long-Term Support (LTS) versions MySQL 8.4 Series Overview Olivier Dasini Cloud Solutions Architect @ Oracle MySQL [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin : www.linkedin.com/in/olivier-dasini Slides : https://speakerdeck.com/freshdaz 13/05/2024 

Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 2 Me, Myself & I 2 Olivier DASINI  MySQL Geek  Enjoying databases for 20+ years  Addicted to MySQL for 15+ years  MySQL Writer, Blogger and Speaker  Also former : DBA, Consultant, Architect, Trainer, ...  Cloud Solutions Architect at Oracle MySQL  Stay up to date!  Blog: www.dasini.net/blog/en  Linkedin: www.linkedin.com/in/olivier-dasini/  Slides: https://speakerdeck.com/freshdaz 

New MySQL Release Model C O M M U N I T Y & E N T E R P R I S E E D I T I O N Copyright © 2024 Oracle and/or its affiliates 3 

Copyright © 2024 Oracle and/or its affiliates 5 

MySQL Innovation & Long-Term Support (LTS) versions Copyright © 2024 Oracle and/or its affiliates 6 The patch releases of MySQL 5.7 and previous releases were focused on bugfix and security patches That changed in MySQL 8.0 with the continuous delivery model in that patch releases also contained new features We are now transitioning to a versioning model where you can choose between Innovation and Long-Term Support (LTS) releases. Both Innovation and LTS are production-grade quality 

MySQL Innovation & Long-Term Support (LTS) versions Copyright © 2024 Oracle and/or its affiliates 7 MySQL Innovation releases MySQL LTS releases ● If you desire access to the latest features and improvements and enjoy staying on top of the latest technologies ● These releases are ideal for developers and DBAs working in fast- paced development environments with high levels of automated tests and modern continuous integration techniques for faster upgrade cycles ● If your environment requires stable behavior ● These releases contain only necessary fixes, so they reduce the risks associated with changes in the database software's behavior 

Flexibility & Support • With the new MySQL versioning model, you have the flexibility to select the option that suits your specific application and environment needs. • Our goal is to ensure that you have an exceptional user experience with both Innovation and LTS releases. Copyright © 2024 Oracle and/or its affiliates 8 

Support Lifecycle • LTS Releases will follow the Oracle Lifetime Support Policy, which includes 5 years of premier and 3 years of extended support. • Innovation releases will be supported until the next innovation release. • Should plan to update your MySQL databases quarterly accordingly to Oracle Critical Patch Updates (CPU) calendar Copyright © 2024 Oracle and/or its affiliates 9 

Transitioning into Innovation and LTS releases • If you want the latest features, improvements, and all bug fixes for your MySQL databases, use the Innovation release track (eg., 8.1.x, 8.2.x, 8.3.x, etc.) • If you need only bug fixes for your MySQL database, use 8.4.x LTS releases (eg., 8.4.0, 8.4.1, etc.) • Now that 8.4 LTS is released, you can plan, test, and migrate from the 8.0.x bug fix track to the LTS release track (ex., from 8.0.37 to 8.4.1). Copyright © 2024 Oracle and/or its affiliates 10 

Copyright © 2024, Oracle and/or its affiliates | Confidential: Internal 11 LTS (Long Term Support) Innovation GA Release Support Period (Premier + Extended) 8 Years ”Short Term” Continuity (No Breaking Changes) New Features Bug Fixes Upgrade Security Fixes Upgrade 8.4 LTS • GA: April 2024 • Supported: April 2032 [8 years] • 8.4 LTS is 8.0, with support until 2032 GA Releases: LTS vs Innovation MySQL 8.4 Series Overview 

MySQL Portfolio & Support Lifecycle • MySQL Server, MySQL Shell, MySQL Router, MySQL Operator for Kubernetes, with InnoDB { ReplicaSet | Cluster | ClusterSet }, MySQL NDB Cluster will have Innovation and LTS releases • MySQL Connectors will use the latest version, but will be considered Generally Available and will remain compatible with all supported MySQL Server versions Copyright © 2024 Oracle and/or its affiliates 12 

Deprecation & Removal of Features • Note that there are no removals within an LTS release. Features can be removed (& added) only in the first LTS release (e.g., 8.4.0 LTS) but not later. Copyright © 2024 Oracle and/or its affiliates 15 

Upgrading & Downgrading within an LTS release • Within an LTS release, functionality remains the same and data format does not change. Therefore: – In-place upgrades and downgrades will be possible. This is an improvement compared to MySQL 8.0.x releases where downgrades were not possible and upgrade by skipping versions were not recommended – InnoDB CLONE and MEB will be supported within an LTS release, both for upgrading and downgrading Copyright © 2024 Oracle and/or its affiliates 17 

Upgrade Support Matrix From - To In-place Clone Asynch replication Dump/Load LTS 8.4.11-> LTS 8.4.20 ☑︎ ☑ ☑ ☑ LTS 8.4 -> LTS 9.7 ☑ ☒ ☑ ☑ LTS 8.4 -> LTS 10.7 ☒ ☒ ☒ ☒ Innovation 8.1 -> Innovation 8.2 ☑ ☒ ☑ ☑ Innovation 8.1 -> Innovation 8.3 ☑ ☒ ☑ ☑ Innovation 9.1 -> LTS 9.7 ☑ ☒ ☑ ☑ 8.0.x -> LTS 8.4 ☑ ☒ ☑ ☑ https://dev.mysql.com/doc/refman/8.4/en/upgrade-paths.html 

Downgrade Support Matrix From - To In-place Clone Asynch replication Dump/Load LTS 8.4.20-> LTS 8.4.11 ☑︎ ☑ ☑ ☑ LTS 9.7 -> LTS 8.4 ☒ ☒ ☑* ☑* LTS 9.7 -> Innovation 9.6 ☒ ☒ ☑* ☑* LTS 9.7 -> Innovation 9.5 ☒ ☒ ☑* ☑* * Indicates support is limited. For rollback purposes only. https://dev.mysql.com/doc/refman/8.4/en/downgrading.html 

References • Oracle Critical Patch Updates (CPU) calendar • Oracle Lifetime Support Policy Copyright © 2024 Oracle and/or its affiliates 23 

MySQL C O M M U N I T Y & E N T E R P R I S E E D I T I O N Copyright © 2024 Oracle and/or its affiliates 24 

MySQL 8.4 – New Productiom ready defaults InnoDB (selection) 8.0.x off on 4 fsync 200 16MB off 1GB on 8.4.x off off 128 O_DIRECT (when supported) 10000 64MB on 3% of Mem (1GB < x < 4GB) off innodb_dedicated_server innodb_adaptive_hash_index innodb_doublewrite_pages innodb_flush_method innodb_io_capacity innodb_log_buffer_size innodb_numa_interleave temptable_max_ram temptable_use_mmap Copyright © 2024 Oracle and/or its affiliates 25 More: https://lefred.be/content/mysql-8-4-lts-new-production-ready-defaults-for-innodb/ Variable 

MySQL 8.4 – New Productiom ready defaults Other (selection) 8.0.x js Eventual As plugin 8.4.x sql Before_On_Primary_Failover during mysqld start --mysql-native-password mysqlsh group_replication_consistency mysql_native_password Copyright © 2024 Oracle and/or its affiliates 26 More: https://lefred.be/content/mysql-8-4-lts-new-production-ready-defaults-for-innodb/ Variable 

MySQL InnoDB Read Replicas Improved scalability and redundancy • Read Replicas (starting with 8.1.0) o read-only copies of a Cluster member • Scalability o Asynchronous replication enable to scale out workload, offload read requests from your Cluster to one, or more, dedicated read-only instances • Additional redundancy o Read replicas can be promoted to cluster members • Fully managed with MySQL Shell 27 Copyright © 2024, Oracle and/or its affiliates 

Starting with 8.2, MySQL Router has a new port (6450) that offers the split between read and writes o This configuration enables you to direct all read traffic to read-only instances, and all write traffic to read-write instances o Each client session can communicate with one read_write and one read_only destination It is also possible to manually, or programmatically, specify the type of query using ROUTER SET or query_attributes Each client session can communicate with one read_write and one read_only destination MySQL Router transparent Read/Write splitting 28 application MySQL Router primary secondary secondary R/W RO RO Copyright © 2024, Oracle and/or its affiliates https://blogs.oracle.com/mysql/post/mysql-82-transparent-readwrite-splitting SELECT INSERT UPDATE DELETE 

InnoDB Cluster/Group Replication: new defaults • New default for consistency after a switchover (fail or administrative), that let clients always see the latest value on the primary o group_replication_consistency=BEFORE_ON_PRIMARY_FAILOVER – previously EVENTUAL – clients must be able to handle the delay in the event that a backlog is being applied • New default for network partition handling, to disconnect clients without CONNECTION_ADMIN privilege and let DBA works on the isolated nodes without worries for applications o group_replication_exit_state_action=OFFLINE_MODE – previously READ_ONLY Copyright © 2024 Oracle and/or its affiliates 29 

Extended GTID format • It's now possible to assign a unique name to the GTIDs of a specific group of transactions o e.g., identify transactions containing data operations from administrative operations o e.g., skip transactions from InnoDB Cluster to an archive replica server without use sql_log_bin=off • The new GTID format is UUID::NUMBER, in which is an arbitrary string up to 8 characters long o This tag persists for all transactions originating in the current session • The original UUID:NUMBER format for GTIDs continues to be supported unchanged, as implemented in previous versions of MySQL o Changes to existing replication setups using GTIDs are not required Copyright © 2024 Oracle and/or its affiliates 30 

Example of data purge from InnoDB Cluster • On the replica, configure gtid_purged to identify transactions to skip (assuming max 9999 transactions to skip) set global gtid_purged= "+group_uuid:archiving:1-9999"; • On cluster, start tagging session transactions set gtid_next="automatic:archiving"; • Purge data • Reset the GTID tagging set gtid_next="automatic" Copyright © 2024 Oracle and/or its affiliates 31 https://lefred.be/content/mysql-8-3-purging-data-from-your-innodb-cluster/ 

MySQL T E L E M E T R Y T R A C I N G Copyright © 2024 Oracle and/or its affiliates 32 

MySQL Telemetry Tracing (OTel project support) Goal: • Capturing raw measurements efficiently and simultaneously from MySQL & MySQL HeatWave • Based on CNCF (Cloud Native Compute Foundation), • Example: OpenTelemetry Metrics Standards and OCI APM (Application Performance Monitoring) Features: • Enables full stack monitoring • Customer sets which metrics to emit (or not) from the server including MySQL Connectors • Simple integration to Metric Products for Centralized Analysis • Very simple to configure • Enterprise Edition only Copyright © 2024 Oracle and/or its affiliates 33 * https://blogs.oracle.com/mysql/post/mysql-telemetry-tracing-with-oci-apm 

Example: MySQL Telemetry Tracing and OCI APM Browser JS framework Webserver MySQL Connector MySQL Database https://docs.oracle.com/en-us/iaas/application-performance-monitoring/doc/application-performance-monitoring-metrics.html 

MySQL M O N I T O R I N G Copyright © 2024 Oracle and/or its affiliates 35 

MySQL Enterprise Monitor EOL January 1st, 2025 Copyright © 2024, Oracle and/or its affiliates - Internal only 36 https://dev.mysql.com/doc/mysql-monitor/8.0/en/ https://support.oracle.com/epmos/faces/DocumentDisplay?id=3008050.1 

MySQL Enterprise Manager Plugin for MySQL • Performance monitoring • Availability monitoring • Replication Monitoring • InnoDB Monitoring • Firewall Monitoring • Audit Monitoring • Reports • Remote Monitoring • Configuration monitoring • Allowing for custom threshold-based incident reports Copyright © 2024 Oracle and/or its affiliates 37 

Query Analyzer • Point-in-time snapshot of top 100 queries and statements on the monitored database • Monitor slow running queries that impact application performance • See details of query such as number of executions, number of full table scans, and average latency Copyright © 2024, Oracle and/or its affiliates 38 

Safeguard MySQL • Evaluate compliance of MySQL against configuration and security best practices o MySQL Administration Standard Rules o MySQL Performance Standard Rules o MySQL Replication Standard Rules o MySQL Schema Standard Rules o MySQL Security Standard Rules Copyright © 2024, Oracle and/or its affiliates 39 

Monitoring MySQL HeatWave in the Oracle Cloud • Fleet management o Provides an overview of your MySQL estate o Details any alarms and their criticality o Easy to drill down into an instance’s performance summary • Performance summary o More than 40 metrics in time correlated graph o View configuration variables in use • HeatWave Cluster monitoring • Performance Hub o Monitor and troubleshoot query performance o Identify expensive queries, filter by o latency, errors, index usage, execution timestamp and counters, locks, temporary tables, sorts, scans, etc. Copyright © 2024, Oracle and/or its affiliates 40 

Coming soon: operational insights • Capacity Planning o Forecast demand for changing workloads: • max and average demand forecasts; machine learning seasonality models; automatically predict near term issues o Quickly isolate the largest, most utilized and fastest growing databases o Identify under-utilized & over-allocated footprint for right-sizing and optimize resource allocation • Workload Analytics o In-context SQL performance metrics dashboard for a particular query on a single database o SQL ‘metadata warehouse’ o Insights: • Plan change? Improved or degraded? • Gradual SQL performance degradation? • How many new SQL since last week? • Root cause identification by comparison and correlation Copyright © 2024, Oracle and/or its affiliates 41 

MySQL HeatWave C O R E Copyright © 2024 Oracle and/or its affiliates 42 

MySQL HeatWave – News • Support for • MySQL 8.0 (today 8.0.37 – down to 8.0.33) • MySQL 8.4 (today 8.4.0) • MySQL 9.x (soon) • Support for ECPU (new) • 1 CPU Thread = 1 ECPU “Elastic CPU“ • Only in multiples of 2 ECPUs (up to 256 ECPUs / 1 TB and 130 TB [Disk]) • No changes in pricing (OCPUs still available but not recommended) • MySQL HeatWave Cluster (aka. Analytics) available for all ECPU shapes  MySQL.2 + 50GB Storage + HeatWave.32GB = $56,45 + $16,37 = $ 72,82 Copyright © 2024 Oracle and/or its affiliates 43 

MySQL HeatWave – more News • Support for “Bring your own Certificate“ o Use your own SSL certificates for encrypted MySQL Connections o Use a X509 Certificate for authenticating MySQL Users o Easy use OCI Certificates Services • Support for “Rolling Restart“ during “Change Shape and Change Configuration“ for MySQL HA o Rolling ‘MySQL Updates (old)’, ’Change Shape’ and ‘Change Configuration (think my.cnf)’ o Changes are applied to the two MySQL Secondaries first, followed by a “Switch over” of the Target IP o Changing the database usually now causes outages of seconds only! Copyright © 2024 Oracle and/or its affiliates 44 https://docs.oracle.com/en-us/iaas/releasenotes/services/mysql-database/ 

MySQL HeatWave C L U S T E R & L A K E H O U S E Copyright © 2024 Oracle and/or its affiliates 45 

MySQL HeatWave Cluster – News • New default: HeatWave Clusters are created by default together with MySQL DB System • Advanced Monitoring of MySQL HeatWave Clusters and Lakehouse (‘HeatWave Insights‘) • ”Header Extraction” for simplified importing of CSV files • Support for JSON columns and DocumentStore in MySQL HeatWave Cluster o No change in application needed, no indexes required! o Support for MySQL X-Protocol o Query execution times are now drastically reduced (20+x faster) o JSON support for Lakehouse is LA – Interested  Ping us! Copyright © 2024 Oracle and/or its affiliates 46 

Summary • MySQL 8.4 LTS is the natural LTS version of 8.0 • Two choices: • Innovation Release • Long-Term-Support Release • Available as • Community Edition • Enterprise Edition • Managed Service in OCI and AWS (MySQL HeatWave Service) Copyright © 2024 Oracle and/or its affiliates 47 

Follow us on Social Media “Data is the Oxygen of Business” 49 Copyright © 2024, Oracle and/or its affiliates 

Get $300 in credits and try free for 30 days Get started with MySQL HeatWave oracle.com/mysql/free Learn more about MySQL HeatWave oracle.com/mysql Request a guided workshop Ask your account manager 50 Copyright © 2024, Oracle and/or its affiliates 

Merci! Q&R Olivier Dasini Cloud Solutions Architect @ Oracle MySQL [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin : www.linkedin.com/in/olivier-dasini Twitter : @freshdaz 

No content