top of page
  • Writer's pictureSavidu Dias

Setting up WSO2 API Manager on Pivotal Cloud Foundry with MySQL

WSO2 API Manager is now available as an installation option on Pivotal Cloud Foundry (PCF). This is a setup guide on the installation process of WSO2 API Manager on PCF with MySQL.


Before we begin, a major prerequisite would be to have a preconfigured PCF Environment. My previous blog post “Simple Guide to Setting up Pivotal Cloud Foundry on AWS” provides basic sets of instructions on how we could get our own PCF environment on AWS.

Additionally, we require a basic WSO2 API Manager PCF setup (tile) uploaded to our PCF Environment. The following blog on “Pivotal Cloud Foundry resources for WSO2 API Manager 2.6.0” describes the process in which API Manager tiles could be built, and uploaded to a PCF environment.

Finally, we require the Cloud Foundry CLI to create and our MySQL database instance.


Pivotal has made the process of setting up a MySQL database within our environment through their MySQL tile. The next part explains how to setup and use MySQL on PCF. However, if you have your own MySQL database environment, skip to here.

Preparing Ops Manager and PCF Installation for MySQL

This topic describes how to provide an existing CA certificate to BOSH CredHub and how to generate a new CA certificate with BOSH CredHub, if you do not already have one.

Enabling TLS provisions a MySQL server with a certificate so that apps and clients can establish an encrypted connection with the data service.

The certificate deployed with the MySQL server is a server certificate. The server certificate is generated by CredHub, a component designed for centralized credential management in PCF, colocated on the BOSH Director.

CredHub generates the server certificate with a Certificate Authority (CA) certificate. The CA certificate must be provided to CredHub by the operator or generated by CredHub.

1. Finding CredHub credentials in Ops Manager

In the Ops Manager Installation dashboard, click the Bosh Director tile, and select the Credentials tab. Afterwards, select the link to Bosh Commandline Credentials.


        BOSH_ENVIRONMENT= bosh "

2. Connecting to OPS Manager via SSH

Download the PCF CLI, set it up as a binary, and create a metadata file containing information related to the Ops Manager setup.

chmod +x pcf_darwin-64bit
mv pcf_darwin-64bit /usr/local/bin/pcf
touch metadata

Open the metadata file and add the following

tags: [ dev ]
purpose: <Description>
  url: <ops_manager_dns>
  ssh_key: |
    -----END RSA PRIVATE KEY-----

For YOUR-ENVIRONMENT-NAME, provide the environment name used when creating the PCF environment.

ops_manager_dns refers to the ops_manager_dns value obtained from the terraform output if the environment was created using Terraform. This value should look like https://YOUR-ENVIRONMENT-NAME.YOUR-DNS-SUFFIX.

OPS-MANAGER-USERNAME and OPS-MANAGER-PASSWORD refer to the username and password used to log in to our Ops Manager environment.

ops_manager_ssh_private_key refers to the ssh private key of Ops Manager. This is also the ops_manager_ssh_private_key value obtained from the terraform output if the environment was created using Terraform.

Run the ssh command from the directory containing the metadata file to connect to Ops Manager.

pcf ssh

We have now connected to the Ops Manager VM via ssh. Get the IP Address of the Bosh Director VM from the BOSH_ENVIRONMENT value of Bosh Commandline Credentials.

Then, from the Ops Manager VM, set the API target of the CredHub CLI to your BOSH CredHub server by running

credhub api https://BOSH-DIRECTOR:8844 --ca-cert=/var/tempest/workspaces/default/root_ca_certificate

where BOSH-DIRECTOR is the IP address of the BOSH Director VM.

Log in to CredHub by running

credhub login --client-name=CREDHUB-CLIENT-NAME --client-secret=CREDHUB-CLIENT-SECRET


  • CREDHUB-CLIENT-NAME is the value we recorded for BOSH_CLIENT

  • CREDHUB-CLIENT-SECRET is the value we recorded for BOSH_CLIENT_SECRET

Use the CredHub CLI to check whether a services CA certificate already is present. Run the following command:

credhub get --name="/services/tls_ca"

If you do not have a CA certificate, use the CredHub CLI to generate one. Enter the following command:

credhub generate \
    --name="/services/tls_ca" \
    --type="certificate" \
    --is-ca \

Use the BOSH CLI v2 to extract the certificate portion from the CA certificate and print it. Run the following command:

bosh2 int <(credhub get \
    --name=/services/tls_ca) \
    --path /value/certificate

Copy the output and navigate to the Ops Manager Installation Dashboard and select the Bosh Director tile, and select Security from the navigation pane. Then, paste the contents of the CA certificate into Trusted Certificates and click Save.

Installing and Configuring MySQL for PCF

Download and install the MySQL service tile from Pivotal Network. Then, navigate to the Ops Manager Installation Dashboard and click Import a Product to upload the product file. Once the tile is imported, a Stemcell may be required. Download the stemcell release (eg: Ubuntu Xenial Stemcell for AWS 170.15) as indicated in the required column for MySQL for Pivotal Cloud Foundry v2 from here, and import it.

Go to the Installation Dashboard and select the MySQL for Pivotal Cloud Foundry v2 tile.

1. AZ and Network Assignments Page

  • Place singleton jobs in: Select the AZ that you want the MySQL broker VM to run in. The broker runs as a singleton job

  • Balance other jobs in: Select any combination of AZs.

  • Network: pcf-management-network

  • Service Network: pcf-services-network

2. Configure Service Plans

MySQL for PCF enables you to configure as many as nine service plans. Each service plan has a corresponding section in the tile configuration, such as Plan 1, Plan 2, and so on. By default, the first three plans are active and the fourth and the rest.

Select Active for the service plans you want to be made active, and leave the rest at the Inactive state.

The fields in the service plans indicate the following:

Multi-node deployment: Configures a leader-follower plan. Leave unchecked to configure each instance as a singleton. For more information, see Configure a Leader-Follower Service Plan.

Service Plan Access:

  • Enable - Gives access to all orgs, and displays the service plan to all members.

  • Disable - Disables access to all orgs, and hides access to all developers in the marketplace.

  • Manual - Lets you manually control service access with the cf CLI. For more information, see Controlling Access to Service Plans by Org.

Plan name: Provide a name for the plan, or provide the default value.

Plan Description: Description to assist developers in understanding the plan features. Pivotal recommends adding VM type details and disk size to this field.

Plan Quota: The maximum number of service instances that can exist at once.

MySQL VM Type: Select a VM type. The plan creates service instances of this size.

MySQL Persistent Disk: The disk size of the mysql database instance.

MySQL Availability Zone(s): The availability zones the database instance will be hosted in.

Once the instances are set up, click Save.

3. Settings Page

In Settings, provide an email address to send mysql monitoring notifications to. Then select Save.

4. Backups Page

In Backups, if you do not wish to use any forms of backup, set “ignore” to all fields in the page. Then, select Save.

5. Security Page

In the Security tab, select Optional in TLS Options and click Save.

6. Service Instance Upgrades

In the Service Instance Upgrades page, enter ‘X’ to acknowledge that you have run the find-deprecated-bindings errand, and confirmed that ALL service instances are on v2.4, and all bindings and service keys use BOSH DNS.

7. Complete the MySQL for PCF Installation

Click the Installation Dashboard link to return to the Installation Dashboard. Click Review Pending Changes:

  • Select the Small Footprint PAS tile and click “Apply Changes”

  • After the update is complete, select “MySQL for Pivotal Cloud Foundry v2” and click “Apply Changes”

Setting up MySQL database from Cloud Foundry Marketplace Services

We’re almost done with setting up our database instance! Our next step is to log into CF from the CF CLI using our Apps Manager credentials. If we do not have the credentials to Apps Manager, visit the Installation Dashboard in Ops Manager, and then select the Pivotal Application Service (PAS) tile.

Click the Credentials tab, and refer to the link in UAA > Admin Credentials.

Open the terminal on your local machine and enter the following command to log in to our CF setup.

cf login --skip-ssl-validation -a api.sys.YOUR-SYSTEM-DOMAIN -u identifier -p password

Where YOUR-SYSTEM-DOMAIN refers to the system domain of our PCF environment. Identifier refers to the identifier value obtained from Admin Credentials, and password is the password obtained from Admin Credentials. Then, select any space from the provided options.

Then, enter the following command to view all of our cloud foundry services.

cf marketplace

If we have not installed any services other than mysql, the only services we would see are app-autoscaler, and p.mysql. The mysql service that I had setup contains only two plans (db-small, and db-medium).

We will now create one db-medium service with the name wso2am-db, and create a service key for it. The set of commands for this is as follows.

