Download as PDF, PPTX
![Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | SandboxInstances • E.g. MySQL configuration file : /home/mysql/mysql-sandboxes/3310/my.cnf • dba.configureLocalInstance(uri) – [mysql@virtual-41 3310]$ ll my.cnf* – -rw------- 1 mysql mysql 2376 May 31 17:04 my.cnf – -rw-rw-r-- 1 mysql mysql 882 May 31 17:04 my.cnf.20170531-170412 30](https://image.slidesharecdn.com/mysqlgroupreplicationdeepdivev1-170613041040/75/MySQL-InnoDB-Cluster-and-MySQL-Group-Replication-HKOSC-2017-30-2048.jpg)
MySQL InnoDB Cluster and MySQL Group Replication @HKOSC 2017
- 1. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | https://www.facebook.com/groups/hkmysqlusergroup/
- 2. Copyright © 2014,Oracle and/or its affiliates. All rights reserved. | MySQL Group Replication Deep Dive 马楚成 (Ivan Ma) ivan-cs.ma@oracle.com 2017-06-10
- 3. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | 22y Constant improvements of MySQL as a relational database. 3 JSON Functions Stored Procedures Async Replication Semi-Sync Replication Group Replication Foreign Keys Performance Schema Multi-Triggers Partitioning Sub-queriesDocument Store Prepared Statements R-Trees B-Trees Row-based Replication Plugin API Event Scheduler Unicode Full-Text Search Enterprise Monitor WorkbenchInnoDB
- 4. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | 4 Scale-Out High Performance Ease-of-Use Built-in HA Out-of-Box Solution Everything Integrated MySQL InnoDB cluster GA
- 5. Copyright © 2017,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 … 5
- 6. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | MySQL InnoDB Cluster: Internal Architecture M M M MySQL Connector Application MySQL Router MySQL Connector Application MySQL Router MySQL Shell HA Group Replication 6
- 7. Copyright © 2017,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 © 2014,Oracle and/or its affiliates. All rights reserved. | MySQL Group Replication Natively distributed and highly available replica sets
- 9. Copyright © 2017,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 our home-grown Paxos implementation – Provides virtually synchronous replication for MySQL 5.7+ • Guarantees eventual consistency – 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.” 9 App Servers with MySQL Router MySQL Group Replication MySQL Shell Setup, Manage, Orchestrate
- 10. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | 10 MySQL Server: Full Stack
- 11. Copyright © 2017,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 11 GCS API Replication Plugin Plugin API MySQL Server Group Comm. System (Corosync) Group Com. Engine
- 12. Copyright © 2017,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 12 GCS API Replication Plugin Plugin API MySQL Server Group Comm. System (Corosync) Group Com. Engine
- 13. Copyright © 2014,Oracle and/or its affiliates. All rights reserved. | MySQL Router Transparent application connection routing
- 14. Copyright © 2017,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!” 14 Transparent access to HA databases for MySQL Applications App Servers with MySQL Router MySQL Group Replication MySQL Shell Setup, Manage, Orchestrate
- 15. Copyright © 2017,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 – Built-in keyring for easy and secure password management 15 App Servers with MySQL Router MySQL Group Replication MySQL Shell Setup, Manage, Orchestrate ”MySQL Router 2.1, with the new metadata_cache plugin, provides transparent client connection routing and failover into your InnoDB clusters!”
- 16. Copyright © 2014,Oracle and/or its affiliates. All rights reserved. | MySQL Shell Single tool for development, setup, management, orchestration, and monitoring
- 17. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | MySQL Shell 17 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 powerful interface for all MySQL related tasks!”
- 18. Copyright © 2017,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 • Validate MySQL instances • Configure MySQL instances • Get cluster info • Modify clusters Database Administration Interface App Servers with MySQL Router MySQL Group Replication MySQL Shell Setup, Manage, Orchestrate 18
- 19. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | MySQL Enterprise Monitor • Native holistic support for Group Replication / InnoDB clusters (GA in 3.4!) – Topology views – Detailed metrics and graphs – Best Practice advice 19 GA
- 20. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | More … Saturday, December 10, 2016
- 21. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Demonstration 21 MySQL Shell MySQL InnoDB cluster Wordpress MySQL MySQL MySQL MySQL Router
- 22. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | MySQL Shell 22
- 23. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | MySQL Shell – dba.help(); 23
- 24. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | MySQL Shell – dba.help(‘deploySandboxInstance’); • dba.deploySandboxInstance(‘3310’); 24 mysql-js> dba.deploySandboxInstance(3310); A new MySQL sandbox instance will be created on this host in /home/mysql/mysql-sandboxes/3310 Please enter a MySQL root password for the new instance: Deploying new MySQL instance... Instance localhost:3310 successfully deployed and started. Use shell.connect('root@localhost:3310'); to connect to the instance.
- 25. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Adding more MySQL Instances • dba.deploySandboxInstance(3310); • dba.deploySandboxInstance(3320); • dba.deploySandboxInstance(3330); 25 mysql-js> dba.checkInstanceConfiguration(3310); Invalid connection options, expected either a URI or a Dictionary (ArgumentError) mysql-js> dba.checkInstanceConfiguration('localhost:3310'); Please provide the password for 'root@localhost:3310': Validating instance... The instance 'localhost:3310' is valid for Cluster usage { "status": "ok" }
- 26. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | MySQL Shell Creating the MySQL InnoDB Cluster • Connecting to Live Instance e.g. mysql-js>connect localhost:3310 • Creating the Cluster e.g. mysql-js>var cluster = dba.createCluster(‘mycluster’) 26 mysql-js> c localhost:3310 Creating a Session to 'root@localhost:3310' Enter password: Classic Session successfully established. No default schema selected. mysql-js> var cluster = dba.createCluster('mycluster'); A new InnoDB cluster will be created on instance 'root@localhost:3310'. Creating InnoDB cluster 'mycluster' on 'root@localhost:3310'... Adding Seed Instance... Cluster successfully created. Use Cluster.addInstance() to add MySQL instances. At least 3 instances are needed for the cluster to be able to withstand up to one server failure.
- 27. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | MySQL Shell Checking Cluster’s status and adding new members to the Cluster 27 mysql-js> cluster.addInstance('localhost:3320'); A new instance will be added to the InnoDB cluster. Depending on the amount of data on the cluster this might take from a few seconds to several hours. Please provide the password for 'root@localhost:3320': Adding instance to the cluster ... The instance 'root@localhost:3320' was successfully added to the cluster. mysql-js> cluster.addInstance('localhost:3330'); A new instance will be added to the InnoDB cluster. Depending on the amount of data on the cluster this might take from a few seconds to several hours. Please provide the password for 'root@localhost:3330': Adding instance to the cluster ... The instance 'root@localhost:3330' was successfully added to the cluster.
- 28. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | 28
- 29. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | MySQL Shell dba.configureLocalInstance(uri) 29 mysql-js> dba.configureLocalInstance('localhost:3310'); Please provide the password for 'root@localhost:3310': Detected as sandbox instance. Validating MySQL configuration file at: /home/mysql/mysql- sandboxes/3310/my.cnf Validating instance... The instance 'localhost:3310' is valid for Cluster usage You can now use it in an InnoDB Cluster. { "status": "ok" } mysql-js> dba.configureLocalInstance('localhost:3320'); Please provide the password for 'root@localhost:3320': Detected as sandbox instance. Validating MySQL configuration file at: /home/mysql/mysql- sandboxes/3320/my.cnf Validating instance... The instance 'localhost:3320' is valid for Cluster usage You can now use it in an InnoDB Cluster. { "status": "ok" } mysql-js>
- 30. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | SandboxInstances • E.g. MySQL configuration file : /home/mysql/mysql-sandboxes/3310/my.cnf • dba.configureLocalInstance(uri) – [mysql@virtual-41 3310]$ ll my.cnf* – -rw------- 1 mysql mysql 2376 May 31 17:04 my.cnf – -rw-rw-r-- 1 mysql mysql 882 May 31 17:04 my.cnf.20170531-170412 30
- 31. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | Persist configuration - dba.configureLocalInstance(uri) 31
- 32. Copyright © 2017,Oracle and/or its affiliates. All rights reserved. | https://www.facebook.com/groups/hkmysqlusergroup/
- 33. Copyright © 2014,Oracle and/or its affiliates. All rights reserved. | Questions Thank You