Installing and Configuring MySQL for Pivotal Platform

Page last updated:

This topic provides instructions to Pivotal Platform operators about how to install, configure, and deploy the MySQL for Pivotal Platform tile. The MySQL for Pivotal Platform service enables Pivotal Platform developers to create and use MySQL service instances on demand.

Role-Based Access in Ops Manager

Ops Manager admins can use Role-Based Access Control (RBAC) to manage which operators can make deployment changes, view credentials, and manage user roles in Ops Manager. Your role permissions might not permit you to do every procedure in this topic.

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

Prerequisites

Before you install the MySQL for Pivotal Platform tile, you must:

Create an App Security Group for MySQL for Pivotal Platform

To enable apps running on Pivotal Platform to communicate with the MySQL service network, you must create an App Security Group (ASG). The ASG enables smoke tests to run when you first install the MySQL for Pivotal Platform service and apps to access the service after it is installed.

To create an ASG for MySQL for Pivotal Platform:

  1. Navigate to Ops Manager Installation Dashboard > BOSH Director.

  2. Click Create Networks.

  3. Click your services network and record the CIDR. Reserved IP Ranges

  4. Create a JSON file named mysql-asg.json using the template below:

    [
      {
        "protocol": "tcp",
        "destination": "CIDR",
        "ports": "3306"
      }
    ]
    

    Where CIDR is the CIDR that you recorded in the above step.

  5. Create an ASG by running:

    $ cf create-security-group p.mysql ./mysql-asg.json
    
  6. Bind the ASG to all running apps by running:

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

For more information about ASGs, see App Security Groups.

Enable the BOSH Resurrector

Pivotal recommends enabling the BOSH Resurrector when installing MySQL for Pivotal Platform. The BOSH Resurrector increases the availability of MySQL for Pivotal Platform by restarting and resuming MySQL service.

The BOSH Resurrector does the following:

  • Reacts to hardware failures 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

For more information about the BOSH Resurrector, see BOSH Resurrector.

To enable the BOSH Resurrector:

  1. Navigate to Ops Manager Installation Dashboard > BOSH Director.

  2. Click Director Config.

  3. Select the Enable VM Resurrector Plugin checkbox.

  4. Click Save.

Download and Import the Tile

To download and import the MySQL for Pivotal Platform 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 Import a Product, click + next to the version number of MySQL for Pivotal Platform. This adds the tile to your staging area.

  4. Click the newly-added MySQL for Pivotal Platform tile to open its configuration panes.

    AZ and Network Assignments pane

Configure the Tile

To configure the MySQL for Pivotal Platform tile, do the procedures below.

Configure AZs and Networks

To configure an availability zone (AZ) to run the service broker and networks for the broker and MySQL service instances:

  1. Click Assign AZs and Networks.

  2. Configure the fields as follows:

    FieldInstructions
    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) component VMs.
    This network is represented by the Default Network in diagram in Default Network and Service Network.
    Service Network Select the subnet for the on-demand service instances. This network is represented by the Service Network in diagram in Default Network and Service Network.

    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.

    Warning: You cannot change the regions or networks after you Apply Changes.

  3. Click Save.

Configure Service Plans

Note: Multi‑Site Replication plans are currently in beta. If you discover any bugs, contact Support.

MySQL for Pivotal Platform enables you to configure as many as nine service plans. Each service plan has a corresponding section in the tile configuration, such as Plan 1, Plan 2, and so on.

By default, plans 1 through 3 are active and plans 4 through 9 are inactive. The procedures below describe how to change these defaults.

Warning: You must not set Plan 1 to Inactive. If you deactivate Plan 1, your installation fails when you apply changes.

Warning: You must configure one of your service plans as a single node plan. This is because you can only restore to a single node plan. You cannot restore directly to a leader-follower plan. Ensure that the persistent disk in the single node plan is at least as large as the persistent disk of your largest leader-follower plan. For more information about restoring services instances, see Restore the Service Instance.

