MySQL InnoDB ClusterSet - Disaster Recovery solution for InnoDB Cluster deployments

Slide 1

Slide 1 text

MySQL InnoDB ClusterSet Disaster Recovery solution for InnoDB Cluster deployments Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz

Slide 2

Slide 2 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 2 Me, Myself & I  MySQL Geek  Addicted to MySQL for 15+ years  Playing with databases for 20+ years  MySQL Writer, Blogger and Speaker  Also: DBA, Consultant, Architect, Trainer, ...  MySQL Cloud Principal Solutions Architect EMEA at Oracle  Stay up to date!  Blog: www.dasini.net/blog/en  Linkedin: www.linkedin.com/in/olivier-dasini/  Twitter: @freshdaz Olivier DASINI

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

IT Disasters and Outages: Examples Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 5-Hour computer outage cost $150 million. The airline eventually canceled about 1,000 flights on the day of the outage and grounded an additional 1,000 flights over the following days. Millions of websites offline after fire at French cloud services firm. The fire is expected to cost the company more than €105 million. Tens of thousands of passengers were stranded in cities around the world due to cancellation of about 130 flights and the delay of 200. Millions of bank customers were unable to access online accounts. The bank took almost 2 days to recover and get back to normal functioning. 5

Slide 6

Slide 6 text

Slide 7

Slide 7 text

Concepts – RTO & RPO • RTO: Recovery Time Objective • How long does it take to recover from a single failure • RPO: Recovery Point Objective • How much data can be lost when a failure occurs Business Requirements Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Types of Failure ● High Availability: Single Server Failure, Network Partition ● Disaster Recovery: Full Region/Network Failure ● Human Error: Little Bobby Tables 7

Slide 8

Slide 8 text

• Setting up Replication topology was usually done manually, taking many steps • Including user management, restoring backups, configuring replication... • MySQL only offered the technical pieces, leaving it up to the user to setup an (always customized) architecture • Even required other software ... bringing lot's of work for DBA's and experts, who spent their time automating and integrating their customized architecture MySQL Database Architectures – Past: Manual Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 8

Slide 9

Slide 9 text

2020 MySQL InnoDB ReplicaSet • Easy to use! • 'Classic', 'asynchronous' Replication based Solution, fully integrated • MySQL Shell to provide a powerful interface that helps in automating and integrating all components • InnoDB CLONE to automatically provision members, fully integrated in InnoDB • MySQL Router • MySQL Server MySQL Database Architectures – Present: Solutions! RPO != 0 RTO = minutes (manual failover) Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 9

Slide 10

Slide 10 text

2016 MySQL InnoDB Cluster • Easy to use! • MySQL Group Replication: Automatic membership changes, network partition handling, consistency... • MySQL Shell to provide a powerful interface that helps in automating and integrating all components • InnoDB CLONE to automatically provision members, fully integrated in InnoDB • MySQL Router • MySQL Server MySQL Database Architectures – Present: Solutions! RPO = 0 RTO = seconds (automatic failover) Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 10

Slide 11

Slide 11 text

Slide 12

Slide 12 text

One or more REPLICA MySQL InnoDB Clusters attached to a PRIMARY MySQL InnoDB Cluster High Availability (Failure within a Region) • RPO=0 • RTO=seconds (automatic failover) Disaster Recovery (Region Failure) • RPO !=0 • RTO = mintues or more (manual failover) • No write performance impact Features • Easy to use! • Familiar interface and usability mysqlsh, CLONE,… • Add/remove nodes/clusters online • Router integration, no need to reconfigure application if the topology changes MySQL InnoDB ClusterSet Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 12

Slide 13

Slide 13 text

Slide 14

Slide 14 text

Slide 15

Slide 15 text

Slide 16

Slide 16 text

Environment, 3 regions, 3 mysql databases each, listening on different ports: ● BRU: – localhost:3331 – localhost:3332 – localhost:3333 ● LIS: – localhost:4441 – localhost:4442 – localhost:4443 ● ROM: – localhost:5551 – localhost:5552 – localhost:5553 ClusterSet Environment Example Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 16

