MySQL InnoDB Cluster in a Nutshell - Hands-on Lab

MySQL InnoDB Cluster in a Nutshell: Hands-On Tutorial HOL7316 Oracle OpenWorld 2017 Frédéric Descamps - MySQL Community Manager - Oracle Matt Lord - MySQL Product Manager - Oracle 3 / 63

Safe Harbor Statement 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 and timing of any features or functionality described for Oracle´s product remains at the sole discretion of Oracle. Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 4 / 63

Frédéric Descamps @lefred MySQL Evangelist Managing MySQL since 3.23 devops believer living in Belgium 🇧🇪 http://about.me/lefred Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 7 / 63

LAB1: MySQL Shell start the shell: [holuser@localhost]$ mysqlsh Change between JavaScript, Python and SQL mode get the Admin API's help Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 15 / 63

InnoDB Cluster's Architecture Application MySQL Connector MySQL Router MySQL Shell InnoDB cluster Application MySQL Connector MySQL Router Mp M M Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 18 / 63

MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 23 / 63

MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 24 / 63

MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol GR allows to write on all Group Members (cluster nodes) simultaneously while retaining consistency Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 25 / 63

MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol GR allows to write on all Group Members (cluster nodes) simultaneously while retaining consistency GR implements conflict detection and resolution Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 26 / 63

MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol GR allows to write on all Group Members (cluster nodes) simultaneously while retaining consistency GR implements conflict detection and resolution GR allows automatic distributed recovery Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 27 / 63

MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol GR allows to write on all Group Members (cluster nodes) simultaneously while retaining consistency GR implements conflict detection and resolution GR allows automatic distributed recovery Supported on all MySQL platforms !! Linux, Windows, Solaris, OSX, FreeBSD Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 28 / 63

And for users ? not longer necessary to handle server fail-over manually or with a complicated script GR provides fault tolerance Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 31 / 63

And for users ? not longer necessary to handle server fail-over manually or with a complicated script GR provides fault tolerance GR enables update-everywhere setups Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 32 / 63

And for users ? not longer necessary to handle server fail-over manually or with a complicated script GR provides fault tolerance GR enables update-everywhere setups GR handles crashes, failures, re-connects automatically Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 33 / 63

And for users ? not longer necessary to handle server fail-over manually or with a complicated script GR provides fault tolerance GR enables update-everywhere setups GR handles crashes, failures, re-connects automatically Allows an easy setup of a highly available MySQL service! Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 34 / 63

MySQL Instances: Deploy 3 sandbox instances using these ports: instance1: 4306 instance2: 4406 instance3: 4506 MySQL-JS> dba.deploySandboxInstance(4306) Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 37 / 63

Instance Configuration Validate the configuration using the Shell: MySQL-JS> dba.checkInstanceCon guration('root@localhost:4406') MySQL-JS> dba.checkInstanceCon guration('root@localhost:4506') MySQL-JS> dba.checkInstanceCon guration('root@localhost:4606') Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 39 / 63

Instance Configuration Validate the configuration using the Shell: MySQL-JS> dba.checkInstanceCon guration('root@localhost:4406') MySQL-JS> dba.checkInstanceCon guration('root@localhost:4506') MySQL-JS> dba.checkInstanceCon guration('root@localhost:4606') Check on the board what could happen Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 40 / 63

MySQL InnoDB Cluster Connect to one instance and create a cluster fromit MySQL-JS> c root@localhost:4406 MySQL-JS> cluster=dba.createCluster('oow17') Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 43 / 63

MySQL InnoDB Cluster Connect to one instance and create a cluster fromit MySQL-JS> c root@localhost:4406 MySQL-JS> cluster=dba.createCluster('oow17') Check the status: MySQL-JS> cluster.status() Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 44 / 63

MySQL Shell in Summary The MySQL Shell is an interactive Javascript, Python, or SQL interface supporting development and administration for MySQL. MySQL Shell includes the AdminAPI--available in JavaScript and Python--which enables you to set up and manage InnoDB clusters. It provides a modern and fluent API which hides the complexity associated with configuring, provisioning, and managing an InnoDB cluster, without sacrificing power, flexibility, or security. Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 46 / 63

Default = Single Primary Mode By default, MySQL InnoDB Cluster enables Single Primary Mode. mysql> show global variables like 'group_replication_single_primary_mode'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | group_replication_single_primary_mode | ON | +---------------------------------------+-------+ Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 48 / 63

Default = Single Primary Mode By default, MySQL InnoDB Cluster enables Single Primary Mode. mysql> show global variables like 'group_replication_single_primary_mode'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | group_replication_single_primary_mode | ON | +---------------------------------------+-------+ In Single Primary Mode, a single member acts as the writable master (PRIMARY) and the rest of the members act as hot-standbys (SECONDARY). The group itself coordinates and configures itself automatically to determine which member will act as the PRIMARY, through a leader election mechanism. Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 49 / 63

Who´s the Primary Master ? old fashion style As the Primary Master is elected, all nodes part of the group knows which one was elected. This value is exposed in status variables: Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 50 / 63

Who´s the Primary Master ? old fashion style As the Primary Master is elected, all nodes part of the group knows which one was elected. This value is exposed in status variables: mysql> show status like 'group_replication_primary_member'; +----------------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------+ | group_replication_primary_member | 28a4e51f-860e-11e6-bdc4-08002718d305 | +----------------------------------+--------------------------------------+ Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 51 / 63

