Updating MySQL Instances

This topic describes how to scale, or change configurations on a VMware Tanzu™ SQL with MySQL for Kubernetes instance. It also covers how to update a Tanzu MySQL instance after an Operator upgrade.

Prerequisites

Before you update the MySQL instance, you must have:

Access and permissions to the MySQL instance.
The Kubernetes Command Line Interface (kubectl) installed: For more information, see the Kubernetes documentation.
An upgraded Operator: This prerequisite is not applicable to users that are only updating instance configuration details.

Updating an Instance

After an Operator upgrade, instances created under an older Tanzu MySQL Operator require reconciliation. Reconciliation allows the older instances to take advantage of the new features of the upgraded Operator.

Review the Operator's supported MySQL versions by running:
```
kubectl get mysqlversions
```
The command displays an output similar to:
```
NAME           DB VERSION
mysql-8.0.26   8.0.26
mysql-8.0.27   8.0.27
mysql-8.0.28   8.0.28
mysql-latest   8.0.28
```
The list indicates the four distinct database versions the Operator supports.

Check the status of your existing instances, to identify any recommended reconciliations. The output of kubectl get mysql command includes an UPDATE STATUS, and an USER ACTION column.

$ kubectl get mysql

The output is similar to:

NAME           READY   STATUS    AGE    TANZU VERSION   DB VERSION   UPDATE STATUS      USER ACTION
mysql-sample   true    Running   6m5s   1.5.0           8.0.28       NoUpdateRequired

NAME           READY   STATUS    AGE    TANZU VERSION   DB VERSION   UPDATE STATUS       USER ACTION

DBUPDONC-9     true    Running   20d    1.5.0           8.0.26       UpdateRequired      Annotate “mysql.with.sql.tanzu.vmware.com/update=once”
instance-10    true    Running   41m    1.4.0           8.0.25       DBUpgradeRequired   Set spec.mysqlVersion.name to a supported DB version
instance-8     true    Running   21d    1.3.0           8.0.25       DBUpgradeRequired   Set spec.mysqlVersion.name to a supported DB version 
instance-7     true    Running   22d    1.3.0           8.0.23       DBUpgradeRequired   Set spec.mysqlVersion.name to a supported DB version
DBAUTOUPG-6    true    Running   120d   1.2.0           8.0.26       UpdateRequired      Annotate “mysql.with.sql.tanzu.vmware.com/update=once” 
instance-5     true    Running   121d   1.2.0           8.0.25       DBUpgradeRequired   Set spec.mysqlVersion.name to a supported DB version         
instance-4     true    Running   122d   1.2.0           8.0.23       DBUpgradeRequired   Set spec.mysqlVersion.name to a supported DB version
DBAUTOUPG-3    true    Running   150d   1.1.0           8.0.25       DBUpgradeRequired   Set spec.mysqlVersion.name to a supported DB version           
instance-2     true    Running   151d   1.1.0           8.0.23       DBUpgradeRequired   Set spec.mysqlVersion.name to a supported DB version

Check the UPDATE STATUS column and note the instances with an update value = "UpdateRequired". These instances need to be brought up-to-date or reconciled, to take advantage of new Operator features, and the self-healing capabilities that are native to Kubernetes.
For instances that require an update, as indicated by the USER ACTION column, use Kubernetes' standard annotation command to mark the instance to be updated/reconciled to the current operator.

WARNING: Annotating an instance will cause instance reboot. Plan your instance annotations according to your applications maintenance windows.

The USER ACTION text Annotate "mysql.with.sql.tanzu.vmware.com/update=once" requires the user to run a command similar to:
```
kubectl annotate mysql instance-8 mysql.with.sql.tanzu.vmware.com/update=once
```
NOTE: The annotation is removed the moment the instance update is complete. You may rerun the command kubectl get mysql to verify that the reconciliation is complete. After the annotate action, the newly updated instances can use all the new features of the latest Operator.

Scale storageSize

For storage classes that support storageSize, you can expand the storageSize but not reduce it.

To scale storageSize:

Target the namespace where you want to scale storageSize:
```
kubectl config set-context --current --namespace=DEVELOPMENT-NAMESPACE
```
Where DEVELOPMENT-NAMESPACE is the namespace in which you want to scale storageSize.

For example:
```
kubectl config set-context --current --namespace=my-namespace
```
Look up the storage class associated with the MySQL Pod’s Persistent Volume Claim (PVC):
```
kubectl get pvc mysql-data-INSTANCE-NAME-N -o jsonpath={.spec.storageClassName}
```
Where:
- INSTANCE-NAME is the value that you configured for metadata.name for your MySQL resource.
- N is the index of the Pod in the MySQL instance.
For example:
```
kubectl get pvc mysql-data-mysql-sample-0 -o jsonpath={.spec.storageClassName}
  standard
```

Check if the storage class allows volume expansion. Look for the ALLOWVOLUMEEXPANSION column by running:

kubectl get storageclass CLASS-RETURNED

Where CLASS-RETURNED is the storageclass associated with the MySQL Pod's PVC from the previous step.

For example:

kubectl get storageclass standard

NAME                 PROVISIONER            RECLAIMPOLICY   VOLUMEBINDINGMODE    ALLOWVOLUMEEXPANSION   AGE
standard (default)   kubernetes.io/gce-pd   Delete          Immediate            true                   91s

If the output does not show the ALLOWVOLUMEEXPANSION column, run:

