A BOSH release of a MySQL database-as-a-service for Cloud Foundry using MariaDB Galera Cluster and a v2 Service Broker.

Component	Description	Build Status
CF MySQL Broker	Advertises the MySQL service and plans. Creates and deletes MySQL databases and credentials (bindings) at the request of Cloud Foundry's Cloud Controller.
MySQL Server	MariaDB 10.0.12; database instances are hosted on the servers.	n/a

For release notes and other documentation, see the release wiki.

There are multiple branches, representing code in different stages of development.

• master is the latest and greatest (untested) code - the bleeding-edge. Use at your own risk.
• release-candidate has passed both automated and manual acceptance. This branch should be suitable for most use cases.
• Final releases are tagged with the version number e.g. v11.

• A deployment of BOSH
• A deployment of Cloud Foundry, final release 169 or greater
• Instructions for installing BOSH and Cloud Foundry can be found at http://docs.cloudfoundry.org/.

1. Upload Stemcell
2. Upload Release
3. Create Manifest and Deploy
4. Register the Service Broker

After installation, the MySQL service will be visible in the Services Marketplace; using the CLI run cf marketplace.

The latest final release, v11, expects the Ubuntu Trusty (14.04) go_agent stemcell version 2682 by default. This release may not work with newer stemcells it may have dependencies on libraries which have been removed from newer stemcells. Stemcells can be downloaded from http://boshartifacts.cfapps.io/file_collections?type=stemcells; choose the ubuntu trusty go_agent stemcell 2682 for your infrastructure (vsphere esxi or aws xen).

Master expects Ubuntu Trusty go_agent by default, but the version changes frequently.

Stemcells can be downloaded from http://boshartifacts.cfapps.io/file_collections?type=stemcells.

You can use a pre-built final release or build a release from HEAD. Final releases contain pre-compiled packages, making deployment much faster. However, these are created manually and infrequently. To be sure you're deploying the latest code, build a release yourself.

1. Check out the tag for the desired version. This is necessary for generating a manifest that matches the code you're deploying.

$ cd ~/workspace/cf-mysql-release
$ ./update
$ git checkout v11
$ git submodule update --recursive

1. Run the upload command, referencing one of the config files in the releases directory.

$ bosh upload release releases/cf-mysql-11.yml

1. Build a BOSH development release from HEAD

$ cd ~/workspace/cf-mysql-release
$ ./update
$ bosh create release

When prompted to name the release, call it cf-mysql.

1. Upload the release to your bosh environment:

$ bosh upload release

1. Generate the manifest using a bosh-lite specific script and a stub provided for you, bosh-lite/cf-mysql-stub-spiff.yml.

   $ ./bosh-lite/make_manifest_spiff_mysql
   

   The resulting file, bosh-lite/manifests/cf-mysql-manifest.yml is your deployment manifest. To modify the deployment configuration, you can edit the stub and regenerate the manifest or edit the manifest directly.

2. The make_manifest_spiff_mysql script will set the deployment to bosh-lite/manifests/cf-mysql-manifest.yml for you, so to deploy you only need to run:

$ bosh deploy

1. Create a stub file called cf-mysql-vsphere-stub.yml by copying and modifying the sample_vsphere_stub.yml in templates/sample_stubs.

2. Generate the manifest:

$ ./generate_deployment_manifest vsphere cf-mysql-vsphere-stub.yml > cf-mysql-vsphere.yml

The resulting file, cf-mysql-vsphere.yml is your deployment manifest. To modify the deployment configuration, you can edit the stub and regenerate the manifest or edit the manifest directly.

3. To deploy:

$ bosh deployment cf-mysql-vsphere.yml && bosh deploy

1. Create a stub file called cf-mysql-aws-stub.yml by copying and modifying the sample_aws_stub.yml in templates/sample_stubs.

2. Generate the manifest:

$ ./generate_deployment_manifest aws cf-mysql-aws-stub.yml > cf-mysql-aws.yml

The resulting file, cf-mysql-aws.yml is your deployment manifest. To modify the deployment configuration, you can edit the stub and regenerate the manifest or edit the manifest directly.

1. To deploy:

$ bosh deployment cf-mysql-aws.yml && bosh deploy

Manifest properties are described in the spec file for each job; see jobs.

You can find your director_uuid by running bosh status.

The MariaDB cluster nodes are configured by default with 100GB of persistent disk. This can be configured in your stub or manifest using jobs.mysql.persistent_disk, however your deployment will fail if this is less than 3GB; we recommend allocating 10GB at a minimum.

BOSH errands were introduced in version 2366 of the BOSH CLI, BOSH Director, and stemcells.

$ bosh run errand broker-registrar

Note: the broker-registrar errand will fail if the broker has already been registered, and the broker name does not match the manifest property jobs.broker-registrar.properties.broker.name. Use the cf rename-service-broker CLI command to change the broker name to match the manifest property then this errand will succeed.

1. First register the broker using the cf CLI. You must be logged in as an admin.

   $ cf create-service-broker p-mysql BROKER_USERNAME BROKER_PASSWORD URL
   

   BROKER_USERNAME and BROKER_PASSWORD are the credentials Cloud Foundry will use to authenticate when making API calls to the service broker. Use the values for manifest properties jobs.cf-mysql-broker.properties.auth_username and jobs.cf-mysql-broker.properties.auth_password.

   URL specifies where the Cloud Controller will access the MySQL broker. Use the value of the manifest property jobs.cf-mysql-broker.properties.external_host.

   For more information, see Managing Service Brokers.

2. Then make the service plan public.

To run the MySQL Release Acceptance tests, you will need:

• a running CF instance
• credentials for a CF Admin user
• a deployed MySQL Release with the broker registered and the plan made public
• an environment variable $CONFIG which points to a .json file that contains the application domain

BOSH errands were introduced in version 2366 of the BOSH CLI, BOSH Director, and stemcells.

The following properties must be included in the manifest (most will be there by default):

• cf.api_url:
• cf.admin_username:
• cf.admin_password:
• cf.apps_domain:
• cf.skip_ssl_validation:
• broker.host:
• service.name:
• service.plans:

The service.plans array must include the following properties for each plan:

• plan_name:
• max_storage_mb:

To customize the following values add them to the manifest:

• mysql.max_user_connections: (default: 40)

To run the errand:

$ bosh run errand acceptance-tests

1. Install Go by following the directions found here

2. cd into cf-mysql-release/test/acceptance-tests/

3. Update cf-mysql-release/test/acceptance-tests/integration_config.json

   The following commands provide a shortcut to configuring integration_config.json with values for a bosh-lite deployment. Copy and paste this into your terminal, then open the resulting integration_config.json in an editor to replace values as appropriate for your environment.

cat > integration_config.json <<EOF
{
  "api_url": "http://api.10.244.0.34.xip.io",
  "apps_domain": "10.244.0.34.xip.io",
  "admin_user": "admin",
  "admin_password": "admin",
  "broker_host": "p-mysql.10.244.0.34.xip.io",
  "service_name": "p-mysql",
  "plans" : [
    {
      "plan_name": "100mb-dev",
      "max_user_connections": 20,
      "max_storage_mb": 10
    },
    {
      "plan_name": "1gb-dev",
      "max_user_connections": 40,
      "max_storage_mb": 20
    }
  ],
  "skip_ssl_validation": true,
  "timeout_scale": 1.0
}
EOF
export CONFIG=$PWD/integration_config.json

When skip_ssl_validation: true, commands run by the tests will accept self-signed certificates from Cloud Foundry. This option requires v6.0.2 or newer of the CLI.

All timeouts in the test suite can be scaled proportionally by changing the timeout_scale factor.

4. Run the tests

$ ./bin/test

Since cf-release v175, applications by default cannot to connect to IP addresses on the private network. This prevents applications from connecting to the MySQL service. To enable access to the service, create a new security group for the IP configured in your manifest for the property jobs.mysql_broker.mysql_node.host.

1. Add the rule to a file in the following json format; multiple rules are supported.

[
    {
      "destination": "10.244.1.18",
      "protocol": "all"
    }
]

• Create a security group from the rule file.

  $ cf create-security-group p-mysql rule.json
  

• Enable the rule for all apps

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

Changes are only applied to new application containers; in order for an existing app to receive security group changes it must be restarted.

The following commands are destructive and are intended to be run in conjuction with deleting your BOSH deployment.

BOSH errands were introduced in version 2366 of the BOSH CLI, BOSH Director, and stemcells.

This errand runs the two commands listed in the manual section below from a BOSH-deployed VM. This errand should be run before deleting your BOSH deployment. If you have already deleted your deployment follow the manual instructions below.

$ bosh run errand broker-deregistrar

Run the following:

$ cf purge-service-offering p-mysql
$ cf delete-service-broker p-mysql

The service broker implements a user-facing UI which users can access via Single Sign-On (SSO) once authenticated with Cloud Foundry. SSO was implemented in build 169 of cf-release, so CF 169 is a minimum requirement for the SSO feature. If you encounter an error when you register the service broker, try removing the following lines from your manifest and redeploy.

dashboard_client:
  id: p-mysql
  secret: yoursecret

Services wanting to implement such a UI and integrate with the Cloud Foundry Web UI should try something similar. Instructions to implement this feature can be found here.

The broker displays usage information on a per instance basis.

The dashboard URL defaults to using the https scheme. To override this, you can change properties.ssl_enabled to false in the cf-mysql-broker job.

Keep in mind that changing the ssl_enabled setting for an existing broker will not update previously advertised dashboard URLs. Visiting the old URL may fail if you are using the SSO integration, because the OAuth2 client registered with UAA will expect users to both come from and return to a URI using the scheme implied by the ssl_enabled setting.

1. Update the broker catalog with the dashboard client properties
2. Implement oauth workflow with the omniauth-uaa-oauth2 gem
3. Use the cf-uaa-lib gem to get a valid access token and request permissions on the instance
4. Before showing the user the dashboard, the broker checks to see if the user is logged-in and has permissions to view the usage details of the instance.

Traffic to the MySQL cluster is routed through an HAProxy node. The intention is to use this proxy for fail-over and load balancing traffic.

The HAProxy node runs a thin web server to display traffic stats. This dashboard can be reached at haproxy-1.p-mysql.<system domain>. Log in as user admin with the password configured by the haproxy_stats_password property in the deployment manifest.

###MyISAM Tables This release does not support replication of MyISAM Tables. However, the service does not prevent the creation of MyISAM tables. When MyISAM tables are created, the tables will be created on every node (DDL statements are replicated), but data written to the node won't be replicated. If the persistent disk is lost, data will be lost. To change a table from MyISAM to InnoDB, please follow this guide.

###Max User Connections When updating the max_user_connections property for an existing plan, the connections currently open will not be affected (ie if you have decreased from 20 to 40, users with 40 open connections will keep them open). To force the changes upon users with open connections, an operator can restart the haproxy job as this will cause the connections to reconnect and stay within the limit. Otherwise, if any connection above the limit is reset it won't be able to reconnect, so the number of connections will eventually converge on the new limit.