From single MySQL instance to High Availability: the journey to MySQL InnoDB Cluster

Frédéric Descamps Community Manager MySQL February 2021 From Single MySQL Instance to HA The Journey To MySQL InnoDB Cluster

Who am I ? about.me/lefred Copyright @ 2021 Oracle and/or its affiliates. 2

Frédéric Descamps @lefred MySQL Evangelist Managing MySQL since 3.20 devops believer living in Belgium h ps://lefred.be hairdressers are closed in .be Copyright @ 2021 Oracle and/or its affiliates. 3

Evolution to HA MySQL 8.0 Copyright @ 2021 Oracle and/or its affiliates. 4

Copyright @ 2021 Oracle and/or its affiliates. 5

This is where it all begins... Some tips: use exclusively InnoDB keep the durability defaults Single MySQL Instance Copyright @ 2021 Oracle and/or its affiliates. 6

Next level The database becomes more important, loosing it might be an issue... Copyright @ 2021 Oracle and/or its affiliates. 7

Next level The database becomes more important, loosing it might be an issue... RTO → hours Copyright @ 2021 Oracle and/or its affiliates. 7

Next level The database becomes more important, loosing it might be an issue... RTO → hours RPO → 1 day Copyright @ 2021 Oracle and/or its affiliates. 7

Next level The database becomes more important, loosing it might be an issue... RTO → hours RPO → 1 day RTO: Recovery Time Objective (how long to recover) RPO: Recovery Point Objective (how much data can be lost) Copyright @ 2021 Oracle and/or its affiliates. 7

Physical Backups Logical Backups For logical backups, please use MySQL Shell Dump & Load Utility ! Backups Copyright @ 2021 Oracle and/or its affiliates. 8

Next level RPO of 1 day ? Really ?? We want to reduce it to minutes at least ! Copyright @ 2021 Oracle and/or its affiliates. 9

Next level RPO of 1 day ? Really ?? We want to reduce it to minutes at least ! RTO → hours Copyright @ 2021 Oracle and/or its affiliates. 9

Next level RPO of 1 day ? Really ?? We want to reduce it to minutes at least ! RTO → hours RPO → minutes Copyright @ 2021 Oracle and/or its affiliates. 9

This is the default in MySQL 8.0: Durable Binlogs Copyright @ 2021 Oracle and/or its affiliates. 10

Next level My data is very important and I've a heavy workload... I would like to loose less than a second ! Copyright @ 2021 Oracle and/or its affiliates. 11

Next level My data is very important and I've a heavy workload... I would like to loose less than a second ! RTO → hours Copyright @ 2021 Oracle and/or its affiliates. 11

Next level My data is very important and I've a heavy workload... I would like to loose less than a second ! RTO → hours RPO → less than a second Copyright @ 2021 Oracle and/or its affiliates. 11

Enable GTID (optional but so convenient) O -load binlogs (in real time) Point-In-Time Recovery (PTR) Copyright @ 2021 Oracle and/or its affiliates. 12

with restart Enable GTID Copyright @ 2021 Oracle and/or its affiliates. 13

with restart without restart Enable GTID Copyright @ 2021 Oracle and/or its affiliates. 13

O -load Binlogs Copyright @ 2021 Oracle and/or its affiliates. 14

O -load Binlogs On another machine: mysqlbinlog --raw --read-from-remote-server --stop-never --host 10.0.0.2 --port 3306 -u getbinlog -ppassword --ssl-mode='REQUIRED' binlog.xxxxxx Copyright @ 2021 Oracle and/or its affiliates. 14

O -load Binlogs On another machine: mysqlbinlog --raw --read-from-remote-server --stop-never --host 10.0.0.2 --port 3306 -u getbinlog -ppassword --ssl-mode='REQUIRED' binlog.xxxxxx See h ps://lefred.be/content/howto-make-mysql-point-in-time-recovery-faster/ Copyright @ 2021 Oracle and/or its affiliates. 14

Next level My service is important, I would like to be up again in some minutes Copyright @ 2021 Oracle and/or its affiliates. 15

Next level My service is important, I would like to be up again in some minutes RTO → minutes Copyright @ 2021 Oracle and/or its affiliates. 15

Next level My service is important, I would like to be up again in some minutes RTO → minutes RPO → less than a second Copyright @ 2021 Oracle and/or its affiliates. 15

