MySQL for Pivotal Cloud Foundry
This is documentation for the MySQL for Pivotal Cloud Foundry (PCF) tile.
Current MySQL for PCF Details
- Version: v1.7.31
- Release Date: June 22, 2017
- Software component versions: MariaDB v10.1.18, Galera v25.3.17
- Compatible Ops Manager version(s): v1.6.x through v1.10.x
- Compatible Elastic Runtime version(s): v1.6.x through v1.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|
|v1.4.x and v1.5.x||v1.3.2||v1.4.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.30|
|v1.7.0 – v1.7.29*||Next v1.7.x release – v1.7.30|
(*) Note there is a known issue upgrading some releases of p-mysql v1.6 and v1.7 on Ops Manager v1.6.
For information about changes between versions of this product, see the Release Notes.
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:
|MySQL||1 node||3-node cluster|
|SQL Proxy||1 node||2 nodes|
|Service Broker||1 node||2 nodes|
|Multi-AZ Support||-||Yes *|
|Customizable VM Instances||Yes||Yes|
|Encrypted Communication||Yes ✝||Yes ✝|
|Encrypted Data at-rest||-||-|
(*) vSphere only, v1.7 and earlier
(✝) Requires IPSEC BOSH plug-in
- 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.
For information about issues in current releases of MySQL for PCF, see Known Issues.
Download the product file from Pivotal Network.
Navigate to the Ops Manager Installation Dashboard.
Click Import a Product to upload the product file to your Ops Manager installation.
Click Add next to the uploaded product description in the Available Products view to add this product to your staging area.
Click the newly added tile to review configurable Settings.
Click Apply Changes to deploy the service.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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