Connecting an Application to a MySQL Instance

This topic describes how to connect applications to a VMware MySQL instance. This topic covers two methods, either using a Tanzu Application Platform (TAP) workflow, or Tanzu Application Services (TAS).

Binding an Application to a MySQL Instance using TAP workflow

The VMware MySQL 1.3 supports Service Binding with Tanzu Application Platform (TAP). This feature eliminates the manual management of the configuration steps needed to securely and successfuly bind a TAP application to a VMware MySQL deployment. For more information on Service Binding, see Service Binding Specification for Kubernetes in the Kubernetes documentation.

For more information on how to create or update TAP workloads, see Working with Workloads in the TAP documentation.

Prerequisites

TAP v1.2.0 or higher has been installed. See Installing Tanzu Application Platform for instructions.
VMware MySQL Operator has been successfully installed on a Kubernetes cluster. See Installing a MySQL Operator for instructions.
MySQL instance has been deployed. See Deploying a MySQL Instance for instructions.

Setup MySQL with Services Toolkit

The Services Toolkit offers a set of modular tools used to provide a self-service experience to application owners, who require Service Resources running on Tanzu.

Review Service Resource Claims for details on ResourceClaim, ResourceClaimPolicy, and RBAC ClusterRole.

To make the MySQL instances discoverable via the Services Toolkit, create a ClusterInstanceClass that refers to the MySQL API and save it in a file like cluster-instance-class-mysql.yaml:
```
---
apiVersion: services.apps.tanzu.vmware.com/v1alpha1
kind: ClusterInstanceClass
metadata:
  name: mysql
spec:
  description:
    short: It's a MySQL database!
  pool:
    group: with.sql.tanzu.vmware.com
    kind: MySQL
```
and then run:
```
kubectl apply -f cluster-instance-class-mysql.yaml
```
Use the Services Toolkit plugin command tanzu services instances list to return the previously created MySQL instance.

(Optional) If you are consuming the MySQL instance from a Workload in another namespace, you will have to enable cross-namespace claims via a ResourceClaimPolicy.

If your MySQL instance resides in the default namespace, save the following in a file like resource-claim-policy-mysql.yaml:

---
apiVersion: services.apps.tanzu.vmware.com/v1alpha1
kind: ResourceClaimPolicy
metadata:
  name: mysql-cross-namespace
  namespace: default
spec:
  consumingNamespaces:
  - '*' # consumable from all namespaces
  subject:
    group: with.sql.tanzu.vmware.com
    kind: MySQL

and then run:

kubectl apply -f resource-claim-policy-mysql.yaml

Create a MySQL Claim

Resource Claims allow Application teams to "claim" resources, and Service Operators to provide resources to be "claimed". This provides a self-service experience for the developer, but gives the Service Operators ultimate control of the Service Resources.

To claim a MySQL instance:

Find the information needed to make a resource claim by running:
```
tanzu service claimable list --class mysql
#! `--class` is the name of the ClusterInstanceClass defined in the previous step
```
With an output similar to:
```
NAME        NAMESPACE  KIND   APIVERSION
MYSQL-NAME  default    MySQL  with.sql.tanzu.vmware.com/v1
```
where MYSQL-NAME is the name of the previously created MySQL instance.

See Discover claimable service instances for more information on the different flags and options available.
Create a claim for the MySQL instance from the namespace of your Workload by running:
```
tanzu service claims create CLAIM-NAME \
  --resource-name MYSQL-NAME \
  --resource-namespace default \
  --resource-kind MySQL \
  --resource-api-version with.sql.tanzu.vmware.com/v1
```
where:
- MYSQL-NAME is the name of the previously created MySQL instance.
- CLAIM-NAME is the name you want to give to the resource claim instance.
The next section shows how to inspect the claim and use it to bind to an application Workload.

Use tanzu service claims create --help for further usage and help information.

Bind a TAP Workload with MySQL

This topic covers:

Bind a new TAP workload
Bind an existing TAP workload

Bind a new TAP Workload

To create an application Workload bound to a MySQL instance:

Inspect the claims in your workload namespace to find the value to pass to --service-ref command by running:
```
tanzu services claims list -n default
```
Expected output:
```
NAME           READY  REASON
CLAIM-NAME     True   Ready
```
Where CLAIM-NAME is the name of the previously created resource claim instance.

See Listing and getting Resource Claims for more information on the different flags and options available.

Retrieve detailed information about the claim by running:

tanzu services claims get CLAIM-NAME

where CLAIM-NAME is the name of the previously created resource claim instance.

The output is similar to:

Name: CLAIM-NAME
Status:
  Ready: True
Namespace: app-namespace
Claim Reference: services.apps.tanzu.vmware.com/v1alpha1:ResourceClaim:CLAIM-NAME
Resource to Claim:
  Name: MYSQL-NAME
  Namespace: default
  Group: with.sql.tanzu.vmware.com
  Version: v1
  Kind: MySQL

