Using VMware Tanzu SQL with MySQL for VMs for Multi-Site Replication

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 for developers using the VMware Tanzu SQL with MySQL for VMs for replication across multiple foundations or data centers.

Overview

In Tanzu SQL for VMs v2.7.4 and later, you provision a leader-follower service instance across two foundations using the Multi‑Site Replication topology. This leader-follower service instance is comprised of two single node instances that are configured for replication.

Note: The Multi‑Site Replication plan type is configured separately from the leader-follower service plan.

For more information about the Multi‑Site Replication topology, see About Multi‑Site Replication.

Prerequisites

Before you use Tanzu SQL for VMs across multiple foundations, you must have:

  • Access to the Multi‑Site Replication plan. Your operator configures this plan and enables access to it in your org and space.
  • Selected two foundations to deploy your Multi‑Site Replication leader and follower VMs. Your operators must configure these foundations for multi‑site replication. For more information configuring a Multi‑Site Replication topology, see Preparing for Multi‑Site Replication.

Create a Multi‑Site Replication Leader-Follower Service Instance

To create a leader-follower service across multiple foundations:

  1. Check the availability of the Multi‑Site Replication plan in the Marketplace in both your foundations. See Confirm the VMware Tanzu SQL with MySQL for VMs Service Availability.
  2. Create one Multi‑Site Replication service instance on each foundation. See Create Multi‑Site Replication Service Instances, below.
  3. Enable replication between the Multi‑Site Replication service instances. See Configure Multi‑Site Replication, below.
  4. Bind the Multi‑Site Replication leader-follower service instance to your apps. See Bind a Multi‑Site Replication Leader-Follower Service Instance to Your App, below.
  5. Modify your app to use the Tanzu SQL for VMs service. See Use the MySQL Service in Your App.

After you create a Multi‑Site Replication leader-follower service instance, you can manage it over the life cycle of your apps and data. For instructions on how to manage a Tanzu SQL for VMs service instance, see Manage Service Instances.

Create Multi‑Site Replication Service Instances

To create a leader-follower service instance across two foundations, you must create one Multi‑Site Replication service instance in each foundation. You must configure the service instances in each foundation for replication.

Note: In the below procedure, the primary foundation is the foundation that your leader VM is deployed to. The secondary foundation is the foundation that the follower VM is deployed to, and usually is your disaster recovery site.

To create a multi-site replication service instance in both foundations:

  1. Create a Multi‑Site Replication service instance in your primary foundation:

    1. Log in to the deployment for your primary foundation by running:

      cf login PRIMARY-API-URL

      Where PRIMARY-API-URL is the API endpoint for the primary foundation.

    2. Create a primary service instance by running:

      cf create-service p.mysql PLAN PRIMARY-INSTANCE

      Where:

      • PLAN is the name of the Multi‑Site Replication plan you want to use.
      • PRIMARY-INSTANCE is a name you choose to identify the service instance. This name appears under service in output from cf services.

      For example:

      $ cf create-service p.mysql db-small primary-node
      
Creating service primary-node in org my-org / space my-space as admin...
OK

      Note: Do not name your service instance leader or follower. If you trigger a failover or switchover, the service instances in your primary and secondary foundations switch roles. For more information, see Triggering Multi-Site Replication Failover and Switchover.

    3. (Optional) Watch the progress of the service instance creation by running:

      watch cf services

      Wait for the last operation for your instance to show as create succeeded.

      For example:

      $ watch cf services
      
Getting services in org my-org / space my-space as admin...
OK
name           service       plan        bound apps    last operation
primary-node   p.mysql       db-small                  create succeeded

      If you get an error, see Troubleshooting Instances.

  2. Create a Multi‑Site Replication service instance in your secondary foundation by repeating step 1 and replacing references to primary with secondary. Ensure that you log in to deployment for your secondary foundation.

Configure Multi‑Site Replication

After you create the Multi‑Site Replication service instance in primary and secondary foundations, you must configure replication between the two service instances.

You configure replication using service keys to pass connection information between the leader and follower VMs. You must not use these service keys for any other use case besides establishing multi‑site replication.

Workflow for Configuring Multi‑Site Replication

The following diagram describes the workflow for configuring multi‑site replication:

Two boxes are shown side by side, labeled 'Secondary Foundation' and 'Primary Foundation'. Six steps are shown. Steps 1 and 2 are in 'Secondary Foundation', Steps 3, 4, and 5 are in 'Primary Foundation', and Step 6 is in 'Secondary Foundation'. The steps are listed below.

The steps shown in the diagram are as follows:

  1. Create host-info service key.
  2. Record host-info service key.
  3. Update secondary service instance with host-info service key.
  4. Create credentials service key.
  5. Record credentials service key.
  6. Update primary service instance with credentials service key.

Procedure for Configuring Multi‑Site Replication

Note: The below procedure assumes you created the leader service instance in the primary foundation and the follower service instance in the secondary foundation. You created these service instances in Create Multi‑Site Replication Service Instances above.

