Configuring MySQL Instances for High Availability

This topic describes how to use VMware SQL with MySQL for Kubernetes to create high-availability (HA) MySQL instances.

Overview

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.

About Planning for Long-Lived HA MySQL Instances

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.

About Explicit Node Selection and Anti-Affinity

VMware SQL with MySQL for Kubernetes does not support explicit node selection for controlling Pod assignment to Kubernetes nodes. The Pod's nodeSelector field is not assigned. Pod assignment to Kubernetes nodes can be controlled using Node Affinity

About Affinity and Tolerations

VMware SQL with MySQL for Kubernetes does support Affinity and Tolerations to control where the Pods are assigned. The Operator assigns the Affinity and Toleration fields within MySQL objects to the corresponding fields in the Pods' spec. To understand how to constrain Pods to run on a particular node refer here

About Scaling Single Node to HA

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.

About Scaling HA to Single Node

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.

About Backing Up HA Instances

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.

About HA Schema Compatibility

Tables in an HA database must be compatible with MySQL Group Replication. Group Replication requires that each table have a PRIMARY KEY or Equivalent (non-null unique key).

Prerequisites

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 VMware MySQL Operator 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.
The VMware SQL with MySQL for Kubernetes deployment templates. See Download the Deployment Templates.
Reviewed how to create and delete single-node MySQL instances. See Creating and Deleting MySQL Instances.

Create an HA MySQL Instance

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 mysql.yaml:
1. Set the value of highAvailability.enabled to true:
```
---
apiVersion: with.sql.tanzu.vmware.com/v1
kind: MySQL
metadata:
  name: mysql-ha-sample
spec:
  imagePullSecretName: tanzu-image-registry
  highAvailability:
    enabled: true
```
2. 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.

Convert a Single-Node MySQL Instance to an HA 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.

Before converting to HA, ensure that your database schema satisfies the Schema Compatability for HA requirements.

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 spec.highAvailability.enabled to true and save the file.
Apply the change and deploy the HA instance by running:
```
kubectl -n DEVELOPMENT-NAMESPACE apply -f FILENAME
```
Where:
- DEVELOPMENT-NAMESPACE is the namespace for the instance.
- FILENAME is the name of the YAML file edited.
For example:
```
kubectl -n my-namespace apply -f testdb.yaml
```
```
mysql.with.sql.tanzu.vmware.com/mysql-sample created
```

Inspect an HA MySQL Instance

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
```
Where POD-NAME is 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.

For example:
```
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
```
Where:
- USER is a username on the database. When the database is first created, only the root user exists.
- PASSWORD is the password for the database user.
For example:
```
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 PRIMARY and two SECONDARY members:

  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 ONLINE for all members. For information about the Group Replication server states, see the MySQL documentation.

Move an HA MySQL Instance to a Single-Node MySQL Instance

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 VMware SQL with MySQL 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 restore.yaml file, set highAvailability.enabled to false.

Note: You cannot scale down an existing HA instance to a single-node instance by applying highAvailablity.enabled: false to an HA instance. The apply command fails.

Delete an HA MySQL Instance

Deleting an HA MySQL instance is the same as deleting a single-node instance. See Delete a MySQL Instance in Creating and Deleting MySQL Instances.