In the context of Pivotal Cloud Foundry, a service instance can be one of many things from a messaging service to a database service or even an API service. Each service and it’s lifecycle is typically managed by Pivotal Cloud Foundry. When it comes to database services, a common question is, “How do we get access to the service to perform administration tasks against the underlying database service?”
This is what Pivotal MySQL*Web solves. A web-based schema administration tool, Pivotal MySQL*Web allows you to safely access MySQL for Pivotal Cloud Foundry instances directly from Pivotal Cloud Foundry. Deploy the Spring Boot application, “Pivotal MySQL*Web,” to your Pivotal Cloud Foundry instance, bind it to your MySQL service instance, and, before you know it, you have a web-based application ready to be used.
Welcome Screen
About MySQL for PCF
MySQL for Pivotal Cloud Foundry enables application developers to provision and use a MySQL database with a single command. The MySQL for Pivotal Cloud Foundry product then delivers dedicated instances on demand, a.k.a. Database as a Service, to Cloud Foundry users. When installed, the tile deploys and maintains a single service broker that is responsible for Cloud Foundry integration. The service is configured with defaults following the principle of least surprise for a general-use relational database service.
About Pivotal MySQL*Web
Pivotal MySQL*Web is a Pivotal open source, browser-based administration and development tool and provides a convenient web-based option to connect to a MySQL for Pivotal Cloud Foundry instance without the need for client-side installs. Like any other Pivotal Cloud Application, it can be scaled to multiple instances and will re-start in the event of an instance failure as you would expect from Pivotal Cloud Foundry.
Pivotal MySQL*Web includes the following capabilities:
- Multiple command SQL worksheet for DDL and DML
- Run an Explain Plan across SQL statements
- View/Run DDL commands against Tables/Views/Indexes/Constraints
- Command history
- Auto-bind to 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
- View database variables
- Table viewer
Table Viewer
A common task of database administration tools is table management. Pivotal MySQL*Web provides a table viewer to give you a snapshot of your database table, from its columns, indexes, data, ddl, etc. Here is how to get to that, as well as what it will show you:
Navigate to a deployed Pivotal MySQL*Web from Pivotal Apps Manager UI and click on it’s route as shown below.
The application will automatically read the VCAP_SERVICES environment variable and start looking for MySQL for Pivotal Cloud Foundry service instances. The MySQL service includes a shared service plan and a dedicated service plan. Either will be read and a connection established automatically as per the bound MySQL for Pivotal Cloud Foundry instance credentials.
Once connected you can start to use the various tabs. To get to the Table Viewer you first need to find the table you wish to view, which is done by clicking on the “Tables[count]” tab that will display a list of tables as shown below.
Click on the table link to get detailed information about the table from a subset of table rows to table DDL a description of the table and more.
As you can see from the screenshot below, you get a detailed page of most table commands you would want to run. Most common of all is the table description and, of course, seeing table data – even just a snapshot of it – is handy from the one page.
Searching through table data more often than not requires a SQL query to pinpoint the data you wish to view, but a query itself will often return multiple rows which still requires you to scan the returned result set. Pivotal MySQL*Web across the various views of table data or schema objects provides a client side search text box which will drill into the exact data you are looking for as shown below. This will avoid having to write further SQL while narrowing down the rows or specific rows you need to look at.
The most common query developers run against a table is a table describe. This gives a developer a quick snapshot of what the table is made up of from column names, column types and what primary key and other constraints exist. Using just a single click without the need to reload the page a table describe is provided as one of the options on the table viewer page. The table viewer page is one stop shop for everything you need to know about the table.
SQL*Worksheet
The SQL Worksheet within Pivotal MySQL*Web allows you to run one or more SQL DML/DDL statements. It also supports loading SQL files ensuring you can easily create the required schema objects and data requirements through a syntax highlighting web-based UI. For example, to create DEPT/EMP tables along with some data to be inserted, we would do the following:
Click on the “SQL Worksheet” icon as shown below
Browse to a SQL file on your local file system and press the “Load” button to load your SQL file
Finally, press the “Execute” button to run each of the SQL statements which will display the result whether it was successful or not for every SQL statement. This makes it easier to isolate any problem SQL and re-run it once you have isolated why it failed. Every SQL statement is color coded green or red to indicate success or failure giving you a quick visual of the execution status.
More often than not, query tuning will require investigation, which generally starts with an explain plan detailing how the database will execute the given SQL. The explain plan details MySQL optimizer considerations to efficiently perform the lookups involved in an SQL query. You can simply invoke your SQL statement in the “SQL Worksheet” and select “Explain Plan” to “Yes” from the drop down combo prior to executing the statement as shown below.
Getting Started
To get started with Pivotal MySQL*Web visit the GitHub page as follows:
https://github.com/pivotal-cf/PivotalMySQLWeb
From here it shows how to clone the project, package it and a sample Cloud Foundry manifest.yml file to deploy to Pivotal Cloud Foundry using “cf push”. Once deployed and bound to a MySQL for Pivotal Cloud Foundry instance, it will automatically connect and you're ready to get started by using the URL you defined in the manifest.
Try It Out
Pivotal MySQL*Web’s sole purpose is to provide a web-based UI running within Pivotal Cloud Foundry for administrators and developers using MySQL for Pivotal Cloud Foundry instances. We at Pivotal use it for demonstrations, database management and, of course, visualization of what actually exists in the database without the need to resort to command line tools. Give it a go. Per the getting started guide above, it’s fast and easy to set up so it won’t take long to be up and running.