kubectl get storageclasses.storage.k8s.io standard \
-o custom-columns='NAME:.metadata.name,ALLOWVOLUMEEXPANSION:.allowVolumeExpansion'

If you are using Kubernetes v1.11 and volume expansion is supported, change the allowVolumeExpansion field to true in StorageClass objects. Only PVCs created from a StorageClass with allowVolumeExpansion set to true are allowed to perform volume expansion. For more information, see the Kubernetes documentation.
Edit the PVC spec.resources.requests.storage field to increase the volume size by running:
```
kubectl patch pvc mysql-data-INSTANCE-NAME-N -p \
'{"spec": {"resources": {"requests": {"storage": "NEW-REQUESTED-SIZE"}}}}'
```
Where:
- INSTANCE-NAME is the value that you configured for metadata.name for your MySQL resource.
- N is the index of the Pod in the MySQL instance.
- NEW-REQUESTED-SIZE is the increased volume size.
For example:
```
kubectl patch pvc mysql-data-mysql-sample-0 -p '{"spec": {"resources": {"requests": {"storage": "50Gi"}}}}'
```

Do one of the following:

If the storageClass supports online expansion: Wait for the PVC to automatically resize. For more information, see the Kubernetes documentation.
Otherwise: Wait for the PVC to have the FileSystemResizePending condition. Delete the MySQL Pod to unmount the PVC and allow it to resize.

kubectl wait --for=condition=FileSystemResizePending pvc/mysql-data-INSTANCE-NAME-N

kubectl rollout restart statefulset INSTANCE-NAME

For example:

kubectl wait --for=condition=FileSystemResizePending pvc/mysql-data-mysql-sample-0

persistentvolumeclaim/mysql-data-mysql-sample-0 condition met

kubectl rollout restart statefulset mysql-sample

statefulset.apps/mysql-sample restarted

After the StatefulSet controller has automatically re-created the MySQL Pod, and the MySQL Pod has successfully restarted, verify that the storage has been resized.
```
kubectl exec INSTANCE-NAME-N -c mysql -- df -h /var/lib/mysql
```
Where:
- INSTANCE-NAME is the value that you configured for metadata.name for your MySQL resource.
- N is the index of the Pod in the MySQL instance.
For example:
```
kubectl exec mysql-sample-0 -c mysql -- df -h /var/lib/mysql
```
```
Filesystem      Size  Used Avail Use% Mounted on
/dev/sdb        3.0G  346M  2.6G  12% /var/lib/mysql
```

Expected Downtime When Scaling storageSize

For HA instances, no downtime is expected.

For single-node instances, the expected downtime when expanding storage is as follows:

With online expansion: There is no downtime expected in most cases.
With offline expansion: There is downtime while the MySQL Pod is re-created and the PVC is being resized.

Scale CPU and Memory Resources

To scale CPU or memory resources, update the MySQL configuration spec.resources field to change the CPU or memory requirements for the mysql or sidecar containers.

You can use one of these methods to update the configuration:

Patch the existing API object in place by running:
```
kubectl patch mysql INSTANCE-NAME --type merge -p \
'{"spec": {"resources": {"mysql": MYSQL-RESOURCES, " mysqlSidecar": SIDECAR-RESOURCES}}}'
```
Where:
- INSTANCE-NAME is the value that you configured for metadata.name for your MySQL resource.
- MYSQL-RESOURCES is a collection of cpu and memory limits and requests for the mysql container.
- SIDECAR-RESOURCES is a collection of cpu and memory limits and requests for the mysql-sidecar container.
For example:
```
kubectl patch mysql mysql-sample --type merge -p '{"spec": {"resources": {"mysql": {"requests": {"cpu": "500m"}}, "mysqlSidecar": {"limits": {"memory": "64Mi"}}}}}'
```
If you manage MySQL resources with a set of configuration files in source control, you can also change the fields in the file and apply the changes.

For example:
```
kubectl apply -f mysql.yaml
```

For more information on managing CPU and Memory resources, see the Kubernetes documentation.

Expected Downtime When Scaling CPU and Memory Resources

For HA instances, no downtime is expected.

For single-node instances, the expected downtime when changing resource reservations is as follows:

Brief downtime while Kubernetes re-creates the MySQL Pods.
Risk of longer downtime if the new requested values exceed the available capacity of the Kubernetes cluster.

Change Other Configurations

Changing storageClassName or imagePullSecretName on a running MySQL instance is not supported and it returns an error. If a MySQL instance is not running due to errors in these fields, you can change them. The changes are propagated into the StatefulSet to correct the error.

Note: You cannot modify storageClassName for a successfully created MySQL instance.

To change storageClassName or imagePullSecretName, update the MySQL configuration by one of the methods below:

Patch the existing API object in place:
```
kubectl patch mysql INSTANCE-NAME -p '{"spec": {"PROPERTY": "VALUE"}}'
```
Where:
- INSTANCE-NAME is the value that you configured for metadata.name for your MySQL resource.
- PROPERTY is the property name.
- VALUE is the new property value.
For example:
```
kubectl patch mysql mysql-sample -p '{"spec": {"imagePullSecretName": "new-secret-name"}}'
```
If you manage MySQL resources with a set of configuration files in source control, you can change the fields in the file and apply the changes.

For example:
```
kubectl apply -f mysql.yaml
```

For information about the properties, see Property Reference for the MySQL Resource.