Migrating data in VMware SQL with MySQL for Tanzu Application Service

You can migrate data from any MySQL database to a VMware SQL with MySQL for Tanzu Application Service service instance.

You can migrate data from a source database to a destination database using the migrate command.

To migrate data in VMware Tanzu for MySQL:

Enable the migrate command to access the source database. See Enable source access.
Migrate the data from the source database to the destination service instance. See Migrate data.
Ensure that the migration was successful. See Validate data.
Rebind and re-stage apps to the new destination service instance. To save resources, you can optionally delete the old source database. See Rebind and re-stage apps.

Caution Migrating large datasets can take several hours. Data migration is linear and depends on the hardware being used. For example, if X amount of data takes 10 minutes to migrate, then 2X amount of data takes 20 minutes to migrate using the same hardware.

Do a test migration with small datasets to estimate how long the entire migration takes before migrating larger datasets.

Prerequisites

Before you do the procedures in this topic, you must have:

Read about the migrate command in About data migration in VMware Tanzu for MySQL.
An existing MySQL database that is the source of the data you want to migrate from. MySQL source databases can be:
- a MySQL for Pivotal Cloud Foundry v1 service instance
- a VMware SQL with MySQL for Tanzu Application Service v2 service instance
- a database that is not a VMware Tanzu for MySQL database
VMware Tanzu for MySQL v2 installed in the destination environment you want to migrate to.
A VMware Tanzu for MySQL service plan available in the destination org and space you want to migrate your data to. This service plan must fulfill the requirements in Resource planning. Talk to your operator to determine which service plan is appropriate.

Resource planning

You must ensure that the VMware Tanzu for MySQL v2 service plan for your destination service instance has your preferred VM type and persistent disk size. If the plan does not enough space on disk to store the data, migration fails.

To view available service plans, run cf marketplace. VMware Tanzu for MySQL v2 appears as p.mysql and MySQL for Pivotal Cloud Foundry v1 appears as p-mysql.

You can choose a service plan for your destination service instance, if you select:

A single node or leader-follower plan, ensure the persistent disk size is three times larger than the size of the source data.
A HA cluster plan, ensure the persistent disk size is two times larger than the size of the source data.

For more information about recommended persistent disk sizes, see Persistent disk usage.

Install the mysql-tools CF CLI plug-in

VMware recommends that developers migrate data using the mysql-tools CLI plug-in with the migrate command. For more information about mysql-tools cf CLI plug-in, see mysql-cli-plugin in GitHub.

You must have TLS activated to use this feature. To configure and activate TLS, see Configure TLS.

To install the mysql-tools cf CLI plug-in:

Install the plug-in by running:

cf install-plugin -r CF-Community "MysqlTools"

Confirm that the plug-in has successfully installed, run:
```
cf mysql-tools version
```

Enable source access

The migrate command must be able to access the source database. How you activate this access depends on where the source database is located relative to the destination space and org.

If you are:

Migrating within an Org and Space: The migrate command can access the source without any preparation. Continue to Migrate data.
Migrating across Spaces: If the source database is in a different space from the destination, enable access using service instance sharing. See Source access across spaces.
Migrating from Off-Platform: If the source database is in a different Ops Manager foundation from the destination or not deployed on Ops Manager, create a user-provided service that can access the remote database. See Source access off-platform.

Source access across spaces

If your source MySQL service instance is in a different development space from your destination org and space, you can migrate your data by sharing the service instance to the destination org and space. Service instance sharing is enabled by default.

To share a source MySQL service instance with your destination org and space:

Do the procedure in Share service instances.
Continue to Migrate data.

Source access off-platform

If your source MySQL database is in a different Ops Manager foundation or not deployed on Ops Manager, you can migrate your data by creating a local user-provided service instance that can access the database.

For more information on user-provided service instances, see User-provided service instances.

To create a user-provided service instance to access the off-platform database:

Confirm that your off-platform MySQL database permits inbound and outbound network traffic to your destination Ops Manager foundation. You might need to modify firewall rules for your off-platform MySQL database. Talk to your platform operator for assistance.
If your off-platform MySQL database requires connections over TLS, verify that your Ops Manager foundation is configured to recognize the CA certificate that the MySQL server certificate signed in with. Talk to your platform operator for assistance.
Record the information needed to access your off-platform database. These values usually include:
- hostname: the domain name or IP address of the off-platform source database.
- name: the name of source database.
- username and password: the database account credentials.
- port: The port number for the database. This number is usually 3306.
If your off-platform database is a VMware Tanzu for MySQL service instance, these values are in your VCAP_SERVICES environment variable credentials hash. For more information, see MySQL Environment Variables.
Create a Cloud Foundry user-provided service instance that can access the off-platform database:
```
cf cups CF-DB-INSTANCE -p CREDS-STRUCT
```
Where:
- CF-DB-INSTANCE is the name that you want to give to the new database service instance that you are migrating to.
- CREDS-STRUCT is a JSON structure that contains the off-platform database access values you recorded in the previous step.
For example:
```
$ cf cups migrating-db -p '{"hostname": "34.192.88.212", "name": "my\_db", \
"username": "root", "password": "P455w0rD", "port": 3306}' 

Creating user provided service migrating-db in org my-org / space my-space as admin...
OK
```
cf cups is a shortcut for the cf create-user-provided-service command.
After your user-provided service instance is created, continue to Migrate data.