Slide 17

Slide 17 text

Start with setting up a regular MySQL InnoDB Cluster: $ mysqlsh root@localhost:3331 JS> \sql create schema sbtest; JS> bru = dba.createCluster("BRU") JS> bru.addInstance('localhost:3332') JS> bru.addInstance('localhost:3333') JS> bru.status() Create MySQL InnoDB Cluster Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 17

Slide 18

Slide 18 text

JS> clusterset = bru.createClusterSet('clusterset') A new ClusterSet will be created based on the Cluster 'BRU'. * Validating Cluster 'BRU' for ClusterSet compliance. * Creating InnoDB ClusterSet 'clusterset' on 'BRU'... * Updating metadata... ClusterSet successfully created. Use ClusterSet.createReplicaCluster() to add Replica Clusters to it. Create ClusterSet Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 18

Slide 19

Slide 19 text

JS> clusterset.status() { "clusters": { "BRU": { "clusterRole": "PRIMARY", "globalStatus": "OK", "primary": "127.0.0.1:3331" } }, "domainName": "clusterset", "globalPrimaryInstance": "127.0.0.1:3331", "primaryCluster": "BRU", "status": "HEALTHY", "statusText": "All Clusters available." } Check ClusterSet Status Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 19

Slide 20

Slide 20 text

• Supports incremental recovery (binlog) & full recovery (CLONE) JS> lis = clusterset.createReplicaCluster('localhost:4441', 'LIS') JS> lis.addInstance('localhost:4442') JS> lis.addInstance('localhost:4443') JS> lis.status() Add Replica Cluster Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 20

Slide 21

Slide 21 text

JS> clusterset.status() { "clusters": { "BRU": { "clusterRole": "PRIMARY", "globalStatus": "OK", "primary": "127.0.0.1:3331" }, "LIS": { "clusterRole": "REPLICA", "clusterSetReplicationStatus": "OK", "globalStatus": "OK" } }, "domainName": "clusterset", "globalPrimaryInstance": "127.0.0.1:3331", "primaryCluster": "BRU", "status": "HEALTHY", "statusText": "All Clusters available." } Check ClusterSet Status Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Or, to get everything in one command: JS> clusterset.status({extended:1}) 21

Slide 22

Slide 22 text

