MySQL InnoDB ClusterSet
Disaster Recovery solution for InnoDB Cluster deployments
Olivier Dasini
MySQL Cloud Principal Solutions Architect EMEA
[Link]@[Link]
Blogs : [Link]/blog/en
: [Link]/blog/fr
Linkedin: [Link]/in/olivier-dasini
Twitter : @freshdaz
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, ... Olivier DASINI
MySQL Cloud Principal Solutions Architect EMEA at Oracle
Stay up to date!
Blog: [Link]/blog/en
Linkedin: [Link]/in/olivier-dasini/
Twitter: @freshdaz
2 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
IT Disasters & Outages: Primary Causes
On-site power failure is the biggest cause of significant outages
3 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
IT Disasters & Outages: Costs are Rising
Over half had experienced an outage costing more than $100,000
4 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
IT Disasters and Outages: Examples
5-Hour computer outage cost $150 million. The Millions of websites offline after fire at French
airline eventually canceled about 1,000 flights on cloud services firm. The fire is expected to cost
the day of the outage and grounded an additional the company more than €105 million.
1,000 flights over the following days.
Tens of thousands of passengers were stranded Millions of bank customers were unable to access
in cities around the world due to cancellation of online accounts. The bank took almost
about 130 flights and the delay of 200. 2 days to recover and get back to normal functioning.
5 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
MySQL Database Architectures
Past, Present & Future
6 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Business Requirements
Concepts – RTO & RPO Types of Failure
• RTO: Recovery Time Objective ●
High Availability: Single Server Failure, Network Partition
• How long does it take to recover from a single failure ●
Disaster Recovery: Full Region/Network Failure
• RPO: Recovery Point Objective ●
Human Error: Little Bobby Tables
• How much data can be lost when a failure occurs
7 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
MySQL Database Architectures – Past: Manual
• 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
8 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
MySQL Database Architectures – Present: Solutions!
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
RPO != 0
RTO = minutes (manual failover)
9 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
MySQL Database Architectures – Present: Solutions!
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
RPO = 0
RTO = seconds (automatic failover)
10 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
MySQL InnoDB ClusterSet
Disaster tolerance for InnoDB Cluster deployments
11 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
MySQL InnoDB ClusterSet
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
12 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
MySQL InnoDB ClusterSet – 3 Data Centers
13 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
MySQL InnoDB ClusterSet – Not every Cluster has to be 3 nodes
14 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
MySQL InnoDB ClusterSet
Configuration Commands
15 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
ClusterSet Environment Example
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
16 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Create MySQL InnoDB Cluster
Start with setting up a regular MySQL InnoDB Cluster:
$ mysqlsh root@localhost:3331
JS> \sql create schema sbtest;
JS> bru = [Link]("BRU")
JS> [Link]('localhost:3332')
JS> [Link]('localhost:3333')
JS> [Link]()
17 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Create ClusterSet
JS> clusterset = [Link]('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 [Link]() to add Replica Clusters
to it.
<ClusterSet:clusterset>
18 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Check ClusterSet Status
JS> [Link]()
{
"clusters": {
"BRU": {
"clusterRole": "PRIMARY",
"globalStatus": "OK",
"primary": "[Link]:3331"
} },
"domainName": "clusterset",
"globalPrimaryInstance": "[Link]:3331",
"primaryCluster": "BRU",
"status": "HEALTHY",
"statusText": "All Clusters available."
}
19 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Add Replica Cluster
• Supports incremental recovery (binlog) & full recovery (CLONE)
JS> lis = [Link]('localhost:4441', 'LIS')
JS> [Link]('localhost:4442')
JS> [Link]('localhost:4443')
JS> [Link]()
20 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Check ClusterSet Status
JS> [Link]()
{
"clusters": {
"BRU": {
"clusterRole": "PRIMARY",
"globalStatus": "OK",
"primary": "[Link]:3331" Or, to get everything in one command:
},
"LIS": {
JS> [Link]({extended:1})
"clusterRole": "REPLICA",
"clusterSetReplicationStatus": "OK",
"globalStatus": "OK"
}
},
"domainName": "clusterset",
"globalPrimaryInstance": "[Link]:3331",
"primaryCluster": "BRU",
"status": "HEALTHY",
"statusText": "All Clusters available."
}
21 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Add second Replica Cluster {
"clusters": {
"ROM": {
JS> rom = [Link]( "clusterRole": "REPLICA",
"clusterSetReplicationStatus": "OK",
'localhost:5551',
"globalStatus": "OK"
'ROM') }, "BRU": {
"clusterRole": "PRIMARY",
"globalStatus": "OK",
JS> [Link]('localhost:5552') "primary": "[Link]:3331"
}, "LIS": {
JS> [Link]('localhost:5553’) "clusterRole": "REPLICA",
"clusterSetReplicationStatus": "OK",
"globalStatus": "OK"
JS> [Link]() }
JS> [Link]() },
"domainName": "clusterset",
"globalPrimaryInstance": "[Link]:3331",
"primaryCluster": "BRU",
"status": "HEALTHY",
"statusText": "All Clusters available."
}
22 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
MySQL InnoDB ClusterSet
Router Integration
23 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Router Integration
Configure your application to connect to
a local MySQL Router to connect to
the ClusterSet
24 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Router Integration
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)
25 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Router Integration – 3 Data Centers
26 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
MySQL InnoDB ClusterSet
Router Integration Commands
27 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Integrating MySQL Router With InnoDB ClusterSet - Bootstrap
$ mysqlrouter --bootstrap icadmin@[Link]:3331 --account=myRouter1
--name='<router name>' --force
●
icadmin@[Link]: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
[Link]() 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
[Link]
28 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Integrating MySQL Router With InnoDB ClusterSet - Check Options
JS> [Link]()
…
JS> [Link]()
{
"domainName": "clusterset",
"global": {
"invalidated_cluster_policy": "drop_all",
"target_cluster": "primary"
},
"routers": {
"<router name>": {}
}
}
[Link]
29 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Integrating MySQL Router With InnoDB ClusterSet - Configuration Options
Set the global routing policy for the target_cluster:
JS> [Link]('target_cluster', 'primary')
Set a routing policy for an instance:
JS> [Link]('<router name>', 'target_cluster', 'BRU')
JS> [Link]('<router name>', '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> [Link]('<router name>', 'invalidated_cluster_policy', 'accept_ro')
JS> [Link]('<router name>', '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> [Link]('<router name>', 'target_cluster', null)
[Link]
30 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
MySQL InnoDB ClusterSet
Managing Commands
31 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Change PRIMARY member in PRIMARY cluster
mysqlsh> [Link]('localhost:3332')
Setting instance 'localhost:3332' as the primary instance of cluster 'BRU'...
Instance '[Link]:3331' was switched from PRIMARY to SECONDARY.
Instance '[Link]:3332' was switched from SECONDARY to PRIMARY.
Instance '[Link]: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 [Link]().
The instance 'localhost:3332' was successfully elected as primary.
32 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Change PRIMARY member in REPLICA cluster
mysqlsh> [Link]('localhost:4442')
Setting instance 'localhost:4442' as the primary instance of cluster 'LIS'...
Instance '[Link]:4442' was switched from SECONDARY to PRIMARY.
Instance '[Link]:4443' remains SECONDARY.
Instance '[Link]: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 [Link]().
The instance 'localhost:4442' was successfully elected as primary.
33 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Switchover - Changing PRIMARY Cluster - setPrimaryCluster()
mysqlsh> [Link]('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 [Link]:4442
- Updating metadata
- Updating topology
-- Changing replication source of [Link]:3331 to [Link]:4442
-- Changing replication source of [Link]:3333 to [Link]:4442
-- Changing replication source of [Link]:3332 to [Link]: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 [Link]:5552 to [Link]:4442
-- Changing replication source of [Link]:5553 to [Link]:4442
-- Changing replication source of [Link]:5551 to [Link]:4442
Cluster 'LIS' was promoted to PRIMARY of the clusterset. The PRIMARY instance is '[Link]:4442'
34 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Failover to another Cluster
mysqlsh> \c root@localhost:3331
mysqlsh> clusterset=[Link]()
mysqlsh> [Link]('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 [Link]:5552 to [Link]:3331
-- Changing replication source of [Link]:5553 to [Link]:3331
-- Changing replication source of [Link]:5551 to [Link]:3331
PRIMARY cluster failed-over to 'BRU'. The PRIMARY instance is '[Link]:3331'
Former PRIMARY cluster was INVALIDATED, transactions that were not yet replicated may be lost.
35 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Removing a Cluster from the ClusterSet
mysqlsh> [Link]('LIS')
36 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
MySQL InnoDB ClusterSet
Scenarios
37 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
PRIMARY Cluster PRIMARY member Crash/Partition - Automatic
• 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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
REPLICA Cluster PRIMARY member Crash/Partition - Automatic
• 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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Changing Primary - Change Primary Cluster on Healthy System
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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Changing Primary - Change Primary Cluster on Healthy System
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)
41 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Datacenter Crash/Partition
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
42 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Datacenter Crash/Partition - forcePrimaryCluster()
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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Datacenter Crash/Partition – Router Integration
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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Group Replication Crash/Partition
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)
45 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Group Replication Crash/Partition - forcePrimaryCluster() & Router
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 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Business Requirements
What is the right solution for you?
47 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Business Requirements
Concepts – RTO & RPO Types of Failure
• RTO: Recovery Time Objective ●
High Availability: Single Server Failure, Network Partition
• How long does it take to recover from a single failure ●
Disaster Recovery: Full Region/Network Failure
• RPO: Recovery Point Objective ●
Human Error: Little Bobby Tables
• How much data can be lost when a failure occurs
48 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
High Availability – Single Region
MySQL InnoDB Cluster MySQL InnoDB ReplicaSet
• RPO = 0 • RPO ! = 0
• RTO = Seconds • RTO = Minutes + (manual failover)
🙂 Best write performance
😕 Manual Failover
49 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Disaster Recovery – Cross Data Center
MySQL InnoDB Cluster
• RPO = 0
• RTO = Seconds
🙂 Multi-Region Multi-Primary
😕 3 DC
😕 Requires very stable WAN
😕 Write performance affected by latency between DCs
50 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Disaster Recovery - Multi Region
MySQL InnoDB Cluster ClusterSet MySQL InnoDB ClusterSet
• RPO != 0
• RTO = Minutes + (manual failover)
🙂 RPO = 0 & RTO = seconds within Region (HA)
😕 Write performance (no sync to other region required)
😕 Higher RTO: Manual failover
😕 RPO != 0 when region fails
51 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Resources – ClusterSet | Cluster | ReplicaSet | Router | Shell
High Availability & Disaster Recovery solutions for MySQL
MySQL InnoDB ClusterSet Documentation
[Link]
Tutoriel – Déployer MySQL 8.0 InnoDB Cluster
[Link]
MySQL InnoDB Cluster – Easy Recovering and provisioning
[Link]
MySQL InnoDB Cluster – Recovering and provisioning with MySQL Enterprise Backup
[Link]
MySQL InnoDB Cluster Documentation
[Link]
MySQL Group Replication Documentation
[Link]
MySQL InnoDB ReplicaSet
[Link]
MySQL Router HA: Pacemaker
[Link]
MySQL Router HA: Keepalived
[Link]
MySQL Router HA: DNS SRV
[Link]
MySQL Shell
[Link]
52 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Follow us on Social Media
53 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.
Merci!
Q&R
Olivier Dasini
MySQL Cloud Principal Solutions Architect EMEA
[Link]@[Link]
Blogs : [Link]/blog/en
: [Link]/blog/fr
Linkedin: [Link]/in/olivier-dasini
Twitter : @freshdaz
54 Copyright © 2022, Oracle and/or its affiliates. All rights reserved.