MySQL Operator for Kubernetes Running MySQL in Kubernetes made easy Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz 

Prochain Webinaire Apprentissage automatique avec MySQL HeatWave 21 juillet 2022 10h CET Inscrivez-vous maintenant avec le QR code ! https://go.oracle.com/MySQLFrenchWebinars 

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 3 Me, Myself & I  MySQL Geek  Addicted to MySQL for 15+ years  Playing with databases for 20+ years  MySQL Writer, Blogger and Speaker  Also: DBA, Consultant, Architect, Trainer, ...  MySQL Cloud Principal Solutions Architect EMEA at Oracle  Stay up to date!  Blog: www.dasini.net/blog/en  Linkedin: www.linkedin.com/in/olivier-dasini/  Twitter: @freshdaz Olivier DASINI 

Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Program Agenda MySQL, InnoDB Cluster introduction Kubernetes Overview MySQL Operator For Kubernetes 1 2 3 

MySQL Copyright © 2022, Oracle and/or its affiliates. All rights reserved. The world’s most popular open source database 

Simplifying automation and container deployment • MySQL InnoDB Cluster • MySQL Shell dump/load • CLONE plugin • SET PERSIST • RESTART • performance_schema.error_log • ... MySQL 8.0 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Connectors supporting DNS-SRV • 8.0.19 • DNS Service record - RFC 2782 "defining the location, i.e., the hostname and port number, of servers for specified services” Available in: • Connector/NET • Connector/ODBC • Connector/J • Connector/Node.js • Connector/Python • Connector/C++ 6 

Options for where you run MySQL Clustering For High-Availability Compute nodes / VMs MySQL InnoDB Cluster Easy to setup Easy to manage Automated failover Not a managed service Kubernetes MySQL Operator Easier to setup Easier to manage Automated failover Mostly managed service Public Cloud MySQL Database Service Easiest to setup Easiest to manage Automated failover Fully managed service Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 7 

MySQL InnoDB Cluster Fault Tolerance Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Avoid loss of service by reducing the risk of failures 

Group Replication Application MySQL Router Primary Secondary Secondary Secondary Secondary Single Primary Mode MySQL InnoDB Cluster MySQL Servers synchronously replicate Secondary servers are read-only MySQL Router sends the write sessions to the primary server only Read-only sessions are distributed by MySQL Router to the secondary servers MySQL Shell provides a full administration interface to the cluster MySQL Shell Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 9 

Group Replication Application MySQL Router Offline Primary Secondary Secondary Secondary Automated Failover MySQL InnoDB Cluster MySQL Servers group automatically and internally elects a new primary The new primary ensures its consistency and becomes read-write No human intervention needed MySQL Router sends writes to the new primary when ready MySQL Shell Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 10 

Adding or restoring members MySQL InnoDB Cluster Everything automated or with simple commands using MySQL Shell • New members clone from others (cloning) • Restored members catch up (synchronization) Group Replication Application MySQL Router Primary Secondary Secondary New Node (offline) Secondary Becomes online only once synchronized MySQL Shell Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 11 

Container based applications Kubernetes Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Accelerate how you build, share, and run MySQL 

MySQL is the Natural Choice for Cloud Native ● MySQL can perform multiple roles: – Traditional RDBMS (SQL) – JSON Document Store (NoSQL) ● MySQL is lightweight, inexpensive and easy to operate – Deploying multiple instances is not a problem ● MySQL produces container-ready images – For both Community Edition and Enterprise Edition – MySQL has a Kubernetes Operator making the deployment of HA MySQL containers simplistic ● MySQL has connectors for all mainstream programming and scripting languages ● MySQL can be consumed as a service in Oracle Cloud Infrastructure ● MySQL Database Service – the only service to be 100% MySQL – Concentrate on your schema – we do all the rest (support, updates, backup, appropriate config, etc) – Removes complexity of handling state in orchestration environments such as Kubernetes Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 13 

Official MySQL container distribution published and supported by Oracle • MySQL Server • MySQL Router Community Edition available on ● https://hub.docker.com/u/mysql Commercial Edition available on ● https://container-registry.oracle.com ● https://edelivery.oracle.com ● https://support.oracle.com Like Community version, but adds: • Security/privacy plugins • MySQL Enterprise Backup MySQL 8.0 Docker Images Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 14 14 Accelerate how you build, share, and run MySQL https://dev.mysql.com/doc/refman/8.0/en/docker-mysql-getting-started.html 

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 16 Create a new container-based application 

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 17 Create a new container-based application 