To configure replication for your Multi‑Site Replication leader-follower service instance:

  1. Create a host-info service key for the service instance in your secondary foundation by running:

    cf create-service-key SECONDARY-INSTANCE SERVICE-KEY \
  -c '{"replication-request": "host-info"}'

    Where:

    • SECONDARY-INSTANCE is the name of the secondary service instance you created in step 2 of Create Multi‑Site Replication Service Instances, above.
    • SERVICE-KEY is a name you choose for the host-info service key.

      For example: 
      $ cf create-service-key secondary-node host-info \
   -c '{"replication-request": "host-info" }'
      
Creating service key host-info for service instance secondary-node as admin...
OK

  2. View the replication-credentials for your host-info service key by running:

    cf service-key SECONDARY-INSTANCE SERVICE-KEY

    Where:

    For example:

    $ cf service-key secondary-node host-info-key 
    
Getting key host-info-key for service instance secondary-node as admin...

  {
    "replication": {
        "peer-info": {
          "hostname": "secondary.bosh",
          "ip": "10.0.19.12",
          "system_domain": "sys.secondary-domain.com",
          "uuid": "ab12cd34-5678-91e2-345f-67891h234567"
      },
      "role": "leader"
    }
  }

  3. Record the output of the above command.

  4. Log in to the deployment for your primary foundation by running:

    cf login PRIMARY-API-URL

  5. Update your primary service instance with the host-info service key by running:

    cf update-service PRIMARY-INSTANCE -c HOST-INFO

    Where:

    For example: 

    $ cf update-service primary-node -c '{"replication":{ \
  "peer-info":{ \
      "hostname": "secondary.bosh", \
      "ip": "10.0.18.12", \
      "system_domain": "sys.secondary-domain.com", \
      "uuid": "ab12cd34-5678-91e2-345f-67891h234567" \
    },\
  "role": "leader" \
  } \
}'
    
Updating service instance primary-node as admin...
OK

  6. Watch the progress of the service instance by running:

    watch cf services

    Wait for the last operation for your instance to show as update succeeded.

    For example:

    $ watch cf services
    
Getting services in org my-org / space my-space as admin...
OK
name           service       plan        bound apps    last operation
primary-node   p.mysql       db-small                  update succeeded

    If you get an error, see Troubleshooting Instances.

  7. Create a credentials service key for the service instance in your primary foundation by running:

    cf create-service-key PRIMARY-INSTANCE SERVICE-KEY-NAME \
  -c '{"replication-request": "credentials"}'

    Where:

    • PRIMARY-INSTANCE is the name of the primary service instance you created in step 1 of Create Multi‑Site Replication Service Instances, above.
    • SERVICE-KEY-NAME is a name you choose for the credentials service key.

      For example: 
      $ cf create-service-key primary-node cred-key \
  -c '{"replication-request": "credentials" }' 
      
Creating service key cred-key for service instance primary-node as admin...
OK

      Note: The -c flag is different than the one in step 1.

  8. View the replication-credentials for your credentials service key by running:

    cf service-key PRIMARY-INSTANCE SERVICE-KEY-NAME

    Where:

    For example:

    $ cf service-key primary-node cred-key 
    
Getting key cred-key for service instance primary as admin...

  {
    "replication": {
      "credentials": {
        "password": "a22aaa2a2a2aaaaa",
        "username": "6bf07ae455a14064a9073cec8696366c"
      },
      "peer-info": {
        "hostname": "primary.bosh",
        "ip": "10.0.17.12",
        "system_domain": "sys.primary-domain.com",
        "uuid": "zy98xw76-5432-19v8-765u-43219t876543"
      },
      "role": "follower"
    }
  }

  9. Record the output of the above command.

  10. Log in to the deployment for your secondary foundation by running:

    cf login SECONDARY-API-URL

  11. Update your secondary service instance with the credentials service key by running:

    cf update-service SECONDARY-INSTANCE -c CREDENTIALS

    Where:

    For example: 

    $ cf update-service secondary-node -c '{"replication": { \
    "credentials": { \
      "password": "a22aaa2a2a2aaaaa", \
      "username": "6bf07ae455a14064a9073cec8696366c" \
    }, \
    "peer-info": { \
      "hostname": "primary.bosh", \
      "ip": "10.0.17.12", \
      "system_domain": "sys.primary-domain.com", \
      "uuid": "zy98xw76-5432-19v8-765u-43219t876543" \
    }, \
    "role": "follower" \
  } \
}'
    
Updating service instance primary-node as admin...
OK

You should now have a Multi‑Site Replication leader-follower service instance that is fully configured and has replication enabled.

Bind a Multi‑Site Replication Leader-Follower Service Instance to Your App

For an app to use a Multi‑Site Replication leader-follower service instance, you must bind your app to your primary service instance in your primary foundation. If you want to use an active-active topology, you must additionally bind your app to the secondary service instance in your secondary foundation.

For information about active-passive and app-layer active-active topologies, see see About Active-Passive Topology and About App-Layer Active-Active Topology.

To bind an app to a leader-follower service instance:

  1. Log in to the deployment for your primary foundation by running:

    cf login PRIMARY-API-URL

  2. Bind your app to your primary service instance by doing the procedure in Bind a Service Instance to Your App.

  3. (Optional) If you are using an active-active topology, you must bind the same app to your secondary service instance in your secondary foundation. To do this, repeat the above steps and replace references to primary with secondary.

  4. Modify your app to use the Tanzu SQL for VMs service by doing the procedure in Use the MySQL Service in Your App.