Installing and Configuring MySQL for PCF

This topic provides instructions to operators of Pivotal Cloud Foundry (PCF) about how to install, configure, and deploy the MySQL for PCF v2.1 tile. The MySQL for PCF v2.1 service lets PCF developers create and use their own MySQL service instances on demand.

Prepare Your Ops Manager and PCF Installation for MySQL for PCF

Before you download and install the MySQL for PCF tile, complete the following procedures:

Create an Application Security Group for MySQL for PCF

To let apps running on Cloud Foundry communicate with the MySQL service network, you must create an Application Security Group (ASG).

The ASG allows smoke tests to run when you first install the MySQL for PCF service and allows apps to access the service after it is installed.

The example below follows this procedure to create an ASG.

To create an ASG for MySQL for PCF, do the following:

  1. Navigate to the Ops Manager Installation Dashboard and click the Ops Manager Director tile.

  2. Click Create Networks.
    Create Networks page

  3. Find the network that has Service Network checked, and find the CIDR that you can use in your ASGs.
    Reserved IP Ranges

  4. Using the CIDR that you found in the above step, create a JSON file mysql-asg.json with the configuration below:

        "protocol": "tcp",
        "destination": "CIDR",
        "ports": "3306"
  5. Use the CF CLI and the JSON file that you created to create an ASG called p.mysql:

    $ cf create-security-group p.mysql ./mysql-asg.json
  6. Bind the ASG to the appropriate space or, to give all started apps access, bind to the default-running ASG set:

    $ cf bind-running-security-group p.mysql

Enable the Ops Manager Resurrector

The Ops Manager Resurrector increases the availability of MySQL for PCF by restarting and resuming MySQL service in the following ways:

  • Reacts to hardware failure and network disruptions by restarting VMs on active, stable hosts
  • Detects operating system failures by continuously monitoring VMs and restarting them as required
  • Continuously monitors the BOSH Agent running on each service instance VM and restarts the VM as required

Pivotal recommends enabling the Ops Manager Resurrector when installing MySQL for PCF. To enable the Ops Manager Resurrector, do the following:

  1. Navigate to the Ops Manager Installation Dashboard and click the Ops Manager Director tile.

  2. Click Director Config.

  3. Select the Enable VM Resurrector Plugin checkbox.

  4. Click Save.

For general information about the Ops Manager Resurrector, see Using Ops Manager Resurrector

Download and Install the Tile

  1. Download the product file from Pivotal Network.

  2. Navigate to the Ops Manager Installation Dashboard and click Import a Product to upload the product file.

  3. Under the Import a Product button, click + next to the version number of MySQL for PCF. This adds the tile to your staging area.

  4. Click the newly-added MySQL for PCF tile to open its configuration panes.

    AZ and Network Assignments pane

Configure the Tile

Follow the steps below to configure the MySQL for PCF service. MySQL for PCF v2.1 has five service plans that deploy dedicated MySQL service instances on demand.

By default, plans 1-3 are active and plans 4 and 5 are inactive. The procedures below describe how to change these defaults.

Important:In order to re-define plans later, you must leave broker de-registrar checked.

Configure AZs and Networks

Follow the steps below to choose an Availability Zone (AZ) to run the service broker and to select networks.

  1. Click Assign AZs and Networks.

  2. Configure the fields as follows:

    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 Ignore; not used.
    Network Select a subnet for the MySQL broker. This is typically the same subnet that includes the Elastic Runtime component VMs.
    This network is represented by the Default Network in this picture.
    Service Network Select the subnet for the on-demand service instances, the Services Network in this picture.
    If you are adding IPsec to encrypt MySQL communication, Pivotal recommends that you deploy MySQL to its own network to avoid conflicts with services that are not IPsec compatible.

    IMPORTANT: You cannot change the regions or networks after you click Apply Changes in the final step below.

  3. Click Save.

Configure Service Plans

MySQL for PCF enables you to configure as many as five 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 fifth plans are inactive.

Perform the following steps for each plan that you want to use in your PCF deployment:

  1. Click the section for the plan. For example, Plan 1.

  2. Ensure that Active is selected.


  3. Configure the fields as follows:

    Service Plan Access Accept the default to enable access to all organizations in your PCF deployment. Select Disable to disable creating new services instances of this plan for all organizations. Select Manual to manually control service access with the cf CLI.
    Plan Name Accept the default or enter a name. This is the name that appears in the PCF Marketplace for developers.
    Plan Description Accept the default or enter a description to help developers understand plan features. Pivotal recommends adding VM type details and disk size to this field.
    Plan Quota Enter the maximum number of service instances that can exist at one time.
    MySQL VM Type Select a VM type. The plan creates service instances of this size.
    MySQL Persistent Disk Select a disk size. This disk stores the MySQL messages.

    Note: If you intend to enable backups, choose a persistent disk type that is three times as large as you intend to provide to developers. For more information, see Backing Up and Restoring On-Demand MySQL.

    MySQL Availability Zone Select an AZ for the node. This AZ should be different than the AZ that the broker is in. The plan creates all service instance VMs in this AZ. To prevent orphaning a disk, only migrate service instances to a different plan if the plan is in the same AZ.

  4. Click Save.

