Using MySQL for PCF
Warning: MySQL for PCF v2.6x is no longer supported because it has reached the End of General Support (EOGS) phase as defined by the Support Lifecycle Policy. To stay up to date with the latest software and security updates, upgrade to a supported version.
Page last updated:
This topic provides instructions for developers using the MySQL for Pivotal Cloud Foundry (PCF) service for their PCF apps.
Overview
MySQL for PCF provides a relational database for apps and devices. To use MySQL for PCF in an app:
Check the service availability in the Marketplace, and see if there is an existing instance of MySQL for PCF in your space.
See Confirm the MySQL for PCF Service Availability, below.
If there is no existing instance or you want to use a different one, create an instance of the MySQL for PCF service in the same space as the app.
See Create a Service Instance, below.
Bind the app to the MySQL for PCF service instance, to enable the app to use MySQL.
See Bind a Service Instance to Your App, below.
Call the MySQL for PCF service in your app code, and then re-push your app into the space.
See Use the MySQL Service in Your App, below.
After you create a MySQL for PCF service instance, you can manage it over the life cycle of your apps and data. See Manage Service Instances, below.
The below 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 MySQL for PCF with your PAS apps, you must:
-
Decide what type of plan you want from the following options: single node, leader-follower,
and highly available (HA) cluster.
Your marketplace might not offer all of these plan types.
For information about service plans, see Availability Options. -
If you intend to use an HA cluster plan, review the limitations associated with this plan type.
For more information, see Highly Available Cluster Limitations. - Have an Ops Manager installation with MySQL for PCF installed and listed in the Marketplace.
For how to verify availability in the Marketplace, see Confirm Service Availability below. - Have a Space Developer or Admin account on the PAS installation.
For more information, see User 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 MySQL for PCF Service Availability
For an app to use the MySQL for PCF 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 MySQL for PCF service is available in the Marketplace:
Enter the following command:
cf marketplace
If the output lists
p.mysql
in theservice
column, MySQL for PCF 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 MySQL for PCF instance is running in the space:
- Use the cf CLI or 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 theservice
column are service instances of MySQL for PCF 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.
To create an instance of the MySQL for PCF 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 MySQL for PCF plan you want to use.SERVICE-INSTANCE
is a name you choose to identify the service instance. This name appears underservice
in output fromcf services
.
Enter the following command and wait for the
last operation
for your instance to show ascreate 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 succeededIf 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 MySQL for PCF instance run the following command:
Push your app into the same space as your MySQL for PCF service instance by running the following command:
cf push
Bind your app to a MySQL for PCF 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 rancf 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 effectRestart 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 aboutVCAP_SERVICES
, see MySQL Environment Variables below.In your app code, call the MySQL service using the connection strings.
See this example Node.js code.
Use Custom Schemas
MySQL for PCF 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 the following command:
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 MySQL for PCF 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 the following command:
cf restart APP
Where
APP
is the app you want to use the custom schema.
MySQL Environment Variables
Apps running in Cloud Foundry 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:z9z6eskdbs1rhtxt@10.0.0.20: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 MySQL for PCF,
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.
Warning: If a developer rebinds an app to the MySQL for PCF 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:
- Move your data to a different plan. See Update a Service Instance, below.
- Share service instances between orgs and spaces. See Share Service Instances, below.
- Remove access to a service from an app that no longer needs it. See Unbind an App from a Service Instance, below.
- Delete a service instance that is not used. See Delete a Service Instance, below.
Update a Service Instance
You can update a service instance using cf update-service
to do the following:
- Migrate data from a single-node plan to a leader-follower plan.
- Migrate data from a leader-follower plan to a single-node plan.
- Migrate data to a larger plan of the same topology. You cannot update a service instance to a smaller plan.
- Change the default parameters for an existing service instance. See Change Default Parameters on an Existing Service Instance.
Warning: You cannot use cf update-service
to migrate data between a HA cluster plan and a plan of another topology.
If you want to do this, you must use the cf mysql-tools
plugin instead. For more information about migrating data, see Migrating Data in MySQL for PCF.
When you update a service instance, you do not need to rebind your app or service keys. However, while the data is being migrated to the new service instance, the database is unavailable for several minutes.
To update a service instance:
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
For more information about using the update-service
command,
see the Cloud Foundry CLI Reference Guide.
Share Service Instances
In MySQL for PCF 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 rancf 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
Warning: If a developer rebinds an app to the MySQL for PCF 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.
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... OKEnter the following command and wait for a
Service instance not found
error indicating that the instance no longer exists:watch cf service SERVICE-INSTANCE