Copyright © 2020, Oracle and/or its affiliates. All rights reserved. 18 Create a new container-based application Use MySQL Kubernetes Operator to automate Use MySQL Database Service - Fully Managed Service - HeatWave Query Acceleration - Peace of Mind Architecture Use OCI Service Operator for Kubernetes (OSOK) 

Kubernetes A portable, extensible, open-source platform for managing containerized workloads and services, that facilitates both both declarative configuration and automation Kubernetes Operator Method of automatically deploying and managing a service • Deployment • Configuration • Automated or simplified maintenance • Self-healing • Backup & Restore • Observability • Using Kubernetes custom resources Both MySQL Operator for Kubernetes and MySQL InnoDB Cluster share a common goal to make it easier to deploy, automate, and manage a service Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 19 

Pod Kubernetes Glossary One or more containers run by Kubernetes working as a unit Think: • Container: atom • Pod: molecule Kubernetes knows a Pod is not running when none of its containers are running Sidecar containers: • Used to make the Pod appear to be running even when the main container is not running • Can be used to execute commands Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 20 

Deployment and Replicaset Kubernetes Glossary Deployments define in Kubernetes what we want to achieve • They are referred to as the Desired State • The desired state and other definitions are defined in a manifest (yaml file) • Deployments are evolved Replicaset Kubernetes compares the current state with the desired state and attempts to maintain the desired state Pods that are managed by Deployments are: • Not started in a specific order • If a Pod is unhealthy, Kubernetes creates a new one • Pod state is not persisted Run a stateless application using a Deployment Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 21 

Statefulset Kubernetes Glossary Statefulsets manage the deployment and scaling of a set of Pods, and provide guarantees about the ordering and uniqueness of these Pods Similar to a Deployment with some different characteristics • Pods have a sticky identity with Statefulsets whereas they are anonymous with Replicasets • Pods can be started in specific order • Pods may not be replaced Run a database server group using a Statefulset Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 22 

Service Kubernetes Glossary Services create the interface between outside of Kubernetes and a Deployment or Statefulset • Manage IP addresses and network names • Provide service information • Perform port mapping • Offer optional connection load-balancing Network access to Kubernetes applications Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 23 

Persisted Volume Claim (PVC) Kubernetes Storage Pod /var/lib/mysql /backup Persisted Volume Claim (PVC) Persisted Volume Claim (PVC) Persisted Volume (PV) Persisted Volume (PV) Physical Storage Physical Storage optional optional optional optional data PVC (optional) backup PVC (optional) Container Bins & Libs MySQL Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 24 

MySQL Operator for Kubernetes Kubernetes Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Manage MySQL InnoDB Cluster inside a K8s Cluster 

About Kubernetes Operator Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Method of automatically deploying and managing a service 26 

MySQL Operator for Kubernetes Consistency: No Data Loss (RPO=0) • In event of failure of primary member • Split brain prevention (quorum) Read Scaling • Add/remove members as needed • Replication Lag handling with Flow Control • Configurable consistency levels • Eventual • Full consistency (no stale reads) Highly Available: Automatic Failover • Primary members are automatically elected • Automatic network partitioning handling Ease of Operation • Backup / Restore • Scale up and down • Version upgrade • Helm support Goals Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 27 

Bringing MySQL InnoDB Cluster to Kubernetes StatefulSet ReplicaSet • StatefulSet: • 3 x nodes InnoDB Cluster with single primary mode • ReplicaSet: • 2x MySQL Routers 30 

MySQL Operator for Kubernetes Copyright © 2022, Oracle and/or its affiliates. All rights reserved. MySQL Operator Pod Backup to Cloud Backup to local storage Architecture 31 

Installation MySQL Operator for Kubernetes Location: https://github.com/mysql/mysql-operator Documentation: https://dev.mysql.com/doc/mysql-operator/en/ 1- Apply Custom Resource Definition: kubectl apply -f deploy_crds.yaml 2- Deploy operator kubectl apply -f deploy-operator.yaml 3- Describe operator pod kubectl -n mysql-operator get pod kubectl describe pod -n mysql-operator Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 33 

