LATEST VERSION: 1.8 - CHANGELOG
MySQL for PCF v1.7

MySQL for Pivotal Cloud Foundry

This is documentation for the MySQL for Pivotal Cloud Foundry (PCF) tile.

Product Snapshot

Current MySQL for PCF Details

  • Version: v1.7.27
  • Release Date: 2017 April 3
  • Software component versions: MariaDB v10.1.18, Galera v25.3.17
  • Compatible Ops Manager Version(s): Versions 1.6.x through 1.10.x
  • Compatible Elastic Runtime Version(s): Versions 1.6.x through 1.10.x
  • vSphere support? Yes
  • AWS support? Yes
  • OpenStack support? Yes
  • IPSec support? Yes

Upgrading to the Latest Version

Consider the following compatibility information before upgrading MySQL for PCF.

For more information, see the full Product Compatibility Matrix.

Ops Manager Version Supported Upgrades from Imported MySQL Installation
From To
v1.3.x v1.2 v1.3
v1.3.2 v1.4.0
v1.4.x and v1.5.x v1.3.2 v1.4.0
v1.5.0
v1.4.x - v1.10.x v1.4.0 v1.5.0
v1.5.0 v1.6.1 – v1.6.26
v1.6.1 – v1.6.25* Next v1.6.x release – v1.6.26
v1.7.0 – v1.7.27
v1.7.0 – v1.7.26* Next v1.7.x release – v1.7.27

(*) Note there is a known issue upgrading some releases of p-mysql v1.6 and v1.7 on Ops Manager v1.6.

Release Notes

For information about changes between versions of this product, see the Release Notes.

Overview

The MySQL for PCF product delivers a fully managed, “Database as a Service” to Cloud Foundry users. When installed, the tile deploys and maintains a single or three-node cluster running a recent release of MariaDB, SQL Proxies for super-fast failover, and Service Brokers for Cloud Foundry integration. We work hard to ship the service configured with sane defaults, following the principle of least surprise for a general-use relational database service.

When installed, developers can attach a database to their applications in as little as two commands, cf create-service and cf bind-service. Connection credentials are automatically provided in the standard manner. Developers can select from a menu of service plans options, which are configured by the platform operator.

Two configurations are supported:

SingleHighly Available
MySQL1 node3-node cluster
SQL Proxy1 node2 nodes
Service Broker1 node2 nodes
High Availability-Yes
Multi-AZ Support-Yes *
Rolling Upgrades-Yes
Automated BackupsYesYes
Customizable PlansYesYes
Customizable VM InstancesYesYes
Plan MigrationsYesYes
Encrypted CommunicationYes ✝Yes ✝
Encrypted Data at-rest--
Long-lived Canaries--

(*) vSphere only, v1.7 and earlier
(✝) Requires IPSEC BOSH plug-in

Limitations

  • Single and three-node clusters are the only supported topologies. Ops Manager will allow the Operator to set the number of instances to other values, only one and three are advised. For more information, see Avoid an even number of nodes in the Cluster Scaling, Node Failure, and Quorum topic.
  • Although two Proxy instances are deployed by default, there is no automation to direct clients from one to the other. See the note in the Proxy section, as well as the entry in Known Issues.
  • Only the InnoDB storage engine is supported; it is the default storage engine for new tables. Attempted use of other storage engines (including MyISAM) may result in data loss.
  • All databases are managed by shared, multi-tenant server processes. Although data is securely isolated between tenants using unique credentials, application performance may be impacted by noisy neighbors.
  • Round-trip latency between database nodes must be less than five seconds; if the latency is higher than this, nodes will become partitioned. If more than half of cluster nodes are partitioned, the cluster will lose quorum and become unusable until manually bootstrapped.
  • See also the list of Known Limitations in MariaDB cluster.

Known Issues

For information about issues in current releases of MySQL for PCF, see Known Issues.

Installation

  1. Download the product file from Pivotal Network.

  2. Navigate to the Ops Manager Installation Dashboard.

    Available products

  3. Click Import a Product to upload the product file to your Ops Manager installation.

    Add product

  4. Click Add next to the uploaded product description in the Available Products view to add this product to your staging area.

    Config tile

  5. Click the newly added tile to review configurable Settings.

    Apply changes

  6. Click Apply Changes to deploy the service.

Settings

Service Plan

A single service plan enforces quotas of 100 megabytes of storage per database and 40 concurrent connections per user by default. Users of Operations Manager can configure these plan quotas. Changes to quotas will apply to all existing database instances as well as new instances. In calculating storage utilization, indexes are included along with raw tabular data.

The name of the plan is 100mb-dev by default and is automatically updated if the storage quota is modified. Thus, if the storage quota is changed to 1024 megabytes, the new default plan name will be 1024mb-dev.

Note: After changing a plan’s definition, all instances of the plan must be updated. For each plan, either the operator or the user must run cf update-service SERVICE_INSTANCE -p NEW_PLAN_NAME on the command line.

Further Note: This feature does not work properly in versions of MySQL for PCF v1.6.3 and earlier. See the entry in Known Issues for the recommended workaround.

Provisioning a service instance from this plan creates a MySQL database on a multi-tenant server, suitable for development workloads. Binding applications to the instance creates unique credentials for each application to access the database.

Proxy

The proxy tier is responsible for routing connections from applications to healthy MariaDB cluster nodes, even in the event of node failure.