WARNING: If you expect your developers to upgrade from one plan to another, do not place the plans in separate AZs. For example, if you create Plan 1 in AZ1 and Plan 2 in AZ2, developers receive an error and cannot continue if they try to upgrade from Plan 1 to Plan 2. This prevents them from losing their data by orphaning their disk in AZ1.

To learn how to manually migrate the data from one AZ to another, contact Pivotal Support.

(Optional) Deactivate Service Plan

Follow the steps below to deactivate a service plan:

  1. If the service plan has existing service instances, perform the following steps:
    1. Click the section for the plan. For example, Plan 2.
    2. Under Service Plan Access, select Disable.
    3. Click Save.
    4. Return to the Ops Manager Installation Dashboard and click Apply Changes to redeploy.
    5. When the PCF deployment has redeployed, use the cf CLI or Apps Manager to delete all existing service instances on the service plan.
    6. Return to the MySQL for PCF tile configuration.
  2. Click the section for the plan. For example, Plan 2.
  3. Click Inactive.


  4. Click Save.

Configure Global Settings

Follow the steps below to determine if service instances are assigned public IP addresses and to set the total number of service instances allowed across all plans.

  1. Click Settings.


  2. Configure the fields as follows:

    Provide public IP addresses to all Service VMs Select this checkbox:
    • If the service instances need an external backup, blobstore, or syslog storage
    • If you have configured BOSH to use an external blobstore.
    Maximum service instances Enter the global quota for all on-demand instances summed across every on-demand plan. For information about determining global quotas, see Service Plan Recommended Usage and Limitations.

  3. Click Save.

Configure MySQL

Follow the steps below to set MySQL defaults and enable developers to customize their instances:

  1. Click Mysql Configuration


  2. Configure the fields as follows:

Enable Lower Case Table Names Select this checkbox to set lower_case_table_names to 1 on all MySQL for PCF instances. For more information read the MySQL documentation.
Allow Developers To Override Lower Case Table Names Select this checkbox to allow developers to override the default Lower Case Table Names value set above. By default, all service instances will be created with the value based on “Enable Lower Case Table Names” above. Checking this box allows developers to modify it using the cf CLI.
Enable Local Infile Select this checkbox to enable data downloading from the client’s local file system. For more information about local data loading capability, see Security Issues with LOAD DATA LOCAL in the MySQL 5.7 Reference Manual.

Configure Monitoring

Follow the steps below to enable different types of monitoring and logging available in the MySQL service.

  1. Click Monitoring.


  2. Configure the fields as follows:

    Metrics Polling Interval Set this time, in seconds, to determine the frequency that the monitor polls for metrics. All service instances emit metrics about the health and status of the MySQL server.
    Enable User Statistics Logging Select this checkbox to better understand server activity and identify sources of load on a MySQL server. For more information about user statistics, see User Statistics Documentation.
    Enable Server Activity Logging Select this checkbox to record who connects to the servers and what queries are processed using the Percona Audit Log Plugin. For more information, see the Percona Documentation.
    Enable Read-Only Admin User Select this checkbox to create a read-only admin user, roadmin, on each service instance. This user can be used for auditing and monitoring without risking mutating or changing any data, because the roadmin user cannot make changes.

    To retrieve the credentials for the roadmin user, see Retrieve Admin and Read-Only Admin Credentials for a Service Instance. The read-only admin user is always roadmin, but the password varies by service instance.

  3. Click Save.

Configure System Logging

Follow the steps below to enable system logging for the MySQL broker and service instance VMs. Logs use RFC5424 format.

  1. Click Syslog.

  2. Click Yes.


  3. Configure the fields as follows:

    Address Enter the address or host of the syslog server for sending logs, for example,
    Port Enter the port of the syslog server for sending logs, for example, 29279.
    Transport Protocol Select the protocol over which you want system logs. Pivotal recommends using TCP.
    Enable TLS If you select TCP, you can also select to send logs encrypted over TLS.
    Permitted Peer Enter either the accepted fingerprint, in SHA1, or the name of the remote peer, for example, *
    SSL Certificate Enter the SSL Certificate(s) for the syslog server. This ensures the logs are transported securely.

    IMPORTANT: If your syslog server is external to PCF, you might need to select Provide public IP addresses to all Service VMs on the Settings page.

  4. Click Save.

Verify Stemcell Version and Apply All Changes

  1. Click Stemcell.

  2. Verify and, if necessary, import a new stemcell version.
    For more information, see about importing the stemcell for your IaaS: AWS, Azure, GCP, or vSphere.

  3. Click Save.

  4. Return to the Ops Manager Installation Dashboard and click Apply Changes.

Create a pull request or raise an issue on the source for this page in GitHub