As a follow up to the getting started post, let’s touch on what it takes to configure a MySQL instance for High Availability in Tanzu SQL/MySQL
Why this is important
In kubernetes, pods are generally treated as transient and ephemeral, they can be restarted quickly and are often stateless. This is certainly not the case with databases. We need to make sure our databases remain online and usable. MySQL itself provides a means to do High Availability with multiple instances and synchronization; we’ll be leveraging this capability today.
High Availability Architecture
Unlike our stand-alone instance when create an instance with HA enabled, the operator creates FIVE pods and two services for us.
You’ll notice that the mysql-ha LoadBalancer uses the proxy pods as its endpoints and the mysql-ha-members uses the database pods themselves.
Create an HA instance
In this example, I’m going to reuse the “harbor” docker-registry secret we created originally, but we’ll want a new tls certificate for this instance.
Create the TLS certificate
Just like previously, save the following as cert-ha.yaml and apply it with kubectl -n mysql-instances -f cert-ha.yaml to create a certificate for our instance. Adjust the names to match your environment of course. Notice the issuerRef.name is ca-issuer
apiVersion: cert-manager.io/v1
kind: Certificate
metadata:
name: mysql-ha-secret
spec:
# Secret names are always required.
secretName: mysql-ha-secret
duration: 2160h # 90d
renewBefore: 360h # 15d
subject:
organizations:
- ragazzilab.com
# The use of the common name field has been deprecated since 2000 and is
# discouraged from being used.
commonName: mysql-ha.ragazzilab.com
dnsNames:
- mysql-ha.ragazzilab.com
- mysql-ha
- mysql-ha.mysql-instances.svc.cluster.local
# Issuer references are always required.
issuerRef:
name: ca-issuer
# We can reference ClusterIssuers by changing the kind here.
# The default value is Issuer (i.e. a locally namespaced Issuer)
kind: Issuer
# This is optional since cert-manager will default to this value however
# if you are using an external issuer, change this to that issuer group.
group: cert-manager.ioCreate the instance
The only differences are highAvailability.enabled:true and the name of the certificate secret
apiVersion: with.sql.tanzu.vmware.com/v1
kind: MySQL
metadata:
name: mysql-ha
spec:
storageSize: 2Gi
imagePullSecret: harbor
#### Set highAvailability.enabled:true to create three pods; one primary and two standby, plus two proxy pods
highAvailability:
enabled: true
#### Set the storage class name to change storage class of the PVC associated with this resource
storageClassName: tanzu
#### Set the type of Service used to provide access to the MySQL database.
serviceType: LoadBalancer # Defaults to ClusterIP
### Set the name of the Secret used for TLS
tls:
secret:
name: mysql-ha-secretApply this as usual: kubectl apply -n mysql-instances -f ./mysql-ha.yaml
Create a database user
The steps to create the database user in an HA instance are just like those for the standalone instance once we determine which Pod is the primary/active and writable one. I was unable to make the one-liner method in the official docs work, so here’s what I did instead.
- Get the MySQL root password:
kubectl get secret -n mysql-instances mysql-ha-credentials -o jsonpath='{.data.rootPassword}' | base64 -D - Get a shell on the mysql-ha-0 pod:
kubectl -n mysql-instances exec --stdin --tty pod/mysql-ha-0 -c mysql -- /bin/bash - Get into the mysql cli:
mysql -uroot -p<root password> - Identify the Primary member:
SELECT MEMBER_HOST, MEMBER_ROLE FROM performance_schema.replication_group_members; - If the primary node is mysql-ha-0 (the one we’re on), proceed to the next step. If it is not, go back to step step 2 to get a shell on the pod that is primary.
- Now, we should be on the mysql cli on the primary pod/member. Just like with the standalone instance, let’s create a user:
CREATE USER 'admin'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'admin'@'%';
FLUSH PRIVILEGES;Type exit twice to get out of mysql and the pod.
Ok, so now, we have a running instance of mysql and we’ve created a user account that can manage it (cannot login remotely as root). We can connect phpMyAdmin to the instance using the admin credentials:
Tanzu Kubernetes Grid includes and supports packages for dex and Gangway. These are used to extend authentication to LDAP and OIDC endpoints. Recall that Kubernetes does not do user-management or traditional authentication. As a K8s cluster admin, you can create service accounts of course, but those are not meant to be used by developers.
Brian Ragazzi 