High Availability in MySQL 8 using InnoDB Cluster

Sven Sandberg MySQL Replication Core Team Lead sven.sandberg@oracle.com slideshare.net/svensandberg High Availability in MySQL 8 using InnoDB Cluster

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.2 Safe Harbour 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.

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.10 Overview Write everywhere is cool MySQL InnoDB Cluster = MySQL Group Replication + MySQL Shell + MySQL Router Open Source! MySQL 8

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.19 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld MySQL Router App MySQL Connector MySQL Shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.23 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.25 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* mysqld MySQL Router App MySQL Connector MySQL Shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.26 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* mysqld cluster.addInstance(“user@host:3306”) MySQL Router App MySQL Connector MySQL Shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.27 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* cluster.addInstance(“user@host:3306”) mysqld MySQL Router App MySQL Connector MySQL Shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.28 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* cluster.removeInstance(“user@host:3306”) mysqld MySQL Router App MySQL Connector MySQL Shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.29 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* cluster.removeInstance(“user@host:3306”) mysqld MySQL Router App MySQL Connector MySQL Shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.32 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.33 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* mysqld MySQL Router App MySQL Connector MySQL Shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.34 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* mysqld cluster.addInstance(“user@host:3306”) MySQL Router App MySQL Connector MySQL Shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.35 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell cluster.addInstance(“user@host:3306”)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.36 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell R/W R R

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.37 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell R/W R R INSERT

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.38 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell R/W R R UPDATE

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.39 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell R/W R R DELETE

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.40 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell R/W R R SELECT

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.41 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell R/W R R SELECT

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.42 MySQL Group Replication Highly Available MySQL with InnoDB Cluster mysqld mysqld mysqld InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell R/W R R

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.43 MySQL Group Replication Highly Available MySQL with InnoDB Cluster InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell R mysqld mysqld R/W

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.44 MySQL Group Replication Highly Available MySQL with InnoDB Cluster InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell R mysqld mysqld R/W mysqld

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.45 MySQL Group Replication Highly Available MySQL with InnoDB Cluster InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell R mysqld mysqld R/W mysqld cluster.addInstance(“user@host:3306”)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.46 MySQL Group Replication Highly Available MySQL with InnoDB Cluster InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell RR/W cluster.addInstance(“user@host:3306”) R mysqld mysqldmysqld

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.47 MySQL Group Replication Highly Available MySQL with InnoDB Cluster InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell R/WR/W mysqld mysqldmysqld R/W

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.48 MySQL Group Replication Highly Available MySQL with InnoDB Cluster InnoDB Cluster: ● Elastic ● Highly available ● Read scale-out ● Write everywhere* MySQL Router App MySQL Connector MySQL Shell R/WR/W mysqld mysqldmysqld R/W But: know the restrictions* Write everywhere is cool Write everywhere is cool https://dev.mysql.com/doc/refman/8.0/en/group-replication-limitations.html* https://dev.mysql.com/doc/refman/8.0/en/group-replication-limitations.html

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.54 Group Replication: Communication System Group Communication System 1. Membership 2. Total order broadcast A B C Write everywhere is cool All members agree on who is a member

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.55 Group Replication: Communication System Group Communication System 1. Membership 2. Total order broadcast A B C We are three: A, B, C We are three: A, B, C We are three: A, B, C Write everywhere is cool All members agree on who is a member

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.56 Group Replication: Communication System Group Communication System 1. Membership 2. Total order broadcast A B C Write everywhere is cool All members agree on who is a member

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.57 Group Replication: Communication System Group Communication System 1. Membership 2. Total order broadcast A B Write everywhere is cool All members agree on who is a member

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.58 Group Replication: Communication System Group Communication System 1. Membership 2. Total order broadcast A B You and me: A and B You and me: A and B Write everywhere is cool All members agree on who is a member

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.59 Group Replication: Communication System Group Communication System 1. Membership 2. Total order broadcast A B CHANNEL_NAME ID HOST PORT STATE ROLE VERSION group_replication_applier e0853bd4-6479-48af-b0df-31c3c3f8d1aa emil 3310 ONLINE PRIMARY 8.0.14 group_replication_applier 64e2b6e5-9036-4e8f-9c2c-ff915c074495 pippi 3310 ONLINE SECONDARY 8.0.14 > SELECT * FROM performance_schema.replication_group_members; Write everywhere is cool All members agree on who is a member

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.60 Group Replication: Communication System Group Communication System 1. Membership 2. Total order broadcast A B Write everywhere is cool All members agree on who is a member CHANNEL_NAME ID HOST PORT STATE ROLE VERSION group_replication_applier e0853bd4-6479-48af-b0df-31c3c3f8d1aa emil 3310 ONLINE PRIMARY 8.0.14 group_replication_applier 64e2b6e5-9036-4e8f-9c2c-ff915c074495 pippi 3310 ONLINE SECONDARY 8.0.14 group_replication_applier 7ffeca3b-3e13-4f35-b7d2-9eb9c32a3dc1 lotta 3310 ONLINE SECONDARY 8.0.14 > SELECT * FROM performance_schema.replication_group_members; C

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.61 Z Group Replication: Communication System Group Communication System 1. Membership 2. Total order broadcast A B All members receive all messages C Y X Z Y X Z Y X

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.62 Z Group Replication: Communication System Group Communication System 1. Membership 2. Total order broadcast A B All members receive all messages C Y X Z Y X Z Y X … in the same order

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.65 Group Replication: Transactions Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.68 Group Replication: Transactions Simplified picture Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.69 Group Replication: Transactions Transaction Simplified picture Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.71 Group Replication: Transactions Simplified picture Transaction Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.73 Group Replication: Transactions Simplified picture Transaction Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.77 Group Replication: Transactions Hypothetical scenario ? Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.80 Group Replication: Transactions Transaction Hypothetical scenario ? Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.82 Group Replication: Transactions Transaction Hypothetical scenario ? Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.84 Group Replication: Transactions This is split-brain! Inconsistent states! Hypothetical scenario ? Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.85 Group Replication: Transactions This is split-brain! Inconsistent states! Write everywhere is cool Group Replication PROTECTS against split-brain. So this can’t happen. Hypothetical scenario ? Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.87 Group Replication: Transactions ! What actually happens Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.88 Group Replication: Transactions Transaction ! What actually happens Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.89 Group Replication: Transactions Transaction ! What actually happens Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.90 Group Replication: Transactions ! What actually happens Transaction Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.91 Group Replication: Transactions ! What actually happens Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.93 Group Replication: Transactions Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent Transaction

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.99 Group Replication: Transactions Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent Transaction remains after 2 failures

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.100 Group Replication: Transactions Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent Transaction remains after 2 failures

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.101 Group Replication: Transactions Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent Operational Non-operational

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.104 Group Replication: Transactions Wait in queue Apply Commit Execute Broadcast Commit Stages of client transactions Stages of replicated transactions Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.105 Group Replication: Transactions Wait in queue Apply Commit Transaction Execute Broadcast Commit Stages of client transactions Stages of replicated transactions Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.126 Group Replication: Transactions Wait in queue Apply Commit Execute Broadcast Commit Stages of client transactions Stages of replicated transactions Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent Transaction

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.144 Group Replication: Transactions Wait in queue Apply Commit Transaction Execute Broadcast Commit Stages of client transactions Stages of replicated transactions Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.148 Group Replication: Transactions Wait in queue Apply Commit Execute Broadcast Commit Stages of client transactions Stages of replicated transactions Transactions in Group Replication 1. No split-brain → Commit on > N/2 members → Tolerates < N/2 failures 2. Eventually consistent Transaction

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.162 Group Replication: Recovery Special logic when member joins the group: 1. Join group 2. Catch up with old transactions - and cache transactions that group applies during catch-up 3. Catch up with cached transactions

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.171 Server left the group Server re-joined the group Group Replication: Recovery Server finished replicating through recovery channel

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.176 Server left the group Server re-joined the group Group Replication: Recovery Server finished replicating through recovery channel Caught up and ONLINE!

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.179 Group Replication: Multi-Primary  Configure the group so that every member accepts writes SET @@global.group_replication_single_primary_mode = OFF; Multi-Primary ● Higher throughput ● Faster fail-over ● Limitations: conflict resolution

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.183 Group Replication: Multi-Primary mysqld mysqldmysqld Multi-Primary ● Higher throughput ● Faster fail-over ● Limitations: conflict resolution mysqld mysqldmysqld writes CPU connections Multi-Primary:  Is better if connections are a bottleneck  Is better if CPU is a bottleneck  Makes no difference if writes are the only bottleneck Single-Primary Multi-Primary

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.193 Group Replication: Multi-Primary MySQL Router Multi-Primary ● Higher throughput ● Faster fail-over ● Limitations: conflict resolution #2 #3mysqld #5 #6mysqld Single-Primary Multi-Primary MySQL Router

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.194 Group Replication: Multi-Primary MySQL Router Multi-Primary ● Higher throughput ● Faster fail-over ● Limitations: conflict resolution #2 #3mysqld #5 #6mysqld Single-Primary Multi-Primary MySQL Router

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.195 Group Replication: Multi-Primary MySQL Router Multi-Primary ● Higher throughput ● Faster fail-over ● Limitations: conflict resolution #2 #3 #5 #6 Single-Primary Multi-Primary MySQL Router I can’t hear #4. I can fail over to #5 now.

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.196 Group Replication: Multi-Primary MySQL Router Multi-Primary ● Higher throughput ● Faster fail-over ● Limitations: conflict resolution #2 #3 #5 #6 Single-Primary Multi-Primary MySQL Router I can’t hear #4. I can fail over to #5 now. I can’t hear #1. I’ll ask the group who is the new primary.

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.197 Group Replication: Multi-Primary MySQL Router Multi-Primary ● Higher throughput ● Faster fail-over ● Limitations: conflict resolution #2 #3 #5 #6 Single-Primary Multi-Primary MySQL Router I can’t hear #4. I can fail over to #5 now. I can’t hear #1. Let me wait 5 seconds, then agree with the group I can’t hear #1. Let me wait 5 seconds, then agree with the group I can’t hear #1. I’ll ask the group who is the new primary.

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.199 Group Replication: Multi-Primary Multi-Primary ● Higher throughput ● Faster fail-over ● Limitations: conflict resolution Two parallel transactions updating different rows = OK

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.202 Group Replication: Multi-Primary Multi-Primary ● Higher throughput ● Faster fail-over ● Limitations: conflict resolution Two parallel transactions updating different rows = OK Two sequential transactions updating the same rows = OK

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.208 Group Replication: Multi-Primary Multi-Primary ● Higher throughput ● Faster fail-over ● Limitations: conflict resolution Two parallel transactions updating different rows = OK Two sequential transactions updating the same rows = OK Two parallel transactions updating the same rows = CONFLICT The first to broadcast wins. The other one rolls back.

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.214 Group Replication: Multi-Primary Multi-Primary ● Higher throughput ● Faster fail-over ● Limitations: conflict resolution Two parallel transactions updating different rows = OK Two sequential transactions updating the same rows = OK Two parallel transactions updating the same rows = CONFLICT The first to broadcast wins. The other one rolls back. Beware of hotspots

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.215 Group Replication: Multi-Primary Multi-Primary ● Higher throughput ● Faster fail-over ● Limitations: conflict resolution Two parallel transactions updating different rows = OK Two sequential transactions updating the same rows = OK Two parallel transactions updating the same rows = CONFLICT The first to broadcast wins. The other one rolls back. Beware of hotspots

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.219 MySQL Group Replication mysqld mysqld mysqld MySQL Router App MySQL Connector MySQL Shell MySQL Shell: easy setup of InnoDB Cluster

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.220 MySQL Shell: easy setup of InnoDB Cluster  Admin API – Groups: configure, create, status – Members: configure, add, remove – Sandbox servers: easy-to-use test servers  Javascript, Python, SQL  NoSQL / DocStore API  Beautiful prompt :-)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.221 MySQL Shell: easy setup of InnoDB Cluster  Admin API – Groups: configure, create, status – Members: configure, add, remove – Sandbox servers: easy-to-use test servers  Javascript, Python, SQL  NoSQL / DocStore API  Beautiful prompt :-)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.222 MySQL Shell: setup InnoDB Cluster easily  Start shell: $ mysqlsh  Create three servers: MySQL JS dba.deploySandboxInstance(3310) MySQL JS dba.deploySandboxInstance(3311) MySQL JS dba.deploySandboxInstance(3312)  Connect to one server. Create a cluster with this member: MySQL JS connect root@localhost:3310 MySQL localhost:3310 ssl JS cluster = dba.createCluster(‘my_cluster’)  Add the other two members: MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3311’) MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3312’)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.223  Start shell: $ mysqlsh  Create three servers: MySQL JS dba.deploySandboxInstance(3310) MySQL JS dba.deploySandboxInstance(3311) MySQL JS dba.deploySandboxInstance(3312)  Connect to one server. Create a cluster with this member: MySQL JS connect root@localhost:3310 MySQL localhost:3310 ssl JS cluster = dba.createCluster(‘my_cluster’)  Add the other two members: MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3311’) MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3312’) MySQL Shell: setup InnoDB Cluster easily

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.224  Start shell: $ mysqlsh  Create three servers: MySQL JS dba.deploySandboxInstance(3310) MySQL JS dba.deploySandboxInstance(3311) MySQL JS dba.deploySandboxInstance(3312)  Connect to one server. Create a cluster with this member: MySQL JS connect root@localhost:3310 MySQL localhost:3310 ssl JS cluster = dba.createCluster(‘my_cluster’)  Add the other two members: MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3311’) MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3312’) MySQL Shell: setup InnoDB Cluster easily shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.225  Start shell: $ mysqlsh  Create three servers: MySQL JS dba.deploySandboxInstance(3310) MySQL JS dba.deploySandboxInstance(3311) MySQL JS dba.deploySandboxInstance(3312)  Connect to one server. Create a cluster with this member: MySQL JS connect root@localhost:3310 MySQL localhost:3310 ssl JS cluster = dba.createCluster(‘my_cluster’)  Add the other two members: MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3311’) MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3312’) MySQL Shell: setup InnoDB Cluster easily No sandboxes in production shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.226  Start shell: $ mysqlsh  Create three servers: MySQL JS dba.deploySandboxInstance(3310) MySQL JS dba.deploySandboxInstance(3311) MySQL JS dba.deploySandboxInstance(3312)  Connect to one server. Create a cluster with this member: MySQL JS connect root@localhost:3310 MySQL localhost:3310 ssl JS cluster = dba.createCluster(‘my_cluster’)  Add the other two members: MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3311’) MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3312’) MySQL Shell: setup InnoDB Cluster easily 3310 No sandboxes in production shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.227  Start shell: $ mysqlsh  Create three servers: MySQL JS dba.deploySandboxInstance(3310) MySQL JS dba.deploySandboxInstance(3311) MySQL JS dba.deploySandboxInstance(3312)  Connect to one server. Create a cluster with this member: MySQL JS connect root@localhost:3310 MySQL localhost:3310 ssl JS cluster = dba.createCluster(‘my_cluster’)  Add the other two members: MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3311’) MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3312’) MySQL Shell: setup InnoDB Cluster easily 3310 3311 No sandboxes in production shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.228  Start shell: $ mysqlsh  Create three servers: MySQL JS dba.deploySandboxInstance(3310) MySQL JS dba.deploySandboxInstance(3311) MySQL JS dba.deploySandboxInstance(3312)  Connect to one server. Create a cluster with this member: MySQL JS connect root@localhost:3310 MySQL localhost:3310 ssl JS cluster = dba.createCluster(‘my_cluster’)  Add the other two members: MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3311’) MySQL localhost:3310 ssl JS cluster.addInstance(‘root@localhost:3312’) MySQL Shell: setup InnoDB Cluster easily 3310 3311 3312 shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.233 MySQL Shell: setup InnoDB Cluster easily  Check the status of the group: MySQL localhost:3310 ssl JS cluster.status() { ... "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "localhost:3310": { "address": "localhost:3310", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "localhost:3311": { ... }, "localhost:3312": { ... } } ... }

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.234 MySQL Shell: setup InnoDB Cluster easily  Check the status of the group: MySQL localhost:3310 ssl JS cluster.status() { ... "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "localhost:3310": { "address": "localhost:3310", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "localhost:3311": { ... }, "localhost:3312": { ... } } ... }

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.235 MySQL Shell: setup InnoDB Cluster easily  Simulate a failure: MySQL localhost:3310 ssl JS dba.killSandboxInstance(3311) 3310 3311 3312 shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.236 MySQL Shell: setup InnoDB Cluster easily  Simulate a failure: MySQL localhost:3310 ssl JS dba.killSandboxInstance(3311) 3310 3311 3312 shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.237 MySQL Shell: setup InnoDB Cluster easily  Simulate a failure: MySQL localhost:3310 ssl JS dba.killSandboxInstance(3311) 3310 3312 shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.238 MySQL Shell: setup InnoDB Cluster easily  Simulate a failure: MySQL localhost:3310 ssl JS dba.killSandboxInstance(3311)  Check the status of the group: MySQL localhost:3310 ssl JS cluster.status() { ... "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", "topology": { "localhost:3310": { ... }, "localhost:3311": { ... "status": "UNREACHABLE" }, "localhost:3312": { ... } } ... } 3310 3312 shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.239 MySQL Shell: setup InnoDB Cluster easily 3310 3312 shell  Check the status again after a few seconds: MySQL localhost:3310 ssl JS cluster.status() { ... "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", "topology": { "localhost:3310": { ... }, "localhost:3311": { ... "status": "(MISSING)" }, "localhost:3312": { ... } } ... }

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.240 MySQL Shell: setup InnoDB Cluster easily  Start up the server again: MySQL localhost:3310 ssl JS dba.startSandboxInstance(3311) 3310 3312 shell

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.241 MySQL Shell: setup InnoDB Cluster easily 3310 3312 shell 3311  Start up the server again: MySQL localhost:3310 ssl JS dba.startSandboxInstance(3311)

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.242 MySQL Shell: setup InnoDB Cluster easily  Start up the server again: MySQL localhost:3310 ssl JS dba.startSandboxInstance(3311)  Check the status of the group: MySQL localhost:3310 ssl JS cluster.status() { ... "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", "topology": { "localhost:3310": { ... }, "localhost:3311": { ... "status": "RECOVERING" }, "localhost:3312": { ... } } ... } 3310 3312 shell 3311

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.243 MySQL Shell: setup InnoDB Cluster easily  Start up the server again: MySQL localhost:3310 ssl JS dba.startSandboxInstance(3311)  Check the status of the group: MySQL localhost:3310 ssl JS cluster.status() { ... "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", "topology": { "localhost:3310": { ... }, "localhost:3311": { ... "status": "RECOVERING" }, "localhost:3312": { ... } } ... } 3310 3312 shell 3311

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.244 MySQL Shell: setup InnoDB Cluster easily 3310 3312 shell 3311  Check the status after a few seconds: MySQL localhost:3310 ssl JS cluster.status() { ... "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "localhost:3310": { ... }, "localhost:3311": { ... "status": "ONLINE" }, "localhost:3312": { ... } } ... }

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.248 MySQL Router: direct queries to the right server 3310 3311 3312shell Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.249 MySQL Router: direct queries to the right server $ mysqlrouter --bootstrap root@localhost:3310 --directory /tmp/myrouter $ cd /tmp/myrouter $ ./start.sh ... Classic MySQL protocol connections to cluster 'cluster': - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 ... shell 3310 3311 3312 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.250 MySQL Router: direct queries to the right server $ mysqlrouter --bootstrap root@localhost:3310 --directory /tmp/myrouter $ cd /tmp/myrouter $ ./start.sh ... Classic MySQL protocol connections to cluster 'cluster': - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 ... shell MySQL Router 3310 3311 3312 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.251 MySQL Router: direct queries to the right server $ mysqlrouter --bootstrap root@localhost:3310 --directory /tmp/myrouter $ cd /tmp/myrouter $ ./start.sh ... Classic MySQL protocol connections to cluster 'cluster': - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 ... shell MySQL Router 3310 3311 3312 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.252 MySQL Router: direct queries to the right server $ mysqlrouter --bootstrap root@localhost:3310 --directory /tmp/myrouter $ cd /tmp/myrouter $ ./start.sh ... Classic MySQL protocol connections to cluster 'cluster': - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 ... shell MySQL Router 3310 3311 3312 6446 6447 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.253 MySQL Router: direct queries to the right server MySQL localhost:3310 ssl JS connect root@localhost:6446 MySQL localhost:6446 ssl JS sql MySQL localhost:6446 ssl SQL SELECT @@port; 3310 MySQL localhost:6446 ssl SQL CREATE DATABASE foo; MySQL localhost:6446 ssl SQL CREATE TABLE foo.bar(a INT PRIMARY KEY); shell MySQL Router 6446 6447 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server 3310 3311 3312

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.254 MySQL localhost:3310 ssl JS connect root@localhost:6446 MySQL localhost:6446 ssl JS sql MySQL localhost:6446 ssl SQL SELECT @@port; 3310 MySQL localhost:6446 ssl SQL CREATE DATABASE foo; MySQL localhost:6446 ssl SQL CREATE TABLE foo.bar(a INT PRIMARY KEY); MySQL Router: direct queries to the right server shell MySQL Router 6446 6447 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server 3310 3311 3312

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.259 MySQL Router: direct queries to the right server MySQL localhost:6446 ssl SQL connect root@localhost:6447 MySQL localhost:6447 ssl SQL SELECT @@port; 3311 MySQL localhost:6447 ssl SQL reconnect MySQL localhost:6447 ssl SQL SELECT @@port; 3312 MySQL localhost:6447 ssl SQL INSERT INTO foo.bar VALUES (1); ERROR: 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement shell MySQL Router 6446 6447 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server 3310 3311 3312

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.260 MySQL Router: direct queries to the right server shell MySQL Router 6446 6447 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server 3310 3311 3312 MySQL localhost:6446 ssl SQL connect root@localhost:6447 MySQL localhost:6447 ssl SQL SELECT @@port; 3311 MySQL localhost:6447 ssl SQL reconnect MySQL localhost:6447 ssl SQL SELECT @@port; 3312 MySQL localhost:6447 ssl SQL INSERT INTO foo.bar VALUES (1); ERROR: 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.261 MySQL Router: direct queries to the right server shell MySQL Router 6446 6447 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server 3310 3311 3312 MySQL localhost:6446 ssl SQL connect root@localhost:6447 MySQL localhost:6447 ssl SQL SELECT @@port; 3311 MySQL localhost:6447 ssl SQL reconnect MySQL localhost:6447 ssl SQL SELECT @@port; 3312 MySQL localhost:6447 ssl SQL INSERT INTO foo.bar VALUES (1); ERROR: 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.262 MySQL localhost:6446 ssl SQL connect root@localhost:6447 MySQL localhost:6447 ssl SQL SELECT @@port; 3311 MySQL localhost:6447 ssl SQL reconnect MySQL localhost:6447 ssl SQL SELECT @@port; 3312 MySQL localhost:6447 ssl SQL INSERT INTO foo.bar VALUES (1); ERROR: 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement MySQL Router: direct queries to the right server shell MySQL Router 6446 6447 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server 3310 3311 3312

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.265 MySQL Router: direct queries to the right server MySQL localhost:6447 ssl SQL js MySQL localhost:3310 ssl JS dba.killSandboxInstance(3312) MySQL localhost:6446 ssl JS sql MySQL localhost:6447 ssl SQL SELECT 1 + 1; ERROR: 2013 (HY000): Lost connection to MySQL server during query The global session got disconnected.. Attempting to reconnect to 'mysql://root@localhost:6447'.. The global session was successfully reconnected. MySQL localhost:6447 ssl SQL SELECT @@port; 3311 shell MySQL Router 6446 6447 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server 3310 3311 3312

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.266 MySQL localhost:6447 ssl SQL js MySQL localhost:3310 ssl JS dba.killSandboxInstance(3312) MySQL localhost:6446 ssl JS sql MySQL localhost:6447 ssl SQL SELECT 1 + 1; ERROR: 2013 (HY000): Lost connection to MySQL server during query The global session got disconnected.. Attempting to reconnect to 'mysql://root@localhost:6447'.. The global session was successfully reconnected. MySQL localhost:6447 ssl SQL SELECT @@port; 3311 MySQL Router: direct queries to the right server shell MySQL Router 6446 6447 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server 3310 3311 3312

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.267 MySQL localhost:6447 ssl SQL js MySQL localhost:3310 ssl JS dba.killSandboxInstance(3312) MySQL localhost:6446 ssl JS sql MySQL localhost:6447 ssl SQL SELECT 1 + 1; ERROR: 2013 (HY000): Lost connection to MySQL server during query The global session got disconnected.. Attempting to reconnect to 'mysql://root@localhost:6447'.. The global session was successfully reconnected. MySQL localhost:6447 ssl SQL SELECT @@port; 3311 MySQL Router: direct queries to the right server shell MySQL Router 3310 3311 6446 6447 3312 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.268 MySQL localhost:6447 ssl SQL js MySQL localhost:3310 ssl JS dba.killSandboxInstance(3312) MySQL localhost:6446 ssl JS sql MySQL localhost:6447 ssl SQL SELECT 1 + 1; ERROR: 2013 (HY000): Lost connection to MySQL server during query The global session got disconnected.. Attempting to reconnect to 'mysql://root@localhost:6447'.. The global session was successfully reconnected. MySQL localhost:6447 ssl SQL SELECT @@port; 3311 MySQL Router: direct queries to the right server shell MySQL Router 6446 6447 Router Example 1. Start from a 3 member group 2. Connect Router to the group 3. Connect Shell to Router 4. Crash Server 3310 3311

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.275 Example code: Shell + Group Replication  Start Shell: $ mysqlsh  Get help: MySQL JS help MySQL JS help dba.deploySandboxInstance etc.  Create a group of three sandbox servers: MySQL JS dba.deploySandboxInstance(3310) MySQL JS dba.deploySandboxInstance(3311) MySQL JS dba.deploySandboxInstance(3312) MySQL JS connect root@localhost:3310 MySQL localhost:3310 ssl JS cluster = dba.createCluster('my_cluster') MySQL localhost:3310 ssl JS cluster.addInstance('root@localhost:3311') MySQL localhost:3310 ssl JS cluster.addInstance('root@localhost:3312') MySQL localhost:3310 ssl JS cluster.status()  Kill and restart a member: MySQL localhost:3310 ssl JS dba.killSandboxInstance(3311) MySQL localhost:3310 ssl JS cluster.status() MySQL localhost:3310 ssl JS dba.startSandboxInstance(3311) MySQL localhost:3310 ssl JS cluster.status()

Copyright © 2018, Oracle and/or its affiliates. All rights reserved.276 Example code: Shell + Group Replication + Router  Start Router: $ mysqlrouter --bootstrap root@localhost:3310 --directory /tmp/myrouter $ cd /tmp/myrouter && ./start.sh  Connect to Router’s read/write port; check that it is writable: MySQL localhost:3310 ssl JS connect root@localhost:6446 MySQL localhost:6446 ssl JS sql MySQL localhost:6446 ssl SQL SELECT @@port; MySQL localhost:6446 ssl SQL CREATE DATABASE foo; MySQL localhost:6446 ssl SQL CREATE TABLE foo.bar(a INT PRIMARY KEY);  Reconnect to Router’s read-only port; reconnect and see that it switches to another member; check that it is read-only: MySQL localhost:6446 ssl SQL connect root@localhost:6447 MySQL localhost:6447 ssl SQL SELECT @@port; MySQL localhost:6447 ssl SQL reconnect MySQL localhost:6447 ssl SQL SELECT @@port; MySQL localhost:6447 ssl SQL INSERT INTO foo.bar VALUES (1);  Kill server and watch Router fail over: MySQL localhost:6447 ssl SQL js MySQL localhost:6447 ssl JS dba.killSandboxInstance(port); MySQL localhost:6447 ssl JS sql MySQL localhost:6447 ssl SQL SELECT 1 + 1; MySQL localhost:6447 ssl SQL SELECT @@port; Note down the result from here... … and use it here

High Availability in MySQL 8 using InnoDB Cluster

More Related Content

What's hot

Similar to High Availability in MySQL 8 using InnoDB Cluster

Recently uploaded

In this document

High Availability in MySQL 8 using InnoDB Cluster