Practical Approach Running MySQL InnoDB Cluster/Set On Kubernetes

Transcript

1. Practical Approach Running MySQL InnoDB Cluster/Set On Kubernetes Hananto Wicaksono MySQL on Cloud Native Database Architect Former Oracle MySQL Senior Principal Product Manager
2. App driver / pool MySQL Router stable endpoint routes RW / RO MySQL Shell AdminAPI InnoDB Cluster Group Replication manages membership, quorum, certification, and primary election. mysql-0 PRIMARY own data mysql-1 SECONDARY own data mysql-2 SECONDARY own data SQL traffic metadata routing admin / bootstrap GROUP REPLICATION Connect to Router. During failover, connections can break; retries must be transaction-aware. InnoDB Cluster: shared-nothing HA InnoDB Cluster is built on MySQL Group Replication, which uses a Paxos-based consensus protocol to coordinate membership and transaction ordering across the cluster. In single-primary mode, transactions are written on the primary member, then replicated to the group where a majority of members must participate in certification before the transaction can be safely committed. This gives InnoDB Cluster automatic failover, consistent transaction ordering, and protection against split-brain as long as quorum is maintained. R/W R/O R/O
3. InnoDB Cluster: Auto Primary Failover 3 Nodes InnoDB Cluster is able to handle 1 node failures. 5 Nodes InnoDB Cluster is able to handle 2 nodes failure. InnoDB Cluster provides scalability across MySQL instances and increases capacity without downtime
4. Data Consistency, Stability & Performance Consideration Failover read safety consistency=BEFORE_ON_PRIMARY_FAILOVER New primary waits for backlog before accepting reads/writes. Flow control flow_control=QUOTA Throttles writers when members lag, protecting the group. Failure detection expel_timeout=5 Controls how quickly an unreachable member is expelled. Self-healing autorejoin=3 tries Lets a transiently failed member rejoin automatically. Developer angle: these settings decide when writes slow down, when connections break, and when the new primary becomes safe to use. Read-Consistency Level: 1. Eventual: may see older data 2. Before: SQL wait new data commit 3. After: commit wait everywhere to commit 4. Before-After: the strongest, the slowest 5. Before-on-primary-failover Transaction size limit default=150MB Max. Transaction in bytes that the group accept. Compression & Fragmentation 30-40% trans. Throughput improvement on slow network
5. Cluster Bootstrap with InnoDB Cluster Admin API • 1. Check each instance configuration • e.g. MySQL JS > dba.checkInstanceConfiguration('…') • 2. If not ok, prepare instances for group replication • e.g. MySQL JS > dba.configureInstance('…') 3. connect to first instance (the primary) • e.g. MySQL JS > \c root@mysql1 4. create cluster • e.g. MySQL JS > var cluster=dba.createCluster('mycluster'); 5. add all other nodes with cluster method • e.g. MySQL JS > cluster.addInstance('root@mysql2') • That's it! • 6. Now you can check cluster status with cluster method • e.g. MySQL JS > cluster.status()
6. InnoDB Cluster Admin API – One Liner mysqlsh --no-wizard -- dba configure-instance { --host=127.0.0.1 --port=3307 --user=root } --clusterAdmin=gradmin --clusterAdminPassword='grpass' --restart=true Configure Instance Create Cluster mysqlsh --no-wizard gradmin:grpass@localhost:3307 -- dba create-cluster mycluster Add Cluster Node mysqlsh --no-wizard gradmin:grpass@localhost:3307 -- cluster add-instance gradmin:grpass@localhost:3308 --recoveryMethod=incremental Setup Cluster Parameter mysqlsh --no-wizard gradmin:grpass@localhost:3307 --js -- cluster set-option consistency BEFORE_ON_PRIMARY_FAILOVER Cluster Status mysqlsh --no-wizard gradmin:grpass@localhost:3307 --js -- cluster status
7. Deploying MySQL as 3 instances Stateful Set in Kubernetes StatefulSet: mysql stable Pod identity + ordered rollout + volumeClaimTemplates mysql-0 node-1 / zone-1 PVC mysql-1 node-2 / zone-2 PVC mysql-2 node-3 / zone-3 PVC Headless Service: mysql mysql-0.mysql | mysql-1.mysql | mysql-2.mysql Secret root password cluster admin app credentials ConfigMap my.cnf GTID, binlog, Group Replication StorageClass fast SSD / CSI RWO volumes backup target Node selector dedicated DB nodes node-role=mysql Scheduling podAntiAffinity topology spread zone awareness PDB / drain PodDisruptionBudget safe node drain Production idea: StatefulSet gives identity, but scheduling, storage class, anti-affinity, topology spread, and PDB decide whether MySQL survives real operations.
8. InnoDB Cluster Admin API on Kubernetes Pre-requisites kubectl -n mysql-cluster exec -it mysql-0 -- mysql -uroot -proot -h127.0.0.1 -e "set persist_only report_host='mysql-0.mysql.mysql-cluster.svc.cluster.local’” kubectl -n mysql-cluster exec -it mysql-1 -- mysql -uroot -proot -h127.0.0.1 -e "set persist_only report_host='mysql-1.mysql.mysql-cluster.svc.cluster.local’” kubectl -n mysql-cluster exec -it mysql-2 -- mysql -uroot -proot -h127.0.0.1 -e "set persist_only report_host='mysql-2.mysql.mysql-cluster.svc.cluster.local'" Configure Instance kubectl -n mysql-cluster exec -it mysql-2 -- mysqlsh --no-wizard root:root@localhost:3306 -- dba configure-instance --clusterAdmin=gradmin --clusterAdminPassword='grpass’ kubectl -n mysql-cluster delete pod mysql-2 Create Cluster kubectl -n mysql-cluster exec -it mysql-0 -- mysqlsh --no-wizard gradmin:grpass@mysql-0.mysql.mysql-cluster.svc.cluster.local:3306 -- dba create-cluster mycluster Add Cluster Node kubectl -n mysql-cluster exec –it mysql-0 -- mysqlsh gradmin:grpass@localhost:3306 -- cluster add-instance mysql-1.mysql.mysql-cluster.svc.cluster.local:3306 --recoveryMethod=incremental
9. InnoDB Cluster Admin API on Kubernetes Mysql-0 Mysql-1 Mysql-2 1 Container / Pod
10. Deploying MySQL Router On Kubernetes https://medium.com/@hananto.wicaksono/running-mysql-router-on- kubernetes-for-an-external-mysql-innodb-cluster-d76306b39bd2 https://medium.com/@hananto.wicaksono/mysql-router-operator- on-kubernetes-with-python-kopf-c57773c6e768 https://github.com/tripplea-sg/mysql-router-operator
11. Deploying MySQL InnoDB Cluster using Operator Install MySQL Operator using Helm Manifest File Create secret kubectl apply –f manifest.yaml Auto create 3 nodes (instances) And 1 MySQL Router (instances)
12. Deploying MySQL InnoDB Cluster using Operator Mysql-0 Mysql-1 Mysql-2 2 Containers / MySQL Pod MySQL-Routers
13. MySQL Operator vs Manual with CI/CD Manual deployment helps expose and understand the moving parts, while MySQL Operator standardizes and automates much of the lifecycle.
14. Production Grade Setting for MySQL on Kubernetes
15. InnoDB ClusterSet for DR-ready Architecture MySQL Routers (not shown) are by default connected to PRIMARY cluster (configurable) Cross K8S-Cluster MySQL InnoDB ClusterSet on Cilium or Calico or Submariner https://blogs.oracle.com/mysql/deploying-mysql-innodb-clusterset- across-kubernetes-clusters-using-cilium
16. Cilium Setting K8s-cluster-Prod Cilium K8s-cluster-DR Cilium K8s-cluster-Prod FQDN-route on core DNS K8s-cluster-DR FQDN-route on core DNS
17. Deploying MySQL InnoDB ClusterSet Configure InnoDB ClusterSet on Production Kubernetes Cluster Re-deploy MySQL Router on PROD cluster to adopt changes on cluster metadata Check InnoDB ClusterSet status on PROD cluster, only 1 InnoDB Cluster is found as a member
18. Deploying MySQL InnoDB ClusterSet Adding Replica Cluster to the ClusterSet
19. Deploying MySQL InnoDB ClusterSet
20. Deploying MySQL InnoDB ClusterSet 1. MySQL Operator now supports InnoDB ClusterSet across Kubernetes clusters: https://blogs.oracle.com/mysql/mysql-operator-for-kubernetes-clusterset-support 2. MySQL Operator is installed per Kubernetes cluster. Therefore, each Kubernetes cluster requires its own MySQL Operator deployment, and each Operator instance receives and manages resource updates from its own Kubernetes control plane. 3. Cilium provides lower-level network routing using eBPF, a Linux kernel technology for advanced packet processing and routing. With Cilium, traffic from one Kubernetes cluster can be routed to another cluster. Care must be taken to avoid overlapping CIDR ranges. 4. Submariner provides multi-cluster connectivity similar to Cilium, but with a simpler feature set. Instead of a full cluster mesh, it creates encrypted tunnels between Kubernetes clusters on demand.
21. Thank you