Using VMware SQL with MySQL for Tanzu Application Service

This topic provides instructions for developers using the VMware SQL with MySQL for Tanzu Application Service service for their VMware Tanzu Application Service for VMs apps.

VMware SQL with MySQL for TAS provides a relational database for apps and devices. To use VMware SQL with MySQL for TAS in an app:

Check the service availability in the Marketplace, and see if there is an existing instance of VMware SQL with MySQL for TAS in your space.

See Confirm the VMware SQL with MySQL for TAS service availability.
If there is no existing instance or you want to use a different one, create an instance of the VMware SQL with MySQL for TAS service in the same space as the app.

See Create a service instance.
Bind the app to the VMware SQL with MySQL for TAS service instance, to enable the app to use MySQL.

See Bind a service instance to your app.
Call the VMware SQL with MySQL for TAS service in your app code, and then push your app again into the space.

For more information on services, see Use the MySQL service in your app.

After you create a VMware SQL with MySQL for TAS service instance, you can manage it over the life cycle of your apps and data.

For more information on managing services instances, see Manage service instances.

Note The following procedures use the Cloud Foundry Command Line Interface (cf CLI). For more information, see Managing service instances with the cf CLI.
You can also use Apps Manager to do the same tasks using a graphical UI. For more information, see Managing apps and service instances using Apps Manager.

Prerequisites

To use VMware SQL with MySQL for TAS with your TAS for VMs apps, you must:

Have an Tanzu Operations Manager installation with VMware SQL with MySQL for TAS installed and listed in the Marketplace.
For how to verify availability in the Marketplace, see Confirm Service Availability.
Have a Space Developer or Admin account on the TAS for VMs installation.
For more information, see Manage Users and Roles.
Have a local machine with the following installed:
- A browser
- A shell
- The Cloud Foundry Command-Line Interface (cf CLI). See Installing the cf CLI.
- The Linux watch command. See the Linux Information Project website.
Log in to the org and space containing your app. For instructions, see Log in with the CLI.

Confirm the VMware SQL with MySQL for TAS service availability

For an app to use the VMware SQL with MySQL for TAS service, both of the following must be true:

The service must be available in the Marketplace for its space.
An instance of the service must exist in its space.

You can confirm both of these using the cf CLI as follows.

Check service availability in the Marketplace

To find out if a VMware SQL with MySQL for TAS service is available in the Marketplace:

Enter the following command:
```
cf marketplace
```

If the output lists p.mysql in the service column, VMware SQL with MySQL for TAS is available. If it is not available, ask your operator to install it.

$ cf marketplace
Getting services from marketplace in org my-org / space my-space as user@example.com...
OK
service             plans          description
[...]
p.mysql             db-small       Dedicated instances of MySQL service to provide a relational database
[...]

Check that an instance is running in the space

To confirm that a VMware SQL with MySQL for TAS instance is running in the space:

Use the Log in to cf CLI or Logging in to Apps Manager to log in to the org and space that contains the app.
Enter the following command:
```
cf services
```

Any p.mysql listings in the service column are service instances of VMware SQL with MySQL for TAS in the space.

For example:

$ cf services
Getting services in org my-org / space my-space as user@example.com...
OK
name          service     plan        bound apps    last operation
my-instance   p.mysql     db-small                  create succeeded

You can bind your app to an existing instance or create a new instance to bind to your app.

Create a service instance

On-demand services are created asynchronously, not immediately. The watch command shows you when your service instance is ready to bind and use.

Note If you are deploying a leader-follower service instance across multiple foundations, follow the procedure in Using VMware SQL with MySQL for TAS for Multi‑Site Replication.

To create an instance of the VMware SQL with MySQL for TAS service:

Create a service instance by running the following command:
```
cf create-service p.mysql PLAN SERVICE-INSTANCE
```
Where:
- PLAN is the name of the VMware SQL with MySQL for TAS plan you want to use.
- SERVICE-INSTANCE is a name you choose to identify the service instance. This name appears under service in output from cf services.

Enter the following command and wait for the last operation for your instance to show as create succeeded.

watch cf services

For example:

$ cf create-service p.mysql db-small my-instance

Creating service my-instance in org my-org / space my-space as user@example.com...
OK

$ watch cf services

Getting services in org my-org / space my-space as user@example.com...
OK
name          service       plan        bound apps    last operation
my-instance   p.mysql       db-small                  create succeeded

If you get an error, see Troubleshooting instances.

Bind a service instance to your app

For an app to use a service, you must bind the app to a service instance. You must do this after every time you run cf push.

To push and bind an app to a VMware SQL with MySQL for TAS instance run the following command:

Push your app into the same space as your VMware SQL with MySQL for TAS service instance by running the following command:
```
cf push
```
Bind your app to a VMware SQL with MySQL for TAS instance by running the following command:
```
cf bind-service APP SERVICE-INSTANCE
```
Where:
- APP is the app you want to use the MySQL service instance.
- SERVICE-INSTANCE is the name you supplied when you ran cf create-service.
For example:
```
$ cf bind-service my-app my-instance

Binding service mydb to my-app in org my-org / space test as user@example.com...
OK
TIP: Use 'cf push' to ensure your env variable changes take effect
```
Restart your app by running the following command:
```
cf restart APP
```
Where APP is the app you want to use the MySQL service instance.

