Using MySQL for PCF

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, do the following:

1. 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.

2. 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.

3. 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.

4. 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 PCF apps, you need:

• Decide what type of plan you want from the following options: single node, leader-follower, highly available (HA) cluster. Your marketplace might not offer all of these types. For about service plans information, see Availability Options.
• If you intend to use an HA cluster plan, review the limitations associated with this plan type. See Highly Available Cluster Limitations.
• A PCF installation with MySQL for PCF installed and listed in the Marketplace
• A Space Developer or Admin account on the PCF installation
• A local machine with the following installed:
  • a browser
  • a shell
  • the Cloud Foundry Command-Line Interface (cf CLI)
  • the Linux watch command
• To log in to the org and space containing your app

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, do the following:

1. Enter the following command:

   cf marketplace
   

2. If the output lists p.mysql in the service 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, do the following:

1. Use the cf CLI or Apps Manager to log in to the org and space that contains the app.

2. Enter the following command:

   cf services
   

3. Any p.mysql listings in the service 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, do the following:

1. 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 under service in output from cf services.

2. 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 MySQL for PCF instance run the following command:

1. Push your app into the same space as your MySQL for PCF service instance by running the following command:

   cf push
   

2. 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 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
   

3. 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:

1. Verify that your app code (or the MySQL client library that the app uses) retries in the case of DNS timeouts.

2. Run the following command:

   cf env APP-NAME
   

   Where APP-NAME is the name of the app bound to the MySQL for PCF instance.

3. In the output, note the connection strings listed in the VCAP_SERVICES > credentials object for the app. For information about VCAP_SERVICES, see MySQL Environment Variables, below.

4. In your app code, call the MySQL service using the connection strings.
   
   See this example Node.js code.

Create and 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 do the following:

1. To create a custom schema using the MySQL client, do the procedures in 3.3.1 Creating and Selecting a Database in the MySQL documentation.
   
   For more information about the CREATE DATABASE SQL statement, see 13.1.12 CREATE DATABASE Syntax.

2. To modify your app to use your custom schema, do one of the following:

   • If your app is written in Java, construct a jdbcUrl that uses your custom schema.
   • If your app is not written in Java, modify your app to use your custom schema in the name credentials property for VCAP_SERVICES.

   For more information about the environment variable credentials hash VCAP_SERVICES, see MySQL Environment Variables, below.

3. Push your app using cf push.

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.

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 to a Larger Plan, 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.
• Use the Log Cache CLI plugin to access MySQL metrics. See Access MySQL Metrics for On-Demand Service Instances, below.

Update a Service Instance to a Larger Plan

As apps and their databases grow, it may be necessary to update the service instance to a larger plan. This does not require a rebinding of your app. However, while the instance is being migrated to a new service instance, the database will be unavailable for several minutes.

Warning: You cannot update to or from a highly available (HA) cluster plan to a plan that uses a single-node or leader-follower topology using the update-service command. To update to or from HA cluster plan, you must migrate your data to a new service instance using the cf mysql-tools plugin. For more information about migrating data, see Migrating Data in MySQL for PCF.

To update a service instance to a larger plan, run the following command:

cf update-service SERVICE-INSTANCE -p PLAN

Where PLAN is the plan you want to upgrade the service instance to.

For example:

$ cf update-service my-instance -p db-large

Unbind an App from a Service Instance

To stop an app from using a service it no longer needs, run the following command to unbind the app from the service:

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, do the following:

1. 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
   

2. 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