Download as PDF, PPTX
Uploaded byMatt Lord
MySQL High Availability -- InnoDB Clusters
The document outlines the features and benefits of MySQL InnoDB Cluster, emphasizing high availability and scalability through integrated solutions like MySQL Router and Group Replication. It describes the architecture, management tools, and operational features designed to facilitate monitoring and orchestration. The goal is to provide a simplified, unified client interface for database administration and automated management of high availability setups.
MySQL High Availability -- InnoDB Clusters
- 2. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL High Availability Matt Lord MySQL Product Manager @mattalord Copyright © 2016, Oracle and/or its affiliates. All rights reserved. #MySQL #oow16
- 3. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. 3
- 4. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | Program Agenda 4 An Introduction to InnoDB Clusters The Components Setup Monitoring and Management What’s Next? 1 2 3 4 5
- 5. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | Program Agenda 5 An Introduction to InnoDB Clusters The Components Setup Monitoring and Management What’s Next? 1 2 3 4 5
- 6. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | Virtually all organizations require their most critical systems to be highly available 6 100%
- 7. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | InnoDB Cluster 7 App Servers with MySQL Router MySQL Group Replication MySQL Shell Setup, Manage, Orchestrate “High Availability becomes a core first class feature of MySQL!”
- 8. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | 8 MySQL InnoDB Cluster: Vision “A single product — MySQL — with high availability and scaling features baked in; providing an integrated end-to-end solution that is easy to use.”
- 9. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | • One Product: MySQL – All components created together – Tested together – Packaged together • Easy to Use – One client: MySQL Shell – Easy packaging – Integrated orchestration – Homogenous servers • Flexible and Modern – SQL and NoSQL together – Protocol Buffers – Developer friendly • Support Read/Write Scale Out – Sharded clusters – Federated system of N replica sets – Each replica set manages a shard 9 MySQL InnoDB Cluster: Goals
- 10. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | 10 Scale-Out High Performance Ease-of-Use Built-in HA Out-of-Box Solution Everything Integrated MySQL InnoDB cluster
- 11. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL InnoDB Cluster: Architecture – Step 2 M M M MySQL Connector Application MySQL Router MySQL Connector Application MySQL Router MySQL Shell HA Group Replication
- 12. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | S1 S2 S3 S4 S… M M M MySQL Connector Application MySQL Router MySQL Connector Application MySQL Router MySQL Shell HA MySQL InnoDB Cluster: Architecture – Step 3 Group Replication Read-Only Slaves
- 13. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | S1 S2 S3 S4 S… M M M MySQL Connector Application MySQL Router MySQL Connector Application MySQL Router MySQL Shell HA ReplicaSet1 S1 S2 S3 S4 S… M M M MySQL Connector Application MySQL Router HA ReplicaSet2 ReplicaSet3 MySQL Connector Application MySQL Router MySQL InnoDB Cluster: Architecture – Step 4 S1 S2 S3 S4 M M M HA
- 14. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Connector Application MySQL Router MySQL Connector Application MySQL Router MySQL Shell MySQL Connector Application MySQL Router MySQL Connector Application MySQL Router MySQL InnoDB Cluster: High Level Architecture MySQL InnoDB cluster MySQL Enterprise Monitor …
- 15. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | 15 MySQL InnoDB Cluster: The End Goal M App M M MySQL Shell and Orchestration Tooling MApp M M Simple Shard Mapping, State and Extra Metadata Control, Coordinate, Provision ... Monitoring (MEM) MySQL Router Group Replication – Shard 1 Group Replication – Shard N MySQL Router
- 16. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | Program Agenda 16 An Introduction to InnoDB Clusters The Components Setup Monitoring and Management What’s Next? 1 2 3 4 5
- 17. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Group Replication Natively distributed and highly available replica sets
- 18. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Group Replication: What Is It? • Group Replication library – Implementation of Replicated Database State Machine theory • MySQL GCS is based on Paxos (variant of Mencius) – Provides virtually synchronous replication for MySQL 5.7+ – Supported on all MySQL platforms • Linux, Windows, Solaris, OSX, FreeBSD “Multi-master update anywhere replication plugin for MySQL with built-in conflict detection and resolution, automatic distributed recovery, and group membership.” 18 App Servers with MySQL Router MySQL Group Replication MySQL Shell Setup, Manage, Orchestrate
- 19. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Group Replication: What Does It Provide? • A highly available distributed MySQL database service – Removes the need for manually handling server fail-over – Provides distributed fault tolerance – Enables Active/Active update anywhere setups – Automates reconfiguration (adding/removing nodes, crashes, failures) – Automatically detects and handles conflicts 19
- 20. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Group Replication: Use Cases • Elastic Replication – Environments that require a very fluid replication infrastructure, where the number of servers has to grow or shrink dynamically and with as little pain as possible. • Highly Available Shards – Sharding is a popular approach to achieve write scale-out. Users can use MySQL Group Replication to implement highly available shards in a federated system. Each shard can map into a Replication Group. • Alternative to Master-Slave replication – It may be that a single master server makes it a single point of contention. Writing to an entire group may prove more scalable under certain circumstances. 20
- 21. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Group Replication: Architecture Node Types R: Traffic routers/proxies: mysqlrouter, haproxy, sqlproxy, ... M: mysqld nodes participating in Group Replication 21
- 22. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | 22 MySQL Server: Full Stack
- 23. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Server: Core • Server calls into the plugin through a generic interface – Most server internals are hidden from the plugin • Plugin interacts with the server through a generic interface – Replication plugin determines the fate of the commit operation through a well defined server interface – The plugin makes use of the relay log infrastructure to inject changes in the receiving server 23 GCS API Replication Plugin Plugin API MySQL Server Group Comm. System (Corosync) Group Com. Engine
- 24. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Server: Group Replication Plugin • The plugin is responsible for – Maintaining distributed execution context – Detecting and handling conflicts – Handling distributed recovery • Detect membership changes • Donate state if needed • Collect state if needed – Proposing transactions to other members – Receiving and handling transactions from other members – Deciding the ultimate fate of transactions • commit or rollback 24 GCS API Replication Plugin Plugin API MySQL Server Group Comm. System (Corosync) Group Com. Engine
- 25. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Server: GCS • The communication API (and binding) is responsible for: – Abstracting the underlying group communication system implementation from the plugin itself – Mapping the interface to a specific group communication system implementation • The Group Communication System engine: – Variant of Paxos developed at MySQL – Building block to provide distributed agreement between servers 25 GCS API Replication Plugin Plugin API MySQL Server Group Comm. System (Corosync) Group Com. Engine
- 26. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL High Availability with Group Replication Nuno Carvalho, Group Replication Development Lead, Oracle Wednesday, September 21, 4:15 pm - 5:00 pm | Park Central - Stanford Building a Highly Available MySQL Database Service with Group Replication Matt Lord, Product Manager, Oracle Thursday, September 22, 1:15 pm - 2:15 pm | Hotel Nikko - Peninsula (25th Floor) Session Hands-On Lab 26
- 27. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Router Transparent application connection routing
- 28. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Router • Transparent client connection routing – Load balancing – Application connection failover • Stateless design offers easy HA client routing – A local Router becomes part of the application stack ”MySQL Router allows you to easily migrate your standalone MySQL instances to natively distributed and highly available InnoDB clusters without affecting existing applications!” 28 Transparent access to HA databases for MySQL Applications App Servers with MySQL Router MySQL Group Replication MySQL Shell Setup, Manage, Orchestrate
- 29. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Router: 2.1 • Native support for InnoDB clusters – Understands Group Replication topology – Utilizes metadata schema stored on each member • Bootstraps itself and sets up client routing for the InnoDB cluster • Allows for intelligent client routing into the InnoDB cluster • Supports multi-master and single primary modes • Core improvements – Logging – Monitoring – Performance – Security 29 App Servers with MySQL Router MySQL Group Replication MySQL Shell Setup, Manage, Orchestrate labs.mysql.com
- 30. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Shell Single tool for development, setup, management, orchestration, and monitoring
- 31. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Shell 31 A single unified client for all administrative and operations tasks App Servers with MySQL Router MySQL Group Replication MySQL Shell Setup, Manage, Orchestrate • Multi-Language: JavaScript, Python, and SQL – Naturally scriptable • Supports both Document and Relational models • Exposes full Development and Admin API ”MySQL Shell provides the developer and DBA with a single intuitive, flexible, and powerfull interface for all MySQL related tasks!”
- 32. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Shell: Admin API • mysql-js> dba.help() • The global variable 'dba' is used to access the MySQL AdminAPI • Perform DBA operations – Manage MySQL InnoDB clusters • Create clusters • Deploy MySQL instances • Get cluster info • Start/Stop MySQL Instances • Validate MySQL instances … Database Administration Interface labs.mysql.com App Servers with MySQL Router MySQL Group Replication MySQL Shell Setup, Manage, Orchestrate
- 33. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | Program Agenda 33 An Introduction to InnoDB Clusters The Components Setup Monitoring and Management What’s Next? 1 2 3 4 5
- 34. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Shell – Deploy MySQL Instances shell> mysqlsh mysql-js> dba.deployLocalInstance(3306) mysql-js> dba.deployInstance(‘hanode2:3306’) mysql-js> dba.deployInstance(‘hanode3:3306’) 34
- 35. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Shell – Create an InnoDB Cluster mysql-js> connect root@hanode1:3306 mysql-js> cluster = dba.createCluster(‘NewAppCluster') mysql-js> cluster.addInstance('root@hanode2:3306') mysql-js> cluster.addInstance('root@hanode3:3306') 35
- 36. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Shell – Add a MySQL Router shell> mysqlrouter --bootstrap hanode1:3306 shell> mysqlrouter & shell> mysqlsh --uri root@localhost:6446 36
- 37. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | Program Agenda 37 An Introduction to InnoDB Clusters The Components Setup Monitoring and Management What’s Next? 1 2 3 4 5
- 38. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Shell – Check Status shell> mysqlsh --uri root@localhost:6446 mysql-js> cluster = dba.getCluster() mysql-js> cluster.status() 38
- 39. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Shell – Check Status mysql-js> cluster.status() { "clusterName": “NewAppCluster", "defaultReplicaSet": { "status": "Cluster tolerant to up to ONE failure.", "topology": { “hanode1:3306": { "address": “hanode1:3306", "status": "ONLINE", "role": "HA", "mode": "R/W", "leaves": { “hanode2:3306": { "address": “hanode2:3306", "status": "ONLINE", "role": "HA", "mode": "R/O", "leaves": {} }, … 39
- 40. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | Program Agenda 40 An Introduction to InnoDB Clusters The Components Setup Monitoring and Management What’s Next? 1 2 3 4 5
- 41. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | 41 MySQL Document Store Relational & Document Models MySQL HA Out-Of-Box HA Read Scale-Out Async Replication + Auto Failover Write Scale-Out Sharding S1 S2 S3 S4 * Download InnoDB Cluster Preview Release from labs.mysql.com
- 42. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | MySQL Enterprise Monitor • Native holistic support for InnoDB clusters – Topology views – Detailed metrics and graphs – Best Practice advice • Monitoring of MySQL Routers • Monitoring of Group Replication 42
- 43. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | Sharded InnoDB Clusters • Group Replication – Each shard is a highly available replica set • MySQL Router – Manages shard mappings and related metadata – Manages client routing – Provides cross shard execution framework • MySQL Shell – Exposes management and orchestration features 43
- 44. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. | THANK YOU! 44
- 45. Copyright © 2016,Oracle and/or its affiliates. All rights reserved. Oracle Support Stars Bar • Ask the Experts your toughest product questions: MySQL & all Oracle products! • View My Oracle Support and Product Demonstrations • Learn what’s new and more! • Moscone West Exhibition Hall, Booth 3451 oracle.com/goto/starsbar