Where:

MYSQL-NAME is the name of the previously created MySQL instance.
CLAIM-NAME is the name of the previously created resource claim instance.

Record the value of Claim Reference and pass it to --service-ref to create the application Workload by running:
```
tanzu apps workload create WORKLOAD-NAME [create flags] \
  --service-ref "db=services.apps.tanzu.vmware.com/v1alpha1:ResourceClaim:CLAIM-NAME" \
  --annotation autoscaling.knative.dev/minScale=1
```
where:
- WORKLOAD-NAME is the name that will be given to the workload.
- [create flags] are the appropriate flags to build the workload from source control, registry image, or local file.
- --service-ref is the reference to the service using the format {key-name}={apiVersion}:{kind}:{instance-name}.
  - CLAIM-NAME is the name of the previously created resource claim instance.
  - key-name is a unique name given to service binding for the Workload.
- --annotation value of autoscaling.knative.dev/minScale: "1" prevents the auto-scaling down of pods done by the TAP components. This can help with debugging the Workload.
See Tanzu apps workload create for more information on the different flags and options available.

Example Workload

You can also create a Workload yaml file where you can specify environment variables, build parameters, etc. that are required by your application to be built and run successfully. The example Workload yaml below shows a spring-petclinic application that binds to a claim of a MySQL instance via a resource claim claim-1.

---
apiVersion: carto.run/v1alpha1
kind: Workload
metadata:
  name: pet-clinic
  labels:
    apps.tanzu.vmware.com/workload-type: web
    app.kubernetes.io/part-of: pet-clinic
spec:
  params:
  - name: annotations
    value:
      autoscaling.knative.dev/minScale: "1"
  build:
    env:
    - name: BP_MAVEN_POM_FILE
      value: skip-pom.xml
  env:
  - name: SPRING_PROFILES_ACTIVE
    value: mysql
  serviceClaims:
  - name: db
    ref:
      apiVersion: services.apps.tanzu.vmware.com/v1alpha1
      kind: ResourceClaim
      name: claim-1
  source:
    git:
      ref:
        branch: main
      url: https://github.com/spring-projects/spring-petclinic

where:

BP_MAVEN_POM_FILE needs to be set to some non-existent value (in this example, skip-pom.xml) in order to build the upstream spring-petclinic app correctly. Refer to this open issue for more details.
SPRING_PROFILES_ACTIVE is set to MySQL to override the default application configuration.
The annotation autoscaling.knative.dev/minScale: "1" prevents the auto-scaling down of Pods done by the TAP components. This can help with debugging the Workload.

Save the above content in workload.yaml file and then run kubectl apply -f workload.yaml

Important: This example creates a workload with the sample Spring application spring-petclinic that is available on Github. The sample application is subject to change and may face issues during build and run steps in the TAP workflow. Consider specifying a commit sha to use an older state of the sample application as needed for demo and test purposes.

Bind an existing TAP workload

To update an existing TAP Workload to connect and utilize a MySQL database instance, run a command similar to:

tanzu apps workload update WORKLOAD-NAME [update flags] --service-ref "db=services.apps.tanzu.vmware.com/v1alpha1:ResourceClaim:CLAIM-NAME"

where:

WORKLOAD-NAME is the name of the Workload.
[update flags] are the appropriate flags to build the Workload from source control, registry image, or local file.
--service-ref is the reference to the service using the format {key-name}={apiVersion}:{kind}:{name}.
- CLAIM-NAME is the name of the previously created resource claim instance.
- key-name is a unique name given to service binding for the Workload.

For example:

tanzu apps workload update pet-clinic --service-ref "db=services.apps.tanzu.vmware.com/v1alpha1:ResourceClaim:claim-1"

See Tanzu apps workload update for more information on the different flags available.

Verify a TAP Workload Service Binding

To check if your application has been successfully bound to the MySQL instance:

Check that the corresponding ResourceClaim object is ready. The ResourceClaim name follows the format <.metadata.name>-<.spec.serviceClaim[0].name> as given in the Workload yaml as shown above. In this example, it would be pet-clinic-db.
```
kubectl get resourceclaims pet-clinic-db
```
```
NAME                                                         READY   REASON
resourceclaim.services.apps.tanzu.vmware.com/pet-clinic-db   True
```

Query the database using the MySQL application user named mysqlappuser.

Fetch the corresponding credentials and connection details from the Secret referenced in .status.binding.name field in the MySQL instance CR. With a MySQL called mysql-sample, the Secret would be mysql-sample-app-user-db-secret and the data can be verified in the mysql-sample database.

