Getting started with InnoDB Cluster in MySQL 8

Since MySQL 5.7 InnoDB Cluster saw the light. Back then it was not as popular because it was considered too new technology. As with any new product, there were numerous bug reports. With MySQL 8.0 the solution has matured a lot, many of the problems have been addressed and so it has became a full scale high availability solution for MySQL.

Transcript

1. Getting started with InnoDB Cluster in MySQL 8 Matthias Crauwels ConFoo 2022 - Online Thu Feb 24th 2022 © Pythian Services Inc 2021 | Confidential | 1
2. Speaker © Pythian Services Inc 2021 | Confidential | 2 Matthias Crauwels Principal Consultant Pythian - OSDB
3. How the data estate is evolving © Pythian Services Inc 2021 | Confidential | 3 Modern Cloud Data Platforms are the enabler for insights (BI), predictions (ML) and product activation (orchestration) and creation (AppDev) across ALL data sources. OFFENSE Data is the driver of innovation and transformation DEFENSE Data powers the software that drives the business Traditional On Premise Enterprise Apps i.e. Oracle, SAP etc slowly moving to Cloud, dragging data with them. Traditional Data Warehouses are being replaced with modern cloud data platforms. Modern Applications/SaaS start with modern, often cloud-native databases. Operational Excellence Business Transformation
4. Pythian’s Services Across the Data Estate © Pythian Services Inc 2021 | Confidential | 4 Modern Apps Traditional Enterprise Apps Traditional Data Warehouses OFFENSE Data is the driver of innovation and transformation. Cloud is the key enabler DEFENSE Data powers the software that drives the business Consulting on Data and cloud strategy, architecture, models and security Cloud Data Platforms Migrate traditional data warehouses to native cloud data warehouses Integrate data from data warehouses into cloud data platforms Design, Build, Manage and Optimize modern data platforms at scale in multi/hybrid clouds Deploy new generation analytics, BI, ML to monetize data via insights, predictions and products Managed services to support 27 different mission critical databases 24/7. Migrate workloads and databases to Cloud, modernize and provide ongoing support. Managed services to support and modernize application infrastructure and database 24/7.
5. Other presentation • Yesterday at 11:15 I had another presentation, slides will be available online (https://speakerdeck.com/mcrauwel) Database reliability engineering for MySQL Databases! Every developer comes across them in their careers. Just like me, I started my career as a PHP backend developer on a typical LAMP stack. In our organisation we did not have a MySQL DBA so I started to learn about how we could make our database as reliable as possible. I will show you ways for making your database more HA, how to make service discovery easy and seamless to the applications and I will talk about backups and monitoring! © Pythian Services Inc 2021 | Confidential | 5
6. Introduction © Pythian Services Inc 2021 | Confidential | 6
7. History lesson © Pythian Services Inc 2021 | Confidential | 7 • MySQL supports replication for a very long time • Originally replication was only asynchronous • What this means: ◦ Transaction happens on a writer instance ◦ At commit time data is written to the table and to the binary log (for full durability you should set sync_binlog = 1 ) ◦ Replica instance pulls the binary log entry from the writer and stores it locally in a file called the relay log ◦ Replica processes the entries in the relay log and applies the change to the local dataset
8. History lesson • The slave processes the relay logs at it's own speed, this might cause replication delay (aka lag) • There are no guarantees that the replica will be able to apply the transaction (replication errors) • Replication historically was applying transactions single threaded (as opposed to the writer where 100s of threads might have been running simultaneously. © Pythian Services Inc 2021 | Confidential | 8
9. Galera Cluster • First attempt to make MySQL act as real cluster • External library added to MySQL (Percona XtraDB Cluster) or MariaDB • Not using native MySQL features such as binary logs • Galera Cluster is "virtually" synchronous ◦ Transactions are being certified before commit is returned to the client ◦ Transactions still need to be applied on other cluster nodes, eventual consistency ◦ No guarantees on read-after-write-consistency © Pythian Services Inc 2021 | Confidential | 9
10. InnoDB Cluster • Introduced in MySQL 5.7 in 2018 • Native clustering solution to MySQL • Much improved in MySQL 8.0 • InnoDB cluster components ◦ InnoDB Group Replication ◦ MySQL shell ◦ MySQL router © Pythian Services Inc 2021 | Confidential | 10
11. InnoDB Cluster - Architecture © Pythian Services Inc 2021 | Confidential | 11
12. InnoDB Group Replication - magic explained • To deep to cover in this "introductory" presentation • Very good presentations online by lefred (https://about.me/lefred) • https://www.slideshare.net/lefred.descamps/mysql-group-replication -the-magic-explained-v2 © Pythian Services Inc 2021 | Confidential | 12
13. Demo © Pythian Services Inc 2021 | Confidential | 13
14. © Pythian Services Inc 2021 | Confidential | 14
15. Demo recording https://tinyurl.com/4ef4842v © Pythian Services Inc 2021 | Confidential | 15
16. Question? © Pythian Services Inc 2021 | Confidential | 16
17. Thank you! crauwels@pythian.com @mcrauwel © Pythian Services Inc 2021 | Confidential | 17