cf create-service p.mysql db-medium wso2am-db
cf create-service-key wso2am-db wso2am-db-key
cf service-key wso2am-db wso2am-db-key

From the last command, save the hostname, name (db_name), port, password, and username.

We have finally set up our MySQL database! Now it’s time to add the database tables.

Inserting Database Tables

1. Obtaining Database Schema

Download the API Manager 2.6.0 binary from the API Manager Page. The binary contains a zip file. Unzip the binary and navigate to the wso2am-2.6.0/dbscripts directory.

Take note of the mysql schema files at the following locations:

  • wso2am-2.6.0/dbscripts/mysql5.7.sql

  • wso2am-2.6.0/dbscripts/apimgt/mysql5.7.sql

These file need to be copied into our database instance.

2. Copying Database Files into the Instance

In order to create the database tables, we must first access our database instance. To do so, ssh into Ops Manager (from the directory containing the metadata file) and view the deployments by running the following commands:

pcf ssh
bosh deployments

The bosh deployments command would list out all of the deployments in our environment. The deployment containing our database would have a name similar to “service-instance_795d6d0b-e174-45f7-b398-2fe41872dbf5”. Copy the name of the instance to your clipboard and run the following command.

bosh vms -d service-instance_795d6d0b-e174-45f7-b398-2fe41872dbf5

This command displays all virtual machines in the deployment. In this case, we would observe just once instance. We can now run the following command to ssh into the instance:

bosh ssh -d service-instance_795d6d0b-e174-45f7-b398-2fe41872dbf5 mysql/f58514c0-2428-4cfd-8f62-9f51826d963a

We are now in the instance containing our MySQL database. The first thing we should do is to copy the mysql files from API Manager into the instance. Create a dbscripts directory, and a apimgt directory within it.

mkdir /tmp/dbscripts /tmp/dbscripts/apimgt

Then, copy the relevant mysql5.7.sql from our API Manager binary into the respective directories.

Now, we can log into our database:

mysql -u <username> -p

Enter the password and connect to the database. Then, view the databases within our instance and change the database into service_instance_db.

service_instance_db must contain all of the tables API Manager interacts with. We may now insert the tables into the database.

> use service_instance_db
> source /tmp/dbscripts/mysql5.7.sql
> source /tmp/dbscripts/apimgt/mysql5.7.sql

We have now completed our entire database setup! We will complete the API Manager set up by setting up the API Manager Tile.

Setting up the API Manager Tile

Navigate back to the Installation Dashboard in Ops Manager, and select the API Manager tile.

1. AZ and Network Assignments Page

  • Place singleton jobs in: Select the AZ that you want the MySQL broker VM to run in. The broker runs as a singleton job

  • Balance other jobs in: Select any combination of AZs.

  • Network: Select pcf-pas-network

Click Save.

2. Carbon Database connection information

  • JDBC URL: jdbc:mysql://<hostname>:<port>/<db_name>?autoReconnect=true&amp;useSSL=false

  • Driver Class Name: com.mysql.jdbc.driver

  • Validation Query: SELECT 1

  • Username: <username>

  • Password: <password>

Click Save.

3. API Manager Database connection information

  • JDBC URL: jdbc:mysql://<hostname>:<port>/<db_name>?autoReconnect=true&amp;useSSL=false

  • Driver Class Name: com.mysql.jdbc.driver

  • Validation Query: SELECT 1

  • Username: <username>

  • Password: <password>

Click Save.

4. API Manager - Analytics Clustering Database connection information

  • JDBC URL: jdbc:mysql://<hostname>:<port>/<db_name>?autoReconnect=true&useSSL=false

  • Driver Class Name: com.mysql.jdbc.driver

  • Validation Query: SELECT 1

  • Username: <username>

  • Password: <password>

Note that the JDBC URL does not contain “&amp”. Instead, it indicates the “&” symbol. Thereafter, click Save.

Finally, return to the Installation Dashboard and select Review Pending Changes. Only select WSO2 API Manager 2.6.0 from the product list, and select Apply Changes. The installation process would take around 25 minutes.

Once the installation is complete, you have successfully set up WSO2 API Manager 2.6.0 on PCF! To test if everything functions as expected, visit https://wso2apim.sys.<domain_name>/publisher/ to create an API (or deploy the sample) and test its functionality.

37 views0 comments

Recent Posts

See All
bottom of page