Introducing Pivotal MySQL*Web, Pivotal’s New Open Source Web-Based Administration UI for MySQL for Pivotal Cloud Foundry

Recently Pivotal announced "Pivotal MySQL*Web" on it's main blog page. You can read more about it here which was an Open Source project I created a while ago for Pivotal MySQL instances on Pivotal Cloud Foundry

https://content.pivotal.io/blog/introducing-pivotal-mysql-web-pivotal-s-new-open-source-web-based-administration-ui-for-mysql-for-pivotal-cloud-foundry

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

Pivotal MySQL*Web - Web Based Spring Boot / Bootstrap Application for Pivotal MySQL tile (p-mysql)

I have been recently working on a web based interface to Pivotal MySQL Tile which can be deployed to Pivotal Cloud Foundry and bound to a p-mysql service instance for instant access to the MySQL DB. Known as PivotalMySQL*Web this 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. It includes the following capabilities

• 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 (PCF)
• Manage JDBC Connections
• Load SQL File into SQL Worksheet from Local File System
• SQL Worksheet with syntax highlighting support

You can read more about it as the GitHub project as follows and of course clone or fork the code if you wish and deploy it your Pivotal Cloud Foundry instance. When bound to a p-mysql service instance it will automatically connect to the instance without promoting for connect details.

I would welcome feedback here as this is a WIP which I am adding features to as often as I can

https://github.com/papicella/PivotalMySQLWeb

Some screen shots below