Use the MySQL service in your app

To access the MySQL service from your app:

Verify that your app code (or the MySQL client library that the app uses) retries in the case of DNS timeouts.
Locate the connection strings listed in the VCAP_SERVICES > credentials object for your app. For information about VCAP_SERVICES, see MySQL environment variables.
In your app code, call the MySQL service using the connection strings.

See this example: Node.js code.

Use custom schemas

VMware SQL with MySQL for TAS supports multiple custom schemas. You can use custom schemas with apps that share a MySQL service instance to isolate app data by schema. By default, service bindings use the default schema service_instance_db.

To use custom schemas in your apps:

Bind your app to the custom schema by running:
```
cf bind-service APP SERVICE-INSTANCE -c '{"schema":"CUSTOM-SCHEMA"}'
```
Where:
- APP is the app you want to use the custom schema.
- SERVICE-INSTANCE is the name of your service instance.
- CUSTOM-SCHEMA is the name of your custom schema. Valid characters include uppercase and lowercase letters, digits, $, and _.
Restart your app by running:
```
cf restart APP
```
Where APP is the app you want to use the custom schema.

MySQL environment variables

Apps running in Tanzu Operations Manager gain access to bound service instances through an environment variable credentials hash called VCAP_SERVICES. This environment variable includes the credentials that apps use to access service instances.

For example:

{
  "p.mysql": [
    {
      "label": "p.mysql",
      "name": "my-instance",
      "plan": "db-medium",
      "provider": null,
      "syslog_drain_url": null,
      "tags": [
        "mysql"
      ],
      "credentials": {
        "hostname": "10.0.0.20",
        "jdbcUrl": "jdbc:mysql://10.0.0.20:3306/service_instance_db?user=fefcbe8360854a18a7994b870e7b0bf5\u0026password=z9z6eskdbs1rhtxt",
        "name": "service_instance_db",
        "password": "z9z6eskdbs1rhtxt",
        "port": 3306,
        "uri": "mysql://fefcbe8360854a18a7994b870e7b0bf5:[email protected]:3306/service_instance_db?reconnect=true",
        "username": "fefcbe8360854a18a7994b870e7b0bf5"
      },
      "volume_mounts": []
    }
  ]
}

You can search for your service by the name given when the service instance was created. You can also search using the tags or label properties. The credentials property can be used to provide access to the MySQL protocol.

VCAP_SERVICES is only modified when an app is bound to a service instance. If you modify your service instance, you must cf unbind-service, cf bind-service and cf restage your app to apply the changes to VCAP_SERVICES.

Note If you use MySQL Connector/J 8.0.13 or later with VMware SQL with MySQL for TAS, you must modify the JDBC URL in VCAP_SERVICES to include sslMode=VERIFY_IDENTITY and verifyServerCertificate=true. MySQL Connector/J 8.0.13 and later does not verify TLS connections. For more information about JDBC URL syntax, see the MySQL documentation.

Important If a developer rebinds an app to the VMware SQL with MySQL for TAS service after unbinding, they must also rebind any existing custom schemas to the app. When you rebind an app, stored code, programs, and triggers break. For more information about binding custom schemas, see Use custom schemas.

Manage a service instance

You can manage service instances in the following ways:

Migrate your data to a different plan. See Migrate data to a different plan.
Upgrade service instance individually to the latest version of VMware SQL with MySQL for TAS. See Upgrade an individual service instance.
Change the default parameters for an existing service instance. See Change default parameters on an existing service instance.
Share service instances between orgs and spaces. See Share service instances.
Remove access to a service from an app that no longer needs it. See Unbind an app from a service instance.
Delete a service instance that is not used. See Delete a service instance.

Migrate data to a different plan

Caution
If you are using multi‑site replication, you must not update the Multi‑Site Replication plan to a plan of another topology. If you do this, your replication breaks.

You can use cf update-service to migrate data to a different plan. When you update a service instance, you do not need to rebind your app or service keys. However, when you migrate data to a new service instance the database is unavailable for several minutes.

For more information about using cf update-service, see the Cloud Foundry CLI Reference Guide.

Caution
You cannot use cf update-service to migrate data between an HA cluster plan and a plan of another topology. If you want to do this, you must use the cf mysql-tools plug-in instead.

For more information about migrating data, see About data migration in VMware SQL with MySQL for Tanzu Application Service.

The following table lists migration use cases for the update-service command: class=“table”>

Use update-service for migrating from... To... Single Node larger Single Node Leader-Follower larger Leader-Follower Single Node Leader-Follower of the same or larger size Leader-Follower Single Node of the same or larger size

To migrate a service instance to another plan:

View the available service plans for VMware SQL with MySQL for TAS by running:
```
cf marketplace
```
Migrate data to another plan by running:
```
cf update-service SERVICE-INSTANCE -p PLAN
```
Where PLAN is the plan you want to update the service instance to.

For example:
```
$ cf update-service my-instance -p db-large
```

Upgrade an individual service instance

Important Before you individually upgrade service instances you must have cf CLI v6.46.0 or later.

You can use cf update-service with the –upgrade flag to individually upgrade on-demand service instances to the latest version of VMware SQL with MySQL for TAS. When you upgrade a service instance, you do not need to rebind your app or service keys. However, when you upgrade a service instance the database is unavailable for several minutes.

For more information about using cf update-service, see the Cloud Foundry CLI Reference Guide.

To upgrade a single service instance:

Confirm that an upgrade is available for the service instance by running:

cf services

The upgrade is available when the upgrade available column in the output says yes.

For example:

$ cf services
Getting services in org system / space system as admin...

name         service    plan      bound apps  last operation    broker                  upgrade available
my-instance  p.mysql    db-small              create succeeded  dedicated-mysql-broker  yes

Upgrade the service instance by running:

cf update-service SERVICE-INSTANCE-NAME --upgrade

When you are prompted, confirm that you want to upgrade.

Upgrade a service instance to MySQL 8.0

When you upgrade a service instance using the cf update-service command with the --upgrade flag, the service instance continues to use MySQL 5.7. To upgrade a service instance to use MySQL 8.0, you have two options:

If you have configured the service plan used by your service instance to use MySQL 8.0, run the cf update-service command with the -p flag and specify the existing service plan used by the service instance:
```
cf update-service SERVICE-INSTANCE-NAME -p my-existing-plan
```
If the service plan used by your service instance is still configured to use MySQL 5.7, run the cf-update-service command with the -p flag and specify a service plan that is configured to use MySQL 8.0:
```
cf update-service SERVICE-INSTANCE-NAME -p new-8-0-plan
```

Where SERVICE-INSTANCE-NAME is the name of the service instance to upgrade.

The MySQL 8.0 version of the plan is applied to the service instance, triggering the service instance upgrade from 5.7 to 8.0.

Share service instances

In VMware SQL with MySQL for TAS you can share service instances between different orgs and spaces using cf share-service. Service instance sharing is enabled by default.

For more information about service instance sharing, see Sharing service instances.

To share a service instance:

Target the source org and space for the service instance you want to share by running:
```
cf target -o SOURCE-ORG -s SOURCE-SPACE
```
Where:
- SOURCE-ORG is the source org for your service instance.
- SOURCE-SPACE is the source space for your service instance.
Share your service instance to the destination org and space by running:
```
cf share-service SERVICE-INSTANCE -o DESTINATION-ORG -s DESTINATION-SPACE
```
Where:
- SERVICE-INSTANCE is the service instance you want to share.
- DESTINATION-ORG is the destination org for the service instance.
- DESTINATION-SPACE is the destination space for the service instance.

Target the destination org and space by running:

cf target -o DESTINATION-ORG -s DESTINATION-SPACE

Confirm the service instance was shared by running:
```
cf service SERVICE-INSTANCE
```
Where SERVICE-INSTANCE is the service instance you shared.

Unbind an app from a service instance

If you want stop an app from using a service you must unbind the app from the service.

Unbind your app by running:

cf unbind-service APP SERVICE-INSTANCE

Where:

APP is the app you want to stop using the MySQL service instance.
SERVICE-INSTANCE is the name you supplied when you ran cf create-service.

For example:

$ cf unbind-service my-app my-instance

Unbinding app my-app from service my-instance in org my-org / space my-space as user@example.com...
OK

Delete a service instance

You cannot delete a service instance that an app is bound to.

To delete a service instance:

Run the following command:

cf delete-service SERVICE-INSTANCE

Where SERVICE-INSTANCE is the name of the service to delete.

For example:

$ cf delete-service my-instance

Are you sure you want to delete the service my-instance ? y
Deleting service my-service in org my-org / space my-space as user@example.com...
OK

Enter the following command and wait for a Service instance not found error indicating that the instance no longer exists:
```
watch cf service SERVICE-INSTANCE
```

Purge a service instance

If the service instance VM is lost, then you cannot delete the service instance. However, you can use the cf CLI to purge a service instance from the Cloud Controller database.

To purge a service instance:

Run:

cf purge-service-instance SERVICE-INSTANCE-NAME

Where SERVICE-INSTANCE-NAME is the name of the service instance to purge.

For example:

$ cf purge-service-instance my-instance

WARNING: This operation assumes that the service broker responsible for this service instance is no longer available or is not responding with a 200 or 410, and the service instance has been deleted, leaving orphan records in Cloud Foundry's database. All knowledge of the service instance are removed from Cloud Foundry, including service bindings and service keys.

Really purge service instance my-instance from Cloud Foundry?> y
Purging service my-instance...
OK