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

    View full-size slide

  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

    View full-size slide

  3. Kenny Gryp & Frédéric Descamps
    Product Manager & Community Manager
    Oracle - MySQL
    MySQL Architectures
    Design the Right Solution for Your Needs

    View full-size slide

  4. Who we are ?
    Copyright @ 2023 Oracle and/or its affiliates.
    4

    View full-size slide

  5. @gryp
    MySQL Product Manager
    likes
    « ☕ . ( 6

    Kenny Gryp
    Copyright @ 2023 Oracle and/or its affiliates.
    5

    View full-size slide

  6. @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

    View full-size slide

  7. Housekeeping
    some information
    Copyright @ 2023 Oracle and/or its affiliates.
    7

    View full-size slide

  8. 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

    View full-size slide

  9. MySQL Shell is now included in Visual Studio Code:
    Copyright @ 2023 Oracle and/or its affiliates.
    9

    View full-size slide

  10. Copyright @ 2023 Oracle and/or its affiliates.
    10

    View full-size slide

  11. Windows Users - Installation
    For Microsoft Windows users, please use MySQL Installer to install the required products:
    Copyright @ 2023 Oracle and/or its affiliates.
    11

    View full-size slide

  12. 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

    View full-size slide

  13. 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

    View full-size slide

  14. MySQL Architectures
    the Agenda in Pictures
    Copyright @ 2023 Oracle and/or its affiliates.
    14

    View full-size slide

  15. We will start with the deployment of a
    single instance of MySQL.
    MySQL architectures: Agenda
    Copyright @ 2023 Oracle and/or its affiliates.
    15

    View full-size slide

  16. 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

    View full-size slide

  17. 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

    View full-size slide

  18. MySQL architectures: Agenda
    To nish with the ultimate High Availability and Disaster Recovery solution:
    Copyright @ 2023 Oracle and/or its affiliates.
    18

    View full-size slide

  19. MySQL
    Single Instance
    Copyright @ 2023 Oracle and/or its affiliates.
    19

    View full-size slide

  20. 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

    View full-size slide

  21. LAB 1
    Copyright @ 2023 Oracle and/or its affiliates.
    21

    View full-size slide

  22. 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

    View full-size slide

  23. LAB 1: deployment and connection to MySQL
    Copyright @ 2023 Oracle and/or its affiliates.
    23

    View full-size slide

  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

    View full-size slide

  25. 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

    View full-size slide

  26. LAB 1: deployment and connection to MySQL
    Copyright @ 2023 Oracle and/or its affiliates.
    root / MySQL123
    port: 3310
    25

    View full-size slide

  27. LAB 1: deployment and connection to MySQL
    Copyright @ 2023 Oracle and/or its affiliates.
    26

    View full-size slide

  28. 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

    View full-size slide

  29. 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

    View full-size slide

  30. LAB 1: inserting data
    Copyright @ 2023 Oracle and/or its affiliates.
    28

    View full-size slide

  31. MySQL InnoDB ReplicaSet
    Easy Setup and Manual Failover
    Copyright @ 2023 Oracle and/or its affiliates.
    29

    View full-size slide

  32. 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

    View full-size slide

  33. 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

    View full-size slide

  34. 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

    View full-size slide

  35. LAB 2
    Copyright @ 2023 Oracle and/or its affiliates.
    32

    View full-size slide

  36. 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

    View full-size slide

  37. 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

    View full-size slide

  38. 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

    View full-size slide

  39. 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

    View full-size slide

  40. 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

    View full-size slide

  41. LAB 2: MySQL instances con guration - example
    Copyright @ 2023 Oracle and/or its affiliates.
    36

    View full-size slide

  42. 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

    View full-size slide

  43. LAB 2: MySQL InnoDB ReplicaSet creation
    Copyright @ 2023 Oracle and/or its affiliates.
    38

    View full-size slide

  44. LAB 2: MySQL InnoDB ReplicaSet creation
    Explore the rs object created and get its status:
    Copyright @ 2023 Oracle and/or its affiliates.
    39

    View full-size slide

  45. 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

    View full-size slide

  46. LAB 2: MySQL InnoDB ReplicaSet status
    Copyright @ 2023 Oracle and/or its affiliates.
    40

    View full-size slide

  47. LAB 2: MySQL InnoDB ReplicaSet status extended
    Copyright @ 2023 Oracle and/or its affiliates.
    41

    View full-size slide

  48. 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

    View full-size slide

  49. 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

    View full-size slide

  50. 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  53. Check again the status of the
    MySQL InnoDB ReplicaSet
    LAB 2: MySQL InnoDB ReplicaSet status
    Copyright @ 2023 Oracle and/or its affiliates.
    46

    View full-size slide

  54. LAB 3
    Copyright @ 2023 Oracle and/or its affiliates.
    47

    View full-size slide

  55. 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

    View full-size slide

  56. 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

    View full-size slide

  57. 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

    View full-size slide

  58. 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

    View full-size slide

  59. 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

    View full-size slide

  60. 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

    View full-size slide

  61. 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

    View full-size slide

  62. LAB 3: MySQL Router
    Copyright @ 2023 Oracle and/or its affiliates.
    51

    View full-size slide

  63. 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

    View full-size slide

  64. 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

    View full-size slide

  65. 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

    View full-size slide

  66. 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

    View full-size slide

  67. LAB 3: MySQL Router - Windows
    And you can now start the MySQL Router service manually:
    Copyright @ 2023 Oracle and/or its affiliates.
    54

    View full-size slide

  68. 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

    View full-size slide

  69. 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

    View full-size slide

  70. 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

    View full-size slide

  71. 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

    View full-size slide

  72. 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

    View full-size slide

  73. 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

    View full-size slide

  74. 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

    View full-size slide

  75. 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

    View full-size slide

  76. 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

    View full-size slide

  77. MySQL InnoDB Cluster
    High Availability and 0 data loss
    Copyright @ 2023 Oracle and/or its affiliates.
    62

    View full-size slide

  78. 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

    View full-size slide

  79. 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

    View full-size slide

  80. 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

    View full-size slide

  81. 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

    View full-size slide

  82. 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

    View full-size slide

  83. LAB 4
    Copyright @ 2023 Oracle and/or its affiliates.
    67

    View full-size slide

  84. 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

    View full-size slide

  85. 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

    View full-size slide

  86. LAB 4: MySQL InnoDB Cluster
    < >
    Copyright @ 2023 Oracle and/or its affiliates.
    If there is an error, where can I find more info ?
    69

    View full-size slide

  87. 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

    View full-size slide

  88. 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

    View full-size slide

  89. 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

    View full-size slide

  90. 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

    View full-size slide

  91. 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

    View full-size slide

  92. LAB 4: MySQL InnoDB Cluster - status
    Copyright @ 2023 Oracle and/or its affiliates.
    74

    View full-size slide

  93. 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

    View full-size slide

  94. 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

    View full-size slide

  95. LAB 5
    Copyright @ 2023 Oracle and/or its affiliates.
    77

    View full-size slide

  96. 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

    View full-size slide

  97. 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

    View full-size slide

  98. 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

    View full-size slide

  99. 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

    View full-size slide

  100. 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

    View full-size slide

  101. 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

    View full-size slide

  102. 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

    View full-size slide

  103. 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

    View full-size slide

  104. 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

    View full-size slide

  105. MySQL InnoDB ClusterSet
    High Availability and Disaster Recovery
    Copyright @ 2023 Oracle and/or its affiliates.
    84

    View full-size slide

  106. 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

    View full-size slide

  107. MySQL InnoDB ClusterSet
    Copyright @ 2023 Oracle and/or its affiliates.
    86

    View full-size slide

  108. 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

    View full-size slide

  109. LAB 6
    Copyright @ 2023 Oracle and/or its affiliates.
    88

    View full-size slide

  110. 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

    View full-size slide

  111. 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

    View full-size slide

  112. 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

    View full-size slide

  113. 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

    View full-size slide

  114. 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

    View full-size slide

  115. 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

    View full-size slide

  116. LAB6: MySQL InnoDB ClusterSet - Replica Cluster
    Copyright @ 2023 Oracle and/or its affiliates.
    94

    View full-size slide

  117. 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

    View full-size slide

  118. LAB 7
    Copyright @ 2023 Oracle and/or its affiliates.
    96

    View full-size slide

  119. 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

    View full-size slide

  120. 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

    View full-size slide

  121. 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

    View full-size slide

  122. 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

    View full-size slide

  123. 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

    View full-size slide

  124. 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

    View full-size slide

  125. 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

    View full-size slide

  126. LAB 8
    Copyright @ 2023 Oracle and/or its affiliates.
    102

    View full-size slide

  127. 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

    View full-size slide

  128. LAB8: MySQL InnoDB ClusterSet - Region Switch
    Output Example
    Copyright @ 2023 Oracle and/or its affiliates.
    104

    View full-size slide

  129. 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

    View full-size slide

  130. 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

    View full-size slide

  131. 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

    View full-size slide

  132. 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

    View full-size slide

  133. 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

    View full-size slide

  134. 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

    View full-size slide

  135. LAB8: MySQL InnoDB ClusterSet - Region Switch
    Copyright @ 2023 Oracle and/or its affiliates.
    111

    View full-size slide

  136. 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

    View full-size slide

  137. LAB 9
    Copyright @ 2023 Oracle and/or its affiliates.
    113

    View full-size slide

  138. 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

    View full-size slide

  139. 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

    View full-size slide

  140. LAB9: MySQL InnoDB ClusterSet - Complete Outage
    Copyright @ 2023 Oracle and/or its affiliates.
    115

    View full-size slide

  141. LAB9: MySQL InnoDB ClusterSet - Complete Outage
    Copyright @ 2023 Oracle and/or its affiliates.
    116

    View full-size slide

  142. LAB9: MySQL InnoDB ClusterSet - Complete Outage
    Copyright @ 2023 Oracle and/or its affiliates.
    117

    View full-size slide

  143. LAB9: MySQL InnoDB ClusterSet - Complete Outage
    Copyright @ 2023 Oracle and/or its affiliates.
    118

    View full-size slide

  144. 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

    View full-size slide

  145. Copyright @ 2023 Oracle and/or its affiliates.
    120

    View full-size slide

  146. Thank you !
    Copyright @ 2023 Oracle and/or its affiliates.
    121

    View full-size slide

  147. Share your
    ❤ to MySQL
    #mysql
    Join our slack channel!
    bit.ly/mysql-slack
    Copyright @ 2023 Oracle and/or its affiliates.
    122

    View full-size slide