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.2 tile. The MySQL for PCF v2.2 service lets PCF developers create and use their own MySQL service instances on demand.

Role-Based Access in Ops Manager

Ops Manager administrators can use Role-Based Access Control (RBAC) to manage which operators can make deployment changes, view credentials, and manage user roles in Ops Manager. Therefore, your role permissions may not allow you to perform every procedure in this operator guide.

For more information about roles in Ops Manager, see Understand Roles in Ops Manager.

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.2 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 Pivotal Application Service (PAS) or 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:

    Multi-node deployment Check the box to configure 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 Select one of the following options:
    • Enable (Default)—Gives access to all orgs, and displays the service plan to all developers in the Marketplace.
    • Disable—Disables access to all orgs, and hides the service plan to all developers in the Marketplace. This disables creating new service instances of this plan.
    • Manual—Lets you manually control service access with the cf CLI. For more information, see Controlling Access to Service Plans by Org.
    Plan Name Accept the default or enter a name. This is the name that appears in the 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 larger than the disk you intend to provide to developers. For more information, see Backing Up and Restoring On-Demand MySQL.

    MySQL Availability Zone(s) If you check the Multi-node deployment box above to enable a leader-follower plan, choose one of the following:
    • To leverage the full HA advantages of leader-follower, select two AZs. You must choose AZs different from the one the broker is in.
    • To leverage some HA advantages, select one AZ. Using only one AZ for a Multi-node deployment greatly reduces the ability to recover from an outage and benefit from such a deployment.
    If you do not check Multi-node deployment, select one AZ.

    Note: To prevent orphaning a disk, only migrate service instances to a different plan if the new plan includes all of the AZs in the existing plan.

  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 store all table names in lowercase. Selecting this sets the MySQL server system variable lower_case_table_names to 1 on all MySQL for PCF instances by default. To allow developers to override this default, see the checkbox below. For more information, see lower_case_table_names in the MySQL documentation.
Allow Developers To Override Lower Case Table Names Select this checkbox to allow developers to override the default Enable Lower Case Table Names value set above, when they are creating a new service instance. For more information, see Optional Parameters for the MySQL for PCF Service Instances.
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 Backups

Follow the steps below to configure automated backups:

Note: As of v2.2.0, the MySQL for PCF tile no longer includes the option to disable automated backups. You must configure automated backups.

  1. Click Backups.


  2. Consult About Automated Backups in Backing Up and Restoring to learn about how automated backups work.

  3. Select a Backup configuration and perform the steps in the appropriate section of Backing Up and Restoring:

    • Option 1: Back up with SCP—MySQL for PCF runs an SCP command that secure-copies backups to a VM or physical machine operating outside of PCF. SCP stands for secure copy protocol, and offers a way to securely transfer files between two hosts. The operator provisions the backup machine separately from their PCF installation. This is the fastest option.
    • Option 2: Back up to Ceph or S3—MySQL for PCF runs an Amazon S3 client that saves backups to an S3 bucket, a Ceph storage cluster, or another S3-compatible endpoint certified by Pivotal.
    • Option 3: Back up to GCS—MySQL for PCF runs an GCS SDK that saves backups to an Google Cloud Storage bucket.
    • Option 4: Back up to Azure Storage—MySQL for PCF runs an Azure SDK that saves backups to an Azure storage account.

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