Using VMware SQL with MySQL for Tanzu Application Service for multi-site replication

You can use VMware Tanzu SQL with MySQL for VMs for replication across multiple foundations or data centers.

You provision a leader-follower service instance across two foundations using the Multi‑Site Replication topology. This leader-follower service instance comprises two single node instances that are configured for replication.

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 using mysql-tools

To create a leader-follower service across multiple foundations with mysql-tools:

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.
Download the latest version of the mysql-tools plugin. For more information about mysql-tools cf CLI plug-in, see mysql-cli-plugin in GitHub.
Configure Replication between the primary and secondary foundation using mysql-tools. See Create Multi‑Site Replication service instances using mysql-tools.
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.
Modify your app to use the Tanzu SQL for VMs service. See Use the MySQL service in your app.

Setup Multi‑Site Replication using mysql-tools

To create a leader-follower service instance across two foundations with mysql-tools, you must: 1. Use mysql-tools to save the cloudfoundry configuration for the primary foundation. 1. Create a Multi‑Site Replication service instance in the primary foundation. 1. Use mysql-tools to save the cloudfoundry configuration for the secondary foundation. 1. Create a Multi‑Site Replication service instance in the secondary foundation. 1. Use mysql-tools to configure replication between the primary and secondary foundations.

The secondary foundation is the foundation where the secondary VM is deployed, and usually is your disaster recovery site.

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

To save the cf config of the primary foundation:

login to the 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. Use mysql-tools to save the config
```
cf mysql-tools save-target PRIMARY-TARGET-NAME
```
  Where PRIMARY-TARGET-NAME is your chosen name for the primary foundation.
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.

To save the cf config of the secondary foundation:

login to the secondary foundation:
1. Log in to the deployment for your secondary foundation by running:
```
cf login SECONDARY-API-URL
```
  Where SECONDARY-API-URL is the API endpoint for the primary foundation.
2. Use mysql-tools to save the config
```
cf mysql-tools save-target SECONDARY-TARGET-NAME
```
  Where SECONDARY-TARGET-NAME is your chosen name for the secondary foundation.

To configure replication between the primary and secondary foundations:

Use mysql-tools to configure replication between the primary and secondary foundation:
1. Configure replication between the primary and secondary foundation by running:
```
cf mysql-tools PRIMARY-TARGET-NAME PRIMARY-INSTANCE SECONDARY-TARGET-NAME SECONDARY-INSTANCE
```
  Where PRIMARY-TARGET-NAME is your chosen name for the primary foundation. Where PRIMARY-INSTANCE is your chosen name for the primary instance. Where SECONDARY-TARGET-NAME is your chosen name for the secondary foundation. Where SECONDARY-INSTANCE is your chosen name for the secondary instance.

Create a Multi‑Site Replication leader-follower service instance

To create a leader-follower service across multiple foundations:

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.
Create one Multi‑Site Replication service instance on each foundation. See Create Multi‑Site Replication service instances.
Enable replication between the Multi‑Site Replication service instances. See Configuring Multi‑Site Replication.
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.
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.

The secondary foundation is the foundation that the follower VM deployed, and usually is your disaster recovery site.

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

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

Configuring 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:

alt-text=Two boxes 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 shown in the diagram are as follows:

Create host-info service key.
Record host-info service key.
Update secondary service instance with host-info service key.
Create credentials service key.
Record credentials service key.
Update primary service instance with credentials service key.

Procedure for configuring Multi‑Site Replication

The following 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.

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

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

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

cf service-key SECONDARY-INSTANCE SERVICE-KEY

Where:

SECONDARY-INSTANCE is the name of the secondary service instance you created in step 2 of Create Multi‑Site Replication service instances.
SERVICE-KEY is the name of the host-info service key you created in step 1.

For example:

$ cf service-key secondary-node host-info-key 

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

  {
    "credentials": {
        "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"
        }
    }
  }

Caution
In cf CLI v8, the response includes a top-level credentials key. Earlier versions of the cf CLI do not include a top-level credentials key. This procedure assumes that you are using cf CLI v8.

Record the output of the previous command, and remove the top-level credentials key.
Log in to the deployment for your primary foundation by running:
```
cf login PRIMARY-API-URL
```

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

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

Where:

PRIMARY-INSTANCE is the name of the primary service instance you created in step 1 of Create Multi‑Site Replication service instances.
HOST-INFO is the output you recorded in step 3.

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

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.

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.
- 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
```
The -c flag is different than the one in step 1.

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

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

Where:

PRIMARY-INSTANCE is the name of the primary service instance you created in step 1 of Create Multi‑Site Replication service instances.
SERVICE-KEY-NAME is the name of the credentials service key you created in step 6.

For example:

$ cf service-key primary-node cred-key 

Getting key cred-key for service instance primary as admin...

  {
    "credentials": {
        "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"
        }
    }
  }

Caution
In cf CLI v8, the response includes a top-level credentials key. Earlier versions of the cf CLI do not include a top-level credentials key. This procedure assumes that you are using cf CLI v8.

Record the output of the previous command, and remove the top-level credentials key.
Log in to the deployment for your secondary foundation by running:
```
cf login SECONDARY-API-URL
```

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

cf update-service SECONDARY-INSTANCE -c CREDENTIALS

Where:

SECONDARY-INSTANCE is name of the secondary service instance you created in step 2 of Create Multi‑Site Replication service instances.
CREDENTIALS is the output you recorded in step 8.

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 now have a Multi‑Site Replication leader-follower service instance that is fully configured and has replication enabled.

Upgrade a Multi‑Site Replication leader-follower service

It is important to upgrade the Multi‑Site Replication leader-follower service in a specific order: Follower first, then leader. This ensures that any incompatibilities between different Tanzu SQL for VMs versions are handled correctly.

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 appp-layer active-active topology.

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

Log in to the deployment for your primary foundation by running:
```
cf login PRIMARY-API-URL
```
Bind your app to your primary service instance by doing the procedure in Bind a service instance to your app.
(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 previous steps and replace references to primary with secondary.
Modify your app to use the Tanzu SQL for VMs service by using the procedure in Use the MySQL service in your app.