use.html.md.erb

---
title: Using MySQL for PCF v2
owner: MySQL
---

This topic provides instructions for developers using the MySQL for Pivotal Cloud Foundry (PCF) v2 service for their
PCF apps. MySQL provides a relational database for apps and devices.

These procedures use the Cloud Foundry Command Line Interface (cf CLI). For more information,
see [Managing Service Instances with the cf CLI](http://docs.pivotal.io/devguide/services/managing-services.html).

You can also use [Apps Manager](http://docs.pivotal.io/pivotalcf/console/dev-console.html) to do the same tasks using a graphical UI.


## <a id="prereqs"></a>Prerequisites

To use MySQL for PCF v2 with your PCF apps, you need:

* A PCF installation with [MySQL for PCF](https://network.pivotal.io/products/p-mysql) installed and listed
in the [Marketplace](http://docs.pivotal.io/devguide/services/#instances)
* A [Space Developer](http://docs.pivotal.io/pivotalcf/concepts/roles.html#roles) or Admin account on the PCF installation
* A local machine with the following installed:
   - a browser
   - a shell
   - the [Cloud Foundry Command-Line Interface](http://docs.pivotal.io/pivotalcf/cf-cli/install-go-cli.html) (cf CLI)
   - the Linux [watch](http://www.linfo.org/watch.html) command
* To [log in to](http://docs.pivotal.io/pivotalcf/cf-cli/getting-started.html#login) the org and space containing your app


## <a id='process'></a>The Process for Using MySQL for PCF in Your App

To use MySQL in a PCF app:

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 v2 Service Availability](#marketplace), 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](#create), below.

3. Push your app into the same space as the MySQL for PCF service instance,
   using `cf push`.

    For information about `cf push`,
   see [Push](http://docs.pivotal.io/pivotalcf/2-0/cf-cli/getting-started.html#push).

4. 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](#bind), below.

5. 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](#call), below.



## <a id='marketplace'></a>Confirm the MySQL for PCF v2 Service Availability

For an app to use the MySQL for PCF v2 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.

### <a id="check-marketplace"></a> Check Service Availability in the Marketplace

To find out if a MySQL for PCF v2 service is available in the Marketplace, do the following:

1. Enter the following command:

    ```
    cf marketplace
    ```

1. If the output lists `p.mysql` in the `service` column, MySQL for PCF v2 is available.
   If it is not available, ask your operator to install it.

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

### <a id="check-instance"></a> Check That an Instance Is Running in the Space

To confirm that a MySQL for PCF v2 instance is running in the space, do the following:

1. Use the [cf CLI](http://docs.pivotal.io/pivotalcf/cf-cli/getting-started.html#login) or
[Apps Manager](http://docs.pivotal.io/pivotalcf/customizing/console-login.html) to log in to the org and space that contains the app.
1. Enter the following command:

    ```
    cf services
    ```
1. Any `p.mysql` listings in the `service` column are service instances of MySQL for PCF v2 in the space.
<br><br>
For example:
    <pre class="terminal">
    $ cf services
    Getting services in org my-org / space my-space as user<span>@</span>example.com...
    OK
    name          service     plan        bound apps    last operation
    my-instance   p.mysql     db-small                  create succeeded
    </pre>
    You can [bind](#bind) your app to an existing instance or [create](#create) a new instance to bind to your app.

## <a id='create'></a>Create a Service Instance

Unlike pre-provisioned services, 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 v2 service, do the following:

1. Run the following command:

    ```
    cf create-service p.mysql PLAN SERVICE-INSTANCE`<br><br>
    ```

    Where:<br>
    - `PLAN` is the name of the MySQL for PCF v2 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`.

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

    ```
    watch cf services
    ```

    For example:

    <pre class="terminal">
   $ cf create-service p.mysql db-small my-instance<br>
    Creating service my-instance in org my-org / space my-space as user<span>@</span>example.com...
    OK<br>
   $ watch cf services<br>
    Getting services in org my-org / space my-space as user<span>@</span>example.com...
    OK
    name          service       plan        bound apps    last operation
    my-instance   p.mysql       db-small                  create succeeded
</pre>

    If you get an error, see [Troubleshooting Instances](./troubleshoot-instances.html).

## <a id="bind"></a>Bind a Service Instance to Your App

For an app to use a service, you must bind the app to a service instance. Do this after you push or re-push the app using `cf push`.

To bind an app to a MySQL for PCF instance run the following command:

```
cf bind-service APP SERVICE-INSTANCE
```

Where:<br>

- `APP` is the app you want to use the MySQL service instance.<br>
- `SERVICE-INSTANCE` is the name you supplied when you ran `cf create-service`.

For example:

<pre class="terminal">
$ cf bind-service my-app my-instance<br>
Binding service mydb to my-app in org my-org / space test as user<span>@</span>example.com...
OK
TIP: Use 'cf push' to ensure your env variable changes take effect
</pre>

## <a id="call"></a>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.

1. Run the following command:

    ```
    cf env APP-NAME
    ```

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

1. In the output, note the connection strings listed in the `VCAP_SERVICES` > `credentials` object for the app.

1. In your app code, call the MySQL service using the connection strings.<br><br>
  See this example [Node.js code](./modify-apps-tls.html#call-service).


## <a id="manage"></a> Manage Service Instances

This section describes tasks you do over the life cycle of your apps and data:

+ Moving your data to a different plan.
+ Removing an app's access to a service it no longer needs.
+ Deleting a service instance that is not used.

### <a id="update"></a>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.

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:

<pre class="terminal">
$ cf update-service my-instance -p db-large
</pre>


### <a id="unbind"></a>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:<br>

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

For example:

<pre class="terminal">
$ cf unbind-service my-app my-instance<br>
Unbinding app my-app from service my-instance in org my-org / space my-space as user<span>@</span>example.com...
OK
</pre>

### <a id="delete"></a>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.
    <br><br>
    For example:

    <pre class="terminal">
    $ cf delete-service my-instance<br>
    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<span>@</span>example.com...
    OK
    </pre>

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


##<a id='establish-tls'></a> Establish a TLS Connection to a Service Instance

You can use `mysql` to establish a TLS connection to a MySQL for PCF service instance that has TLS enabled. For more information about how to enable TLS for a service instance, see [Using TLS](using-tls.html).

Perform the following steps:

1. Create a new service key for the service instance with TLS enabled. For example:
  <pre class="terminal">$ cf create-service-key my-service-instance my-tls-service-key
  {
   "hostname": "10.1.16.5",
   "jdbcUrl": "jdbc:mysql<span>://</span>10.244.16.3:3306/service\_instance\_db?user=6bf07ae455a14064a9073cec8696366c\u0026password=a22aaa2a2a2aaaaa\u0026=true",
   "name": "service\_instance\_db",
   "password": "a22aaa2a2a2aaaaa",
   "port": 3306,
   "tls": {
    "cert": {
     "ca": "-----BEGIN CERTIFICATE-----\...n-----END CERTIFICATE-----\n"
    }
   },
   "uri": "mysql<span>://</span>6bf07ae455a14064a9073cec8696366c:a22aaa2a2a2aaaaa<span>@</span>10.244.16.3:3306/service\_instance\_db?reconnect=true",
   "username": "6bf07ae455a14064a9073cec8696366c"
  }
  </pre>
  If the service key does not have a CA certificate under `tls.cert.ca`, the service key may be stale. Create a new one.

1. Copy the contents of the CA certificate under `tls.cert.ca` and paste it into a file. For example:
  <pre class="terminal">$ pbpaste > root.pem</pre>

1. Record the values for `username`, `password`, and `hostname`.

1. Use `mysql` to establish a TLS connection to the MySQL instance.
  Run the following command:
  <pre><code>mysql --host=HOSTNAME \
    --user=USERNAME \
    --password=PASSWORD \
    --ssl-ca=root.pem \
    --ssl-verify-server-cert</code></pre>
  <br>
  Where:
    * `HOSTNAME` is the value for `hostname` retrieved above.
    * `USERNAME` is the value for `username` retrieved above.
    * `PASSWORD` is the value for `password` retrieved above.
  <br><br>
  For example:
    <pre class="terminal">
    $ mysql --hostname=10.1.16.5 \
      --user=6bf07ae455a14064a9073cec8696366c \
      --password=a22aaa2a2a2aaaaa \
      --ssl-ca=root.pem \
      --ssl-verify-server-cert
    </pre>

## <a id="dev-tools"></a>MySQL for PCF Tools

The following tools let developers access their MySQL for PCF databases.

### <a id="mysqlweb"></a>Pivotal MySQLWeb Database Management App

The Pivotal MySQLWeb app provides a web-based UI for managing MySQL for PCF databases.
The free app lets you view and operate on tables, indexes, constraints, and other database structures, and directly execute SQL commands.

![Pivotal MySQLWeb](mysqlweb.png)

You can run the Pivotal MySQLWeb app in two ways:

* Standalone on your own machine
* Deployed to PCF

If you deploy Pivotal MySQLWeb to PCF, you can configure it in the deployment manifest to automatically bind to a specific service instance.

See the Pivotal MySQLWeb [code repo](https://github.com/pivotal-cf/PivotalMySQLWeb) and
[demo video](https://drive.google.com/file/d/0B2wmRcJj8XF-SXloNFNNaGJXbFk/view),
for how to install and run Pivotal MySQLWeb.

### <a id="plugin"></a>cf CLI MySQL Plugin

To connect to your MySQL for PCF databases from a command line, use the cf CLI MySQL plugin.
The plugin lets you:

* Inspect databases for debugging
* Manually adjust database schema or contents in development environments
* Dump and restore databases

To install the cf CLI MySQL plugin, run the following:

<pre class="terminal">
$ cf install-plugin -r "CF-Community" mysql-plugin
</pre>

For more information, see the [cf-mysql-plugin](https://github.com/andreasf/cf-mysql-plugin) repository.

### <a id="desktop"></a> Use Desktop Tools

You can connect your MySQL for PCF databases to a desktop tool, such as MySQL Workbench 
or Sequel Pro, if you have the credentials for your MySQL service instance. 

If you do not have credentials for your MySQL service instance, follow the procedure in 
[Create Read-only Access Credentials](././customize-access.html).

To connect your databases to a desktop tool do the following:
    
1. To retrieve the credentials from your MySQL service instance service key, run the following 
command:

    ```
    cf service-key SERVICE-INSTANCE MYSQL-SERVICE-KEY
    ```
    Where:   
      * `SERVICE-INSTANCE` is the name of your service instance.
      * `KEY-NAME` is the name of your service key.
  
    For example:
  
    <pre class="terminal">
      $ cf service-key mydb mykey1<br>
      {
        "hostname": "99.99.99.9",
        "jdbcUrl": "jdbc:mysql://99.99.99.9:3306/cf\_e2d148a8\_1baa\_4961\_b314\_2431f57037e5?user=abcdefghijklm\u0026password=123456789",
        "name": "cf\_e2d148a8\_1baa\_4961\_b314\_2431f57037e5",
        "password": "123456789",
        "port": 3306,
        "uri": "mysql://abcdefghijklm:123456789<span>@</span>99.99.99.9:3306/cf\_e2d148a8\_1baa\_4961\_b314\_2431f57037e5?reconnect=true",
        "username": "abcdefghijklm"
      }
    </pre>  
  
1. Record the values for the following:  
    * `hostname`
    * `name`
    * `password`
    * `port`
    * `username`
    
1. Configure an SSH tunnel using the values for `hostname` and `port` that you recorded in the above step.
   For information on configuring an SSH tunnel,
   see [Configure Your SSH Tunnel](https://docs.pivotal.io/pivotalcf/2-3/devguide/deploy-apps/ssh-services.html#ssh-tunnel).
    
1. Configure a connection in your desktop tool using the values for `hostname`, `name`, `password`, `port`, and `username` that you recorded in the above step. 

## <a id="example-app"></a>Example App

To help app developers get started with MySQL for PCF, Pivotal provides an example app, which can be downloaded [here][example-app].
Instructions are in the README.

[example-app]:mysql-example-app.tgz