Home / server / Questions / 997728
Accepted
laimison
Asked: 2020-01-06 16:02:17 +0800 CST

Kubernetes MySQL cluster in a private cloud

  • 772

I'm interested in MySQL cluster formed from 1 primary and 2 secondaries.

Usually in a public cloud we

  • use external storage

  • use services such as RDS so replication and failover handled behind this service

  • you can recreate failed pod on a different node, because storage and DB are not running on any of your k8s nodes

Solution that worked in a private cloud, but not in Kubernetes:

  • by using local storage

  • by using mysqlfailover utility so it can nominate a new primary

  • by changing DNS record of "mysql-0" (primary) and instructing application to refresh DNS so it can see a new primary in failover event

Exploring Kubernetes solution:

  • which one to use local storage or NFS? (if NFS, how would you do a cluster between different servers?)

  • by using https://github.com/oracle/mysql-operator , Percona, similar solutions or even the same mysqlfailover - which one would you prefer and how it deals with failover case? Preferable is open source option.

If I try to merge my current working mysqlfailover solution and move to Kubernetes, I may need to set up Node Affinity so pods get their local storages attached correctly.

Also this mysqlfailover mechanism should be improved (starting point is here https://medium.com/@zzdjk6/step-by-step-setup-gtid-based-mysql-replica-and-automatic-failover-with-mysqlfailover-using-docker-489489d2922 ) because it can for example nominate a new primary mysql-1, while original one (mysql-0) is down. Based on my understanding this could be not the best option, because in usual architecture, we always want to have mysql-0 as primary in a StatefulSet while mysqlfailover works completely opposite.

So which option would you choose if not solving existing issue? Which steps would you take? What MySQL and Kubernetes components would you use?

Many thanks

mysql

1 Answers

  1. Best Answer

    The solution I ended up is Percona XtraDB Cluster on Kubernetes. It has a Kubernetes operator to automatically manage failover scenarios.

    Your app shouldn't know anything about clustering, because it's sorted out transparently under kubernetes-service-hostname:3306. So app calls this address and behind it there are 3x SQLProxy/HAProxy containers (per server). Then query is routed to one of three MySQL containers.

    When server goes down, failed SQLProxy/HAProxy and MySQL containers are removed from Kubernetes so kubernetes-service-hostname contains two instead of three members.

    When server is back online, containers are created to have full cluster again.

    There is also Percona operator container which automatically helps to manage pods and do other actions so cluster is fully operating.

    In terms of storage, it can be just hostPath local directory which shows a sign of simplicity from storage perspective. You could also use PersistentVolumeClaim and any type of Storage Class behind it or external storage such as NFS.

    It's actually multi-master setup.

    More details:

    https://www.percona.com/doc/kubernetes-operator-for-pxc/kubernetes.html

    • 0