Download as PDF, PPTX
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 58/230 MySQL 8.0.16 is already installed on mysql3. Let´s take a backup on mysql1: [mysql1 ~]# mysqlpump --set-gtid-purged=ON --exclude-databases=mysql --include-users=repl%,clusteradmin%,app% > /tmp/backup.sql Copy the backup from mysql1 to mysql3: [mysql1 ~]# scp -r /tmp/backup.sql mysql3:/tmp LAB2: Prepare mysql3 - Asynchronous slave Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 58 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-58-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 60/230 LAB3: mysql3 as asynchronous slave (2) Restore backup: [mysql3 ~]# mysql < /tmp/backup.sql Connect to mysql3 and setup replication: mysql3> CHANGE MASTER TO MASTER_HOST="mysql1", MASTER_USER="repl", MASTER_PASSWORD='X', MASTER_AUTO_POSITION=1; mysql3> START SLAVE; Check that you receive the application´s traffic Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 60 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-60-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 67/230 LAB5: add mysql4 to the cluster (2) Copy the backup from mysql1 to mysql4: [mysql1 ~]# scp -r /tmp/backup.sql mysql4:/tmp Restore the backup: [mysql4 ~]# mysql < /tmp/backup.sql Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 67 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-67-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 76/230 LAB6: Migrate the application Now we need to point the application to mysql3, this is the only downtime ! Make sure the master and replica is in sync Start application, connecting to the Group Replication master (mysql3) ... [ 21257s] threads: 4, tps: 12.00, reads: 167.94, writes: 47.98, response t [ 21258s] threads: 4, tps: 6.00, reads: 83.96, writes: 23.99, response t [ 21259s] threads: 4, tps: 7.00, reads: 98.05, writes: 28.01, response t [ 31250s] threads: 4, tps: 8.00, reads: 111.95, writes: 31.99, response t [ 31251s] threads: 4, tps: 11.00, reads: 154.01, writes: 44.00, response t [ 31252s] threads: 4, tps: 11.00, reads: 153.94, writes: 43.98, response t [ 31253s] threads: 4, tps: 10.01, reads: 140.07, writes: 40.02, response t ^C [mysql1 ~]# run_app.sh mysql3 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 76 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-76-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 77/230 LAB6: Migrate the application Make sure gtid_executed range on mysql2 is lower or equal than on mysql3 mysql[2-3]> SHOW GLOBAL VARIABLES LIKE 'gtid_executed'G Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 77 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-77-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 78/230 LAB6: Migrate the application Make sure gtid_executed range on mysql2 is lower or equal than on mysql3 mysql[2-3]> SHOW GLOBAL VARIABLES LIKE 'gtid_executed'G When they are OK, stop asynchronous replication on mysql2 and mysql3: mysql2> STOP SLAVE; mysql3> STOP SLAVE; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 78 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-78-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 79/230 LAB6: Migrate the application Make sure gtid_executed range on mysql2 is lower or equal than on mysql3 mysql[2-3]> SHOW GLOBAL VARIABLES LIKE 'gtid_executed'G When they are OK, stop asynchronous replication on mysql2 and mysql3: mysql2> STOP SLAVE; mysql3> STOP SLAVE; Now they can forget about mysql1: mysql[2-3]> RESET SLAVE ALL FOR CHANNEL ''; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 79 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-79-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 82/230 LAB7: Add mysql2 to the group We first upgrade to MySQL 8.0.16 : [mysql2 ~]# systemctl stop mysqld [mysql2 ~]# rpm -Uvh /root/rpms/*rpm --force [mysql2 ~]# systemctl start mysqld Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 82 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-82-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 83/230 LAB7: Add mysql2 to the group We first upgrade to MySQL 8.0.16 : [mysql2 ~]# systemctl stop mysqld [mysql2 ~]# rpm -Uvh /root/rpms/*rpm --force [mysql2 ~]# systemctl start mysqld No need to run mysql_updgrade as we are using MySQL 8.0.16 ! 😁 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 83 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-83-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 101/230 Sys Schema The easiest way to detect if a node is a member of the primary component (when there are partitioning of your nodes due to network issues for example) and therefore a valid candidate for routing queries to it, is to use the sys table. Additional information for sys can be found at https://goo.gl/XFp3bt On the primary node: [mysql3 ~]# mysql < /root/addition_to_sys_8.0.2.sql Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 101 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-101-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 116/230 LAB10: MySQL Router (2) Configure MySQL Router that will run on the app server (mysql1). We bootstrap it using the Primary-Master: [root@mysql1 ˜]# mysqlrouter --bootstrap clusteradmin@mysql3:3306 --user mysqlrouter Please enter MySQL password for clusteradmin: Bootstrapping system MySQL Router instance... Checking for old Router accounts Creating account mysql_router1_rvbecwmlc6uf@'%' MySQL Router has now been con gured for the InnoDB cluster 'perconalive'. The following connection information can be used to connect to the cluster after MySQL Router has been started with generated con guration.. Classic MySQL protocol connections to cluster 'perconalive': - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 X protocol connections to cluster 'perconalive': - Read/Write Connections: localhost:64460 - Read/Only Connections: localhost:64470 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 116 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-116-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 118/230 LAB10: MySQL Router (3) Now let´s modify the configuration file to listen on port 3306: in /etc/mysqlrouter/mysqlrouter.conf: [routing:perconalive_default_rw] -bind_port=6446 +bind_port=3306 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 118 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-118-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 119/230 LAB10: MySQL Router (3) Now let´s modify the configuration file to listen on port 3306: in /etc/mysqlrouter/mysqlrouter.conf: [routing:perconalive_default_rw] -bind_port=6446 +bind_port=3306 We can stop mysqld on mysql1 and start mysqlrouter: [mysql1 ~]# systemctl stop mysqld [mysql1 ~]# systemctl start mysqlrouter Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 119 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-119-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 120/230 LAB10: MySQL Router (5) Now we can point the application to the router (back to mysql1): [mysql1 ~]# run_app.sh Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 120 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-120-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 121/230 LAB10: MySQL Router (5) Now we can point the application to the router (back to mysql1): [mysql1 ~]# run_app.sh Check app and kill mysqld on mysql3 (the Primary Master R/W node) ! [mysql3 ~]# failure-scenario.sh kill-node Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 121 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-121-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 122/230 LAB10: MySQL Router (5) Now we can point the application to the router (back to mysql1): [mysql1 ~]# run_app.sh Check app and kill mysqld on mysql3 (the Primary Master R/W node) ! [mysql3 ~]# failure-scenario.sh kill-node mysql2> select member_host as `primary` from performance_schema.replication_group_members where member_role='PRIMARY'; +----------+ | primary | +----------+ | mysql4 | +----------+ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 122 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-122-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 139/230 Create a Multi-Primary Cluster: It´s also possible to create a Multi-Primary Cluster using the Shell: mysql-js> cluster=dba.createCluster('perconalive',{multiMaster: true}) A new InnoDB cluster will be created on instance 'clusteradmin@mysql3:3306'. The MySQL InnoDB cluster is going to be setup in advanced Multi-Master Mode. Before continuing you have to con rm that you understand the requirements and limitations of Multi-Master Mode. Please read the manual before proceeding. I have read the MySQL InnoDB cluster manual and I understand the requirements and limitations of advanced Multi-Master Mode. Con rm [y|N]: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 139 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-139-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 140/230 Create a Multi-Primary Cluster: It´s also possible to create a Multi-Primary Cluster using the Shell: mysql-js> cluster=dba.createCluster('perconalive',{multiMaster: true}) A new InnoDB cluster will be created on instance 'clusteradmin@mysql3:3306'. The MySQL InnoDB cluster is going to be setup in advanced Multi-Master Mode. Before continuing you have to con rm that you understand the requirements and limitations of Multi-Master Mode. Please read the manual before proceeding. I have read the MySQL InnoDB cluster manual and I understand the requirements and limitations of advanced Multi-Master Mode. Con rm [y|N]: Or you can force it to avoid interaction (for automation) : Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 140 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-140-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 141/230 Create a Multi-Primary Cluster: It´s also possible to create a Multi-Primary Cluster using the Shell: mysql-js> cluster=dba.createCluster('perconalive',{multiMaster: true}) A new InnoDB cluster will be created on instance 'clusteradmin@mysql3:3306'. The MySQL InnoDB cluster is going to be setup in advanced Multi-Master Mode. Before continuing you have to con rm that you understand the requirements and limitations of Multi-Master Mode. Please read the manual before proceeding. I have read the MySQL InnoDB cluster manual and I understand the requirements and limitations of advanced Multi-Master Mode. Con rm [y|N]: Or you can force it to avoid interaction (for automation) : > cluster=dba.createCluster('perconalive',{multiMaster: true, force: true}) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 141 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-141-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 155/230 LAB11: Network Partitions - Default mysql3 '[GCS] Installed site start={ed88d526 1339 0} boot_key={ed88d526 1328 0} event_horizon=10 node 1' '[GCS] Group is able to support up to communication protocol version 8.0.16' 'Members removed from the group: mysql4:3306' 'Group membership changed to mysql3:3306, mysql2:3306 on view 1557809996177 '[GCS] Failure reading from fd=-1 n=18446744073709551615' mysql4 'Member with address mysql2:3306 has become unreachable.' 'Member with address mysql3:3306 has become unreachable.' 'This server is not able to reach a majority of members in the group. This server will now block all updates. The server will remain blocked until contact with the majority is restored. It is possible to use group_replication_force_members to force a new group membership.' Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 155 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-155-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 157/230 LAB11: Network Partition - Default mysql4# failure-scenario.sh network-partition end Does the node come back? NO: it was expelled: 'Member with address mysql2:3306 is reachable again.' 'Member with address mysql3:3306 is reachable again.' 'The member has resumed contact with a majority of the members in the group. Regular operation is restored and transactions are unblocked.' '[GCS] Re-using server node 0 host mysql2' '[GCS] Re-using server node 1 host mysql3' '[GCS] Installed site start={ed88d526 1339 0} boot_key={ed88d526 1328 0} event_horizon=10 node 4294967295' '[GCS] Node has already been removed: mysql4:33061 15578108329721490.' 'Member was expelled from the group due to network failures, changing member status to ERROR.' Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 157 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-157-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 160/230 LAB12: Network Partitions - With Retries Did mysql4 join the group again? YES! 'Member with address mysql2:3306 is reachable again.' 'The member has resumed contact with a majority of the members in the group. Regular operation is restored and transactions are unblocked.' 'Member with address mysql3:3306 is reachable again.' '[GCS] Re-using server node 0 host mysql2' '[GCS] Re-using server node 1 host mysql3' '[GCS] Installed site start={ed88d526 2445 0} boot_key={ed88d526 2434 0} event_horizon=10 node 4294967295' '[GCS] Node has already been removed: mysql4:33061 15578118987619030.' 'Member was expelled from the group due to network failures, changing member status to ERROR.' 'Started auto-rejoin procedure attempt 1 of 2' ... 'This server was declared online within the replication group.' Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 160 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-160-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 180/230 LAB14: Consistency Levels: EVENTUAL On mysql4 lock a table and write to that table on mysql2: mysql4> lock tables app.sbtest1 read; mysql2> update app.sbtest1 set k=k+123 where id = 2000; You can now select that row on all members: mysql[2-3-4]> select id, k from app.sbtest1 where id=2000; What do you see ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 180 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-180-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 181/230 LAB14: Consistency Levels: EVENTUAL On mysql4 lock a table and write to that table on mysql2: mysql4> lock tables app.sbtest1 read; mysql2> update app.sbtest1 set k=k+123 where id = 2000; You can now select that row on all members: mysql[2-3-4]> select id, k from app.sbtest1 where id=2000; What do you see ? mysql4> UNLOCK TABLES; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 181 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-181-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 192/230 LAB15: Stopping A Node On mysql4, check the error log: mysql4# tail -f /var/log/mysqld.log Cleanly stop MySQL on mysql2: mysql2# systemctl stop mysqld We can see: [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: mysql2:3306' Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 192 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-192-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 193/230 LAB15: Stopping A Node On mysql4, check the error log: mysql4# tail -f /var/log/mysqld.log Cleanly stop MySQL on mysql2: mysql2# systemctl stop mysqld We can see: [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: mysql2:3306' when performing a graceful shutdown, quorum is not taken in consideration Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 193 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-193-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 198/230 LAB16: Full Power Loss (2) Connect to one node and retrieve the cluster object, after a timeout, the following error will be seen: mysql1# mysqlsh mysql-js> c clusteradmin@mysql2 mysql-js> cluster=dba.getCluster() Dba.getCluster: This function is not available through a session to a standalone instance (metadata exists, but GR is not active) (RuntimeError) Check the error log, no members where able to join and recreate a group: '[GCS] Error connecting to all peers. Member join failed. Local port: 33061' '[GCS] The member was unable to join the group. Local port: 33061' 'Timeout on wait for view after joining group' Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 198 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-198-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 199/230 LAB16: Full Power Loss - restart the cluster (3) Use the dba object to 'reboot' the cluster: mysql-js> cluster=dba.rebootClusterFromCompleteOutage() Note: mysql-js> cluster=dba.rebootClusterFromCompleteOutage() Recon guring the default cluster from complete outage... The instance 'mysql3:3306' was part of the cluster con guration. Would you like to rejoin it to the cluster? [y/N]: y The instance 'mysql4:3306' was part of the cluster con guration. Would you like to rejoin it to the cluster? [y/N]: y Dba.rebootClusterFromCompleteOutage: The active session instance isn't the most updated in comparison with the ONLINE instances of the Cluster's metadata. Please use the most up to date instance: 'mysql3:3306'. (RuntimeError) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 199 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-199-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 203/230 LAB17: Run MySQL without quorum For this lab, stop mysqld on mysql2and mysql4: mysql3# tail -f /var/log/mysqld.log mysql2# failure-scenario.sh kill-node mysql4# failure-scenario.sh kill-node [WARNING] 'Member with address mysql2:3306 has become unreachable.' 'Member with address mysql4:3306 has become unreachable.' [ERROR] 'This server is not able to reach a majority of members in the group. This server will now block all updates. The server will remain blocked until contact with the majority is restored. It is possible to use group_replication_force_members to force a new group membership.' Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 203 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-203-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 206/230 LAB17: Run MySQL without quorum (3) assume mysql2 and mysql4 are gone forever there is no majority mysql3 will not be primary make mysql3 primary mysql-js> cluster.forceQuorumUsingPartitionOf('clusteradmin@mysql3') Restoring replicaset 'default' from loss of quorum, by using the partition composed of [mysql3:3306] Restoring the InnoDB cluster ... The InnoDB cluster was successfully restored using the partition from the instance 'clusteradmin@mysql3'. WARNING: To avoid a split-brain scenario, ensure that all other members of the replicaset are removed or joined back to the group that was restored. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 206 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-206-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 207/230 LAB17: Run MySQL without quorum (3) assume mysql2 and mysql4 are gone forever there is no majority mysql3 will not be primary make mysql3 primary mysql-js> cluster.forceQuorumUsingPartitionOf('clusteradmin@mysql3') Restoring replicaset 'default' from loss of quorum, by using the partition composed of [mysql3:3306] Restoring the InnoDB cluster ... The InnoDB cluster was successfully restored using the partition from the instance 'clusteradmin@mysql3'. WARNING: To avoid a split-brain scenario, ensure that all other members of the replicaset are removed or joined back to the group that was restored. As soon as this is done, the application is running again ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 207 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-207-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 225/230 LAB19: Degraded Performance On A Node (2) When 100 is reached: writes reduced slowly as queue grows: more slowdown never goes to 0 It´s possible to ignore the health of mysql4 by telling to the PRIMARY to ignore the FC statistics received: mysql[2-3]> set global group_replication_ ow_control_mode='DISABLED'; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 225 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-225-2048.jpg)
![5/25/2019 MySQL InnoDB Cluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 226/230 LAB19: Degraded Performance On A Node (2) When 100 is reached: writes reduced slowly as queue grows: more slowdown never goes to 0 It´s possible to ignore the health of mysql4 by telling to the PRIMARY to ignore the FC statistics received: mysql[2-3]> set global group_replication_ ow_control_mode='DISABLED'; As soon as it´s disabled, you can see the application going back at full speed! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 226 / 230](https://image.slidesharecdn.com/mysqlinnodbclusterandgroupreplicationinanutshellhands-ontutorial-190603151152/75/MySQL-InnoDB-Cluster-and-Group-Replication-in-a-Nutshell-hands-on-tutorial-226-2048.jpg)
Uploaded byFrederic Descamps
MySQL InnoDB Cluster and Group Replication in a Nutshell: hands-on tutorial
The document is a hands-on tutorial on MySQL InnoDB Cluster and Group Replication presented by Oracle's MySQL community manager and product manager. It covers the setup, functionalities, and advantages of using InnoDB Cluster for high availability, along with practical labs and configurations. Key topics include migrating from asynchronous replication to group replication, monitoring, and application behavior within the cluster.
MySQL InnoDB Cluster and Group Replication in a Nutshell: hands-on tutorial
- 1. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 1/230 1 / 230
- 2. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 2/230 2 / 2302 / 230
- 3. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 3/230 MySQL InnoDB Cluster in a Nutshell Hands-On Tutorial Percona Live USA - Austin, TX 2019 Frédéric Descamps - MySQL Community Manager - Oracle Kenny Gryp - MySQL Product Manager - Oracle 3 / 230
- 4. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 4/230 Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purpose only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied up in making purchasing decisions. The development, release and timing of any features or functionality described for Oracle´s product remains at the sole discretion of Oracle. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 4 / 230
- 5. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 5/230 Who are we ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 5 / 230
- 6. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 6/230 Frédéric Descamps @lefred MySQL Evangelist Managing MySQL since 3.23 devops believer http://about.me/lefred Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 6 / 230
- 7. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 7/230 Kenny Gryp @gryp MySQL Product Manager High Availability & InnoDB Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 7 / 230
- 8. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 8/230 how will the session proceed? Agenda Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 8 / 230
- 9. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 9/230 Agenda Prepare your workstation Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 9 / 230
- 10. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 10/230 Agenda Prepare your workstation What is MySQL InnoDB Cluster & Group Replication ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 10 / 230
- 11. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 11/230 Agenda Prepare your workstation What is MySQL InnoDB Cluster & Group Replication ? Migrating from Asynchronous Replication to Group Replication Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 11 / 230
- 12. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 12/230 Agenda Prepare your workstation What is MySQL InnoDB Cluster & Group Replication ? Migrating from Asynchronous Replication to Group Replication Monitoring Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 12 / 230
- 13. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 13/230 Agenda Prepare your workstation What is MySQL InnoDB Cluster & Group Replication ? Migrating from Asynchronous Replication to Group Replication Monitoring Connecting to the cluster Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 13 / 230
- 14. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 14/230 Agenda Prepare your workstation What is MySQL InnoDB Cluster & Group Replication ? Migrating from Asynchronous Replication to Group Replication Monitoring Connecting to the cluster Various Configuration Options Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 14 / 230
- 15. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 15/230 Agenda Prepare your workstation What is MySQL InnoDB Cluster & Group Replication ? Migrating from Asynchronous Replication to Group Replication Monitoring Connecting to the cluster Various Configuration Options Application Behavior & Consistency Guarantees Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 15 / 230
- 16. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 16/230 Agenda Prepare your workstation What is MySQL InnoDB Cluster & Group Replication ? Migrating from Asynchronous Replication to Group Replication Monitoring Connecting to the cluster Various Configuration Options Application Behavior & Consistency Guarantees Failure Scenarios - Labs Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 16 / 230
- 17. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 17/230 VirtualBox Setup your workstation Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 17 / 230
- 18. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 18/230 Setup your workstation Install VirtualBox 6.0 On the USB key, COPY MySQLInnoDBCluster_PLUS19.ova and click on it Ensure you have vboxnet0 network interface Older VirtualBox: VirtualBox Pref. -> Network -> Host-Only Networks -> + New VirtualBox: Global Tools -> Host Network Manager -> + Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 18 / 230
- 19. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 19/230 Login on your workstation Start all virtual machines (mysql1, mysql2, mysql3 & mysql4) Try to connect to all VM´s from your terminal or putty (root password is X) : ssh root@192.168.56.11 mysql1 ssh root@192.168.56.12 mysql2 ssh root@192.168.56.13 mysql3 ssh root@192.168.56.14 mysql4 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 19 / 230
- 20. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 20/230 LAB1: Current situation Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 20 / 230
- 21. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 21/230 all running database servers (mysql1 & mysql2) are using MySQL 5.7.24 LAB1: Current situation Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 21 / 230
- 22. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 22/230 launch run_app.sh on mysql1 into a screen session verify that mysql2 is a running replica LAB1: Current situation Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 22 / 230
- 23. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 23/230 Summary ROLE INTERNAL IP mysql1 master 192.168.56.11 mysql2 replica 192.168.56.12 mysql3 n/a 192.168.56.13 mysql4 n/a 192.168.56.14 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 23 / 230
- 24. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 24/230 Summary ROLE INTERNAL IP mysql1 master 192.168.56.11 mysql2 replica 192.168.56.12 mysql3 n/a 192.168.56.13 mysql4 n/a 192.168.56.14 write this down somewhere ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 24 / 230
- 25. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 25/230 Easy High Availability MySQL InnoDB Cluster Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 25 / 230
- 26. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 26/230 InnoDB cluster Ease-of-Use Extreme Scale-Out Out-of-Box Solution Built-in HA High Performance Everything Integrated Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 26 / 230
- 27. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 27/230 InnoDB Cluster's Architecture Application MySQL Connector MySQL Router MySQL Shell InnoDB cluster Application MySQL Connector MySQL Router Mp M M Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 27 / 230
- 28. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 28/230 Group Replication: heart of MySQL InnoDB Cluster Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 28 / 230
- 29. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 29/230 Group Replication: heart of MySQL InnoDB Cluster Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 29 / 230
- 30. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 30/230 MySQL Group Replication but what is it ?!? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 30 / 230
- 31. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 31/230 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 31 / 230
- 32. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 32/230 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 32 / 230
- 33. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 33/230 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 33 / 230
- 34. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 34/230 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol GR allows to write on all Group Members (cluster nodes) simultaneously while retaining consistency Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 34 / 230
- 35. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 35/230 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol GR allows to write on all Group Members (cluster nodes) simultaneously while retaining consistency GR implements conflict detection and resolution Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 35 / 230
- 36. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 36/230 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol GR allows to write on all Group Members (cluster nodes) simultaneously while retaining consistency GR implements conflict detection and resolution GR allows automatic distributed recovery Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 36 / 230
- 37. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 37/230 MySQL Group Replication but what is it ?!? GR is a plugin for MySQL, made by MySQL and packaged with MySQL GR is an implementation of Replicated Database State Machine theory Paxos based protocol GR allows to write on all Group Members (cluster nodes) simultaneously while retaining consistency GR implements conflict detection and resolution GR allows automatic distributed recovery Supported on all MySQL platforms !! Linux, Windows, Solaris, OSX, FreeBSD Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 37 / 230
- 38. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 38/230 And for users ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 38 / 230
- 39. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 39/230 And for users ? not longer necessary to handle server fail-over manually or with a complicated script Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 39 / 230
- 40. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 40/230 And for users ? not longer necessary to handle server fail-over manually or with a complicated script GR provides fault tolerance Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 40 / 230
- 41. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 41/230 And for users ? not longer necessary to handle server fail-over manually or with a complicated script GR provides fault tolerance GR enables update-everywhere setups Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 41 / 230
- 42. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 42/230 And for users ? not longer necessary to handle server fail-over manually or with a complicated script GR provides fault tolerance GR enables update-everywhere setups GR handles crashes, failures, re-connects automatically Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 42 / 230
- 43. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 43/230 And for users ? not longer necessary to handle server fail-over manually or with a complicated script 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 @ 2019 Oracle and/or its affiliates. All rights reserved. 43 / 230
- 44. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 44/230 Migrating from Asynchronous Replication to Group Replication Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 44 / 230
- 45. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 45/230 The plan Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 45 / 230
- 46. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 46/230 These are the 10 steps for the migration: 1) We install and setup MySQL InnoDB Cluster on one of the new servers The plan Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 46 / 230
- 47. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 47/230 2) we restore a backup 3) we update to MySQL 8.0 4) setup asynchronous replication on the new server. The plan Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 47 / 230
- 48. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 48/230 5) we add a new instance to our group The plan Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 48 / 230
- 49. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 49/230 6) we point the application to one of our new nodes. 7) we wait and check that asynchronous replication is caught up 8) we stop those asynchronous slaves The plan Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 49 / 230
- 50. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 50/230 9) we upgrade and attach mysql2 (previous slave) to the group The plan Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 50 / 230
- 51. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 51/230 10) use MySQL Router for directing traffic The plan Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 51 / 230
- 52. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 52/230 LAB2: Verify if your environment can be upgraded to MySQL 8.0 We will first create an admin account on mysql1 that we will use throughout the tutorial: mysql1> CREATE USER clusteradmin@'%' identi ed by 'mysql'; mysql1> GRANT ALL PRIVILEGES ON *.* TO clusteradmin@'%' WITH GRANT OPTION; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 52 / 230
- 53. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 53/230 LAB2: Verify if your environment can be upgraded to MySQL 8.0 - util Now, we use MySQL Shell to check if the settings on mysql1 can be upgraded to MySQL 8.0 without problem: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 53 / 230
- 54. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 54/230 LAB2: Verify if your environment can be upgraded to MySQL 8.0 - util Now, we use MySQL Shell to check if the settings on mysql1 can be upgraded to MySQL 8.0 without problem: # mysqlsh -- util check-for-server-upgrade clusteradmin@mysql1 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 54 / 230
- 55. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 55/230 LAB2: Verify if your environment can be upgraded to MySQL 8.0 - util Now, we use MySQL Shell to check if the settings on mysql1 can be upgraded to MySQL 8.0 without problem: # mysqlsh -- util check-for-server-upgrade clusteradmin@mysql1 Anything special in the output ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 55 / 230
- 56. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 56/230 LAB2: Verify if your environment can be upgraded to MySQL 8.0 - util Now, we use MySQL Shell to check if the settings on mysql1 can be upgraded to MySQL 8.0 without problem: # mysqlsh -- util check-for-server-upgrade clusteradmin@mysql1 Anything special in the output ? # mysqlsh -- util check-for-server-upgrade clusteradmin@mysql1 --con gPath=/etc/my.cnf Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 56 / 230
- 57. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 57/230 LAB2: Beautiful MySQL Shell You can setup a better shell environment : # mysqlsh mysql-js> shell.options.setPersist('history.autoSave', 1) mysql-js> shell.options.setPersist('history.maxSize', 5000) # cp /usr/share/mysqlsh/prompt/prompt_256pl+aw.json ~/.mysqlsh/prompt.json # mysqlsh clusteradmin@mysql2 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 57 / 230
- 58. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 58/230 MySQL 8.0.16 is already installed on mysql3. Let´s take a backup on mysql1: [mysql1 ~]# mysqlpump --set-gtid-purged=ON --exclude-databases=mysql --include-users=repl%,clusteradmin%,app% > /tmp/backup.sql Copy the backup from mysql1 to mysql3: [mysql1 ~]# scp -r /tmp/backup.sql mysql3:/tmp LAB2: Prepare mysql3 - Asynchronous slave Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 58 / 230
- 59. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 59/230 restore the dump change MASTER start replication LAB3: mysql3 as asynchronous slave (1) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 59 / 230
- 60. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 60/230 LAB3: mysql3 as asynchronous slave (2) Restore backup: [mysql3 ~]# mysql < /tmp/backup.sql Connect to mysql3 and setup replication: mysql3> CHANGE MASTER TO MASTER_HOST="mysql1", MASTER_USER="repl", MASTER_PASSWORD='X', MASTER_AUTO_POSITION=1; mysql3> START SLAVE; Check that you receive the application´s traffic Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 60 / 230
- 61. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 61/230 Administration made easy and more... MySQL InnoDB Cluster Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 61 / 230
- 62. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 62/230 LAB4: MySQL InnoDB Cluster Create a single instance cluster (mysql3) # mysqlsh Let´s verify if our server is ready to become a member of a new cluster: mysql-js> dba.checkInstanceCon guration('clusteradmin@mysql3') Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 62 / 230
- 63. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 63/230 LAB4: MySQL InnoDB Cluster Create a single instance cluster (mysql3) # mysqlsh Let´s verify if our server is ready to become a member of a new cluster: mysql-js> dba.checkInstanceCon guration('clusteradmin@mysql3') Change the configuration ! mysql-js> dba.con gureInstance('clusteradmin@mysql3') Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 63 / 230
- 64. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 64/230 LAB4: MySQL InnoDB Cluster (2) Wait just a bit if a restart is required (which is not the case here) and create a single instance cluster mysql-js> dba.checkInstanceCon guration('clusteradmin@mysql3') mysql-js> c clusteradmin@mysql3 mysql-js> cluster = dba.createCluster('perconalive') Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 64 / 230
- 65. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 65/230 LAB4: Cluster Status mysql-js> cluster.status() { "clusterName": "perconalive", "defaultReplicaSet": { "name": "default", "primary": "mysql3:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "mysql3:3306": { "address": "mysql3:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE", "version": "8.0.16" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "mysql3:3306" } Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 65 / 230
- 66. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 66/230 Add mysql4 to the Group: restore the backup use MySQL Shell LAB5: add mysql4 to the cluster (1) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 66 / 230
- 67. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 67/230 LAB5: add mysql4 to the cluster (2) Copy the backup from mysql1 to mysql4: [mysql1 ~]# scp -r /tmp/backup.sql mysql4:/tmp Restore the backup: [mysql4 ~]# mysql < /tmp/backup.sql Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 67 / 230
- 68. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 68/230 LAB5: MySQL Shell to add an instance (3) Let´s add mysql4 (check configuration, check instance and add to cluster all in one command that can be done from the opened shell in mysql3): mysql-js> dba.con gureInstance('clusteradmin@mysql4') mysql-js> cluster.addInstance('clusteradmin@mysql4') mysql-js> cluster.status() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 68 / 230
- 69. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 69/230 Cluster Status mysql-js> cluster.status() { "clusterName": "perconalive", "defaultReplicaSet": { "name": "default", "primary": "mysql3:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures. 1 member is no "topology": { "mysql3:3306": { "address": "mysql3:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql4:3306": { "address": "mysql4:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "RECOVERING" Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 69 / 230
- 70. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 70/230 Recovering progress On standard MySQL, monitor the group_replication_recovery channel to see the progress: mysql4> show slave status for channel 'group_replication_recovery'G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql3 Master_User: mysql_innodb_cluster_rpl_user ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Retrieved_Gtid_Set: 6e7d7848-860f-11e6-92e4-08002718d305:1-6, 7c1f0c2d-860d-11e6-9df7-08002718d305:1-15, b346474c-8601-11e6-9b39-08002718d305:1964-77177, e8c524df-860d-11e6-9df7-08002718d305:1-2 Executed_Gtid_Set: 7c1f0c2d-860d-11e6-9df7-08002718d305:1-7, b346474c-8601-11e6-9b39-08002718d305:1-45408, e8c524df-860d-11e6-9df7-08002718d305:1-2 ... Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 70 / 230
- 71. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 71/230 Cluster Status { "clusterName": "perconalive", "defaultReplicaSet": { "name": "default", "primary": "mysql3:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "mysql3:3306": { "address": "mysql3:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql4:3306": { "address": "mysql4:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" } Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 71 / 230
- 72. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 72/230 Asynchronous Replication When a group is created, the asynchronous SQL Thread is stopped and needs to be restarted. Verify that replication from mysql1 is still running on mysql3, and restart the SQL Thread: mysql-sql> c clusteradmin@mysql3 mysql-sql> SHOW SLAVE STATUSG mysql-sql> START SLAVE SQL_THREAD; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 72 / 230
- 73. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 73/230 point the application to the cluster Migrate the application Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 73 / 230
- 74. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 74/230 LAB6: Migrate the application Now we need to point the application to mysql3, this is the only downtime ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 74 / 230
- 75. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 75/230 LAB6: Migrate the application Now we need to point the application to mysql3, this is the only downtime ! Make sure the master and replica is in sync Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 75 / 230
- 76. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 76/230 LAB6: Migrate the application Now we need to point the application to mysql3, this is the only downtime ! Make sure the master and replica is in sync Start application, connecting to the Group Replication master (mysql3) ... [ 21257s] threads: 4, tps: 12.00, reads: 167.94, writes: 47.98, response t [ 21258s] threads: 4, tps: 6.00, reads: 83.96, writes: 23.99, response t [ 21259s] threads: 4, tps: 7.00, reads: 98.05, writes: 28.01, response t [ 31250s] threads: 4, tps: 8.00, reads: 111.95, writes: 31.99, response t [ 31251s] threads: 4, tps: 11.00, reads: 154.01, writes: 44.00, response t [ 31252s] threads: 4, tps: 11.00, reads: 153.94, writes: 43.98, response t [ 31253s] threads: 4, tps: 10.01, reads: 140.07, writes: 40.02, response t ^C [mysql1 ~]# run_app.sh mysql3 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 76 / 230
- 77. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 77/230 LAB6: Migrate the application Make sure gtid_executed range on mysql2 is lower or equal than on mysql3 mysql[2-3]> SHOW GLOBAL VARIABLES LIKE 'gtid_executed'G Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 77 / 230
- 78. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 78/230 LAB6: Migrate the application Make sure gtid_executed range on mysql2 is lower or equal than on mysql3 mysql[2-3]> SHOW GLOBAL VARIABLES LIKE 'gtid_executed'G When they are OK, stop asynchronous replication on mysql2 and mysql3: mysql2> STOP SLAVE; mysql3> STOP SLAVE; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 78 / 230
- 79. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 79/230 LAB6: Migrate the application Make sure gtid_executed range on mysql2 is lower or equal than on mysql3 mysql[2-3]> SHOW GLOBAL VARIABLES LIKE 'gtid_executed'G When they are OK, stop asynchronous replication on mysql2 and mysql3: mysql2> STOP SLAVE; mysql3> STOP SLAVE; Now they can forget about mysql1: mysql[2-3]> RESET SLAVE ALL FOR CHANNEL ''; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 79 / 230
- 80. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 80/230 previous slave (mysql2) can now be part of the cluster Add a third instance Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 80 / 230
- 81. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 81/230 LAB7: Add mysql2 to the group We first upgrade to MySQL 8.0.16 : Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 81 / 230
- 82. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 82/230 LAB7: Add mysql2 to the group We first upgrade to MySQL 8.0.16 : [mysql2 ~]# systemctl stop mysqld [mysql2 ~]# rpm -Uvh /root/rpms/*rpm --force [mysql2 ~]# systemctl start mysqld Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 82 / 230
- 83. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 83/230 LAB7: Add mysql2 to the group We first upgrade to MySQL 8.0.16 : [mysql2 ~]# systemctl stop mysqld [mysql2 ~]# rpm -Uvh /root/rpms/*rpm --force [mysql2 ~]# systemctl start mysqld No need to run mysql_updgrade as we are using MySQL 8.0.16 ! 😁 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 83 / 230
- 84. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 84/230 LAB7: Add mysql2 to the group (2) mysql-js> dba.con gureInstance('clusteradmin@mysql2') Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 84 / 230
- 85. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 85/230 LAB7: Add mysql2 to the group (2) mysql-js> dba.con gureInstance('clusteradmin@mysql2') In case you don´t have the cluster object in your shell anymore, you can still retrieve it: mysql-js> c clusteradmin@mysql3 mysql-js> cluster = dba.getCluster() Add mysql2 to the cluster: mysql-js> cluster.checkInstanceState('clusteradmin@mysql2') mysql-js> cluster.addInstance("clusteradmin@mysql2") mysql-js> cluster.status() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 85 / 230
- 86. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 86/230 LAB7: Add mysql2 to the group (3) { "clusterName": "perconalive", "defaultReplicaSet": { "name": "default", "primary": "mysql3:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "mysql2:3306": { "address": "mysql2:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "mysql3:3306": { "address": "mysql3:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 86 / 230
- 87. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 87/230 get more info Monitoring Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 87 / 230
- 88. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 88/230 Performance Schema Group Replication uses Performance_Schema to expose status mysql3> SELECT * FROM performance_schema.replication_group_membersG *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: ade14d5c-9e1e-11e7-b034-08002718d305 MEMBER_HOST: mysql4 MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: SECONDARY MEMBER_VERSION: 8.0.16 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: b9d01593-9dfb-11e7-8ca6-08002718d305 MEMBER_HOST: mysql3 MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: PRIMARY MEMBER_VERSION: 8.0.16 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 88 / 230
- 89. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 89/230 mysql3> SELECT * FROM performance_schema.replication_connection_statusG *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier GROUP_NAME: 8fc848d7-9e1c-11e7-9407... SOURCE_UUID: 8fc848d7-9e1c-11e7-9407... THREAD_ID: NULL SERVICE_STATE: ON COUNT_RECEIVED_HEARTBEATS: 0 LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00 RECEIVED_TRANSACTION_SET: 8fc848d7-9e1c-11e7-9407... b9d01593-9dfb-11e7-8ca6-08002718d305:1-21, da2f0910-8767-11e6-b82d-08002718d305:1-164741 LAST_ERROR_NUMBER: 0 LAST_ERROR_MESSAGE: LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00 LAST_QUEUED_TRANSACTION: 8fc848d7-9e1c-11e7-9407... LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00 LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00 LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2017-09-20 16:22:36.486... LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2017-09-20 16:22:36.486... QUEUEING_TRANSACTION: QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00 QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00 QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 89 / 230
- 90. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 90/230 Member State These are the different possible state for a node member: ONLINE OFFLINE RECOVERING ERROR: when a node is leaving but the plugin was not instructed to stop UNREACHABLE Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 90 / 230
- 91. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 91/230 Status information & metrics Members mysql> select member_host, member_state, member_role, member_version from performance_schema.replication_group_members; +-------------+--------------+-------------+----------------+ | member_host | member_state | member_role | member_version | +-------------+--------------+-------------+----------------+ | mysql3 | ONLINE | PRIMARY | 8.0.16 | | mysql4 | ONLINE | SECONDARY | 8.0.16 | | mysql2 | ONLINE | SECONDARY | 8.0.16 | +-------------+--------------+-------------+----------------+ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 91 / 230
- 92. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 92/230 Status information & metrics - connections To see group_replication_applier and group_replication_recovery: mysql> SELECT * FROM performance_schema.replication_connection_statusG 8.0 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 92 / 230
- 93. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 93/230 Status information & metrics Previously there were only local node statistics, now they are exposed all over the Group mysql> select * from performance_schema.replication_group_member_statsG Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 93 / 230
- 94. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 94/230 Status information & metrics Previously there were only local node statistics, now they are exposed all over the Group mysql> select * from performance_schema.replication_group_member_statsG *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 15059231192196925:2 MEMBER_ID: ade14d5c-9e1e-11e7-b034-08002... COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 27992 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 TRANSACTIONS_COMMITTED_ALL_MEMBERS: 8fc848d7-9e1c-11e7-9407-08002... b9d01593-9dfb-11e7-8ca6-08002718d305:1-21, da2f0910-8767-11e6-b82d-08002718d305:1-164741 LAST_CONFLICT_FREE_TRANSACTION: 8fc848d7-9e1c-11e7-9407-08002... COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0 COUNT_TRANSACTIONS_REMOTE_APPLIED: 27992 COUNT_TRANSACTIONS_LOCAL_PROPOSED: 0 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 94 / 230
- 95. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 95/230 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier VIEW_ID: 15059231192196925:2 MEMBER_ID: b9d01593-9dfb-11e7-8ca6-08002... COUNT_TRANSACTIONS_IN_QUEUE: 0 COUNT_TRANSACTIONS_CHECKED: 28000 COUNT_CONFLICTS_DETECTED: 0 COUNT_TRANSACTIONS_ROWS_VALIDATING: 0 TRANSACTIONS_COMMITTED_ALL_MEMBERS: 8fc848d7-9e1c-11e7-9407-08002... b9d01593-9dfb-11e7-8ca6-08002718d305:1-21, da2f0910-8767-11e6-b82d-08002718d305:1-164741 LAST_CONFLICT_FREE_TRANSACTION: 8fc848d7-9e1c-11e7-9407-08002... COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE: 0 COUNT_TRANSACTIONS_REMOTE_APPLIED: 1 COUNT_TRANSACTIONS_LOCAL_PROPOSED: 28000 COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 95 / 230
- 96. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 96/230 Performance_Schema You can find GR information in the following Performance_Schema tables: replication_applier_con guration replication_applier_status replication_applier_status_by_worker replication_connection_con guration replication_connection_status replication_group_member_stats replication_group_members Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 96 / 230
- 97. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 97/230 Get the info from the MySQL Shell It´s also possible to get more info using the Shell: mysql-js> cluster.status({extended: true}) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 97 / 230
- 98. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 98/230 Get the info from the MySQL Shell It´s also possible to get more info using the Shell: mysql-js> cluster.status({extended: true}) In the next release you will also be able to see info from each members: mysql-js> cluster.status({queryMembers: true}) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 98 / 230
- 99. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 99/230 Status during recovery mysql> SHOW SLAVE STATUS FOR CHANNEL 'group_replication_recovery'G Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 99 / 230
- 100. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 100/230 Status during recovery mysql> SHOW SLAVE STATUS FOR CHANNEL 'group_replication_recovery'G *************************** 1. row *************************** Slave_IO_State: Master_Host: <NULL> Master_User: gr_repl Master_Port: 0 ... Relay_Log_File: mysql4-relay-bin-group_replication_recovery.000001 ... Slave_IO_Running: No Slave_SQL_Running: No ... Executed_Gtid_Set: 5de4400b-3dd7-11e6-8a71-08002774c31b:1-814089, afb80f36-2bff-11e6-84e0-0800277dd3bf:1-5718 ... Channel_Name: group_replication_recovery Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 100 / 230
- 101. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 101/230 Sys Schema The easiest way to detect if a node is a member of the primary component (when there are partitioning of your nodes due to network issues for example) and therefore a valid candidate for routing queries to it, is to use the sys table. Additional information for sys can be found at https://goo.gl/XFp3bt On the primary node: [mysql3 ~]# mysql < /root/addition_to_sys_8.0.2.sql Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 101 / 230
- 102. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 102/230 Sys Schema Is this node part of PRIMARY Partition: mysql3> SELECT sys.gr_member_in_primary_partition(); +------------------------------------+ | sys.gr_node_in_primary_partition() | +------------------------------------+ | YES | +------------------------------------+ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 102 / 230
- 103. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 103/230 Sys Schema Is this node part of PRIMARY Partition: mysql3> SELECT sys.gr_member_in_primary_partition(); +------------------------------------+ | sys.gr_node_in_primary_partition() | +------------------------------------+ | YES | +------------------------------------+ To use as healthcheck: mysql3> SELECT * FROM sys.gr_member_routing_candidate_status; +------------------+-----------+---------------------+----------------------+ | viable_candidate | read_only | transactions_behind | transactions_to_cert | +------------------+-----------+---------------------+----------------------+ | YES | YES | 0 | 0 | +------------------+-----------+---------------------+----------------------+ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 103 / 230
- 104. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 104/230 LAB8: Sys Schema - Health Check On one of the non Primary nodes, run the following command: mysql-sql> ush tables with read lock; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 104 / 230
- 105. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 105/230 LAB8: Sys Schema - Health Check On one of the non Primary nodes, run the following command: mysql-sql> ush tables with read lock; Now you can verify what the healthcheck exposes to you: mysql-sql> SELECT * FROM sys.gr_member_routing_candidate_status; +------------------+-----------+---------------------+----------------------+ | viable_candidate | read_only | transactions_behind | transactions_to_cert | +------------------+-----------+---------------------+----------------------+ | YES | YES | 950 | 0 | +------------------+-----------+---------------------+----------------------+ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 105 / 230
- 106. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 106/230 LAB8: Sys Schema - Health Check On one of the non Primary nodes, run the following command: mysql-sql> ush tables with read lock; Now you can verify what the healthcheck exposes to you: mysql-sql> SELECT * FROM sys.gr_member_routing_candidate_status; +------------------+-----------+---------------------+----------------------+ | viable_candidate | read_only | transactions_behind | transactions_to_cert | +------------------+-----------+---------------------+----------------------+ | YES | YES | 950 | 0 | +------------------+-----------+---------------------+----------------------+ mysql-sql> UNLOCK TABLES; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 106 / 230
- 107. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 107/230 LAB9: Reporting in MySQL Shell 8.0.16 Since 8.0.16, it´s possible in MySQL Shell to create User Defined Reports. We will create a report to monitor the asynchronous replication used in the recovery channel and see the progress of it. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 107 / 230
- 108. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 108/230 LAB9: Reporting in MySQL Shell 8.0.16 Since 8.0.16, it´s possible in MySQL Shell to create User Defined Reports. We will create a report to monitor the asynchronous replication used in the recovery channel and see the progress of it. Let´s start by stopping group replication on mysql2: mysql2> stop group_replication; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 108 / 230
- 109. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 109/230 LAB9: Reporting in MySQL Shell 8.0.16 (2) Now it´s time to create the folder where the User Defined Reports can be installed: # mkdir ~/.mysqlsh/init.d # cd ~/.mysqlsh/init.d And we can download the report: wget https://tinyurl.com/gr-recovery -O gr_recovery_progress.py Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 109 / 230
- 110. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 110/230 LAB9: Reporting in MySQL Shell 8.0.16 (2) Now it´s time to create the folder where the User Defined Reports can be installed: # mkdir ~/.mysqlsh/init.d # cd ~/.mysqlsh/init.d And we can download the report: wget https://tinyurl.com/gr-recovery -O gr_recovery_progress.py mysqlsh clusteradmin@mysql2 mysql-js> show Available reports: gr_recovery_progress, query. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 110 / 230
- 111. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 111/230 LAB9: Reporting in MySQL Shell 8.0.16 (3) Now we can put back mysql2 in the group and use the new report query: mysql-js> sql start group_replication mysql-js> show gr_recovery_progress +----------------+ | trx_to_recover | +----------------+ | 8673 | +----------------+ You can also try watch gr_recovery_progress Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 111 / 230
- 112. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 112/230 Connecting to the cluster MySQL Router Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 112 / 230
- 113. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 113/230 MySQL Router MySQL Router is lightweight middleware that provides transparent routing between your application and backend MySQL Servers. It can be used for a wide variety of use cases, such as providing high availability and scalability by effectively routing database traffic to appropriate backend MySQL Servers. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 113 / 230
- 114. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 114/230 MySQL Router MySQL Router is lightweight middleware that provides transparent routing between your application and backend MySQL Servers. It can be used for a wide variety of use cases, such as providing high availability and scalability by effectively routing database traffic to appropriate backend MySQL Servers. MySQL Router doesn´t require any specific configuration. It configures itself automatically (bootstrap) using MySQL InnoDB Cluster ´s metadata. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 114 / 230
- 115. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 115/230 LAB10: MySQL Router We will now use mysqlrouter between our application and the cluster. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 115 / 230
- 116. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 116/230 LAB10: MySQL Router (2) Configure MySQL Router that will run on the app server (mysql1). We bootstrap it using the Primary-Master: [root@mysql1 ˜]# mysqlrouter --bootstrap clusteradmin@mysql3:3306 --user mysqlrouter Please enter MySQL password for clusteradmin: Bootstrapping system MySQL Router instance... Checking for old Router accounts Creating account mysql_router1_rvbecwmlc6uf@'%' MySQL Router has now been con gured for the InnoDB cluster 'perconalive'. The following connection information can be used to connect to the cluster after MySQL Router has been started with generated con guration.. Classic MySQL protocol connections to cluster 'perconalive': - Read/Write Connections: localhost:6446 - Read/Only Connections: localhost:6447 X protocol connections to cluster 'perconalive': - Read/Write Connections: localhost:64460 - Read/Only Connections: localhost:64470 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 116 / 230
- 117. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 117/230 LAB10: MySQL Router (3) Now let´s modify the configuration file to listen on port 3306: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 117 / 230
- 118. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 118/230 LAB10: MySQL Router (3) Now let´s modify the configuration file to listen on port 3306: in /etc/mysqlrouter/mysqlrouter.conf: [routing:perconalive_default_rw] -bind_port=6446 +bind_port=3306 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 118 / 230
- 119. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 119/230 LAB10: MySQL Router (3) Now let´s modify the configuration file to listen on port 3306: in /etc/mysqlrouter/mysqlrouter.conf: [routing:perconalive_default_rw] -bind_port=6446 +bind_port=3306 We can stop mysqld on mysql1 and start mysqlrouter: [mysql1 ~]# systemctl stop mysqld [mysql1 ~]# systemctl start mysqlrouter Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 119 / 230
- 120. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 120/230 LAB10: MySQL Router (5) Now we can point the application to the router (back to mysql1): [mysql1 ~]# run_app.sh Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 120 / 230
- 121. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 121/230 LAB10: MySQL Router (5) Now we can point the application to the router (back to mysql1): [mysql1 ~]# run_app.sh Check app and kill mysqld on mysql3 (the Primary Master R/W node) ! [mysql3 ~]# failure-scenario.sh kill-node Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 121 / 230
- 122. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 122/230 LAB10: MySQL Router (5) Now we can point the application to the router (back to mysql1): [mysql1 ~]# run_app.sh Check app and kill mysqld on mysql3 (the Primary Master R/W node) ! [mysql3 ~]# failure-scenario.sh kill-node mysql2> select member_host as `primary` from performance_schema.replication_group_members where member_role='PRIMARY'; +----------+ | primary | +----------+ | mysql4 | +----------+ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 122 / 230
- 123. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 123/230 ProxySQL / HA Proxy / F5 / ... 3rd party router/proxy Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 123 / 230
- 124. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 124/230 3rd party router/proxy MySQL InnoDB Cluster can also work with third party router / proxy. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 124 / 230
- 125. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 125/230 3rd party router/proxy MySQL InnoDB Cluster can also work with third party router / proxy. If you need some specific features that are not yet available in MySQL Router, like transparent R/W splitting, then you can use your software of choice. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 125 / 230
- 126. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 126/230 3rd party router/proxy MySQL InnoDB Cluster can also work with third party router / proxy. If you need some specific features that are not yet available in MySQL Router, like transparent R/W splitting, then you can use your software of choice. The important part of such implementation is to use a good health check to verify if the MySQL server you plan to route the traffic is in a valid state. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 126 / 230
- 127. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 127/230 3rd party router/proxy MySQL InnoDB Cluster can also work with third party router / proxy. If you need some specific features that are not yet available in MySQL Router, like transparent R/W splitting, then you can use your software of choice. The important part of such implementation is to use a good health check to verify if the MySQL server you plan to route the traffic is in a valid state. MySQL Router implements that natively, and it´s very easy to deploy. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 127 / 230
- 128. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 128/230 ProxySQL also has native support for Group Replication which makes it maybe the best choice for advanced users. 3rd party router/proxy MySQL InnoDB Cluster can also work with third party router / proxy. If you need some specific features that are not yet available in MySQL Router, like transparent R/W splitting, then you can use your software of choice. The important part of such implementation is to use a good health check to verify if the MySQL server you plan to route the traffic is in a valid state. MySQL Router implements that natively, and it´s very easy to deploy. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 128 / 230
- 129. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 129/230 operational tasks Recovering Node Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 129 / 230
- 130. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 130/230 Recovering Nodes/Members The old master (mysql3) got killed. Start it again: mysql3# systemctl start mysqld The node will automatically join the group again Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 130 / 230
- 131. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 131/230 You know... There are ways... Various Configuration Options Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 131 / 230
- 132. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 132/230 Various Configuration Options Single & Multiple Primary Mode Network Partitions What to do? when will a node leave? when will a node be expelled? what should a node do when it´s gone? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 132 / 230
- 133. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 133/230 Default = Single Primary Mode By default, MySQL InnoDB Cluster enables Single Primary Mode. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 133 / 230
- 134. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 134/230 Default = Single Primary Mode By default, MySQL InnoDB Cluster enables Single Primary Mode. mysql> show global variables like 'group_replication_single_primary_mode'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | group_replication_single_primary_mode | ON | +---------------------------------------+-------+ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 134 / 230
- 135. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 135/230 Default = Single Primary Mode By default, MySQL InnoDB Cluster enables Single Primary Mode. mysql> show global variables like 'group_replication_single_primary_mode'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | group_replication_single_primary_mode | ON | +---------------------------------------+-------+ In Single Primary Mode, a single member acts as the writable master (PRIMARY) and the rest of the members act as hot- standbys (SECONDARY). The group itself coordinates and configures itself automatically to determine which member will act as the PRIMARY, through a leader election mechanism. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 135 / 230
- 136. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 136/230 Who´s the Primary Master ? You can already see it in the output of cluster.status() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 136 / 230
- 137. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 137/230 Who´s the Primary Master ? You can already see it in the output of cluster.status() But you can also check in performance_schema: mysql> select member_host from performance_schema.replication_group_members where member_role='PRIMARY'; +-------------+ | member_host | +-------------+ | mysql3 | +-------------+ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 137 / 230
- 138. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 138/230 Create a Multi-Primary Cluster: It´s also possible to create a Multi-Primary Cluster using the Shell: mysql-js> cluster=dba.createCluster('perconalive',{multiMaster: true}) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 138 / 230
- 139. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 139/230 Create a Multi-Primary Cluster: It´s also possible to create a Multi-Primary Cluster using the Shell: mysql-js> cluster=dba.createCluster('perconalive',{multiMaster: true}) A new InnoDB cluster will be created on instance 'clusteradmin@mysql3:3306'. The MySQL InnoDB cluster is going to be setup in advanced Multi-Master Mode. Before continuing you have to con rm that you understand the requirements and limitations of Multi-Master Mode. Please read the manual before proceeding. I have read the MySQL InnoDB cluster manual and I understand the requirements and limitations of advanced Multi-Master Mode. Con rm [y|N]: Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 139 / 230
- 140. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 140/230 Create a Multi-Primary Cluster: It´s also possible to create a Multi-Primary Cluster using the Shell: mysql-js> cluster=dba.createCluster('perconalive',{multiMaster: true}) A new InnoDB cluster will be created on instance 'clusteradmin@mysql3:3306'. The MySQL InnoDB cluster is going to be setup in advanced Multi-Master Mode. Before continuing you have to con rm that you understand the requirements and limitations of Multi-Master Mode. Please read the manual before proceeding. I have read the MySQL InnoDB cluster manual and I understand the requirements and limitations of advanced Multi-Master Mode. Con rm [y|N]: Or you can force it to avoid interaction (for automation) : Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 140 / 230
- 141. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 141/230 Create a Multi-Primary Cluster: It´s also possible to create a Multi-Primary Cluster using the Shell: mysql-js> cluster=dba.createCluster('perconalive',{multiMaster: true}) A new InnoDB cluster will be created on instance 'clusteradmin@mysql3:3306'. The MySQL InnoDB cluster is going to be setup in advanced Multi-Master Mode. Before continuing you have to con rm that you understand the requirements and limitations of Multi-Master Mode. Please read the manual before proceeding. I have read the MySQL InnoDB cluster manual and I understand the requirements and limitations of advanced Multi-Master Mode. Con rm [y|N]: Or you can force it to avoid interaction (for automation) : > cluster=dba.createCluster('perconalive',{multiMaster: true, force: true}) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 141 / 230
- 142. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 142/230 Dynamically change to Multi Primary Mode (UDFs) Change to/from single primary mode online: mysql3> SELECT group_replication_switch_to_multi_primary_mode(); mysql3> SELECT group_replication_switch_to_single_primary_mode(); Choose primary node mysql3> SELECT group_replication_set_as_primary(server_uuid); Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 142 / 230
- 143. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 143/230 Dynamically change to Multi Primary Mode (UDFs) mysql3> SELECT group_replication_switch_to_multi_primary_mode(); +--------------------------------------------------+ | group_replication_switch_to_multi_primary_mode() | +--------------------------------------------------+ | Mode switched to multi-primary successfully. | +--------------------------------------------------+ mysql3> select member_host from performance_schema.replication_group_members where member_role='PRIMARY'; +-------------+ | member_host | +-------------+ | mysql2 | | mysql3 | | mysql4 | +-------------+ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 143 / 230
- 144. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 144/230 Dynamically change Mode: UDFs VS Metadata However, this should be done from the MySQL Shell to avoid metadata inconsistencies. Try to get the cluster status: JS> cluster.status() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 144 / 230
- 145. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 145/230 Dynamically change Mode: UDFs VS Metadata However, this should be done from the MySQL Shell to avoid metadata inconsistencies. Try to get the cluster status: JS> cluster.status() What do you see ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 145 / 230
- 146. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 146/230 Dynamically change Mode: UDFs VS Metadata However, this should be done from the MySQL Shell to avoid metadata inconsistencies. Try to get the cluster status: JS> cluster.status() What do you see ? Fix it: JS> cluster.rescan() JS> cluster.status() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 146 / 230
- 147. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 147/230 Dynamically change to Single Primary Mode - Shell Switch back to Single Primary Mode: JS> cluster.switchToSinglePrimaryMode() JS> cluster.status() Other MySQL Shell syntax: (do not execute !) JS> cluster.setPrimaryInstance('mysql1:3306') JS> cluster.switchToMultiPrimaryMode() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 147 / 230
- 148. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 148/230 Network Partitions What happens when mysql4 is being network partitioned? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 148 / 230
- 149. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 149/230 Network Partitions - Quorum Calculations 3 notes, each 1 vote. Majority is when a partition sees +50% Minority is when a partition sees <=50% Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 149 / 230
- 150. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 150/230 Network Partitions - Quorum Calculations 3 notes, each 1 vote. Majority is when a partition sees +50% Minority is when a partition sees <=50% mysql2 and mysql3 can still connect to eachother, 2 votes out of 3 = 66% = MAJORITY mysql4 can only see itself, 1 vote out of 3 = 33% = MINORITY Majority network partition is mysql2 and mysql3 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 150 / 230
- 151. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 151/230 Network Partitions - Quorum Calculations With even amount of nodes: it is possible to have 50% for each partition both will have minority Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 151 / 230
- 152. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 152/230 Network Partitions - Actions by Majority mysql2 and mysql3 actions: 1. wait 5 seconds, then suspect mysql4 is gone 2. expel mysql4 after group_replication_member_expel_timeout seconds (default 0, immediately) do not put the variable too high, between suspect and expel members cannot be added/removed no new leader can be elected Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 152 / 230
- 153. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 153/230 Network Partitions - Actions by Minority mysql4 actions: 1. wait 5 seconds before suspecting itself to lost connection to the majority 2. wait group_replication_unreachable_majority_timeout (default 0, forever) 3. when timeout: group_replication_autorejoin_tries (default 0 times) retries every 5 minutes 4. Only then group_replication_exit_state_action READ_ONLY: allow stale reads to be served (latest default) ABORT_SERVER: Shutdown MySQL Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 153 / 230
- 154. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 154/230 LAB11: Network Partitions - Default Let´s network partition mysql4! Check logs (we have log_error_verbosity=3) mysql3# tail -f /var/log/mysqld.log & mysql4# tail -f /var/log/mysqld.log & On mysql4, run: mysql4# failure-scenario.sh network-partition begin Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 154 / 230
- 155. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 155/230 LAB11: Network Partitions - Default mysql3 '[GCS] Installed site start={ed88d526 1339 0} boot_key={ed88d526 1328 0} event_horizon=10 node 1' '[GCS] Group is able to support up to communication protocol version 8.0.16' 'Members removed from the group: mysql4:3306' 'Group membership changed to mysql3:3306, mysql2:3306 on view 1557809996177 '[GCS] Failure reading from fd=-1 n=18446744073709551615' mysql4 'Member with address mysql2:3306 has become unreachable.' 'Member with address mysql3:3306 has become unreachable.' 'This server is not able to reach a majority of members in the group. This server will now block all updates. The server will remain blocked until contact with the majority is restored. It is possible to use group_replication_force_members to force a new group membership.' Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 155 / 230
- 156. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 156/230 LAB11: Network Partition - Default mysql4# failure-scenario.sh network-partition end Does the node come back? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 156 / 230
- 157. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 157/230 LAB11: Network Partition - Default mysql4# failure-scenario.sh network-partition end Does the node come back? NO: it was expelled: 'Member with address mysql2:3306 is reachable again.' 'Member with address mysql3:3306 is reachable again.' 'The member has resumed contact with a majority of the members in the group. Regular operation is restored and transactions are unblocked.' '[GCS] Re-using server node 0 host mysql2' '[GCS] Re-using server node 1 host mysql3' '[GCS] Installed site start={ed88d526 1339 0} boot_key={ed88d526 1328 0} event_horizon=10 node 4294967295' '[GCS] Node has already been removed: mysql4:33061 15578108329721490.' 'Member was expelled from the group due to network failures, changing member status to ERROR.' Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 157 / 230
- 158. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 158/230 LAB11: Network Partition - Default Fix with shell: JS>cluster.rejoinInstance('clusteradmin@mysql4') Rejoining the instance to the InnoDB cluster. Depending on the original problem that made the instance unavailable, the rejoin operation might not be successful and further manual steps will be needed to x the underlying problem. Please monitor the output of the rejoin operation and take necessary action if the instance cannot rejoin. Rejoining instance to the cluster ... The instance 'mysql4' was successfully rejoined on the cluster. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 158 / 230
- 159. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 159/230 LAB12: Network Partitions - With Retries Let´s try again, but let´s configure retries: mysql4> SET GLOBAL group_replication_autorejoin_tries=2; # default is 0 mysql4# failure-scenario.sh network-partition begin && sleep 10 && failure-scenario.sh network-partition end Did mysql4 join the group again? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 159 / 230
- 160. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 160/230 LAB12: Network Partitions - With Retries Did mysql4 join the group again? YES! 'Member with address mysql2:3306 is reachable again.' 'The member has resumed contact with a majority of the members in the group. Regular operation is restored and transactions are unblocked.' 'Member with address mysql3:3306 is reachable again.' '[GCS] Re-using server node 0 host mysql2' '[GCS] Re-using server node 1 host mysql3' '[GCS] Installed site start={ed88d526 2445 0} boot_key={ed88d526 2434 0} event_horizon=10 node 4294967295' '[GCS] Node has already been removed: mysql4:33061 15578118987619030.' 'Member was expelled from the group due to network failures, changing member status to ERROR.' 'Started auto-rejoin procedure attempt 1 of 2' ... 'This server was declared online within the replication group.' Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 160 / 230
- 161. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 161/230 LAB12: Network Partitions - monitoring auto-rejoin It´s possible to verify if there is an auto-rejoin: SELECT COUNT(*) FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE '%auto-rejoin%'; View the amount of retries so far: SELECT WORK_COMPLETED FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE '%auto-rejoin%'; Estimated time remaining until next retry: SELECT (300.0 - ((TIMER_WAIT*10e-12) - 300.0 * num_retries)) AS time_remaining FROM (SELECT COUNT(*) - 1 AS num_retries FROM performance_schema.events_stages_current WHERE EVENT_NAME LIKE '%auto-rejoin%') AS T, performance_schema.events_stages_current WHERE EVENT_NAME LIKE '%auto-rejoin%'; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 161 / 230
- 162. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 162/230 be aware of the behavior change & take control Application Behavior & Consistency Guarantees Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 162 / 230
- 163. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 163/230 Application Behavior & Consistency Guarantees Group Conflicts Configurable Consistency Guarantees Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 163 / 230
- 164. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 164/230 Group Conflicts Local vs. Group: Local Conflicts are handled using InnoDB's row locking. Conflicts between different nodes are handled with "first committer wins!" Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 164 / 230
- 165. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 165/230 Group Conflicts Local vs. Group: Local Conflicts are handled using InnoDB's row locking. Conflicts between different nodes are handled with "first committer wins!" Group Conflicts: happen only in Multi-Primary Mode happen if 2 transactions are updating/deleting the same records on two different members of the group, will be detected only during group certification (after at least one has committed) (Optimistic Locking) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 165 / 230
- 166. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 166/230 LAB13: Group Conflicts Let´s see this in action ! First switch to Multi-Primary Mode: mysql-js> cluster.switchToMultiPrimaryMode() Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 166 / 230
- 167. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 167/230 LAB13: Group Conflicts Let´s see this in action ! First switch to Multi-Primary Mode: mysql-js> cluster.switchToMultiPrimaryMode() Now on mysql2 start the following transaction: mysql2> start transaction; mysql2> update app.sbtest1 set k=k+50000 where id = 2000; Note that we don't commit the transaction yet Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 167 / 230
- 168. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 168/230 LAB13: Group Conflicts (2) Now on mysql4 we also create a new transaction: mysql4> start transaction; mysql4> update app.sbtest1 set k=k+200 where id > 1999 and id < 2010; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 168 / 230
- 169. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 169/230 LAB13: Group Conflicts (2) Now on mysql4 we also create a new transaction: mysql4> start transaction; mysql4> update app.sbtest1 set k=k+200 where id > 1999 and id < 2010; Now commit the one in mysql4: mysql4> commit; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 169 / 230
- 170. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 170/230 LAB13: Group Conflicts (3) What do you see on mysql2 ´s session ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 170 / 230
- 171. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 171/230 LAB13: Group Conflicts (3) What do you see on mysql2 ´s session ? If you try any statement you will see: ERROR: 1213: Deadlock found when trying to get lock; try restarting transaction If you try to commit you will see: ERROR: 1180: Got error 149 - 'Lock deadlock; Retry transaction' during COMMIT This happens because a conflict was detected during certification ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 171 / 230
- 172. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 172/230 Group Conflicts (4) 2 types of Group Conflicts: Conflicts detected by InnoDB high priority transactions (previous lab) Conflicts detected by Group Replication: Commit 2 transactions at almost the same time: ERROR 3101 (40000): Plugin instructed the server to rollback the current transaction. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 172 / 230
- 173. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 173/230 Group Conflicts (5) Check these in Performance_Schema (or using cluster.status({extended: true}): SELECT MEMBER_ID,COUNT_CONFLICTS_DETECTED,COUNT_TRANSACTIONS_LOCAL_ROLLBACK FROM performance_schema.replication_group_member_statsG *************************** 1. row *************************** MEMBER_ID: 130942a7-75c4-11e9-b75d-08002718d305 COUNT_CONFLICTS_DETECTED: 1 COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0 *************************** 2. row *************************** MEMBER_ID: 265d96f7-7333-11e9-b4c9-08002718d305 COUNT_CONFLICTS_DETECTED: 1 COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 1 *************************** 3. row *************************** MEMBER_ID: 6361b4e3-75c2-11e9-851b-08002718d305 COUNT_CONFLICTS_DETECTED: 1 COUNT_TRANSACTIONS_LOCAL_ROLLBACK: 0 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 173 / 230
- 174. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 174/230 Configurable Consistency Guarantees Default consistency guarantee is EVENTUAL if you write some data on nodeA, if you immediately read it on nodeB, it might be there... or not yet. Since MySQL 8.0.14, you can configure various transaction consistency guarantees mysql> show variables like 'group_replication_consistency'; +-------------------------------+----------+ | Variable_name | Value | +-------------------------------+----------+ | group_replication_consistency | EVENTUAL | +-------------------------------+----------+ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 174 / 230
- 175. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 175/230 Consistency Levels The consistency levels are defined in group_replication_consistency. The scope can be SESSION or GLOBAL. The possible values are: EVENTUAL (default) BEFORE_ON_PRIMARY_FAILOVER BEFORE AFTER BEFORE_AND_AFTER Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 175 / 230
- 176. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 176/230 Consistency Levels - EVENTUAL EVENTUAL: do not wait Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 176 / 230
- 177. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 177/230 Consistency Levels - BEFORE BEFORE: reads & writes wait on other trx BEFORE they start Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 177 / 230
- 178. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 178/230 Consistency Levels - AFTER AFTER: writes wait that other nodes have prepared before committing Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 178 / 230
- 179. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 179/230 Consistency Levels - BEFORE_AND_AFTER BEFORE_AND_AFTER: reads & writes wait before start, writes wait until other nodes prepared before commit Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 179 / 230
- 180. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 180/230 LAB14: Consistency Levels: EVENTUAL On mysql4 lock a table and write to that table on mysql2: mysql4> lock tables app.sbtest1 read; mysql2> update app.sbtest1 set k=k+123 where id = 2000; You can now select that row on all members: mysql[2-3-4]> select id, k from app.sbtest1 where id=2000; What do you see ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 180 / 230
- 181. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 181/230 LAB14: Consistency Levels: EVENTUAL On mysql4 lock a table and write to that table on mysql2: mysql4> lock tables app.sbtest1 read; mysql2> update app.sbtest1 set k=k+123 where id = 2000; You can now select that row on all members: mysql[2-3-4]> select id, k from app.sbtest1 where id=2000; What do you see ? mysql4> UNLOCK TABLES; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 181 / 230
- 182. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 182/230 LAB14: Consistency Levels: AFTER set the session consistency level to AFTER (WRITE) on mysql2 and lock the table on mysql4: mysql2> SET @@SESSION.group_replication_consistency='AFTER'; mysql4> lock tables app.sbtest1 read; Now let´s modify one record on mysql2: mysql2> update app.sbtest1 set k=k+456 where id = 2000; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 182 / 230
- 183. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 183/230 LAB14: Consistency Levels: AFTER set the session consistency level to AFTER (WRITE) on mysql2 and lock the table on mysql4: mysql2> SET @@SESSION.group_replication_consistency='AFTER'; mysql4> lock tables app.sbtest1 read; Now let´s modify one record on mysql2: mysql2> update app.sbtest1 set k=k+456 where id = 2000; What do you see? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 183 / 230
- 184. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 184/230 LAB14: Consistency Levels: AFTER set the session consistency level to AFTER (WRITE) on mysql2 and lock the table on mysql4: mysql2> SET @@SESSION.group_replication_consistency='AFTER'; mysql4> lock tables app.sbtest1 read; Now let´s modify one record on mysql2: mysql2> update app.sbtest1 set k=k+456 where id = 2000; What do you see? mysql4> UNLOCK TABLES; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 184 / 230
- 185. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 185/230 LAB14: Consistency Levels: observability On the server writing (mysql2), you can run this to see the query waiting for the AFTER synchronization: mysql2>SELECT *, TIME_TO_SEC(TIMEDIFF(now(),trx_started)) lock_time_sec FROM information_schema.innodb_trx JOIN information_schema.processlist ON processlist.ID=innodb_trx.trx_mysql_thread_id WHERE state='waiting for handler commit' ORDER BY trx_startedG *************************** 1. row *************************** trx_id: 49234 trx_state: RUNNING trx_started: 2019-05-14 22:34:31 trx_mysql_thread_id: 14 trx_query: update app.sbtest1 set k=456 where id = 2000 ... ID: 14 USER: clusteradmin HOST: mysql3:33484 STATE: waiting for handler commit INFO: PLUGIN: update app.sbtest1 set k=k+456 where id=2000 lock_time_sec: 52 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 185 / 230
- 186. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 186/230 More about Consistency Levels By default the wait time (timeout) for these operations is very long (8 hours) and can be modified to a shorter period by setting wait_timeout to a shorter delay. More examples and observability queries at https://lefred.be/content/mysql-innodb-cluster-consistency-levels/ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 186 / 230
- 187. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 187/230 Back to Single Primary Mode Let´s put the cluster back in single primary mode and make sure mysql3 is primary mysql-js> cluster.switchToSinglePrimaryMode('mysql3:3306') Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 187 / 230
- 188. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 188/230 Let´s break it! Failure Scenarios Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 188 / 230
- 189. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 189/230 Failure Scenarios - Labs Stopping a node, bringing it back Full power loss No more quorum The Impact of Network Latency & Packet Loss Degraded Performance On A Node Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 189 / 230
- 190. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 190/230 Stopping A Node As seen in previous labs: nodes can be removed/expelled from cluster But nodes can be gracefully removed as well (for maintenance...) 2 distinct terms are used: Group: The current set of members of an active Group Replication view. Cluster: All mysql nodes defined in MySQL InnoDB Cluster, regardless if they are active or not. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 190 / 230
- 191. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 191/230 LAB15: Stopping A Node On mysql4, check the error log: mysql4# tail -f /var/log/mysqld.log Cleanly stop MySQL on mysql2: mysql2# systemctl stop mysqld Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 191 / 230
- 192. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 192/230 LAB15: Stopping A Node On mysql4, check the error log: mysql4# tail -f /var/log/mysqld.log Cleanly stop MySQL on mysql2: mysql2# systemctl stop mysqld We can see: [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: mysql2:3306' Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 192 / 230
- 193. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 193/230 LAB15: Stopping A Node On mysql4, check the error log: mysql4# tail -f /var/log/mysqld.log Cleanly stop MySQL on mysql2: mysql2# systemctl stop mysqld We can see: [MY-011499] [Repl] Plugin group_replication reported: 'Members removed from the group: mysql2:3306' when performing a graceful shutdown, quorum is not taken in consideration Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 193 / 230
- 194. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 194/230 LAB15: Stopping A Node : Member VS Node mysql> SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE, MEMBER_VERSION FROM performance_schema.replication_group_members; +-------------+-------------+--------------+-------------+----------------+ | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +-------------+-------------+--------------+-------------+----------------+ | mysql4 | 3306 | ONLINE | PRIMARY | 8.0.16 | | mysql3 | 3306 | ONLINE | PRIMARY | 8.0.16 | +-------------+-------------+--------------+-------------+----------------+ mysql-js> cluster.status() { ... "mysql2:3306": { "address": "mysql2:3306", "mode": "n/a", "readReplicas": {}, "role": "HA", "status": "(MISSING)" }, ... } Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 194 / 230
- 195. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 195/230 LAB15: Rejoining A Node As group_replication_start_on_boot is enabled by default, just restarting mysql2 will allow it to join back the group. mysql2# systemctl start mysqld If it was not the case, from the MyShell you could use the rejoinInstance() method: mysql-js> cluster.rejoinInstance('clusteradmin@mysql2') Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 195 / 230
- 196. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 196/230 LAB16: Full Power Loss Now just power off all the 3 VMs part of the cluster (mysql2, mysql3 and mysql4) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 196 / 230
- 197. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 197/230 LAB16: Full Power Loss Now just power off all the 3 VMs part of the cluster (mysql2, mysql3 and mysql4) And restart them and check that mysqld is restarted too. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 197 / 230
- 198. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 198/230 LAB16: Full Power Loss (2) Connect to one node and retrieve the cluster object, after a timeout, the following error will be seen: mysql1# mysqlsh mysql-js> c clusteradmin@mysql2 mysql-js> cluster=dba.getCluster() Dba.getCluster: This function is not available through a session to a standalone instance (metadata exists, but GR is not active) (RuntimeError) Check the error log, no members where able to join and recreate a group: '[GCS] Error connecting to all peers. Member join failed. Local port: 33061' '[GCS] The member was unable to join the group. Local port: 33061' 'Timeout on wait for view after joining group' Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 198 / 230
- 199. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 199/230 LAB16: Full Power Loss - restart the cluster (3) Use the dba object to 'reboot' the cluster: mysql-js> cluster=dba.rebootClusterFromCompleteOutage() Note: mysql-js> cluster=dba.rebootClusterFromCompleteOutage() Recon guring the default cluster from complete outage... The instance 'mysql3:3306' was part of the cluster con guration. Would you like to rejoin it to the cluster? [y/N]: y The instance 'mysql4:3306' was part of the cluster con guration. Would you like to rejoin it to the cluster? [y/N]: y Dba.rebootClusterFromCompleteOutage: The active session instance isn't the most updated in comparison with the ONLINE instances of the Cluster's metadata. Please use the most up to date instance: 'mysql3:3306'. (RuntimeError) Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 199 / 230
- 200. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 200/230 LAB16: Full Power Loss - restart the cluster (4) If one of the previous members need to recover some transactions, the next node won´t join automatically (fixed in 8.0.17). You need to use: cluster.rejoinInstance('clusteradmin@mysqlX') Join the members in "status": "(MISSING)" Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 200 / 230
- 201. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 201/230 When the majority of nodes are 'lost', the remaining minority (not reaching quorum) will not accept any transactions to avoid split-brain situation. How to bring back the service with the remaining ondes? This does require human intervention first guarantee the lost majority of the nodes are actually gone then make the minority network partition primary No more Quorum Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 201 / 230
- 202. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 202/230 LAB17: Run MySQL without quorum For this lab, stop mysqld on mysql2and mysql4: mysql3# tail -f /var/log/mysqld.log mysql2# failure-scenario.sh kill-node mysql4# failure-scenario.sh kill-node Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 202 / 230
- 203. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 203/230 LAB17: Run MySQL without quorum For this lab, stop mysqld on mysql2and mysql4: mysql3# tail -f /var/log/mysqld.log mysql2# failure-scenario.sh kill-node mysql4# failure-scenario.sh kill-node [WARNING] 'Member with address mysql2:3306 has become unreachable.' 'Member with address mysql4:3306 has become unreachable.' [ERROR] 'This server is not able to reach a majority of members in the group. This server will now block all updates. The server will remain blocked until contact with the majority is restored. It is possible to use group_replication_force_members to force a new group membership.' Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 203 / 230
- 204. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 204/230 LAB17: Run MySQL without quorum (2) Run cluster.status() on mysql3: { "clusterName": "perconalive", "defaultReplicaSet": { "name": "default", "ssl": "REQUIRED", "status": "NO_QUORUM", "statusText": "Cluster has no quorum as visible from 'mysql3:3306' and cannot process write transactions. 2 members are not active", "topology": { "mysql2:3306": { "address": "mysql2:3306", "mode": "n/a", "readReplicas": {}, "role": "HA", "status": "UNREACHABLE", "version": "8.0.16" },... Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 204 / 230
- 205. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 205/230 LAB17: Run MySQL without quorum (3) assume mysql2 and mysql4 are gone forever there is no majority mysql3 will not be primary make mysql3 primary Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 205 / 230
- 206. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 206/230 LAB17: Run MySQL without quorum (3) assume mysql2 and mysql4 are gone forever there is no majority mysql3 will not be primary make mysql3 primary mysql-js> cluster.forceQuorumUsingPartitionOf('clusteradmin@mysql3') Restoring replicaset 'default' from loss of quorum, by using the partition composed of [mysql3:3306] Restoring the InnoDB cluster ... The InnoDB cluster was successfully restored using the partition from the instance 'clusteradmin@mysql3'. WARNING: To avoid a split-brain scenario, ensure that all other members of the replicaset are removed or joined back to the group that was restored. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 206 / 230
- 207. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 207/230 LAB17: Run MySQL without quorum (3) assume mysql2 and mysql4 are gone forever there is no majority mysql3 will not be primary make mysql3 primary mysql-js> cluster.forceQuorumUsingPartitionOf('clusteradmin@mysql3') Restoring replicaset 'default' from loss of quorum, by using the partition composed of [mysql3:3306] Restoring the InnoDB cluster ... The InnoDB cluster was successfully restored using the partition from the instance 'clusteradmin@mysql3'. WARNING: To avoid a split-brain scenario, ensure that all other members of the replicaset are removed or joined back to the group that was restored. As soon as this is done, the application is running again ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 207 / 230
- 208. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 208/230 LAB17: Run MySQL without quorum (4) Restart MySQL on mysql2and mysql4: mysql2# systemctl start mysqld mysql4# systemctl start mysqld Check cluster.status(), you may need to use cluster.rejoinInstance(). More info: https://lefred.be/content/mysql-innodb-cluster-how-to-manage-a-split-brain-situation/ https://lefred.be/content/mysql-innodb-cluster-avoid-split-brain-while-forcing-quorum/ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 208 / 230
- 209. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 209/230 The Impact of Network Latency & Packet Loss How is the group impacted when there is higher network latency between nodes? Does it impact read & write performance? If only 1 node is in another datacenter, does it impact latency? Given it´s a majority concensus protocol, does it mean there is no impact as majority can be achieved in 1 datacenter? Let´s do some tests... change the app to only perform writes (on mysql1): ^C # run_app.sh mysql1 write Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 209 / 230
- 210. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 210/230 LAB18: Distant Node Latency On mysql2 ping mysql4: mysql2# ping mysql4 Add some latency on mysql4: mysql4# failure-scenario.sh add-latency begin Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 210 / 230
- 211. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 211/230 LAB18: Distant Node Latency On mysql2 ping mysql4: mysql2# ping mysql4 Add some latency on mysql4: mysql4# failure-scenario.sh add-latency begin You can see that now it takes 200ms to ping Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 211 / 230
- 212. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 212/230 LAB18: Distant Node Latency On mysql2 ping mysql4: mysql2# ping mysql4 Add some latency on mysql4: mysql4# failure-scenario.sh add-latency begin You can see that now it takes 200ms to ping What do we see in the app ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 212 / 230
- 213. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 213/230 LAB18: Distant Node Latency (2) Time to time, we can see that the application slows down... why ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 213 / 230
- 214. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 214/230 LAB18: Distant Node Latency (2) Time to time, we can see that the application slows down... why ? That´s because the system has to wait for the noop (single skip message) from the “distant” node. Members can propose messages for their slots without having to wait for other members. But if they don't have anything to say, they need to tell it too and this is where we are affected by the latency. More Info: https://lefred.be/content/mysql-group-replication-about-ack-from-majority/ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 214 / 230
- 215. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 215/230 LAB18: General Latency What happens if we add latency on all nodes ? mysql2# failure-scenario.sh add-latency begin mysql3# failure-scenario.sh add-latency begin Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 215 / 230
- 216. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 216/230 LAB18: General Latency What happens if we add latency on all nodes ? mysql2# failure-scenario.sh add-latency begin mysql3# failure-scenario.sh add-latency begin this increases the overal latency much more than expected ! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 216 / 230
- 217. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 217/230 LAB18: General Latency What happens if we add latency on all nodes ? mysql2# failure-scenario.sh add-latency begin mysql3# failure-scenario.sh add-latency begin this increases the overal latency much more than expected ! Remove the latency: mysql2# failure-scenario.sh add-latency end mysql3# failure-scenario.sh add-latency end mysql4# failure-scenario.sh add-latency end Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 217 / 230
- 218. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 218/230 LAB18 Unreliable Network (packet loss) Put mysql4 on a flaky network: mysql4# failure-scenario.sh packet-loss begin Check the error log on mysql4. Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 218 / 230
- 219. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 219/230 LAB18 Unreliable Network (packet loss) Put mysql4 on a flaky network: mysql4# failure-scenario.sh packet-loss begin Check the error log on mysql4. 'Member with address mysql2:3306 has become unreachable.' 'Member with address mysql2:3306 is reachable again.' 'Member with address mysql2:3306 has become unreachable.' 'Member with address mysql3:3306 has become unreachable.' 'This server is not able to reach a majority of members in the group. This server will now block all updates. The server will remain blocked until contact with the majority is restored. It is possible to use group_replication_force_members to force a new group membership.' 'Member with address mysql3:3306 is reachable again.' 'Member with address mysql3:3306 has become unreachable.' Member was expelled from the group due to network failures, changing member status to ERROR.' Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 219 / 230
- 220. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 220/230 LAB18 Unreliable Network (packet loss) Remove packet loss: mysql4# failure-scenario.sh packet-loss end Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 220 / 230
- 221. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 221/230 LAB18 Unreliable Network (packet loss) Remove packet loss: mysql4# failure-scenario.sh packet-loss end We can stop the specific application and run it now in a different mode to prepare the next lab: ^C # run_app.sh mysql1 owcontrol Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 221 / 230
- 222. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 222/230 Degraded Performance On A Node Flow control is implemented to prevent from falling behind: less hardware resources hardware/resource issue too high workload, ... On the nodes that are receiving writes: as soon as another node in the group has a growing apply queue Flow Control will start rate limit Default: group_replication_ ow_control_applier_threshold is 25000 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 222 / 230
- 223. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 223/230 LAB19: Degraded Performance On A Node Set group_replication_ ow_control_applier_threshold to 100 on all nodes: mysql> SET GLOBAL group_replication_ ow_control_applier_threshold = 100; Now we simulate degraded performance on mysql4 (make sure it is not PRIMARY): mysql4# failure-scenario.sh disk-issue Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 223 / 230
- 224. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 224/230 LAB19: Degraded Performance On A Node Set group_replication_ ow_control_applier_threshold to 100 on all nodes: mysql> SET GLOBAL group_replication_ ow_control_applier_threshold = 100; Now we simulate degraded performance on mysql4 (make sure it is not PRIMARY): mysql4# failure-scenario.sh disk-issue SELECT COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE FROM performance_schema.replication_group_member_stats; +--------------------------------------------+ | COUNT_TRANSACTIONS_REMOTE_IN_APPLIER_QUEUE | +--------------------------------------------+ | 697 | | 0 | | 0 | +--------------------------------------------+ Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 224 / 230
- 225. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 225/230 LAB19: Degraded Performance On A Node (2) When 100 is reached: writes reduced slowly as queue grows: more slowdown never goes to 0 It´s possible to ignore the health of mysql4 by telling to the PRIMARY to ignore the FC statistics received: mysql[2-3]> set global group_replication_ ow_control_mode='DISABLED'; Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 225 / 230
- 226. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 226/230 LAB19: Degraded Performance On A Node (2) When 100 is reached: writes reduced slowly as queue grows: more slowdown never goes to 0 It´s possible to ignore the health of mysql4 by telling to the PRIMARY to ignore the FC statistics received: mysql[2-3]> set global group_replication_ ow_control_mode='DISABLED'; As soon as it´s disabled, you can see the application going back at full speed! Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 226 / 230
- 227. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 227/230 other talks More during the Conference Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 227 / 230
- 228. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 228/230 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 228 / 230
- 229. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 229/230 Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 229 / 230
- 230. 5/25/2019 MySQL InnoDBCluster and Group Replication in a Nutshell: Hands-On Tutorial file:///home/fred/workspace/MySQL-InnoDB-Cluster---Nutshell/MySQL InnoDB Cluster - Nutshell.html#226 230/230 Thank you ! Any Questions ? Copyright @ 2019 Oracle and/or its affiliates. All rights reserved. 230 / 230