Migrate data

After your source database can access the destination space:

If your source MySQL database is running on Ops Manager, you must stop all traffic to the service instance before you migrate your data. You can do this by stopping and unbinding all of your apps. See Stop and Unbind Apps.
If your source MySQL database is running off-platform, do the procedure in Migrate Data to Destination Instance.

Stop and unbind apps

To stop and unbind your apps:

Log in to your BOSH deployment by running:
```
cf login API-URL
```
When prompted, enter your credentials.
Target the org and space for the new destination service instance by running:
```
cf target -o DESTINATION-ORG -s DESTINATION-SPACE
```
Retrieve and record a list of your bound apps by running:
```
cf services
```
Your apps are listed the bound apps column.
For each bound app you recorded in the previous step:
1. Stop the app by running:
```
cf stop APP
```
2. Unbind the app by running:
```
cf unbind-service APP SOURCE-INSTANCE
```
  Where:
  - APP is the name of your app.
  - SOURCE-INSTANCE is the name of your source VMware Tanzu for MySQL service instance.
Do the procedure in Migrate data to destination instance.

Migrate data to destination instance

To migrate data from your source database to your destination service instance:

Log in to your BOSH deployment and target the destination org and space by doing steps 1 and 2 of Stop and unbind apps.
View and select an available VMware Tanzu for MySQL v2 service plans by running:
```
cf marketplace
```
VMware Tanzu for MySQL v2 service plans are under p.mysql.

Migrate your data by running:

cf mysql-tools migrate SOURCE-INSTANCE V2-PLAN

Where:

SOURCE-INSTANCE is the name of your source MySQL service instance or user-provided service instance.
V2-PLAN is the name of the service plan that you previously selected.

For example:

$ cf mysql-tools migrate my-instance db-small 

2018/04/24 11:31:19 Creating new service instance "my-instance" for service p.mysql using plan db-small
2018/04/24 11:41:01 Unpacking assets for migration to /var/folders/dm/66n2j9xx02l8vs58q2whz4080000gn/T/migrate\_app\_101341527
2018/04/24 11:41:02 Started to push app
Done uploading
2018/04/24 11:41:09 Successfully pushed app
2018/04/24 11:41:10 Successfully bound app to v1 instance
2018/04/24 11:41:12 Successfully bound app to v2 instance
2018/04/24 11:41:12 Starting migration app
2018/04/24 11:41:25 Started to run migration task
2018/04/24 11:41:27 Migration completed successfully
2018/04/24 11:41:29 Cleaning up...

For debugging purposes, you can add the --no-cleanup flag to the previous command. If a migration fails, this flag preserves the migration app and the newly-created service instance. However, if a migration succeeds, the migration app is still deleted.

Validate data

After you migrate your data, you must verify that the data was successfully migrated by validating the data in the new VMware Tanzu for MySQL v2.x service instance.

You can validate the data by creating an SSH tunnel to gain direct command line access to the new VMware Tanzu for MySQL v2.x service instance.

To create an SSH tunnel to the instance and validate your data:

Create an SSH tunnel to your VMware Tanzu for MySQL v2.x service instance by doing the following procedures in Accessing Services with SSH:
From the MySQL shell, validate that the data that you expect to see has been imported into the VMware Tanzu for MySQL v2.x service instance.
Exit the MySQL shell and stop the SSH tunnel.

Rebind and re-stage apps

To complete the migration, rebind and re-stage any bound apps in your destination org and space. After rebinding and re-staging your apps, VMware recommends deleting the old source database instance to save resources.

If a developer rebinds an app to the VMware Tanzu for MySQL 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.

To rebind and re-stage your apps and delete the source database instance:

Bind the app to the new service instance by running

cf bind-service APP V2-INSTANCE

Where:

APP is the name of your app.
V2-INSTANCE is the name of your VMware Tanzu for MySQL v2.x service instance.

For example:

$ cf bind-service my-app my-v2-instance
Binding service my-v2-instance to app my-app in org my-org / space my-space as user@example.com...
OK
TIP: Use 'cf restage my-app' to ensure your env variable changes take effect

Re-stage the app by running:
```
cf restage APP
```
The app is now using your new VMware Tanzu for MySQL v2.x service instance and should be operational again.
(Optional) Delete your source database. If your source database is deployed on Ops Manager, delete the old database instance by running in your source space and org:
```
cf delete-service SOURCE-INSTANCE
```
Where SOURCE-INSTANCE is the name of your source VMware Tanzu for MySQL service instance.