Configuring MySQL for PCF

This topic explains how to configure the MySQL for PCF service.

To configure your MySQL service, click the MySQL for PCF tile in the Ops Manager Installation Dashboard, open each pane under the Settings tab, and review or change the configurable settings as described in the sections below.

Configure Settings

Assign AZs and Networks

Configure AZs and Networks

MySQL for PCF supports deployment to multiple availability zones (AZs).

To maximize uptime, deploy a load balancer in front of the SQL Proxy nodes. Please see the note in the proxy section below. When configuring this load balancer, increase the minimum idle timeout if possible, as many load balancers are tuned for short-lived connections unsuitable for long-running database queries. See the load balancer configuration instructions for details.

Service Plans

Configure Service Plans

Service plans offer developers different versions of the MySQL service. An example is tiered service plans with that offer a range of resource limits and pricing. See Service Plans for how to configure one or more service plans in the Service Plans pane.

Note: You cannot deploy MySQL for PCF without at least one service plan defined.


Configure Proxy

The proxy tier routes connections from apps to healthy MariaDB cluster nodes, even in the event of node failure.

  • In the Proxy IPs field, enter a list of IP addresses that should be assigned to the proxy instances. These IP addresses must be in the CIDR range configured in the Director tile and not be currently allocated to another VM. Look at the Status pages of other tiles to see what IP addresses are in use.

  • In the Binding Credentials Hostname field, enter the hostname or IP address that should be given to bound applications for connecting to databases managed by the service. This hostname or IP address should resolve to your load balancer and be considered long-lived. When this field is modified, applications must be rebound to receive updated credentials.

To enable their PCF apps to use a MySQL database, developers bind their apps to instances of the MySQL for PCF service. For more information, see Application Binding. By default, the MySQL service provides bound apps with the IP address of the first instance in the proxy tier, even with multiple proxy instances deployed. This makes the first proxy instance a single point of failure unless you deploy a load balancer.

Note: To eliminate the first proxy instance as a single point of failure, configure a load balancer to route client connections to all proxy IPs, and configure the MySQL service to give bound applications a hostname or IP address that resolves to the load balancer.

Proxy Count Cannot be Reduced

Once an operator deploys MySQL for PCF, they cannot reduce the number of proxy IPs or proxy instances, and cannot remove the configured IPs from the Proxy IPs field.

If the product is initially deployed without proxy IPs, adding IPs to the Proxy IPs field can only add additional proxy instances. Scaling down is unpredictably permitted, and the first proxy instance can never be assigned an operator-configured IP.

MySQL Server Configuration

Configure MySQL Server Configuration

You can change the following options to suit your environment. Since these changes affect all service instances, exercise caution when making changes.

For more information about how MySQL for PCF is configured, see the architecture documentation.

  • Enable InnoDB Strict Mode

    Default: Enabled

    See the architecture documentation for information on this setting.

  • Allow Clients to Send Files

    Default: Enabled

    See the architecture documentation for information on this setting.

  • Allow Command History

    Default: Enabled

    See the architecture documentation for information on this setting.

  • Disable Reverse DNS lookups

    Default: Enabled

    See the architecture documentation for information on this setting.

  • Allow Remote Admin Access

    Default: Disabled

    See the architecture documentation for information on how to configure this setting. Enabling this checkbox will affect how you back up and restore your MySQL data manually. See Perform Manual Backup.

  • Read-Only User Password

    Default: Disabled

    Activates a special user, roadmin, a read-only administrator. Supply a special password for administrators who require the ability to view all of the data maintained by the MySQL for PCF installation. Leaving this field blank de-activates the read-only user.

  • MySQL Start Timeout

    Default: 60 seconds

    The maximum amount of time necessary for the MySQL process to start, in seconds. When restarting the MySQL server processes, there are conditions under which the process takes longer than expected to appear as running. This can cause parts of the system automation to assume that the process failed to start properly, and appear as failing in Ops Manager and BOSH output. Depending on the data stored by the database, and the time represented in logs, you may need to increase this above the default of 60 seconds.

  • New Cluster Probe Timeout

    Default: 10 seconds

    There is special logic to detect if a starting node is the first node of a new installation, or if it is part of an existing cluster. Part of this logic is to probe if the other nodes of the cluster have already been deployed. In cases of high latency, this timeout period may be too long. When these probes take too long to respond, it’s possible that the MySQL Start Timeout might fail the deployment. To account for this, either increase the MySQL Start Timeout or lower the New Cluster Probe Timeout so that a new node doesn’t spend too long performing this test.

  • Allow Table Locks

    Default: Enabled

    When enabled, clients can acquire table locks on the node processing the transaction. This is enabled for backwards compatibility. Pivotal recommends disabling this for all new deployments, because table locks are not replicated across cluster nodes.

  • Enable Large Indices

    Default: Enabled

    See the architecture documentation for information on this setting.

  • Enable replication debug logging

    Default: Enabled

    Directs MySQL for PCF service to log replication error events. Disable this option only if error logging is overloading your logging systems’ capacity.

  • Server Activity Logging

    The MySQL service includes the MariaDB Audit plugin to log server activity. You can disable this plugin, or configure which events are recorded. The log can be found at /var/vcap/store/mysql_audit_logs/mysql_server_audit.log on each VM. When server logging is enabled, the file is rotated every 100 megabytes, and the 30 most recent files are retained.

    • Event types

      Default: connect,query

    • Exclude users

      Supply a comma-separated list of additional database users that should not be included in the activity log. Note that these users are always excluded form audit logging:

      • repcanary
      • mysql-metrics
      • cluster-health-logger
      • galera-healthcheck
      • quota-enforcer

    Note: Due to the sensitive nature of these logs, they are not transmitted to the syslog server.

  • Maximum Temporary Table Memory Size

    Default: 33554432 bytes (32 MB)

    The maximum size (in bytes) of internal in-memory temporary tables. See the architecture documentation for information on this setting.

  • Table Open Cache Size

    Default: 2000 tables

    The number of table handles to keep open. See the architecture documentation for information on this setting.

  • Table Definition Cache Size

    Default: 8192 tables

    Set this to a number relative to the number of tables the server will manage. See the architecture documentation for information on this setting.

  • InnoDB Buffer Pool Size

    Default: 50 percent of instance RAM

    Choose Percent to configure the percent of system RAM allocated to the memory buffer used by InnoDB. Choose Bytes to configure the size in bytes, instead. See the architecture documentation for information on this setting.

  • InnoDB Log Flush Timing

    Default: 2

    InnoDB log buffer is written to the log file at each commit; the log file is flushed to disk once per second. Set to 1 when running in non-HA mode. This default improves cluster performance. For more information, see the InnoDB Flush Method. The MariaDB default, 1, is to flush the log to disk at every commit, which is required for ACID compliance.

    Note: The most recent second’s transactions is flushed to disk only if all nodes crash simultaneously. All other cluster lifecycle events do not affect data retention. To eliminate this risk, set Log Flush Timing to 1, which improves the performance of heavily active clusters.

  • Maximum Server Connections

    Default: 1500

    The maximum number of simultaneous client connections for the entire deployment, regardless of service instance.

  • Binary Log Retention Time

    Default: 7 days

    Time in days to store binary logs before purging. These logs are not used by MySQL for PCF. They are stored only for diagnostic purposes.


