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