Creation MySQL Operator for Kubernetes 1- Create a "secret" for the root user: kubectl create secret generic mypwds \ --from-literal=rootUser=root \ --from-literal=rootHost=% \ --from-literal=rootPassword="your password" 2- Create cluster using Operator: kubectl apply -f mycluster.yaml The operator will create: • A statefulset and services for MySQL server named mycluster • pods mycluster-0..2 with PVC, sidecar container agents • A replicaset for MySQL Router named mycluster-router • pods mycluster-router-xyz, no agents • A service for MySQL InnoDB Cluster named mycluster apiVersion: mysql.oracle.com/v2alpha1 kind: InnoDBCluster metadata: name: mycluster spec: secretName: mypwds instances: 3 router: instances: 2 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 34 

Information MySQL Operator for Kubernetes Monitor cluster status: kubectl get innodbcluster --watch Retrieve IP address of cluster: kubectl get service mycluster Describe the storage (PVC) for a MySQL server: kubectl describe pvc datadir-mycluster-0 Create external connectivity (applications outside of Kubernetes): kubectl port-forward service/mycluster mysql kubectl load-balancing service/mycluster mysql Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 35 

Scaleout MySQL Operator for Kubernetes kubectl apply -f mycluster.yaml kubectl edit innodbcluster mycluster • Automated scaling • No downtime • NOTE: Manually updating statefulset and replicaset definitions will not be reflected in the InnoDBCluster object apiVersion: mysql.oracle.com/v2alpha1 kind: InnoDBCluster metadata: name: mycluster spec: instances: 5 router: instances: 3 apiVersion: mysql.oracle.com/v2alpha1 kind: InnoDBCluster metadata: name: mycluster spec: instances: 3 router: instances: 2 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 36 

Upgrade MySQL Operator for Kubernetes kubectl apply -f mycluster.yaml kubectl edit innodbcluster mycluster • Kubernetes will kill and recreate each pod of the MySQL InnoDB Cluster from last to first • Make sure the primary node of the MySQL InnoDB Cluster is the first node • This will be a rolling upgrade apiVersion: mysql.oracle.com/v2alpha1 kind: InnoDBCluster metadata: name: mycluster spec: version: 8.0.29 router: version: 8.0.29 apiVersion: mysql.oracle.com/v2alpha1 kind: InnoDBCluster metadata: name: mycluster spec: version: 8.0.28 router: version: 8.0.28 Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 37 

Backup And Restore MySQL Operator for Kubernetes kubectl apply -f backup.yaml • The profile is declared with the cluster definition • Ad-hoc backups are also possible using yaml files that provide the backup information (dumpInstance) • To restore the backup, create a new cluster and use the MySQL Shell loadDump() function apiVersion: mysql.oracle.com/v2alpha1 kind: InnoDBCluster metadata: ... spec: ... backupProfiles: - name: dump-instance-profile-pvc dumpInstance: dumpOptions: excludeSchemas: ["excludeDB"] storage: persistentVolumeClaim: claimName: backup-volume-claim-1 apiVersion: mysql.oracle.com/v2alpha1 kind: MySQLBackup metadata: name: mybackup spec: clusterName: mycluster backupProfileName: dump-instance-profile-pvc Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 38 

Self-healing cluster MySQL Operator for Kubernetes The Kubernetes system at large monitors the state of the pods: • The container runtime restarts individual failed containers • Kubernetes restarts pods • Kubernetes moves pods of failed host machines • And so on... The Operator ensures that: • MySQL servers rejoin the cluster in a healthy and complete manner • The cluster is able to restart from a complete shutdown Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 39 

Destruction MySQL Operator for Kubernetes Delete all resources with a single command: Kubectl delete innodbcluster mycluster Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 40 

Conclusion The MySQL Operator for Kubernetes is designed to help operate MySQL InnoDB Cluster in Kubernetes environment: ● Greatly simplify operations ● Improve high-availability ● Offer a more "native" Kubernetes experience to the user Please visit: ● https://github.com/mysql/mysql-operator ● https://dev.mysql.com/doc/mysql-operator/en/ Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 41 

www.oracle.com/cloudworld/mysql-summit/ Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 

Q&A Thank You! Copyright © 2022, Oracle and/or its affiliates. All rights reserved.