Accessing a Pivotal MySQL service instance within Pivotal Cloud Foundry

Recently at a hackathon we used the Pivotal MySQL service rather then a ClearDB MySQL service. As a result we could not connect to our instance from a third party tool as the service instance is locked down. There are various way to access the MySQL service to me the best two options are as follows.

1. Cloud Foundry CLI MySQL Plugin

cf-mysql-plugin makes it easy to connect the mysql command line client to any MySQL-compatible database used by Cloud Foundry apps. Use it to

• inspect databases for debugging purposes
• manually adjust schema or contents in development environments
• dump and restore databases

Install it as explained in the link below:

  https://github.com/andreasf/cf-mysql-plugin

** Using It ** 

1. First ensure you are logged into a Pivotal Cloud Foundry instance you can determine that as follows

pasapicella@pas-macbook:~$ cf target -o ben.farrelly-org -s hackathon
API endpoint:   https://api.run.pivotal.io
API version:    2.78.0
User:           papicella@pivotal.io
Org:            ben.farrelly-org
Space:          hackathon

2. Verify you have a MySQL instance provisioned

pasapicella@pas-macbook:~$ cf services
Getting services in org ben.farrelly-org / space hackathon as papicella@pivotal.io...
OK

name        service   plan    bound apps                                                     last operation
nab-mysql   p-mysql   100mb   nabhackathon-beacon, nabhackathon-merchant, pivotal-mysqlweb   create succeeded

3. Log in as shown below

pasapicella@pas-macbook:~$ cf mysql nab-mysql

...

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

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> show databases;
+-----------------------------------------+
| Database                                |
+-----------------------------------------+
| cf_53318c9c_caec_49be_9e33_075fade26183 |
| information_schema                      |
+-----------------------------------------+
2 rows in set (0.30 sec)

mysql> use cf_53318c9c_caec_49be_9e33_075fade26183;
Database changed

mysql> show tables;
+---------------------------------------------------+
| Tables_in_cf_53318c9c_caec_49be_9e33_075fade26183 |
+---------------------------------------------------+
| beacon                                            |
| beacon_product                                    |
| customer                                          |
| customer_registration                             |
| merchant                                          |
| payment                                           |
| payment_product                                   |
| product                                           |
+---------------------------------------------------+
8 rows in set (0.29 sec)

2. Pivotal MySQL*Web

PivotalMySQL*Web is a browser based SQL tool rendered using Bootstrap UI for MySQL PCF service instances which allows you to run SQL commands and view schema objects from a browser based interface. Use it to

• Multiple Command SQL worksheet for DDL and DML
• Run Explain Plan across SQL Statements
• View/Run DDL command against Tables/Views/Indexes/Constraints
• Command History
• Auto Bind to Pivotal MySQL Services bound to the Application within Pivotal Cloud Foundry 
• Manage JDBC Connections
• Load SQL File into SQL Worksheet from Local File System
• SQL Worksheet with syntax highlighting support
• HTTP GET request to auto login without a login form
• Export SQL query results in JSON or CSV formats
• Generate DDL for schema objects

It does this deployed within Pivotal Cloud Foundry as an application instance and auto binds to the MySQL service for you if you choose to bind it as part of the "cf push" and a manifest.yml which looks as follows

---
applications:
- name: pivotal-mysqlweb
  memory: 512M
  instances: 1
  host: pivotal-mysqlweb-${random-word}
  path: ./target/PivotalMySQLWeb-0.0.1-SNAPSHOT.jar
  services:
    - pas-mysql

Install it as explained in the link below:

  https://github.com/pivotal-cf/PivotalMySQLWeb