Based on native Asynchronous Replication But easier.... easier is be er ! Data provisioning included (clone) 2 or more nodes Manual Failover Transparent/Automatic query routing with MySQL Router MySQL InnoDB ReplicaSet Copyright @ 2021 Oracle and/or its affiliates. 16

MySQL InnoDB ReplicaSet Copyright @ 2021 Oracle and/or its affiliates. 17

MySQL InnoDB ReplicaSet On the current server: JS > dba.con gureReplicaSetInstance() JS > rs=dba.createReplicaSet('myreplicaset') On a new one where MySQL (server and shell) is just installed: JS > dba.con gureReplicaSetInstance() Back on the rst instance: JS > rs.addInstance('10.0.1.2') Copyright @ 2021 Oracle and/or its affiliates. 18

MySQL InnoDB ReplicaSet - examples Copyright @ 2021 Oracle and/or its affiliates. 19

MySQL Router It's very easy to con gure MySQL Router with the bootstrap command ! Copyright @ 2021 Oracle and/or its affiliates. 22

MySQL Router And as usual, visible in MySQL Shell Copyright @ 2021 Oracle and/or its affiliates. 23

MySQL InnoDB ReplicaSet - Manual Failover MySQL Shell is connected to MySQL InnoDB ReplicaSet using MySQL Router. Copyright @ 2021 Oracle and/or its affiliates. 24

MySQL InnoDB ReplicaSet - Manual Failover Copyright @ 2021 Oracle and/or its affiliates. 25

Next level Now my service is very important, I would like to be almost always up (automatic failover) and never loose data ! Copyright @ 2021 Oracle and/or its affiliates. 26

Next level Now my service is very important, I would like to be almost always up (automatic failover) and never loose data ! RTO → seconds Copyright @ 2021 Oracle and/or its affiliates. 26

Next level Now my service is very important, I would like to be almost always up (automatic failover) and never loose data ! RTO → seconds RPO → 0 Copyright @ 2021 Oracle and/or its affiliates. 26

Based on native Group Replication But easier.... easier is still be er ! Data provisioning included (clone) 3 or more nodes (odd number) Automatic Failover Uses MySQL Router Con gurable Consistency Levels MySQL InnoDB Cluster Copyright @ 2021 Oracle and/or its affiliates. 27

On the ReplicaSet Primary Server: JS > dba.dropMetadataSchema() JS > cluster=dba.createCluster('mycluster') On the ReplicaSet Secondary instance: SQL > STOP REPLICA; SQL > RESET ALL REPLICA; And back on the new Cluster Primary member: JS > cluster.addInstance('mysql-2') MySQL InnoDB Cluster And we can add a third note for automatic fail-over, on that node: Copyright @ 2021 Oracle and/or its affiliates. 29

On the ReplicaSet Primary Server: JS > dba.dropMetadataSchema() JS > cluster=dba.createCluster('mycluster') On the ReplicaSet Secondary instance: SQL > STOP REPLICA; SQL > RESET ALL REPLICA; And back on the new Cluster Primary member: JS > cluster.addInstance('mysql-2') JS > dba.con gureInstance() From a member of the cluster: JS > cluster=dba.getCluster() JS > cluster.addInstance('mysql-3') Finally, don't forget to bootstrap MySQL Router again: # mysqlrouter --bootstrap clusteradmin@single-mysql:3306 --conf-use-gr-noti cations --user mysqlrouter --force # systemctl restart mysqlrouter MySQL InnoDB Cluster And we can add a third note for automatic fail-over, on that node: Copyright @ 2021 Oracle and/or its affiliates. 29

And what's next ? MySQL InnoDB Cluster with an asynchronous replica for DR: asynchronous replication source connection failover (8.0.22) with support for Group Replication (8.0.23) MySQL InnoDB Cluster with asynchronous Group Replication nodes and more to come... Copyright @ 2021 Oracle and/or its affiliates. 31

From single MySQL instance to High Availability: the journey to MySQL InnoDB Cluster

More Related Content

What's hot

Similar to From single MySQL instance to High Availability: the journey to MySQL InnoDB Cluster

More from Frederic Descamps

Recently uploaded

From single MySQL instance to High Availability: the journey to MySQL InnoDB Cluster