Configuring MySQL Instances for High Availability
This topic describes how to use VMware Tanzu™ SQL with MySQL for Kubernetes to create high-availability (HA) MySQL instances.
High-availability (HA) MySQL instances offer automatic failover, ensuring that app requests operate continuously and without extended downtime.
For more information about high availability including an architecture diagram, see Architecture of an HA MySQL Instance in Architecture.
HA MySQL instances are often used in production environments. Before creating an HA instance that you intend to rely on for a long time, carefully consider the resource requests and limits.
If you do not designate resource requests and limits, then Kubernetes schedules Pods onto node resources according to best effort policies. If resources become constrained, your Pods running MySQL risk eviction as Kubernetes manages resources allocation among its Pods. For information about the properties used to set limits and requests, see Property Reference for the MySQL Resource.
VMware Tanzu™ SQL with MySQL for Kubernetes does not support explicit node selection or anti-affinity for controlling Pod assignment to Kubernetes nodes. Kubernetes distributes Pods according to its regular placement and scheduling policies including any resource requests.
You can convert a single-node MySQL instance to an HA MySQL instance with a single command. See Convert a Single-Node MySQL Instance to an HA MySQL Instance below.
It is not straightforward to convert an HA instance to a single-node instance. To move from an HA instance to a single-node instance, you must take a backup of the HA instance and restore it to a new single-node instance. See Move an HA MySQL Instance to a Single-Node MySQL Instance below.
HA MySQL instances are backed up and restored the same way as single-node MySQL instances. Backups from HA instances are created from the primary MySQL Pod. You can restore the backups from HA instances to either single-node or HA instances. For information about backup and restore, see Backing Up and Restoring MySQL Instances.
Before you can configure high availability, you must have:
The Kubernetes Command Line Interface (kubectl) installed. For more information, see the Kubernetes documentation.
Full admin access to all Kubernetes resources in your developer namespace.
For information about Roles and RoleBindings that your Kubernetes cluster admin needs to create, see the Kubernetes documentation.
The URL and credentials to access the registry that stores the Tanzu MySQL for Kubernetes images. This can be the VMware Tanzu Network registry or the private registry configured for your environment. If you do not have access to the registry credentials, contact your Kubernetes admin to have these set up for you in your namespace.
- Reviewed how to create and delete single-node MySQL instances. See Creating and Deleting MySQL Instances.
Creating an HA MySQL instance is very similar to creating a single-node MySQL instance. By default, MySQL instances are single node.
To create an HA instance:
Follow the steps 1–4 in Create a MySQL Instance.
Edit your uniquely named copy of
Set the value of
--- apiVersion: with.sql.tanzu.vmware.com/v1 kind: MySQL metadata: name: mysql-ha-sample spec: storageSize: 1Gi imagePullSecret: tanzu-mysql-image-registry highAvailability: enabled: true
Edit the other properties for the instance as needed. For information about the properties that you can set for the MySQL resource, see Property Reference for the MySQL Resource.
Create and verify the instance by following steps 6 and 7 in Create a MySQL Instance.
If a single-node MySQL instance already exists, you can edit its YAML file to set high-availability and then redeploy the instance.
Note: Ensure that you want an HA instance before following this procedure. You cannot easily change an HA instance back to a single-node one.
To change a single-node instance into an HA instance:
Open the YAML file that you created in step 4 of Create a MySQL Instance in Creating and Deleting MySQL Instances.
Change the value of
trueand save the file.
Apply the change and deploy the HA instance by running:
kubectl -n DEVELOPMENT-NAMESPACE apply -f FILENAME
DEVELOPMENT-NAMESPACEis the namespace for the instance.
FILENAMEis the name of the YAML file edited.
$ kubectl -n my-namespace apply -f testdb.yaml mysql.with.sql.tanzu.vmware.com/mysql-sample created
After you have created your HA MySQL instance, you can inspect the instance to confirm that all the Pods are running.
This inspection procedure can also be used for troubleshooting.
Connect to one of the instance Pods, by running:
kubectl exec -it POD-NAME --container=mysql -- bash
POD-NAMEis the name of the primary Pod or one of the secondary Pods. The Pod name is the instance name appended with an index of 0–2. When the instance is first created, the index for the primary Pod is 0.
$ kubectl exec -it mysql-ha-sample-0 --container=mysql -- bash
On the Pod, log in to the MySQL database by running:
mysql --user=USER --password=PASSWORD
USERis a username on the database. When the database is first created, only the root user exists.
PASSWORDis the password for the database user.
mysql@mysql-sample-0:/$ mysql --user=root --password=$(cat $MYSQL_ROOT_PASSWORD_FILE)
Query the database by running:
mysql>SELECT * FROM performance_schema.replication_group_members\G;
The following example is from a healthy cluster with a single member designated
mysql>SELECT * FROM performance_schema.replication_group_members\G; *************************** 1. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 157baa2a-8c22-11eb-847c-0242ac110009 MEMBER_HOST: mysql-ha-sample-0.mysql-ha-sample-members.default.svc.cluster.local MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: PRIMARY MEMBER_VERSION: 8.0.22 *************************** 2. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 281ad3c9-8c22-11eb-b3aa-0242ac11000a MEMBER_HOST: mysql-ha-sample-1.mysql-ha-sample-members.default.svc.cluster.local MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: SECONDARY MEMBER_VERSION: 8.0.22 *************************** 3. row *************************** CHANNEL_NAME: group_replication_applier MEMBER_ID: 3c52bb9a-8c22-11eb-aade-0242ac11000b MEMBER_HOST: mysql-ha-sample-2.mysql-ha-sample-members.default.svc.cluster.local MEMBER_PORT: 3306 MEMBER_STATE: ONLINE MEMBER_ROLE: SECONDARY MEMBER_VERSION: 8.0.22
Review the value of
MEMBER_STATE. A healthy cluster shows
ONLINEfor all members. For information about the Group Replication server states, see the MySQL documentation.
To move from an HA MySQL instance to a single-node MySQL instance, you need to back up the HA instance and restore the backup to a single-node instance.
Back up the HA instance. See Back Up Tanzu SQL for Kubernetes Data in Backing Up and Restoring MySQL Instances.
Restore the backup to a single-instance node. See Restore from a Backup in Backing Up and Restoring MySQL Instances. When you edit your copy of the
You cannot scale down an existing HA instance to a single-node instance
highAvailablity.enabled: false to an HA instance.
apply command fails.
Deleting an HA MySQL instance is the same as deleting a single-node instance.
To delete an HA instance:
- See Delete a MySQL Instance in Creating and Deleting MySQL Instances.