Another HA Solution for ProxySQL Users, Easy and All Integrated: MySQL InnoDB Cluster with ProxySQL

This presentation was given at ProxySQL Technology Day Ghent 2019 by Frederic Descamps, Oracle MySQL's Community Manager.

Transcript

1. 1 / 41
2. Another HA Solution for ProxySQL Users Easy and All Integrated: MySQL InnoDB Cluster Frédéric Descamps MySQL Community Manager October 2019 Copyright @ 2019 Oracle and/or its affiliates. 2 / 41
3. Safe Harbor The following is intended to outline our general product direction. It is intended for information purpose 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 up in making purchasing decisions. The development, release, timing and pricing of any features or functionality described for Oracle´s product may change and remains at the sole discretion of Oracle Corporation. Statement in this presentation relating to Oracle´s future plans, expectations, beliefs, intentions and ptospects are "forward-looking statements" and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that a ect our business is contained in Oracle´s Securities and Exchange Commission (SEC) lings, including our most recent reports on Form 10-K and Form 10-Q under the heading "Risk Factors". These lings are available on the SEC´s website or on Oracle´s website at h p://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Copyright @ 2019 Oracle and/or its affiliates. 3 / 41
4. about.me/lefred Who am I ? Copyright @ 2019 Oracle and/or its affiliates. 4 / 41
5. Frédéric Descamps @lefred MySQL Evangelist Managing MySQL since 3.23 devops believer living in Belgium h ps://lefred.be Copyright @ 2019 Oracle and/or its affiliates. 5 / 41
6. MySQL InnoDB Cluster "A single product — MySQL — with high availability and scaling features baked in; providing an integrated end-to-end solution that is easy to use." Copyright @ 2019 Oracle and/or its affiliates. 6 / 41
7. MySQL InnoDB Cluster "A single product — MySQL — with high availability and scaling features baked in; providing an integrated end-to-end solution that is easy to use." Copyright @ 2019 Oracle and/or its affiliates. 7 / 41
8. Components: MySQL Server MySQL Group Replication MySQL Shell MySQL Router MySQL InnoDB Cluster "A single product — MySQL — with high availability and scaling features baked in; providing an integrated end-to-end solution that is easy to use." Copyright @ 2019 Oracle and/or its affiliates. 8 / 41
9. One Product: MySQL All components created together Tested together Packaged together MySQL InnoDB Cluster - Goals Copyright @ 2019 Oracle and/or its affiliates. 9 / 41
10. One Product: MySQL All components created together Tested together Packaged together Easy to Use One client: MySQL Shell Easy packaging Integrated orchestration Homogenous servers (*) Secure automatic provisioning MySQL InnoDB Cluster - Goals Copyright @ 2019 Oracle and/or its affiliates. 10 / 41
11. One Product: MySQL All components created together Tested together Packaged together Easy to Use One client: MySQL Shell Easy packaging Integrated orchestration Homogenous servers (*) Secure automatic provisioning Flexible and Modern SQL and NoSQL together Protocol Bu ers Asynchronous API Developer friendly MySQL InnoDB Cluster - Goals Copyright @ 2019 Oracle and/or its affiliates. 11 / 41
12. One Product: MySQL All components created together Tested together Packaged together Easy to Use One client: MySQL Shell Easy packaging Integrated orchestration Homogenous servers (*) Secure automatic provisioning Flexible and Modern SQL and NoSQL together Protocol Bu ers Asynchronous API Developer friendly Completely Open Source -- GPL! No license required to have High Availability MySQL InnoDB Cluster - Goals Copyright @ 2019 Oracle and/or its affiliates. 12 / 41
13. Heart of MySQL InnoDB Cluster MySQL Group Replication Copyright @ 2019 Oracle and/or its affiliates. 13 / 41
14. MySQL Group Replication Copyright @ 2019 Oracle and/or its affiliates. 14 / 41
15. High Available Distributed MySQL database Open Source -- GPL Distributed fault tolerance Automatic failover Active/Active update anywhere (limits apply) Automatic membership con guration Adding/removing members Network partitions, failures Con ict detection and resolution Prevents data loss MySQL Group Replication Copyright @ 2019 Oracle and/or its affiliates. 15 / 41
16. MySQL Group Replication Implementation of Replicated Database State Machine Total Order - Writes XCOM - Paxos implementation Con gurable Consistency Guarantees eventual consistency 8.0+: per session & global read/write consistency Using MySQL replication framework by design binary logs, relay logs GTIDs: Global Transaction IDs Generally Available since MySQL 5.7 Supported on all platforms: linux, windows, solaris, macosx, freebsd Copyright @ 2019 Oracle and/or its affiliates. 16 / 41
17. MySQL Group Replication - Consistency Default consistency guarantee is EVENTUAL Write some data on nodeA, Read same data immediately on nodeB, data might be there yet... or not yet. (Writes remain ordered and write consistency is never compromised) Copyright @ 2019 Oracle and/or its affiliates. 17 / 41
18. MySQL Group Replication - Consistency Default consistency guarantee is EVENTUAL Write some data on nodeA, Read same data immediately on nodeB, data might be there yet... or not yet. (Writes remain ordered and write consistency is never compromised) This is a reason why ProxySQL 2.0 implemented GTID Consistent Reads Copyright @ 2019 Oracle and/or its affiliates. 18 / 41
19. MySQL Group Replication - Consistency Consistency level is con gurable since MySQL 8.0.14 The scope can be SESSION or GLOBAL. The possible values are: EVENTUAL (default) BEFORE_ON_PRIMARY_FAILOVER BEFORE AFTER BEFORE_AND_AFTER Copyright @ 2019 Oracle and/or its affiliates. 19 / 41
20. MySQL Group Replication - Consistency Consistency level is con gurable since MySQL 8.0.14 The scope can be SESSION or GLOBAL. The possible values are: EVENTUAL (default) BEFORE_ON_PRIMARY_FAILOVER BEFORE AFTER BEFORE_AND_AFTER Now the application can de ne the required consistency ! Copyright @ 2019 Oracle and/or its affiliates. 20 / 41
21. Consistency : EVENTUAL Copyright @ 2019 Oracle and/or its affiliates. 21 / 41
22. Consistency : BEFORE BEFORE READ or WRITE: wait for all trx to be applied Impact on READ & WRITE: added response time: at least network latency Copyright @ 2019 Oracle and/or its affiliates. 22 / 41
23. Consistency : AFTER AFTER WRITE: wait for all trx to be applied Impact on WRITE only: added response time: at least network latency Copyright @ 2019 Oracle and/or its affiliates. 23 / 41
24. Consistency : BEFORE_AND_AFTER BEFORE READ or WRITE: wait for all trx to be applied AFTER WRITE: wait for all trx to be applied Copyright @ 2019 Oracle and/or its affiliates. 24 / 41
25. provides transparent routing between your application and back-end MySQL Servers MySQL Router Copyright @ 2019 Oracle and/or its affiliates. 25 / 41
26. provides transparent routing between your application and back-end MySQL Servers Transparent Access to Database Architecture Open Source -- GPL Transparent client connection routing Load balancing Application connection failover Stateless design o ers easy HA client routing Router as part of the application stack Native support for InnoDB clusters Understands Group Replication topology Utilizes metadata schema on each member Currently TCP Port each for PRIMARY and NON- PRIMARY tra c MySQL Router Copyright @ 2019 Oracle and/or its affiliates. 26 / 41
27. MySQL Router Con gure MySQL Router: # mysqlrouter --bootstrap mysql1:3306 # systemctl start mysqlrouter Copyright @ 2019 Oracle and/or its affiliates. 27 / 41
28. data unit layers Data Data Data Segments Packets Frames Bits Application Network Process to Application Presentation Data Representation and Encryption Session Interhost Communication Transport End-to-End Connections and Reliability Network Path Determination and Logical Addressing (IP) Data Link Physical Addressing (MAC and LLC) Physical Media, Signal and Binary Transmission Host Layers Media Layers 1 2 3 4 5 6 7 Router ProxySQL MySQL Router VS ProxySQL Copyright @ 2019 Oracle and/or its affiliates. 28 / 41
29. data unit layers Data Data Data Segments Packets Frames Bits Application Network Process to Application Presentation Data Representation and Encryption Session Interhost Communication Transport End-to-End Connections and Reliability Network Path Determination and Logical Addressing (IP) Data Link Physical Addressing (MAC and LLC) Physical Media, Signal and Binary Transmission Host Layers Media Layers 1 2 3 4 5 6 7 Router ProxySQL MySQL Router VS ProxySQL MySQL Router is a TCP Level 4 router, it doesn't understand the MySQL protocol MySQL Router can only take decision on routing the tra c related to the TCP port used ProxySQL is a TCP Level 7 proxy, it understands the MySQL protocol ProxySQL can take decision related to the user connecting or regular expressions on the statement performed Copyright @ 2019 Oracle and/or its affiliates. 29 / 41
30. ProxySQL and MySQL InnoDB Cluster ProxySQL supports natively MySQL Group Replication (was the rst !) ProxySQL doesn't use the meta-data of MySQL InnoDB Cluster natively (*) ProxySQL is very e cient and has many features not included in MySQL Router: Transparent Read/Write Spli ing Mirroring Multiplexing Query Caching ProxySQL as TCP/7 proxy, is less secure (passwords stored in clear text) Copyright @ 2019 Oracle and/or its affiliates. 30 / 41
31. MySQL Shell provides the developer and DBA with a single intuitive, exible, and powerful interface for all MySQL related tasks! MySQL Shell Copyright @ 2019 Oracle and/or its affiliates. 31 / 41
32. MySQL Shell provides the developer and DBA with a single intuitive, exible, and powerful interface for all MySQL related tasks! Database Administration Interface Open Source -- GPL Multi-Language: JavaScript, Python, and SQL Naturally scriptable Supports Document and Relational models Exposes full Development and Admin API Classic MySQL protocol and X protocol MySQL Shell Copyright @ 2019 Oracle and/or its affiliates. 32 / 41
33. MySQL Shell in Action ! Copyright @ 2019 Oracle and/or its affiliates. 33 / 41
34. MySQL Shell and ProxySQL It's possible to extend MySQL Shell with plugins. The follwing example on how to add ProxySQL support in the MySQL Shell can be found on github: h ps://github.com/lefred/mysqlshell-plugins Copyright @ 2019 Oracle and/or its affiliates. 34 / 41
35. MySQL Shell and ProxySQL Copyright @ 2019 Oracle and/or its affiliates. 35 / 41
36. MySQL Shell and ProxySQL Copyright @ 2019 Oracle and/or its affiliates. 36 / 41
37. MySQL Shell and ProxySQL Copyright @ 2019 Oracle and/or its affiliates. 37 / 41
38. MySQL Shell and ProxySQL Copyright @ 2019 Oracle and/or its affiliates. 38 / 41
39. MySQL Shell and ProxySQL Copyright @ 2019 Oracle and/or its affiliates. 39 / 41
40. MySQL Shell and ProxySQL Copyright @ 2019 Oracle and/or its affiliates. 40 / 41
41. Thank you ! Copyright @ 2019 Oracle and/or its affiliates. 41 / 41