Applications are provided with a hostname or IP address to reach a database managed by the service. For more information, see Application Binding. By default, the MySQL service will provide bound applications with the IP of the first instance in the proxy tier. Even if additional proxy instances are deployed, client connections will not be routed through them. This means the first proxy instance is a single point of failure.

In order to eliminate the first proxy instance as a single point of failure, operators must 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.

Configuring a Load Balancer

In older versions of the product, applications were given the IP of the single MySQL server in bind credentials. When upgrading to v1.5.0, existing applications will continue to function, but, to take advantage of high availability features, they must be rebound to receive either the IP of the first proxy instance or the IP/hostname of a load balancer.

In order to configure a load balancer with the IPs of the proxy tier before v1.5.0 is deployed and prevent applications from obtaining the IP of the first proxy instance, the product enables an operator to configure the IPs that will be assigned to proxy instances. The following instructions applies to the Proxy settings page for the MySQL product in Operation Manager.

  • 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.

Configure your load balancer to route connections for a hostname or IP to the proxy IPs. As proxy instances are not synchronized, we recommend configuring your load balancer to send all traffic to one proxy instance at a time until it fails, then failover to another proxy instance. For more information, see Known Issues.

Important: To configure your load balancer with a healthcheck or monitor, use TCP against port 1936. Unauthenticated healthchecks against port 3306 will cause the service to become unavailable, and will require manual intervention to fix.

Adding a Load Balancer after an Initial Deploy

If v1.5.0 is initially deployed without a load balancer and without proxy IPs configured, a load balancer can be setup later to remove the proxy as a single point of failure. However, there are several implications to consider:

  • Applications will have to be rebound to receive the hostname or IP that resolves to the load balancer. To rebind: unbind your application from the service instance, bind it again, then restage your application. For more information, see Managing Service Instances with the CLI. In order to avoid unnecessary rebinding, we recommend configuring a load balancer before deploying v1.5.0.
  • Instead of configuring the proxy IPs in Operations manager, use the IPs that were dynamically assigned by looking at the Status page. Configuration of proxy IPs after the product is deployed with dynamically assigned IPs is not well supported; see Known Issues.

Application Service Groups

You must create appropriate Application Security Groups (ASGs) for the MySQL for PCF in order for applications to have access to the service.

Note: Without ASGs, the service will not be usable.

See Creating Application Security Groups for MySQL for instructions.

Lifecycle Errands

Two lifecycle errands are 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 applications pushed to Elastic Runtime can be bound and write to MySQL service instances. Both errands can be turned on or off on the Lifecycle Errands page under the Settings tab.

Note: You might also notice a broker-deregistrar errand. Do not run this errand unless instructed to do so by Support. Broker-deregistrar is a part of the automation used by Ops Manager while deleting a tile. Running this errand under any other circumstance will delete user data.

Resource Config

Instance Capacity

An operator can configure how many database instances can be provisioned (instance capacity) by configuring the amount of persistent disk allocated to the MySQL server nodes. The broker will provision a requested database if there is sufficient unreserved persistent disk. This can be managed using the Persistent Disk field for the MySQL Server job in the Resource Config setting page in Operations Manager. Not all persistent disk will be available for instance capacity; about 2-3 GB is reserved for service operation. Adding nodes to the cluster increases durability, not capacity. Multiple backend clusters, to increase capacity or for isolation, are not yet supported.

In determining how much persistent disk to make available for databases, operators should also consider that MariaDB servers require sufficient CPU, RAM, and IOPS to promptly respond to client requests for all databases.

Provisioning and Binding via Cloud Foundry

As part of installation the product is automatically registered with Pivotal Cloud Foundry Elastic Runtime (see Lifecycle Errands). On successful installation, the MySQL service is available to application developers in the Services Marketplace, via the web-based Developer Console or cf marketplace. Developers can then provision instances of the service and bind them to their applications:

$ cf create-service p-mysql 100mb-dev mydb
$ cf bind-service myapp mydb
$ cf restart myapp

For more information about the use of services, see the Services Overview.

Example Application

To help application developers get started with MySQL for PCF, we have provided an example application, which can be downloaded here. Instructions can be found in the included README.

Service Instance Dashboard

Cloud Foundry users can access a dashboard for each MySQL service instances via SSO from Apps Manager. The dashboard displays current storage utilization of the database and the plan quota for storage. On the Space page in Apps Manager, users with the SpaceDeveloper role will find a Manage link next to the instance. Clicking this link will log users into the service dashboard via SSO.

Connect to your Database with the MySQL Plugin

You can use the Cloud Foundry Command Line Interface (cf CLI) MySQL plugin to connect to the MySQL databases used by your Cloud Foundry apps. The plugin supports the following actions:

  • Inspecting databases for debugging purposes.
  • Manually adjusting database schema or contents in development environments.
  • Dumping and restoring databases.

For more information, see the cf-mysql-plugin repository.

Proxy Dashboard

The service provides a dashboard where administrators can observe health and metrics for each instance in the proxy tier. Metrics include the number of client connections routed to each backend database cluster node.

The dashboard for each proxy instance can be found at: http://proxy-<job index>.p-mysql.<system-domain>. Job index starts at 0 so if you have two proxy instances deployed and your system-domain is example.com, dashboards would be accessible at http://proxy-0.p-mysql.example.com and http://proxy-1.p-mysql.example.com.

Basic auth credentials are required to access the dashboard. These can be found in the Credentials tab of the MySQL product in Operations Manager.

For more information about SwitchBoard, read the proxy documentation

See Also

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