For each plan that you want to use in your Pivotal Platform deployment:

  1. Click the section for the plan. For example, Plan 1.

  2. Select the plan for your desired topology.

    image shows four plan types and inactive.

    The tabs below expand to show an example screenshot of each plan:

  3. Configure the fields as follows:

    FieldDescription
    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. If the plan quota field is blank, the plan quota is set to the global quota by default. If you have selected the highly available cluster plan, the Plan Quota maximum is 5.
    For information about the global quota, see Setting Limits for On-Demand Service Instances.
    Paid Plan Check this box to indicate that this service plan is paid.
    MySQL VM Type Select a VM type for the MySQL nodes.
    Jumpbox VM Type Only for highly available cluster plans. Select a VM type for the MySQL jumpbox node. This VM is also called mysql-monitor.
    MySQL Persistent Disk Select a disk size. This disk stores the MySQL data.
    For sizing recommendations, see Persistent Disk Usage.
    Jumpbox Persistent Disk Only for highly available cluster plans. Select a disk size. This disk stores backups.
    For sizing recommendations, see Persistent Disk Usage.
    MySQL Availability Zone(s) BOSH deploys your service instances to the selected AZs. If more than one AZ is selected, BOSH randomizes which AZ to place each VM.
    For more information, see About Availability Zones.

  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.

If you need to manually migrate the data from one AZ to another, see About Data Migration in MySQL for Pivotal Platform.

(Optional) Deactivate Service Plan

To deactivate a service plan:

  1. If the service plan has existing service instances:
    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 Pivotal Platform 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 Pivotal Platform tile configuration.
  2. Click the section for the plan. For example, Plan 2.
  3. Click Inactive.
  4. Click Save.

Configure Global Settings

To configure global settings for all service instances:

  1. Click Settings.

    global-settings.

  2. Configure the fields as follows:

    FieldInstructions
    Provide public IP addresses to all Service VMs Select this checkbox if either of the following apply:
    • Your service instances need an external backup, blobstore, or syslog storage
    • 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.
    Email address Enter an email address to send MySQL monitoring notifications to.

  3. Click Save.

Configure MySQL

To set MySQL defaults and enable developers to customize their instances:

  1. Click Mysql Configuration

    mysql-config.

  2. Configure the fields as follows:

    FieldInstructions
    Enable Lower Case Table Names Select this checkbox to store all table names in lowercase. This sets the MySQL server system variable lower_case_table_names to 1 on all MySQL for Pivotal Platform instances by default.
    To permit developers to override this default, see the checkbox below.
    For more information about lower_case_table_names , see the MySQL documentation.

    Warning: Before you enable this feature, ensure all tables have lowercase names. Tables with uppercase names are inaccessible after enabling lowercase table names.

    Allow Developers To Override Lower Case Table Names Select this checkbox to permit developers to override the configured default Enable Lower Case Table Names value. For more information, see Optional Parameters for the MySQL for Pivotal Platform Service Instances.
    Enable Local Infile Select this checkbox to enable data downloading from the local file system of the client. Pivotal discourages selecting this checkbox. Before you enable local in-file, review the security issues associated with LOAD DATA LOCAL. See the MySQL documentation.
    Wait Timeout Enter the amount of time in seconds that MySQL waits to close inactive connections. For more information about wait_timeout, see the MySQL documentation.

Configure Backups

To configure automated backups:

Note: You must configure automated backups. Automated backups can no longer be disabled.

  1. Click Backups.

    The radio buttons for backup configuration are
    Ceph or Amazon S3, SCP, GCS, and Azure.

  2. To learn how automated backups work, see About Automated Backups.

  3. Select a Backup configuration and do the procedure for your storage solution in Backing Up and Restoring:

    • Ceph or Amazon S3: MySQL for Pivotal Platform runs an Amazon S3 client that saves backups to an S3 bucket, a Ceph storage cluster, or another S3-compatible endpoint certified by VMware.
      For instructions about using Ceph or Amazon S3 for backups, see Back up to Ceph or S3.

    • SCP: MySQL for Pivotal Platform runs an Secure Copy Protocol (SCP) command that secure-copies backups to a VM or physical machine operating outside of Pivotal Platform. This is the fastest option.
      SCP enables you to securely transfer files between two hosts. You can provision the backup machine separately from your installation.
      For instructions about using SCP for backups, see Back up with SCP.

    • GSC: MySQL for Pivotal Platform runs a Google Cloud Storage (GCS) SDK that saves backups to an GCS bucket.
      For instructions about using GCS for backups, see Back up to GCS.

    • Azure: MySQL for Pivotal Platform runs an Azure SDK that saves backups to an Azure storage account.
      For instructions about using Azure for backups, see Back up to Azure Storage.