$ dbname=$(kubectl get secrets mysql-sample-app-user-db-secret -o jsonpath='{.data.database}' | base64 -D)
$ username=$(kubectl get secrets mysql-sample-app-user-db-secret -o jsonpath='{.data.username}' | base64 -D)
$ password=$(kubectl get secrets mysql-sample-app-user-db-secret -o jsonpath='{.data.password}' | base64 -D)

$ host=$(kubectl get secrets mysql-sample-app-user-db-secret -o jsonpath='{.data.host}' | base64 -D)
$ port=$(kubectl get secrets mysql-sample-app-user-db-secret -o jsonpath='{.data.port}' | base64 -D)

For example, in case of a spring-petclinic application, on running a mysql query:

$ mysql -u$username -p $password

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.26-17 Percona Server (GPL), Release '17', Revision 'd7119cd'

Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysqlappuser_data;
Database changed
mysql> show tables;
+-----------------------------+
| Tables_in_mysqlappuser_data |
+-----------------------------+
| owners                      |
| pets                        |
| specialties                 |
| types                       |
| vet_specialties             |
| vets                        |
| visits                      |
+-----------------------------+
7 rows in set (0.01 sec)

mysql> select * from owners;
+----+------------+-----------+-----------------------+-------------+------------+
| id | first_name | last_name | address               | city        | telephone  |
+----+------------+-----------+-----------------------+-------------+------------+
|  1 | George     | Franklin  | 110 W. Liberty St.    | Madison     | 6085551023 |
|  2 | Betty      | Davis     | 638 Cardinal Ave.     | Sun Prairie | 6085551749 |
|  3 | Eduardo    | Rodriquez | 2693 Commerce St.     | McFarland   | 6085558763 |
|  4 | Harold     | Davis     | 563 Friendly St.      | Windsor     | 6085553198 |
|  5 | Peter      | McTavish  | 2387 S. Fair Way      | Madison     | 6085552765 |
|  6 | Jean       | Coleman   | 105 N. Lake St.       | Monona      | 6085552654 |
|  7 | Jeff       | Black     | 1450 Oak Blvd.        | Monona      | 6085555387 |
|  8 | Maria      | Escobito  | 345 Maple St.         | Madison     | 6085557683 |
|  9 | David      | Schroeder | 2749 Blackhawk Trail  | Madison     | 6085559435 |
| 10 | Carlos     | Estaban   | 2335 Independence La. | Waunakee    | 6085555487 |
+----+------------+-----------+-----------------------+-------------+------------+
10 rows in set (0.01 sec)```

Binding VMware MySQL to a TAS Application

Prerequisites

Before you can connect an application running on TAS to a VMware MySQL Operator instance, you must have:

The Kubernetes Command Line Interface (kubectl) installed. For more information, see the Kubernetes documentation.
A VMware MySQL Operator instance installed on a Kubernetes cluster that supports a form of external ingress, for example a LoadBalancer. For more details see Creating and Deleting MySQL Instances.
An application that is deployed to TAS and can access the Kubernetes cluster ingress points.

Binding an Application

To bind a MySQL instance to an application running on TAS:

Deploy an instance of VMware SQL with MySQL for Kubernetes that uses a public ingress method, such as LoadBalancer.
Enable TLS for the instance, following the instructions in Configuring TLS for MySQL Instances.
Create a database and a privileged MySQL user for the application, following the procedure Create a Database and Privileged MySQL User for the App.

Set the environment variables for your newly created user and for the load balancer hostname:

export HOSTNAME=$(kubectl get service mysql-sample  -o 'jsonpath={.status.loadBalancer.ingress[].ip}')
export USER=bn_wordpress
export PASSWORD=hunter2
export DATABASE=for_my_app

Create a credentials hash for a TAS user-provided service configuration:

cf create-user-provided-service k8s-vmware-sql -p "{\"uri\":\"mysql://$USER:$PASSWORD@$HOSTNAME:3306/$DATABASE?reconnect=true&sslMode=required&ssl_mode=required\"}"

If autoconnect is supported by the applicatioin buildpack, as is common with data services, the application will automatically connect to the database instance. If the buildpack does not support autoconnect, you will need to manually use the uri service credentials to connect the application to the database instance.

Troubleshooting

This procedure, including autowiring, was validated with a Spring Boot application. Autowiring did not work with a Ruby on Rails application, because the version of the MariaDB client library in the Cloud Foundry cflinuxfs3 stack does not support sslMode.

If your application cannot make SSL connections, start by properly consuming the connection information components and making a manual connection from the application.
Some techniques for connecting non-Spring applications are provided in the Activate TLS for Non-Spring Apps section of the MySQL for VMs documentation. The credentials hash created in the Binding an Application procedure above does not include individual components of connection information, such as username and hostname, but you can derive these components by parsing the uri in the hash.
As a last resort, you can deactivate TLS on the MySQL instance and remove the ssl_mode and sslMode parameters from the credentials hash.