Who´s the Primary Master ? old fashion style As the Primary Master is elected, all nodes part of the group knows which one was elected. This value is exposed in status variables: mysql> show status like 'group_replication_primary_member'; +----------------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------+ | group_replication_primary_member | 28a4e51f-860e-11e6-bdc4-08002718d305 | +----------------------------------+--------------------------------------+ mysql> select member_host as "primary master" from performance_schema.global_status join performance_schema.replication_group_members where variable_name = 'group_replication_primary_member' and member_id=variable_value; +---------------+ | primary master| +---------------+ | mysql3 | +---------------+ Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 52 / 63

Who´s the Primary Master ? new fashion style mysql> select member_host from performance_schema.replication_group_members where member_role='PRIMARY'; +-------------+ | member_host | +-------------+ | mysql3 | +-------------+ Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 53 / 63

Create a Multi-Primary Cluster: It´s also possible to create a Multi-Primary Cluster using the Shell: mysql-js> cluster=dba.createCluster('oow17',{multiMaster: true}) Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 55 / 63

Create a Multi-Primary Cluster: It´s also possible to create a Multi-Primary Cluster using the Shell: mysql-js> cluster=dba.createCluster('oow17',{multiMaster: true}) A new InnoDB cluster will be created on instance 'root@mysql3:3306'. The MySQL InnoDB cluster is going to be setup in advanced Multi-Master Mode. Before continuing you have to con rm that you understand the requirements and limitations of Multi-Master Mode. Please read the manual before proceeding. I have read the MySQL InnoDB cluster manual and I understand the requirements and limitations of advanced Multi-Master Mode. Con rm [y|N]: Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 56 / 63

Create a Multi-Primary Cluster: It´s also possible to create a Multi-Primary Cluster using the Shell: mysql-js> cluster=dba.createCluster('oow17',{multiMaster: true}) A new InnoDB cluster will be created on instance 'root@mysql3:3306'. The MySQL InnoDB cluster is going to be setup in advanced Multi-Master Mode. Before continuing you have to con rm that you understand the requirements and limitations of Multi-Master Mode. Please read the manual before proceeding. I have read the MySQL InnoDB cluster manual and I understand the requirements and limitations of advanced Multi-Master Mode. Con rm [y|N]: Or you can force it to avoid interaction (for automation) : Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 57 / 63

Create a Multi-Primary Cluster: It´s also possible to create a Multi-Primary Cluster using the Shell: mysql-js> cluster=dba.createCluster('oow17',{multiMaster: true}) A new InnoDB cluster will be created on instance 'root@mysql3:3306'. The MySQL InnoDB cluster is going to be setup in advanced Multi-Master Mode. Before continuing you have to con rm that you understand the requirements and limitations of Multi-Master Mode. Please read the manual before proceeding. I have read the MySQL InnoDB cluster manual and I understand the requirements and limitations of advanced Multi-Master Mode. Con rm [y|N]: Or you can force it to avoid interaction (for automation) : > cluster=dba.createCluster('oow17',{multiMaster: true, force: true}) Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 58 / 63

MySQL Router MySQL Router is lightweight middleware that provides transparent routing between your application and backend MySQL Servers. It can be used for a wide variety of use cases, such as providing high availability and scalability by effectively routing database traffic to appropriate backend MySQL Servers. Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 59 / 63

MySQL Router MySQL Router is lightweight middleware that provides transparent routing between your application and backend MySQL Servers. It can be used for a wide variety of use cases, such as providing high availability and scalability by effectively routing database traffic to appropriate backend MySQL Servers. MySQL Router doesn´t require any specific configuration. It configures itself automatically (bootstrap) using MySQL InnoDB Cluster´s metadata. Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 60 / 63

MySQL Router Configure MySQL Router: we bootstrap it using the Primary-Master: [root@mysql1 ~]# mysqlrouter --bootstrap localhost:4306 --user mysqlrouter Please enter MySQL password for root: WARNING: The MySQL server does not have SSL ... Bootstrapping system MySQL Router instance... MySQL Router has now been con gured for the InnoDB cluster 'oow17'. The following connection information can be used to connect to the cluster. Classic MySQL protocol connections to cluster 'oow17': - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 X protocol connections to cluster 'oow17': - Read/Write Connections: localhost:64460 - Read/Only Connections: localhost:64470 [root@mysql1 ~]# chown -R mysqlrouter. /var/lib/mysqlrouter Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 61 / 63

MySQL Router (2) Connect to MySQL using the MySQL Shell or MySQL client through the router (port 6446): [root@mysql1 ~]# mysqlh root@localhost:6446 --sql mysql-sql> select @@hostname, sleep(360); Check in which node this query is running. Copyright @ 2017 Oracle and/or its affiliates. All rights reserved. 62 / 63

MySQL InnoDB Cluster in a Nutshell - Hands-on Lab

More Related Content

What's hot

Similar to MySQL InnoDB Cluster in a Nutshell - Hands-on Lab

More from Frederic Descamps

Recently uploaded

MySQL InnoDB Cluster in a Nutshell - Hands-on Lab