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.
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
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
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
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
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
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
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
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
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
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
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
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
MySQL InnoDB ReplicaSet: JS> rs.addInstance('localhost:3320') LAB 2: MySQL InnoDB ReplicaSet adding a member Copyright @ 2023 Oracle and/or its affiliates. 43
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
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
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
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
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
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
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
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
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
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
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
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
[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
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
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
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 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 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
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
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
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
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
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
@ 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
@ 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
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
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
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
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
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
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
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
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> 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
- 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
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> 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
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
- 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
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
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
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
- 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
miu_crescent
syoshie
xerial
taichinakamura
shimacos
spatial_ai_network
rayuron
fnifni
bigmuramura
matthewlujp
kutsushitaneko
yokawasa
samlambert
danielanewman
chriscoyier
bryan
pedronauck
tammielis
malarkey
jmmastey
moore
sstephenson
paulrobertlloyd
aki_iinuma
![LAB 3: MySQL Router JS> \c root@localhost:3310 [3310] JS> rs=dba.getReplicaSet()](https://files.speakerdeck.com/presentations/f4513863ee0e4d0cb663c461fc58052b/slide_72.jpg){kind=link}
![LAB 3: MySQL Router - Change Primary (planned) [3310] JS>](https://files.speakerdeck.com/presentations/f4513863ee0e4d0cb663c461fc58052b/slide_73.jpg){kind=link}
![We dissolve the MySQL InnoDB ReplicaSet: On the Primary: [3310]](https://files.speakerdeck.com/presentations/f4513863ee0e4d0cb663c461fc58052b/slide_88.jpg){kind=link}
![LAB 5: MySQL InnoDB Cluster JS> \c root@localhost:3310 [3310] JS>](https://files.speakerdeck.com/presentations/f4513863ee0e4d0cb663c461fc58052b/slide_96.jpg){kind=link}
![LAB 5: MySQL InnoDB Cluster - change Primary [3310] JS>](https://files.speakerdeck.com/presentations/f4513863ee0e4d0cb663c461fc58052b/slide_97.jpg){kind=link}
![LAB 5: MySQL InnoDB Cluster - failure [3310] JS> dba.killSandboxInstance(3320)](https://files.speakerdeck.com/presentations/f4513863ee0e4d0cb663c461fc58052b/slide_98.jpg){kind=link}
![LAB 5: MySQL InnoDB Cluster - failure [3310] JS> dba.killSandboxInstance(3320)](https://files.speakerdeck.com/presentations/f4513863ee0e4d0cb663c461fc58052b/slide_99.jpg){kind=link}
![LAB 5: MySQL InnoDB Cluster - mode [3310] JS> dba.startSandboxInstance(3320)](https://files.speakerdeck.com/presentations/f4513863ee0e4d0cb663c461fc58052b/slide_100.jpg){kind=link}
![LAB 5: MySQL InnoDB Cluster - mode [3310] JS> dba.startSandboxInstance(3320)](https://files.speakerdeck.com/presentations/f4513863ee0e4d0cb663c461fc58052b/slide_101.jpg){kind=link}
