MySQL Architectures - Design the Right Solution for Your Needs
Hands-on tutorial about the new MySQL 8.0 easy solutions to deploy the right architecture you need for your database.
From a single instance to a multiple DC High Availability and Disaster Recovery.
All automated and integrated.
Before we start: Ensure that you have: a laptop (or a computer, or a cloud instance) MySQL 8.0.33 installed on it MySQL Shell 8.0.33 installed on it MySQL Router 8.0.33 installed on it Visual Studio Code and MySQL Shell extention [optional] If not, now, it's the right time to do it before we start... Copyright @ 2023 Oracle and/or its affiliates. 2
@lefred MySQL Evangelist using MySQL since version 3.20 devops believer likes living in h ps://lefred.be Frédéric Descamps Copyright @ 2023 Oracle and/or its affiliates. 6
Housekeeping and FAQ Slides will be available later today ! We will have 1 break Don't fall behind, if you have a question or a problem do not hesitate to reach out to me! When you see this image (look on the top right of this slide), it's your turn. Yes, you need to do all labs, each lab builds on the previous lab You will get a pdf with the labs Copyright @ 2023 Oracle and/or its affiliates. 8
Windows Users - Installation For Microsoft Windows users, please use MySQL Installer to install the required products: Copyright @ 2023 Oracle and/or its affiliates. 11
Windows Users - Installation (2) For Microsoft Windows users, please use MySQL Installer to install the required products: Copyright @ 2023 Oracle and/or its affiliates. 12
Windows Users - Installation (3) For Microsoft Windows users, please use MySQL Installer to install the required products: Copyright @ 2023 Oracle and/or its affiliates. 13
We will create a second instance and setup Asynchronous replication. We will also deploy MySQL Router. MySQL architectures: Agenda Copyright @ 2023 Oracle and/or its affiliates. 16
We will then upgrade to MySQL InnoDB Cluster and add a third MySQL instance. MySQL architectures: Agenda Copyright @ 2023 Oracle and/or its affiliates. 17
MySQL architectures: Agenda To nish with the ultimate High Availability and Disaster Recovery solution: Copyright @ 2023 Oracle and/or its affiliates. 18
You should have already downloaded and installed MySQL and MySQL Shell on your system. During this tutorial, we will use MySQL Shell sandbox capabilities to simulate multiple servers. Let's start the rst hands-on LAB ! MySQL Single Instance Copyright @ 2023 Oracle and/or its affiliates. 20
LAB 1: deployment and connection to MySQL You can use MySQL Shell or MySQL Shell for Visual Studio Code. We need to rst launch MySQL Shell and deploy our rst sandbox. This is the information we will use: port: 3310 root password: MySQL123 We will use the dba.deploySandboxInstance() method: JS> dba.deploySandboxInstance(3310, {password: 'MySQL123'}) Copyright @ 2023 Oracle and/or its affiliates. 22
LAB 1: deployment and connection to MySQL Now we can connect to our sandbox MySQL instance: JS> \c root@localhost:3310 Copyright @ 2023 Oracle and/or its affiliates. root / MySQL123 port: 3310 24
LAB 1: deployment and connection to MySQL Now we can connect to our sandbox MySQL instance: JS> \c root@localhost:3310 Copyright @ 2023 Oracle and/or its affiliates. root / MySQL123 port: 3310 24
LAB 1: inserting data We will now create a new table and add data to it: SQL> CREATE DATABASE perconalive; SQL> \u perconalive SQL> CREATE TABLE t1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, port INT); SQL> INSERT into t1 (port) VALUES (@@port); Copyright @ 2023 Oracle and/or its affiliates. 27
LAB 1: inserting data We will now create a new table and add data to it: SQL> CREATE DATABASE perconalive; SQL> \u perconalive SQL> CREATE TABLE t1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, port INT); SQL> INSERT into t1 (port) VALUES (@@port); SQL> SELECT * FROM t1; +----+---------------------+------+ | id | timestamp | port | +----+---------------------+------+ | 1 | 2023-04-24 19:21:32 | 3310 | +----+---------------------+------+ 1 row in set (0.0010 sec) Copyright @ 2023 Oracle and/or its affiliates. 27
MySQL InnoDB ReplicaSet - our needs Now, we need to have a solution in case our MySQL instance crashes and cannot be restarted. We also need, when possible, to o load some read queries to another instance. Our RPO can be greater than 0. Our RTO can be de ned in several minutes. Copyright @ 2023 Oracle and/or its affiliates. 30
MySQL InnoDB ReplicaSet - our needs Now, we need to have a solution in case our MySQL instance crashes and cannot be restarted. We also need, when possible, to o load some read queries to another instance. Our RPO can be greater than 0. Our RTO can be de ned in several minutes. RPO: Recovery Point Objective (how much data can be lost) RTO: Recovery Time Objective (how long to recover) Copyright @ 2023 Oracle and/or its affiliates. 30
Based on the popular MySQL Asynchronous Replication Failover is Manual Fully integrated with MySQL Shell and MySQL Router Data provisioning included (CLONE) Recommended for unstable networks RPO != 0 & RTO = minutes MySQL InnoDB ReplicaSet Copyright @ 2023 Oracle and/or its affiliates. 31
We use again MySQL Shell to deploy another instance: JS> dba.deploySandboxInstance(3320, {password: 'MySQL123'}) LAB 2: deployment of a second MySQL sandbox Copyright @ 2023 Oracle and/or its affiliates. 33
LAB 2: MySQL instances con guration We need to con gure the instances so that they are ready to be part of a MySQL InnoDB ReplicaSet: JS> dba.configureReplicaSetInstance("root@localhost:3310") JS> dba.configureReplicaSetInstance("root@localhost:3320") Copyright @ 2023 Oracle and/or its affiliates. 34
LAB 2: MySQL instances con guration We need to con gure the instances so that they are ready to be part of a MySQL InnoDB ReplicaSet: JS> dba.configureReplicaSetInstance("root@localhost:3310") JS> dba.configureReplicaSetInstance("root@localhost:3320") Copyright @ 2023 Oracle and/or its affiliates. sandboxes are already con gured 34
LAB 2: MySQL instances con guration < > Copyright @ 2023 Oracle and/or its affiliates. When I am not using a sandbox instance, MySQL Shell warns me about the root user having access only from localhost. It proposes 4 choices, what should I do? 35
LAB 2: MySQL instances con guration < > Copyright @ 2023 Oracle and/or its affiliates. When I am not using a sandbox instance, MySQL Shell warns me about the root user having access only from localhost. It proposes 4 choices, what should I do? The recommendation is to create a dedicated user to manage the solution on all instances with the same credentials (option 2). Check the example on the next slide ! 35
We now create the MySQL InnoDB ReplicaSet when connected to our rst instance: JS> rs=dba.createReplicaSet( 'myreplicaset') Since 8.0.33 you can specify a Replication SSL Mode: JS> rs=dba.createReplicaSet( 'myreplicaset', {"replicationSslMode": "REQUIRED"}) LAB 2: MySQL InnoDB ReplicaSet creation Copyright @ 2023 Oracle and/or its affiliates. 37
LAB 2: MySQL InnoDB ReplicaSet creation Explore the rs object created and get its status: JS> rs.status() Copyright @ 2023 Oracle and/or its affiliates. 39
LAB 2: MySQL InnoDB ReplicaSet status < > Copyright @ 2023 Oracle and/or its affiliates. When I created my ReplicaSet, I forgot to assign it to a variable... What can I do ? 42
LAB 2: MySQL InnoDB ReplicaSet status < > Copyright @ 2023 Oracle and/or its affiliates. When I created my ReplicaSet, I forgot to assign it to a variable... What can I do ? Just assing it again using the dba.ReplicaSet() method: rs=dba.getReplicaSet() 42
It's time to add our second MySQL instance to our MySQL InnoDB ReplicaSet: JS> rs.addInstance('localhost:3320') LAB 2: MySQL InnoDB ReplicaSet adding a member Copyright @ 2023 Oracle and/or its affiliates. 43
LAB 3: MySQL InnoDB ReplicaSet - test In this lab, we will try to read and write data on both MySQL instances. Then we will bootstrap MySQL Router and test it. Copyright @ 2023 Oracle and/or its affiliates. 48
LAB 3: MySQL InnoDB ReplicaSet - test In this lab, we will try to read and write data on both MySQL instances. Then we will bootstrap MySQL Router and test it. Connect to both instances on two di erent terminals (or tabs if you are using VS Code) and list the records in the table perconalive.t1: Copyright @ 2023 Oracle and/or its affiliates. 48
LAB 3: MySQL InnoDB ReplicaSet - test In this lab, we will try to read and write data on both MySQL instances. Then we will bootstrap MySQL Router and test it. Connect to both instances on two di erent terminals (or tabs if you are using VS Code) and list the records in the table perconalive.t1: Copyright @ 2023 Oracle and/or its affiliates. 48
LAB 3: MySQL InnoDB ReplicaSet - test write Now let's try to write on both instances: SQL> insert into perconalive.t1 (port) values (@@port); Copyright @ 2023 Oracle and/or its affiliates. 49
Primary Secondary LAB 3: MySQL InnoDB ReplicaSet - test write Now let's try to write on both instances: SQL> insert into perconalive.t1 (port) values (@@port); Copyright @ 2023 Oracle and/or its affiliates. 49
MySQL Router is very easy to con gure, you only need to use the bootstrap command: $ sudo mysqlrouter --bootstrap \ root@localhost:3310 --user mysqlrouter $ sudo systemctl start mysqlrouter It's also possible to create a MySQL user account for Router (optional): JS> rs.setupRouterAccount("router") LAB 3: MySQL Router Copyright @ 2023 Oracle and/or its affiliates. 50
MySQL Router is very easy to con gure, you only need to use the bootstrap command: $ sudo mysqlrouter --bootstrap \ root@localhost:3310 --user mysqlrouter $ sudo systemctl start mysqlrouter It's also possible to create a MySQL user account for Router (optional): JS> rs.setupRouterAccount("router") LAB 3: MySQL Router Copyright @ 2023 Oracle and/or its affiliates. Windows users, info coming 50
LAB 3: MySQL Router - Windows Open a command terminal and bootstrap MySQL Router: C:\Users\fred\>"C:\Program Files\MySQL\MySQL Router 8.0\bin\mysqlrouter.exe" --bootstrap root@localhost:3310 --directory=./router Copyright @ 2023 Oracle and/or its affiliates. 52
LAB 3: MySQL Router - Windows Open a command terminal and bootstrap MySQL Router: C:\Users\fred\>"C:\Program Files\MySQL\MySQL Router 8.0\bin\mysqlrouter.exe" --bootstrap root@localhost:3310 --directory=./router Copyright @ 2023 Oracle and/or its affiliates. Mac OSX users, info coming 52
And in the new terminal we run: C:\WINDOWS\system32>"C:\Program Files\MySQL\MySQL Router 8.0\bin\ mysqlrouter.exe" --install-service-manual -c "C:\Users\fred\router\mysqlrouter.conf" LAB 3: MySQL Router - Windows We need to install the Windows Service to start/stop MySQL Router. We need to open a new command terminal but as an administrator: Copyright @ 2023 Oracle and/or its affiliates. 53
And in the new terminal we run: C:\WINDOWS\system32>"C:\Program Files\MySQL\MySQL Router 8.0\bin\ mysqlrouter.exe" --install-service-manual -c "C:\Users\fred\router\mysqlrouter.conf" LAB 3: MySQL Router - Windows We need to install the Windows Service to start/stop MySQL Router. We need to open a new command terminal but as an administrator: Copyright @ 2023 Oracle and/or its affiliates. one single line command ! 53
LAB 3: MySQL Router - Mac OSX For Mac OS X users, you can easily bootstrap the cluster with the following command in a folder of your choice (~/router_tutorial/): $ mysqlrouter -B root@localhost:3310 -d ~/router_tutorial Copyright @ 2023 Oracle and/or its affiliates. 55
LAB 3: MySQL Router - Mac OSX For Mac OS X users, you can easily bootstrap the cluster with the following command in a folder of your choice (~/router_tutorial/): $ mysqlrouter -B root@localhost:3310 -d ~/router_tutorial To start it: $ ~/router_tutorial/start.sh Copyright @ 2023 Oracle and/or its affiliates. 55
LAB 3: MySQL Router - Mac OSX For Mac OS X users, you can easily bootstrap the cluster with the following command in a folder of your choice (~/router_tutorial/): $ mysqlrouter -B root@localhost:3310 -d ~/router_tutorial To start it: $ ~/router_tutorial/start.sh And to stop it: $ ~/router_tutorial/stop.sh Copyright @ 2023 Oracle and/or its affiliates. 55
LAB 3: MySQL Router We will now connect MySQL Shell via MySQL Router. We use the following ports: Read/Write: 6446 Read/Only: 6447 We will now use 3 terminals, all with MySQL Shell. Copyright @ 2023 Oracle and/or its affiliates. 57
LAB 3: MySQL Router - Change Primary (failure) JS> dba.killSandboxInstance(3320) [6446] SQL> insert into perconalive.t1 (port) values (@@port); ERROR: 2013 (HY000): Lost connection to MySQL server during query The global session got disconnected.. Attempting to reconnect to 'mysql://root@localhost:6446'.............. The global session could not be reconnected automatically. Please use '\reconnect' instead to manually reconnect. [6447] SQL> select * from perconalive.t1; 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. +----+---------------------+------+ | id | timestamp | port | +----+---------------------+------+ | 1 | 2023-04-24 19:21:32 | 3310 | ... | 4 | 2023-05-05 14:59:08 | 3320 | +----+---------------------+------+ Copyright @ 2023 Oracle and/or its affiliates. RW: 6446 RO: 6447 60
MySQL InnoDB Cluster - our needs Our business requires to have RPO=0 and to have automatic failover in case of problem. We still need, when possible, to o load some read queries to another instance and eventually write on multiple MySQL instances simultaneously. Of course, our network is reliable and the latency is small. Copyright @ 2023 Oracle and/or its affiliates. 63
Based on Group Replication Failover is Automatic Fully integrated with MySQL Shell and MySQL Router Data provisioning included (CLONE) Writing to multiple nodes is possible (not the default) RPO = 0 & RTO = seconds MySQL InnoDB Cluster Copyright @ 2023 Oracle and/or its affiliates. 64
MySQL Group Replication GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of the Replicated Database State Machine theory GR's protocol is based on Paxos GR allows to write on all Group Members (cluster nodes) simultaneously while retaining consistency GR implements con ict detection and resolution GR allows automatic distributed recovery Supported on all MySQL platforms !! Linux, Windows, Solaris, OSX, FreeBSD Copyright @ 2023 Oracle and/or its affiliates. 65
MySQL Group Replication This means that with Group Replication and MySQL InnoDB Cluster: it's not longer necessary to handle server fail-over manually 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 @ 2023 Oracle and/or its affiliates. 66
MySQL Group Replication This means that with Group Replication and MySQL InnoDB Cluster: it's not longer necessary to handle server fail-over manually 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 This is a solution for High Availability ! Copyright @ 2023 Oracle and/or its affiliates. 66
LAB 4: MySQL InnoDB Cluster In lab4, we will rst deploy a new MySQL instance (sandbox). Then we will "upgrade" (replace) our MySQL InnoDB ReplicaSet to InnoDB Cluster. After, we will join the new instance. Finally, we will recon gure MySQL Router for the cluster. Copyright @ 2023 Oracle and/or its affiliates. 68
LAB 4: MySQL InnoDB Cluster In lab4, we will rst deploy a new MySQL instance (sandbox). Then we will "upgrade" (replace) our MySQL InnoDB ReplicaSet to InnoDB Cluster. After, we will join the new instance. Finally, we will recon gure MySQL Router for the cluster. Let's restart the sandbox (3320) we killed at the end of lab 3: JS> dba.startSandboxInstance(3320) Copyright @ 2023 Oracle and/or its affiliates. 68
LAB 4: MySQL InnoDB Cluster < > Copyright @ 2023 Oracle and/or its affiliates. If there is an error, where can I find more info ? With MySQL 8.0, you can parse the error log directly from SQL. Try this: select * from performance_schema.error_log order by logged desc limit 10; 69
Deploy a new MySQL instance next to our current MySQL InnoDB ReplicaSet JS> dba.deploySandboxInstance(3330, {password: 'MySQL123'}) LAB 4: MySQL InnoDB Cluster Copyright @ 2023 Oracle and/or its affiliates. 70
We dissolve the MySQL InnoDB ReplicaSet: On the Primary: [3310] JS> dba.dropMetadataSchema() On the Secondary: [3320] SQL> stop replica; [3320] SQL> reset replica all; [3320] SQL> drop database if exists mysql_innodb_cluster_metadata; LAB 4: MySQL InnoDB Cluster - dissolve Copyright @ 2023 Oracle and/or its affiliates. 71
We now create the new MySQL InnoDB Cluster: On the Primary: [3310] JS> cluster=dba.createCluster( 'mycluster') [3310] JS> cluster.addInstance( 'localhost:3320') [3310] JS> cluster.status() LAB 4: MySQL InnoDB Cluster - creation Copyright @ 2023 Oracle and/or its affiliates. 72
We add the third instance to our new MySQL InnoDB Cluster. For automatic HA, we need to have at least 3 nodes and an odd amount of nodes is recommended. Adding the new instance [3310] JS> dba.configureInstance( 'root@localhost:3330') [3310] JS> cluster.addInstance( 'localhost:3330') [3310] JS> cluster.status() LAB 4: MySQL InnoDB Cluster - creation Copyright @ 2023 Oracle and/or its affiliates. 73
LAB 5: MySQL InnoDB Cluster We will now connect again MySQL Shell via MySQL Router. We use the following ports (same as for ReplicaSet): Read/Write: 6446 Read/Only: 6447 And we will still use 3 terminals with MySQL Shell. Copyright @ 2023 Oracle and/or its affiliates. 78
LAB 5: MySQL InnoDB Cluster - mode < > Copyright @ 2023 Oracle and/or its affiliates. I'm trying to write multiple times... even making new connections but it seems all writes are made on same instance, I expected to write on all nodes ! 83
LAB 5: MySQL InnoDB Cluster - mode < > Copyright @ 2023 Oracle and/or its affiliates. I'm trying to write multiple times... even making new connections but it seems all writes are made on same instance, I expected to write on all nodes ! All nodes have the possibility to receive writes, the default policy it to always use the first one. Try to change the strategy for [routing:bootstrap_rw] to routing_strategy=round-robin in router's config file and restart it. 83
MySQL InnoDB ClusterSet High Availability is perfect ! But this is within a region... what can we do in case of datacenter issue ? And how do we deal with all the topoly changes ? And data provisioning ? Copyright @ 2023 Oracle and/or its affiliates. 85
LAB6: MySQL InnoDB ClusterSet - creation Before the creation of the MySQL InnoDB ClusterSet we need to switch back our Cluster to Single Primary mode: JS> \c root@localhost:3310 [3310] JS> cluster = dba.getCluster() [3310] JS> cluster.switchToSinglePrimaryMode() Now we can create our new ClusterSet. This is a method of a cluster object: [3310] JS> cs=cluster.createClusterSet('mydomain') Copyright @ 2023 Oracle and/or its affiliates. 90
This illustrates what we just created. Let's have a look at the status of our new ClusterSet: LAB6: MySQL InnoDB ClusterSet - creation Copyright @ 2023 Oracle and/or its affiliates. 91
LAB6: MySQL InnoDB ClusterSet Like for the other status() methods, ClusterSet supports the extended option. Please try the following commands: [3310] JS> cs.status({extended: 1}) [3310] JS> cs.status({extended: 2}) [3310] JS> cs.status({extended: 3}) Copyright @ 2023 Oracle and/or its affiliates. 92
LAB6: MySQL InnoDB ClusterSet Like for the other status() methods, ClusterSet supports the extended option. Please try the following commands: [3310] JS> cs.status({extended: 1}) [3310] JS> cs.status({extended: 2}) [3310] JS> cs.status({extended: 3}) Get more info with cs.help('status') or \? clusterset.status Copyright @ 2023 Oracle and/or its affiliates. 92
It's time to create the Replica Cluster. We start by adding one instance from the other region: JS> cluster2= cs.createReplicaCluster( 'localhost:4420', 'mycluster2') LAB6: MySQL InnoDB ClusterSet - Replica Cluster Copyright @ 2023 Oracle and/or its affiliates. 93
We need to add the other 2 instances to our Secondary Cluster: JS> cluster2.addInstance( 'localhost:4410') JS> cluster2.addInstance( 'localhost:4430') LAB6: MySQL InnoDB ClusterSet - Replica Cluster Copyright @ 2023 Oracle and/or its affiliates. 95
LAB7: MySQL InnoDB ClusterSet - Router What about the MySQL Router ? We already have one bootstrapped and running ? Copyright @ 2023 Oracle and/or its affiliates. 97
LAB7: MySQL InnoDB ClusterSet - Routing MySQL InnoDB ClusterSet provides the possibility to de ne routing policies per Router being registered in the ClusterSet. Those Routing Policies can be changed ONLINE within MySQL Shell. In a ClusterSet you can deploy 2 types of Routers: targeting the PRIMARY to send writes to the PRIMARY cluster (and the PRIMARY node in it) targeting a speci c cluster to keep tra c local for example (writes not allowed) It's also possible to de ne a policiy for INVALIDATED clusters. Copyright @ 2023 Oracle and/or its affiliates. 99
LAB7: MySQL InnoDB ClusterSet - Routing Options We can use the routingOptions() method of the ClusterSet object to list the current routing policies: Copyright @ 2023 Oracle and/or its affiliates. 100
LAB7: MySQL InnoDB ClusterSet - Routing Options We can use the routingOptions() method of the ClusterSet object to list the current routing policies: Let's change the Policy of our MySQL Router to only send tra c to our local datacenter in region 2: JS> cs.setRoutingOption('dell::system', 'target_cluster', 'mycluster2') Routing option 'target_cluster' successfully updated in router 'dell::system'. Copyright @ 2023 Oracle and/or its affiliates. Region 1 3310, 3320, 3330 Region 2 4410, 4420, 4430 100
SQL> \c root@localhost:6447 SQL> select *, @@port `read from` from perconalive.t1 order by id desc limit 1; +----+---------------------+------+-----------+ | id | timestamp | port | read from | +----+---------------------+------+-----------+ | 10 | 2023-05-05 15:29:12 | 3310 | 4410 | +----+---------------------+------+-----------+ 1 row in set (0.0006 sec) SQL> \c root@localhost:6447 SQL> select *, @@port `read from` from perconalive.t1 order by id desc limit 1; +----+---------------------+------+-----------+ | id | timestamp | port | read from | +----+---------------------+------+-----------+ | 10 | 2023-05-05 15:29:12 | 3310 | 4420 | +----+---------------------+------+-----------+ 1 row in set (0.0006 sec) LAB7: MySQL InnoDB ClusterSet - Routing Options Now try to connect with MySQL Shell on port 6446 and 6447: SQL> \c root@localhost:6446 As the routing target is not PRIMARY (or not the Primary cluster) writes are not allowed and the connection is refused ! Copyright @ 2023 Oracle and/or its affiliates. cs.setRoutingOption('dell::system', 'target_cluster', 'mycluster2') 101
SQL> \c root@localhost:6447 SQL> select *, @@port `read from` from perconalive.t1 order by id desc limit 1; +----+---------------------+------+-----------+ | id | timestamp | port | read from | +----+---------------------+------+-----------+ | 10 | 2023-05-05 15:29:12 | 3310 | 4410 | +----+---------------------+------+-----------+ 1 row in set (0.0006 sec) SQL> \c root@localhost:6447 SQL> select *, @@port `read from` from perconalive.t1 order by id desc limit 1; +----+---------------------+------+-----------+ | id | timestamp | port | read from | +----+---------------------+------+-----------+ | 10 | 2023-05-05 15:29:12 | 3310 | 4420 | +----+---------------------+------+-----------+ 1 row in set (0.0006 sec) LAB7: MySQL InnoDB ClusterSet - Routing Options Now try to connect with MySQL Shell on port 6446 and 6447: SQL> \c root@localhost:6446 As the routing target is not PRIMARY (or not the Primary cluster) writes are not allowed and the connection is refused ! Copyright @ 2023 Oracle and/or its affiliates. cs.setRoutingOption('dell::system', 'target_cluster', 'mycluster2') Don´t forget to set back the Routing Policy to PRIMARY: JS> cs.setRoutingOption('dell::system', 'target_cluster', 'primary') Routing option 'target_cluster' successfully updated in router 'dell::system'. 101
SQL> \c root@localhost:6446 SQL> insert into perconalive.t1 (port) values (@@port); SQL> select *, @@port `read from` from perconalive.t1 order by id desc limit 1; +----+---------------------+------+-----------+ | id | timestamp | port | read from | +----+---------------------+------+-----------+ | 11 | 2023-05-07 17:57:49 | 4420 | 4420 | +----+---------------------+------+-----------+ SQL> \c root@localhost:6447 SQL> select *, @@port `read from` from perconalive.t1 order by id desc limit 1; +----+---------------------+------+-----------+ | id | timestamp | port | read from | +----+---------------------+------+-----------+ | 11 | 2023-05-07 17:57:49 | 4420 | 4410 | +----+---------------------+------+-----------+ LAB8: MySQL InnoDB ClusterSet - Region Switch Now we will see how we can switch the PRIMARY role to another region. First as a planned operation and then after a huge failure. Planned Region Switch JS> cs.setPrimaryCluster('mycluster2') Copyright @ 2023 Oracle and/or its affiliates. 103
Now we can check the status: LAB8: MySQL InnoDB ClusterSet - Region Switch Unplanned Region Switch Simulate the issue: JS [3310]> dba.killSandboxInstance(4410);dba.killSandboxInstance(4420);dba.killSandboxInstance(4430) Copyright @ 2023 Oracle and/or its affiliates. 105
LAB8: MySQL InnoDB ClusterSet - Region Switch Unplanned Region Switch We need to force the available secondary region to take over: JS [3310]> cs.forcePrimaryCluster('mycluster') Copyright @ 2023 Oracle and/or its affiliates. 106
SQL> \c root@localhost:6446 SQL> insert into perconalive.t1 (port) values (@@port); SQL> select *, @@port `read from` from perconalive.t1 order by id desc limit 1; +----+---------------------+------+-----------+ | id | timestamp | port | read from | +----+---------------------+------+-----------+ | 12 | 2023-05-07 18:06:17 | 3330 | 3330 | +----+---------------------+------+-----------+ SQL> \c root@localhost:6447 SQL> select *, @@port `read from` from perconalive.t1 order by id desc limit 1; +----+---------------------+------+-----------+ | id | timestamp | port | read from | +----+---------------------+------+-----------+ | 12 | 2023-05-07 18:06:17 | 3330 | 3320 | +----+---------------------+------+-----------+ SQL> \reconnect SQL> select *, @@port `read from` from perconalive.t1 order by id desc limit 1; +----+---------------------+------+-----------+ | id | timestamp | port | read from | +----+---------------------+------+-----------+ | 12 | 2023-05-07 18:06:17 | 3330 | 3310 | +----+---------------------+------+-----------+ LAB8: MySQL InnoDB ClusterSet - Region Switch Unplanned Region Switch We can perform again read and writes via MySQL Router: Copyright @ 2023 Oracle and/or its affiliates. 107
LAB8: MySQL InnoDB ClusterSet - Region Switch Unplanned Region Switch In case of network partition, the DBA has also the possibility to fence the tra c to avoid a split-brain situation. You have 3 fencing operations available: cluster.fenceWrites(): stop write tra c to a Primary Cluster of a ClusterSet. cluster.unfenceWrites(): resume write tra c. cluster.fenceAllTraf c(): fences a cluster from all tra c. Only rebootClusterFromCompleteOutage can put back tra c to a cluster fenced with such method. Copyright @ 2023 Oracle and/or its affiliates. 108
Now we can check the status: LAB8: MySQL InnoDB ClusterSet - Region Switch Unplanned Region Switch When the o ine region is back: JS [3310]> dba.startSandboxInstance(4410);dba.startSandboxInstance(4420);dba.startSandboxInstance(4430) Copyright @ 2023 Oracle and/or its affiliates. 109
JS> \c root@localhost:4410 JS > mycluster2=dba.rebootClusterFromCompleteOutage() The cluster won't rejoin automatically the ClusterSet as it was invalidated. JS> dba.getClusterSet().status() LAB8: MySQL InnoDB ClusterSet - Region Switch Unplanned Region Switch Now we need to connect to one instance of the cluster that failed and restart the cluster: Copyright @ 2023 Oracle and/or its affiliates. 110
LAB8: MySQL InnoDB ClusterSet - Region Switch We need to rejoin the Cluster to the ClusterSet to x it: JS> dba.getClusterSet().rejoinCluster('mycluster2') Copyright @ 2023 Oracle and/or its affiliates. 112
LAB9: MySQL InnoDB ClusterSet - Complete Outage What to do if everything was abruptly stopped? How can we put back the system ? JS> dba.killSandboxInstance(4410);dba.killSandboxInstance(4420);dba.killSandboxInstance(4430) JS> dba.killSandboxInstance(3310);dba.killSandboxInstance(3320);dba.killSandboxInstance(3330 Copyright @ 2023 Oracle and/or its affiliates. 114
LAB9: MySQL InnoDB ClusterSet - Complete Outage What to do if everything was abruptly stopped? How can we put back the system ? JS> dba.killSandboxInstance(4410);dba.killSandboxInstance(4420);dba.killSandboxInstance(4430) JS> dba.killSandboxInstance(3310);dba.killSandboxInstance(3320);dba.killSandboxInstance(3330 Let's start the instances again: JS> dba.startSandboxInstance(3310);dba.startSandboxInstance(3320);dba.startSandboxInstance(3330) JS> dba.startSandboxInstance(4410);dba.startSandboxInstance(4420);dba.startSandboxInstance(4430) Copyright @ 2023 Oracle and/or its affiliates. 114
And we are back healthy ! LAB9: MySQL InnoDB ClusterSet - Complete Outage We need to do the same for the second cluster. We start the process by connecting to an instance belonging to the second cluster: JS> \c root@localhost:4410 JS> cluster2=dba.rebootClusterFromCompleteOutage('mycluster2') Copyright @ 2023 Oracle and/or its affiliates. 119
lefred
gl_tsukasa
autifyhq
gaelvaroquaux
mottox2
charity
ewolff
kentosuzuki
oracle4engineer
.png){kind=icon}
3playmarketing
hankehly
linedevth
tmm1
keavy
revolveconf
denniskardys
bluesmoon
bkeepers
scottboms
smashingmag
holman
hatefulcrawdad
mraible