Installing and Configuring VMware Tanzu SQL with MySQL for VMs

Note: In v2.9 and later, MySQL for VMware Tanzu is named VMware Tanzu SQL with MySQL for VMs.

Page last updated:

This topic provides instructions to operators about how to install, configure, and deploy the VMware Tanzu SQL with MySQL for VMs tile. The Tanzu SQL for VMs service enables 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 Tanzu SQL for VMs tile, you must:

Create an App Security Group for Tanzu SQL for VMs

To enable apps running on VMware Tanzu Application Service for VMs 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 Tanzu SQL for VMs service and apps to access the service after it is installed.

To create an ASG for Tanzu SQL for VMs:

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

  2. Click Create Networks.

  3. Click your services network and record the CIDR. In the 'Create Networks' pane, in the 'Networks' section the 'services' network is expanded. The 'Name', 'Google Network Name', and 'CIDR', fields are shown.

  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

VMware recommends enabling the BOSH Resurrector when installing Tanzu SQL for VMs. The BOSH Resurrector increases the availability of Tanzu SQL for VMs 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 Tanzu SQL for VMs tile:

  1. Download the product file from VMware Tanzu 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 Tanzu SQL for VMs. This adds the tile to your staging area.

  4. Click the newly-added Tanzu SQL for VMs tile to open its configuration panes.

    On the left side of the image, 'Assign AZs and Networks' is selected. The 'AZ and Network Assignments' configuration pane is shown. See the following section for a description of the fields in this pane.

Configure the Tile

To configure the Tanzu SQL for VMs 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 VMware Tanzu Application Service for VMs (TAS for VMs) 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, VMware 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

Tanzu SQL for VMs 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.

About Creating Plans for Restoring Multi-Node Service Instances

If you offer leader-follower or highly available (HA) cluster plans, then you must configure single-node or Multi‑Site Replication plans that can be used to restore a multi-node plan from backup.

If you offer service plans of type… Then configure a service plan of type…
leader-follower single node, with the persistent disk as large as the largest leader-follower plan offered.
HA cluster Multi‑Site Replication, with the persistent disk as large as the largest HA cluster plan offered.

For information about how multi-node service instances are restored, see Restore a Service Instance in Backing Up and Restoring VMware Tanzu SQL with MySQL for VMs.

Procedure for Configuring Service Plans

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

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

  2. Select the plan for your desired topology.

    The 'Plan' section is shown. The radio button for 'Inactive' is selected.

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

    The 'Plan' section is shown and the radio button next to 'Single Node' is selected.
    The 'Plan' section is shown and the radio button next to 'Leader Follower' is selected.
    The 'Plan' section is shown and the radio button next to 'HA cluster' is selected.
    The 'Plan' section is shown and the radio button next to 'Multi-Site Replication' is selected.
  3. Configure the fields as follows:

    Note: If you want to replicate data across multiple foundations or data centers, you must configure a Multi‑Site Replication plan in both foundations using the same configurations.

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

  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 Tanzu SQL for VMs.

(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 deployment has redeployed, use the cf CLI or Apps Manager to delete all existing service instances on the service plan.
    6. Return to the Tanzu SQL for VMs 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.

    The 'Settings' pane. See the following step for a detailed description of the configurable fields.

  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.

    The 'Mysql Configuration' pane. See the following step for a detailed description of the configurable fields.

  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 Tanzu SQL for VMs 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 Tanzu SQL for VMs Service Instances.
    Enable Local Infile Select this checkbox to enable data downloading from the local file system of the client. VMware 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 learn how backups work, see About Backups.

To configure backups:

Note: You must configure backups. You cannot disable this feature.

  1. Click Backups.

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

  2. Select a Backup configuration and follow the procedure for your storage solution in the Configuring Automated Backups topic:

    • Ceph or Amazon S3: Tanzu SQL for VMs 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: Tanzu SQL for VMs runs an Secure Copy Protocol (SCP) command that secure-copies backups to a VM or physical machine operating outside of your deployment. 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.

    • GCS: Tanzu SQL for VMs 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: Tanzu SQL for VMs 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:

The 'Security' pane. The following 'TLS Options' are listed: 'Not Configured', 'Optional - Developers may configure their service VMs to use TLS', and 'Required - All connections MUST use TLS. Any application that is not using TLS communcation with MySQL will break.'

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 TAS for VMs tile to support securing service instance credentials in runtime CredHub. For instructions, see Step 1: Configure the TAS for VMs 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 Tanzu SQL for VMs 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. See the following step for detailed descriptions of the configurable fields.

  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. VMware 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 your deployment, 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. Tanzu SQL for VMs uses this errand to upgrade service instances. For more information about the upgrade-all-service-instances errand, see upgrade-all-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. Tanzu SQL for VMs 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 Tanzu SQL for VMs tile or re-define plans. Setting this errand to Off can cause problems when attempting to reinstall the tile or re-define plans. VMware recommends that you do not set this errand to Off.

Tanzu SQL for VMs 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 Tanzu SQL for VMs tile.

Tanzu SQL for VMs also uses errands to configure leader-follower service instances. For more information about leader-follower errands, see Errands.

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 VMware Tanzu SQL with MySQL for VMs.

  2. Review the settings for the following errands:

    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.

    If you want developers to individually upgrade service instances, set this errand to OFF. For more information about individual service instance upgrades, see About Individual Service Instance Upgrades.
    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 Tanzu SQL for VMs, see Release Notes or VMware Tanzu Network.