JS> rom = clusterset.createReplicaCluster( 'localhost:5551', 'ROM') JS> rom.addInstance('localhost:5552') JS> rom.addInstance('localhost:5553’) JS> rom.status() JS> clusterset.status() Add second Replica Cluster Copyright © 2022, Oracle and/or its affiliates. All rights reserved. { "clusters": { "ROM": { "clusterRole": "REPLICA", "clusterSetReplicationStatus": "OK", "globalStatus": "OK" }, "BRU": { "clusterRole": "PRIMARY", "globalStatus": "OK", "primary": "127.0.0.1:3331" }, "LIS": { "clusterRole": "REPLICA", "clusterSetReplicationStatus": "OK", "globalStatus": "OK" } }, "domainName": "clusterset", "globalPrimaryInstance": "127.0.0.1:3331", "primaryCluster": "BRU", "status": "HEALTHY", "statusText": "All Clusters available." } 22

Slide 23

Slide 23 text

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Router target modes: • Follow the PRIMARY cluster – Writes & Reads go to the PRIMARY Cluster • Connect to the configured target cluster – When target cluster is PRIMARY ● Write port opens – When target cluster is not PRIMARY: ● Only read traffic is open ● Writes are blocked Features: • Configuration can be changed online using mysqlsh • Configurable per Router instance • Deploy 2 types of routers: – Target PRIMARY to send writes to the primary node – Define target cluster to keep read traffic local • INVALIDATED clusters can still be used for read traffic (configurable) Router Integration Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 25

Slide 26

Slide 26 text

Slide 27

Slide 27 text

Slide 28

Slide 28 text

$ mysqlrouter --bootstrap [email protected]:3331 --account=myRouter1 --name='' --force ● [email protected]:3331 is the URI-like connection string for any member server instance that is online in the InnoDB ClusterSet deployment ● myRouter1 is the user name for a MySQL Router administrator account that was set up using the cluster.setupRouterAccount() command on the primary cluster ● --name can be used to assign a non-default name to the MySQL Router instance, to make it easily identifiable in the output from InnoDB ClusterSet status commands ● --force is required if you are bootstrapping MySQL Router again for an existing InnoDB Cluster where it was previously bootstrapped Integrating MySQL Router With InnoDB ClusterSet - Bootstrap Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 28 https://dev.mysql.com/doc/mysql-shell/8.0/en/innodb-clusterset-router.html

Slide 29

Slide 29 text

JS> clusterset.listRouters() … JS> clusterset.routingOptions() { "domainName": "clusterset", "global": { "invalidated_cluster_policy": "drop_all", "target_cluster": "primary" }, "routers": { "": {} } } Integrating MySQL Router With InnoDB ClusterSet - Check Options Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 29 https://dev.mysql.com/doc/mysql-shell/8.0/en/innodb-clusterset-router.html

Slide 30

Slide 30 text

Set the global routing policy for the target_cluster: JS> clusterset.setRoutingOption('target_cluster', 'primary') Set a routing policy for an instance: JS> clusterset.setRoutingOption('', 'target_cluster', 'BRU') JS> clusterset.setRoutingOption('', 'target_cluster', 'primary') Change the invalidated_cluster_policy: When the target_cluster cluster is invalidated, should it still accept reads, knowing that they will be stale reads or should all traffic be dropped? JS> clusterset.setRoutingOption('', 'invalidated_cluster_policy', 'accept_ro') JS> clusterset.setRoutingOption('', 'invalidated_cluster_policy', 'drop_all') target_cluster is also configurable with mysqlrouter --bootstrap using --conf-target-cluster or –-conf-target-cluster-by-name Clear a routing policy for an instance: JS> clusterset.setRoutingOption('', 'target_cluster', null) Integrating MySQL Router With InnoDB ClusterSet - Configuration Options Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 30 https://dev.mysql.com/doc/mysql-shell/8.0/en/innodb-clusterset-router.html

Slide 31

Slide 31 text

Slide 32

Slide 32 text

mysqlsh> bru.setPrimaryInstance('localhost:3332') Setting instance 'localhost:3332' as the primary instance of cluster 'BRU'... Instance '127.0.0.1:3331' was switched from PRIMARY to SECONDARY. Instance '127.0.0.1:3332' was switched from SECONDARY to PRIMARY. Instance '127.0.0.1:3333' remains SECONDARY. WARNING: The cluster internal session is not the primary member anymore. For cluster management operations please obtain a fresh cluster handle using dba.getCluster(). The instance 'localhost:3332' was successfully elected as primary. Change PRIMARY member in PRIMARY cluster Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 32

Slide 33

Slide 33 text

mysqlsh> lis.setPrimaryInstance('localhost:4442') Setting instance 'localhost:4442' as the primary instance of cluster 'LIS'... Instance '127.0.0.1:4442' was switched from SECONDARY to PRIMARY. Instance '127.0.0.1:4443' remains SECONDARY. Instance '127.0.0.1:4441' was switched from PRIMARY to SECONDARY. WARNING: The cluster internal session is not the primary member anymore. For cluster management operations please obtain a fresh cluster handle using dba.getCluster(). The instance 'localhost:4442' was successfully elected as primary. Change PRIMARY member in REPLICA cluster Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 33

Slide 34

Slide 34 text

mysqlsh> clusterset.setPrimaryCluster('LIS') Switching the primary cluster of the clusterset to 'LIS' - Verifying clusterset status -- Checking cluster BRU - Cluster 'BRU' is available -- Checking cluster ROM - Cluster 'ROM' is available -- Checking cluster LIS - Cluster 'LIS' is available - Refreshing replication account of demoted cluster - Synchronizing transaction backlog at 127.0.0.1:4442 - Updating metadata - Updating topology -- Changing replication source of 127.0.0.1:3331 to 127.0.0.1:4442 -- Changing replication source of 127.0.0.1:3333 to 127.0.0.1:4442 -- Changing replication source of 127.0.0.1:3332 to 127.0.0.1:4442 - Acquiring locks in replicaset instances -- Pre-synchronizing SECONDARIES -- Acquiring global lock at PRIMARY & SECONDARIES - Synchronizing remaining transactions at promoted primary - Updating replica clusters -- Changing replication source of 127.0.0.1:5552 to 127.0.0.1:4442 -- Changing replication source of 127.0.0.1:5553 to 127.0.0.1:4442 -- Changing replication source of 127.0.0.1:5551 to 127.0.0.1:4442 Cluster 'LIS' was promoted to PRIMARY of the clusterset. The PRIMARY instance is '127.0.0.1:4442' Switchover - Changing PRIMARY Cluster - setPrimaryCluster() Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 34

Slide 35

Slide 35 text

mysqlsh> \c root@localhost:3331 mysqlsh> clusterset=dba.getClusterSet() mysqlsh> clusterset.forcePrimaryCluster('BRU') Failing-over primary cluster of the clusterset to 'BRU' - Verifying primary cluster status None of the instances of the PRIMARY cluster 'LIS' could be reached. - Verifying clusterset status -- Checking cluster BRU Cluster 'BRU' is available -- Checking cluster ROM Cluster 'ROM' is available -- Checking whether target cluster has the most recent GTID set - Promoting cluster 'BRU' - Updating metadata -- Changing replication source of 127.0.0.1:5552 to 127.0.0.1:3331 -- Changing replication source of 127.0.0.1:5553 to 127.0.0.1:3331 -- Changing replication source of 127.0.0.1:5551 to 127.0.0.1:3331 PRIMARY cluster failed-over to 'BRU'. The PRIMARY instance is '127.0.0.1:3331' Former PRIMARY cluster was INVALIDATED, transactions that were not yet replicated may be lost. Failover to another Cluster Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 35

Slide 36

Slide 36 text

Slide 37

Slide 37 text

Slide 38

Slide 38 text

PRIMARY Cluster PRIMARY member Crash/Partition - Automatic Copyright © 2022, Oracle and/or its affiliates. All rights reserved. • When there is newly elected PRIMARY member in a cluster • Works on failures in PRIMARY and REPLICA clusters Automatic Handling of InnoDB Cluster state changes • Asynchronous replication is automatically reconfigured after primary change 38

Slide 39

Slide 39 text

REPLICA Cluster PRIMARY member Crash/Partition - Automatic Copyright © 2022, Oracle and/or its affiliates. All rights reserved. • When there is newly elected PRIMARY member in a cluster • Works on failures in PRIMARY and REPLICA clusters Automatic Handling of InnoDB Cluster state changes • Asynchronous replication is automatically reconfigured after primary change 39

Slide 40

Slide 40 text

Changing Primary - Change Primary Cluster on Healthy System Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Switchover • One command that does it all: setPrimaryCluster() • Asynchronous replication channels between clusters are automatically reconfigured • Consistency guaranteed • All routers will immediately redirect if needed (depending on target mode) 40

Slide 41

Slide 41 text

Changing Primary - Change Primary Cluster on Healthy System Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 41 Switchover • One command that does it all: setPrimaryCluster() • Asynchronous replication channels between clusters are automatically reconfigured • Consistency guaranteed • All routers will immediately redirect if needed (depending on target mode)

Slide 42

Slide 42 text

Datacenter Crash/Partition Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 42 Failover to another Cluster • One command to invalidate the PRIMARY cluster and promote a new PRIMARY cluster: forcePrimaryCluster() • Other REPLICA clusters replication will be recon Split Brain Warning • Local Routers that cannot connect to other clusters will not learn about new topology • If datacenter is network partitioned, it will continue to operate as PRIMARY

Slide 43

Slide 43 text

Datacenter Crash/Partition - forcePrimaryCluster() Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Failover to another Cluster • One command to invalidate the PRIMARY cluster and promote a new PRIMARY cluster: forcePrimaryCluster() • Other REPLICA clusters replication will be recon Split Brain Warning • Local Routers that cannot connect to other clusters will not learn about new topology • If datacenter is network partitioned, it will continue to operate as PRIMARY 43

Slide 44

Slide 44 text

Datacenter Crash/Partition – Router Integration Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Router Integration • Routers will learn about new topology and redirect traffic • Routers that come back, will learn about new topology and abandon the old PRIMARY Cluster (e.g failed DC comes back online) 44

Slide 45

Slide 45 text

Group Replication Crash/Partition Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 45 Router Integration When GR is offline: • Network partition • No quorum • Full cluster lost (e.g. power outage) Failover to another Cluster • One command to invalidate the PRIMARY cluster and promote a new PRIMARY cluster: forcePrimaryCluster() • Router instances will follow PRIMARY (depending on target mode)

Slide 46

Slide 46 text

Group Replication Crash/Partition - forcePrimaryCluster() & Router Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Router Integration When GR is offline: • Network partition • No quorum • Full cluster lost (e.g. power outage) Failover to another Cluster • One command to invalidate the PRIMARY cluster and promote a new PRIMARY cluster: forcePrimaryCluster() • Router instances will follow PRIMARY (depending on target mode) 46

Slide 47

Slide 47 text

Slide 48

Slide 48 text

Slide 49

Slide 49 text

MySQL InnoDB Cluster • RPO = 0 • RTO = Seconds High Availability – Single Region Copyright © 2022, Oracle and/or its affiliates. All rights reserved. MySQL InnoDB ReplicaSet • RPO ! = 0 • RTO = Minutes + (manual failover) 🙂 Best write performance 😕 Manual Failover 49

Slide 50

Slide 50 text

MySQL InnoDB Cluster • RPO = 0 • RTO = Seconds Disaster Recovery – Cross Data Center Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 🙂 Multi-Region Multi-Primary 😕 3 DC 😕 Requires very stable WAN 😕 Write performance affected by latency between DCs 50

Slide 51

Slide 51 text

MySQL InnoDB Cluster ClusterSet • RPO != 0 • RTO = Minutes + (manual failover) Disaster Recovery - Multi Region Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 😕 Write performance (no sync to other region required) 😕 Higher RTO: Manual failover 😕 RPO != 0 when region fails MySQL InnoDB ClusterSet 51 🙂 RPO = 0 & RTO = seconds within Region (HA)

Slide 52

Slide 52 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 52  MySQL InnoDB ClusterSet Documentation https://dev.mysql.com/doc/mysql-shell/8.0/en/innodb-clusterset.html  Tutoriel – Déployer MySQL 8.0 InnoDB Cluster http://dasini.net/blog/2019/09/03/tutoriel-deployer-mysql-8-0-innodb-cluster-09-2019/  MySQL InnoDB Cluster – Easy Recovering and provisioning http://dasini.net/blog/2019/09/10/mysql-innodb-cluster-easy-recovering-and-provisioning/  MySQL InnoDB Cluster – Recovering and provisioning with MySQL Enterprise Backup http://dasini.net/blog/2019/07/11/mysql-innodb-cluster-recovering-and-provisioning-with-mysql-enterprise-backup/  MySQL InnoDB Cluster Documentation https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-userguide.html  MySQL Group Replication Documentation https://dev.mysql.com/doc/refman/8.0/en/group-replication.html  MySQL InnoDB ReplicaSet https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-replicaset.html  MySQL Router HA: Pacemaker https://lefred.be/content/mysql-router-ha-with-pacemaker/  MySQL Router HA: Keepalived https://lefred.be/content/mysql-router-ha-with-keepalived/  MySQL Router HA: DNS SRV https://www.slideshare.net/Grypyrg/mysql-connectors-8019-dns-srv  MySQL Shell https://dev.mysql.com/doc/mysql-shell/8.0/en/ Resources – ClusterSet | Cluster | ReplicaSet | Router | Shell High Availability & Disaster Recovery solutions for MySQL

Slide 53

Slide 53 text

Slide 54

Slide 54 text

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 54 Merci! Q&R Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz

Slide 55

Slide 55 text

No content