Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQL Architectures - Design the Right Solution for Your Needs

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.

lefred

May 25, 2023
Tweet

More Decks by lefred

Other Decks in Technology

Transcript

  1. Kenny Gryp & Frédéric Descamps Product Manager & Community Manager

    Oracle - MySQL MySQL Architectures Design the Right Solution for Your Needs
  2. 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
  3. Kenny Gryp & Frédéric Descamps Product Manager & Community Manager

    Oracle - MySQL MySQL Architectures Design the Right Solution for Your Needs
  4. @gryp MySQL Product Manager likes « ☕  . (

     6 ⛰ Kenny Gryp Copyright @ 2023 Oracle and/or its affiliates. 5
  5. @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
  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
  7. MySQL Shell is now included in Visual Studio Code: Copyright

    @ 2023 Oracle and/or its affiliates. 9
  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
  9. 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
  10. 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
  11. We will start with the deployment of a single instance

    of MySQL. MySQL architectures: Agenda Copyright @ 2023 Oracle and/or its affiliates. 15
  12. 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
  13. 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
  14. MySQL architectures: Agenda To nish with the ultimate High Availability

    and Disaster Recovery solution: Copyright @ 2023 Oracle and/or its affiliates. 18
  15. 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
  16. 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
  17. 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
  18. 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
  19. LAB 1: deployment and connection to MySQL Copyright @ 2023

    Oracle and/or its affiliates. root / MySQL123 port: 3310 25
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. 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
  27. 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
  28. 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
  29. 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
  30. LAB 2: MySQL instances con guration - example Copyright @

    2023 Oracle and/or its affiliates. 36
  31. 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
  32. LAB 2: MySQL InnoDB ReplicaSet creation Explore the rs object

    created and get its status: Copyright @ 2023 Oracle and/or its affiliates. 39
  33. 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
  34. 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
  35. 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
  36. 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
  37. LAB 2: MySQL InnoDB ReplicaSet adding a member Copyright @

    2023 Oracle and/or its affiliates. 44
  38. LAB 2: MySQL InnoDB ReplicaSet adding a member Copyright @

    2023 Oracle and/or its affiliates. 45
  39. Check again the status of the MySQL InnoDB ReplicaSet LAB

    2: MySQL InnoDB ReplicaSet status Copyright @ 2023 Oracle and/or its affiliates. 46
  40. 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
  41. 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
  42. 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
  43. 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
  44. 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
  45. 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
  46. 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
  47. 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
  48. 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
  49. 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
  50. 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
  51. LAB 3: MySQL Router - Windows And you can now

    start the MySQL Router service manually: Copyright @ 2023 Oracle and/or its affiliates. 54
  52. 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
  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 To start it: $ ~/router_tutorial/start.sh Copyright @ 2023 Oracle and/or its affiliates. 55
  54. 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
  55. LAB 3: MySQL Router We have the possibility to list

    all MySQL Router registered with our ReplicaSet: Copyright @ 2023 Oracle and/or its affiliates. 56
  56. 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
  57. LAB 3: MySQL Router JS> \c root@localhost:3310 [3310] JS> rs=dba.getReplicaSet()

    SQL> \c root@localhost:6446 [6446] SQL> insert into perconalive.t1 (port) values (@@port); SQL> \c root@localhost:6447 [6447] SQL> select * from perconalive.t1; +----+---------------------+------+ | id | timestamp | port | +----+---------------------+------+ | 1 | 2023-04-24 19:21:32 | 3310 | | 2 | 2023-05-05 14:33:46 | 3310 | | 3 | 2023-05-05 14:57:19 | 3310 | +----+---------------------+------+ Copyright @ 2023 Oracle and/or its affiliates. RW: 6446 RO: 6447 58
  58. LAB 3: MySQL Router - Change Primary (planned) [3310] JS>

    rs.setPrimaryInstance('localhost:3320') [6446] SQL> insert into perconalive.t1 (port) values (@@port); --> MySQL Shell needs to reconnect, and we need to run the query [6447] SQL> select * from perconalive.t1; +----+---------------------+------+ | id | timestamp | port | +----+---------------------+------+ | 1 | 2023-04-24 19:21:32 | 3310 | | 2 | 2023-05-05 14:33:46 | 3310 | | 3 | 2023-05-05 14:57:19 | 3310 | | 4 | 2023-05-05 14:59:08 | 3320 | +----+---------------------+------+ Copyright @ 2023 Oracle and/or its affiliates. RW: 6446 RO: 6447 59
  59. 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
  60. LAB 3: MySQL Router - Change Primary (failure) JS> rs=dba.getReplicaSet()

    JS> rs.status() JS> rs.forcePrimaryInstance('localhost:3310') [6446] SQL> \reconnect [6446] SQL> insert into perconalive.t1 (port) values (@@port); [6447] SQL> select * from perconalive.t1; +----+---------------------+------+ | id | timestamp | port | +----+---------------------+------+ | 1 | 2023-04-24 19:21:32 | 3310 | | 2 | 2023-05-05 14:33:46 | 3310 | | 3 | 2023-05-05 14:57:19 | 3310 | | 4 | 2023-05-05 14:59:08 | 3320 | | 5 | 2023-05-05 15:04:10 | 3310 | +----+---------------------+------+ Copyright @ 2023 Oracle and/or its affiliates. RW: 6446 RO: 6447 61
  61. 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
  62. 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
  63. 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
  64. 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
  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 This is a solution for High Availability ! Copyright @ 2023 Oracle and/or its affiliates. 66
  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
  67. 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
  68. LAB 4: MySQL InnoDB Cluster < > Copyright @ 2023

    Oracle and/or its affiliates. If there is an error, where can I find more info ? 69
  69. 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
  70. 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
  71. 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
  72. 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
  73. 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
  74. LAB 4: MySQL InnoDB Cluster - status JS > cluster.status({extended:

    1}) { "clusterName": "mycluster", "defaultReplicaSet": { "GRProtocolVersion": "8.0.27", "communicationStack": "MYSQL", "groupName": "d4a605b7-eb45-11ed-8c80-c8cb9e32df8e", "groupViewChangeUuid": "d4a607bf-eb45-11ed-8c80-c8cb9e32df8e", "groupViewId": "16832920673169417:7", "name": "default", "paxosSingleLeader": "OFF", "primary": "127.0.0.1:3310", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "127.0.0.1:3310": { "address": "127.0.0.1:3310", "applierWorkerThreads": 4, ... Copyright @ 2023 Oracle and/or its affiliates. 75
  75. It's time to recon gure and restart MySQL Router $

    sudo mysqlrouter --bootstrap \ root@localhost:3310 \ --user mysqlrouter \ --conf-use-gr-notifications \ --force $ sudo systemctl restart mysqlrouter LAB 4: MySQL InnoDB Cluster - Router Copyright @ 2023 Oracle and/or its affiliates. 76
  76. 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
  77. LAB 5: MySQL InnoDB Cluster JS> \c root@localhost:3310 [3310] JS>

    cluster=dba.getCluster() SQL > \c root@localhost:6446 [6446] SQL > insert into perconalive.t1 (port) values (@@port); SQL > \c root@localhost:6447 [6447] SQL> select *, @@port `read_from` from perconalive.t1 order by id desc limit 1; +----+---------------------+------+-----------+ | id | timestamp | port | read_from | +----+---------------------+------+-----------+ | 6 | 2023-05-05 15:24:40 | 3310 | 3320 | +----+---------------------+------+-----------+ Copyright @ 2023 Oracle and/or its affiliates. RW: 6446 RO: 6447 79
  78. LAB 5: MySQL InnoDB Cluster - change Primary [3310] JS>

    cluster.setPrimaryInstance('localhost:3320') --> reconnect [6446] SQL > insert into perconalive.t1 (port) values (@@port); [6447] SQL > select *, @@port `read_from` from perconalive.t1 order by id desc limit 1; +----+---------------------+------+-----------+ | id | timestamp | port | read_from | +----+---------------------+------+-----------+ | 7 | 2023-05-05 15:26:04 | 3320 | 3330 | +----+---------------------+------+-----------+ Copyright @ 2023 Oracle and/or its affiliates. RW: 6446 RO: 6447 80
  79. LAB 5: MySQL InnoDB Cluster - failure [3310] JS> dba.killSandboxInstance(3320)

    --> reconnect [6446] SQL> insert into perconalive.t1 (port) values (@@port); [6447] SQL> select *, @@port `read_from` from perconalive.t1 order by id desc limit 1; +----+---------------------+------+-----------+ | id | timestamp | port | read_from | +----+---------------------+------+-----------+ | 8 | 2023-05-05 15:27:19 | 3330 | 3330 | +----+---------------------+------+-----------+ Copyright @ 2023 Oracle and/or its affiliates. RW: 6446 RO: 6447 81
  80. LAB 5: MySQL InnoDB Cluster - failure [3310] JS> dba.killSandboxInstance(3320)

    --> reconnect [6446] SQL> insert into perconalive.t1 (port) values (@@port); [6447] SQL> select *, @@port `read_from` from perconalive.t1 order by id desc limit 1; +----+---------------------+------+-----------+ | id | timestamp | port | read_from | +----+---------------------+------+-----------+ | 8 | 2023-05-05 15:27:19 | 3330 | 3330 | +----+---------------------+------+-----------+ Don't forget to check the output of cluster.status() ! Copyright @ 2023 Oracle and/or its affiliates. RW: 6446 RO: 6447 81
  81. LAB 5: MySQL InnoDB Cluster - mode [3310] JS> dba.startSandboxInstance(3320)

    [3310] JS> cluster.switchToMultiPrimaryMode() --> reconnect [6446] SQL> insert into perconalive.t1 (port) values (@@port); [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 | 3310 | +----+---------------------+------+-----------+ Copyright @ 2023 Oracle and/or its affiliates. RW: 6446 RO: 6447 82
  82. LAB 5: MySQL InnoDB Cluster - mode [3310] JS> dba.startSandboxInstance(3320)

    [3310] JS> cluster.switchToMultiPrimaryMode() --> reconnect [6446] SQL> insert into perconalive.t1 (port) values (@@port); [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 | 3310 | +----+---------------------+------+-----------+ Don't forget to check the output of cluster.status() ! Copyright @ 2023 Oracle and/or its affiliates. RW: 6446 RO: 6447 82
  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 ! 83
  84. 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
  85. 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
  86. High Availability (failure within a region) RPO = 0 RTO

    = seconds (automatic failover) Disaster Recovery (region failure) RPO != 0 RTO = minutes or more (manual failover) No write performance impact MySQL InnoDB ClusterSet Copyright @ 2023 Oracle and/or its affiliates. 87
  87. We need to deploy 3 new instances (secondary region). JS>

    dba.deploySandboxInstance(4410, {password: 'MySQL123'}) JS> dba.deploySandboxInstance(4420, {password: 'MySQL123'}) JS> dba.deploySandboxInstance(4430, {password: 'MySQL123'}) LAB6: MySQL InnoDB ClusterSet - deploy Copyright @ 2023 Oracle and/or its affiliates. 89
  88. 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
  89. 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
  90. 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
  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}) Get more info with cs.help('status') or \? clusterset.status Copyright @ 2023 Oracle and/or its affiliates. 92
  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
  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
  94. 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
  95. LAB7: MySQL InnoDB ClusterSet - Router Let's replace again the

    con guration: $ sudo mysqlrouter --bootstrap root@localhost:3310 \ --user mysqlrouter --force And restart MySQL Router: $ sudo systemctl restart mysqlrouter Copyright @ 2023 Oracle and/or its affiliates. 98
  96. 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
  97. 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
  98. 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
  99. 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
  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') 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
  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
  102. 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
  103. 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
  104. 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
  105. 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
  106. 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
  107. 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
  108. 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
  109. 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
  110. 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
  111. 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
  112. Share your ❤ to MySQL #mysql Join our slack channel!

    bit.ly/mysql-slack Copyright @ 2023 Oracle and/or its affiliates. 122