Configure Security

To configure the security settings for the MySQL service, do one or both:

security

Configure TLS

To enable TLS for the MySQL service:

  1. Do the procedures in Preparing for TLS.

  2. Click Security.

  3. Under TLS Options, select one of the following:

    • Optional: This enables developers to configure their MySQL service VMs to use TLS.
    • Required: This enables developers to configure their MySQL service VMs to use TLS and requires all MySQL service VMs to only accept secure connections.

      Warning: Selecting Required breaks any apps that are not currently connecting over TLS.

  4. Click Save.

  5. After deploying the tile, notify your developers that they must enable TLS for their service instances and activate TLS for their apps. See Using TLS.

Configure Secure Service Instance Credentials

You can store your service instance credentials in runtime CredHub instead of the Cloud Controller database (CCDB). For more information about runtime CredHub, see CredHub.

To store your service instance credentials in runtime CredHub:

  1. Ensure that you have configured the PAS tile to support securing service instance credentials in runtime CredHub. For instructions, see Step 1: Configure the PAS Tile.
  2. Click Security.
  3. Select the Enable Secure Service Instance Credentials checkbox.
  4. Click Save.
  5. After deploying the tile, notify the developers that they must unbind and rebind any existing service instances bindings if they want to use secure service instance credentials. Developers must:

    1. Unbind the service instance from the app by running:

      cf unbind-service APP SERVICE-INSTANCE
      
    2. Rebind the service instance to the app by running:

      cf bind-service PP SERVICE-INSTANCE
      
    3. Restart the app to apply the new binding by running:

      cf restart APP
      
    4. Verify that the binding includes CredHub pointers in the VCAP_SERVICES environment variable by running:

      cf env APP
      

      For example:

      $ cf env my-app
      Getting env variables for app my-app in org system / space example as admin...
      OK
      System-Provided:
      {
       "VCAP_SERVICES": {
        "p.mysql": [
         {
          "credentials": {
           "credhub-ref": "/c/548966e5-e333-4d65-8773-7b4e3bb6ca97/4a246b0b-83bb-46d0-b8ac-35a93374ae67/caf6e32e-7361-4869-9a57-54ab8ae67b3f/credentials"
          },
      [...]
      

Warning: If a developer rebinds an app to the MySQL for Pivotal Platform service after unbinding, they must also rebind any existing custom schemas to the app. When you rebind an app, stored code, programs, and triggers break. For more information about binding custom schemas, see Use Custom Schemas.

Configure Monitoring

To enable monitoring and logging in the MySQL service:

  1. Click Monitoring.

    The metrics polling interval defaults to 30
    and the min is 10.

  2. Configure the fields as follows:

    FieldInstructions
    Metrics Polling Interval Enter the amount of time in seconds for 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 collect user statistics. You can use these statistics to better understand server activity and identify load sources. For more information about user statistics, see the MySQl 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

    Note: MySQL audit logs are not forwarded to the syslog server because they can contain personally identifying information (PII) and secrets.
    You can use the download-logs script to retrieve the logs, which each MySQL cluster node VM stores in /var/vcap/store/mysql_audit_logs/.

    Enable Read Only Admin User Select this checkbox to create a read-only admin user, named roadmin, on each service instance. This user can be used for auditing and monitoring without risking mutating or changing any data. This is because roadmin cannot make changes to data.

    For instructions about retrieving the credentials for roadmin, see Retrieve Admin and Read-Only Admin Credentials for a Service Instance. The read-only admin user is always named roadmin, however, the password varies by service instance.

  3. Click Save.

Configure System Logging

To enable RFC 5424 system logging for the MySQL broker and service instance VMs:

  1. Click Syslog.

  2. Click Yes.

    Fields for configuring syslog.

  3. Configure the fields as follows:

    FieldInstructions
    Address Enter the IP address or hostname of the syslog server for sending logs. For example: logmanager.example.com.
    Port Enter the port of the syslog server for sending logs. For example: 29279.
    Transport Protocol Select the protocol you want to use to send 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 format.
    • The name of the remote peer. For example: *.example.com.
    SSL Certificate Enter the SSL certificates for the syslog server. This ensures the logs are transported securely.

    Note: If your syslog server is external to Pivotal Platform, you might need to select Provide public IP addresses to all Service VMs on the Settings page. See Configure Global Settings above.

  4. Click Save.

Configure Service Instance Upgrades

This section configures the upgrade-all-service-instances errand. MySQL for Pivotal Platform uses this errand to upgrade service instances.

To configure service instance upgrades.

  1. Click Service Instance Upgrades.

    Screenshot of service instance upgrades section in the MySQL tile in Ops Manager. Includes a header called 'Configuration for the upgrade-all-service-instances errand and several fields, described below.

  2. Configure the fields as follows:

    FieldInstructions
    Number of simultaneous upgrades Enter the maximum number of service instances that can upgrade at the same time. The minimum value is 0 and the maximum is 1 less than the number of BOSH workers. Increasing this value reduces the runtime of service instance upgrades.

    Note: To determine the number of BOSH workers, navigate to BOSH Director > Director Config and locate the value of Director Workers.

    Number of upgrade canary instances Enter the number of service instances to upgrade first before upgrading the rest of the instances. Increasing this value enables service instance upgrades to fail faster.
    BOSH Upgrade Timeout Enter the amount of time in seconds to wait for BOSH to respond before timing out when upgrading service instances. Increasing this value enables service instance upgrades to fail faster.
    Please type ‘X’ to acknowledge that you have run… If this is a fresh installation, enter X. Do not be concerned with the label instructions.

  3. Click Save.

(Optional) Review Errands

Errands are scripts that run at specfic times to do various tasks. MySQL for Pivotal Platform can run errands to manage the broker and service instances. You do not need to change the default configurations for errands.

Warning: The Delete All Service Instances and Deregister Broker errand does necessary cleanup tasks when you delete the MySQL for Pivotal Platform tile or re-define plans. Setting this errand to Off can cause problems when attempting to reinstall the tile or re-define plans. Pivotal recommends that you do not set this errand to Off.

MySQL for Pivotal Platform uses the following types of errands:

  • Post-Deploy Errands: These errands run when you click Apply Changes.
  • Pre-Delete Errands: These errands run before you delete the MySQL for Pivotal Platform tile.

MySQL for Pivotal Platform also uses errands to configure leader-follower service instances. For more information about leader-follower errands, see Errands Used in Leader-Follower Failover.

You can use errands when troubleshooting the broker or service instances. For more information about using errands for troubleshooting, see Run Service Broker Errands to Manage Brokers and Instances.

To review errands:

  1. Click Errands.

    Errand list for MySQL for Pivotal Platform.

  2. Review the settings for the following erands:

    Errand Description
    Post-Deploy Errands
    Register On-demand MySQL Broker Registers a broker with the Cloud Controller and lists it in the Marketplace.
    Smoke Tests Validates basic MySQL operations.
    Validate no IP-based bindings in use before upgrade-all-service-instances Checks if service instances have app bindings or service keys using IP addresses or have a TLS certificate that is signed with an IP address. If either is true, the installation fails.
    Upgrade all On-demand MySQL Service Instances Upgrades existing instances of a service to its latest installed version.
    Pre-Delete Errands
    Delete All Service Instances and Deregister Broker Deletes all service instances and deregisters the broker.

Verify Stemcell Version and Apply All Changes

To verify your stemcell version and apply all changes:

  1. Click Stemcell Library. For more information about using the Stemcell Library, see Importing and Managing Stemcells.

  2. Verify and, if necessary, import a new stemcell version.

  3. Navigate to Ops Manager Dashboard > Review Pending Changes.

  4. Click Apply Changes.

For information about the Xenial stemcells that are compatible with MySQL for Pivotal Platform, see Release Notes or Pivotal Network.