Configure Backups

The Backups pane configures automated database backups. To configure backups:

  1. Choose Disable Backups or Enable Backups. If you enable automated backups, follow the instructions in the Backups topic to configure the backups.

  2. Ensure that the Instances setting for Backup Prepare Node in the Resource Config pane matches your automated backups enable or disable choice:

    • Disable Backups selected: set Backup Prepare Node > Instances to 0.
    • Enable Backups selected: set Backup Prepare Node > Instances to 1.

Advanced Options

Configure Advanced Options

The Advanced Options pane lets you configure the following features:

  • The Replication Canary: For more information, see Monitoring the MySQL Service.

  • The Interruptor: For more information, see Using the Interruptor.

  • Quota Enforcer Frequency

    By default, the Quota Enforcer polls for violators and reformers every 30 seconds. This setting, in seconds, changes how long the quota enforcer pauses between checks. Quota Enforcer polling draws minimal resources, but if you want to reduce this load, increase this interval. Be aware, however, that a long Quota Enforcer interval may cause apps to write more data than their pre-determined limit allows.

  • Cluster Name

    Default: cf-mariadb-galera-cluster

    Set a unique name for the cluster. When examining the state or making manual changes to a Pivotal MySQL cluster, it’s useful to check the name of the cluster. You can see the name of the cluster by running the query, select @@wsrep_cluster_name.

    Important: may only be set during initial deployment. Changing this field after the cluster has been deployed causes Apply Changes to fail.

    Please contact Pivotal Support for help in changing the name of a cluster that has already been deployed.


Two post-deploy errands run by default: the broker registrar and the smoke test. The broker registrar errand registers the broker with the Cloud Controller and makes the service plan public. The smoke test errand runs basic tests to validate that service instances can be created and deleted, and that apps pushed to Elastic Runtime can be bound and write to MySQL service instances. You can turn both errands on or off in the Errands pane under the Settings tab.

Note: Disabling errands can result in unexpected side effects. Do not reconfigure errands in your deployment without instruction from Pivotal Support. Additionally, the Errands pane also shows a broker-deregistrar pre-delete errand. Ops Manager runs the broker-deregistrar errand to clean up when it uninstalls a tile. Running bosh run errand broker-registrar under any other circumstances deletes user data. Do not run this errand unless instructed to do so by Pivotal Support.

Resource Config

Configure Resources

This pane configures the number, persistent disk capacity, and VM type for all component VMs that run the MySQL for PCF service.

Make sure to provision ample resources for your MySQL Server nodes. MariaDB servers require sufficient CPU, RAM, and IOPS to promptly respond to client requests. Also note that the MySQL for PCF reserves about 2-3 GB of each instance’s persistent disk for service operations use. The rest of the capacity is available for the databases. The MariaDB cluster nodes are configured by default with 100GB of persistent disk. The deployment fails if this is less than 3GB; we recommend allocating 10GB minimum.

If Enable Backups is selected in the Backups pane, set Backup Prepare Node > Instances to 1.

Switch Between Single and HA Topologies

To switch your MySQL for PCF service between single-node and high availability (HA) topologies, navigate to the Resource Config pane and change the Instances settings for the MySQL Server, Proxy, and Service Broker components as shown in this table:

ResourceSingle-NodeHigh Availability (HA)
MySQL Server13
Service Broker11-2*

*Routine database operations do not require two service brokers.

Single and three-node clusters are the only supported topologies. Ops Manager allows you to set the number of MySQL Server instances to other values, but Pivotal recommends only one or three.

If you scale up to three MySQL nodes, Pivotal recommends spreading them across different Availability Zones to maximize cluster availability. An Availability Zone (AZ) is a network-distinct section of a region. For more information about Amazon AZs, see Amazon’s documentation.

When you change the instance counts for a MySQL service, a top-level property is updated with the new nodes’ IP addresses. As BOSH deploys, it updates the configuration and restart all of the MySQL nodes and the proxy nodes (to inform them of the new IP addresses as well). Restarting the nodes causes all connections to that node to be dropped while the node restarts.


This pane uploads the stemcell that you want the service components to run on. Find available stemcells at Pivotal Network.

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