Migrating Data in 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 describes how to migrate the data from any MySQL database to a VMware Tanzu SQL with MySQL for VMs service instance.

Overview

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

To migrate data in Tanzu SQL for VMs:

  1. Enable the migrate command to access the source database. See Enable Source Access below.
  2. Migrate the data from the source database to the destination service instance. See Migrate Data below.
  3. Ensure that the migration was successful. See Validate Data below.
  4. 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 below.

Warning: 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 will take 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 Tanzu SQL for VMs.
  • An existing MySQL database that is the source of the data you want to migrate from. MySQL source databases can be:
    • a v1 service instance
    • a VMware Tanzu SQL with MySQL for VMs v2 service instance
    • a database that is not a Tanzu SQL for VMs database
  • Tanzu SQL for VMs v2 installed in the destination environment you want to migrate to.
  • A Tanzu SQL for VMs 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 below. Talk to your operator to determine which service plan is appropriate.

Resource Planning

You must ensure that the Tanzu SQL for VMs 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.

Note: To view available service plans, run cf marketplace. Tanzu SQL for VMs v2 appears as p.mysql and v1 appears as p-mysql.

When choosing 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 Plugin

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

Note: You must have TLS enabled to use this feature. To configure and enable TLS, see Configure TLS.

To install the mysql-tools cf CLI plugin:

  1. Install the plugin by running:

    cf install-plugin -r CF-Community "MysqlTools"
    
  2. Confirm that the plugin has installed successfully by running:

    cf mysql-tools version
    

Enable Source Access

The migrate command must be able to access the source database. How you enable 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 below.
  • 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 below.
  • 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 below.

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:

  1. Do the procedure in Share Service Instances.

  2. Continue to Migrate Data below.

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:

  1. 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.
  2. If your off-platform MySQL database requires connections over TLS, make sure that your Ops Manager foundation is configured to recognize the CA certificate that the MySQL server certificate is signed with. Talk to your platform operator for assistance.
  3. 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 Tanzu SQL for VMs service instance, these values are in your VCAP_SERVICES environment variable credentials hash. For more information, see MySQL Environment Variables.

  4. 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 above 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

    Note: cf cups is a shortcut for the cf create-user-provided-service command.

  5. After your user-provided service instance is created, continue to Migrate Data below.

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

  • If your source MySQL database is running off-platform, do the procedure in Migrate Data to Destination Instance below.

Stop and Unbind Apps

To stop and unbind your apps:

  1. Log in to your BOSH deployment by running:

    cf login API-URL
    

    When prompted, enter your credentials.

  2. Target the org and space for the new destination service instance by running:

    cf target -o DESTINATION-ORG -s DESTINATION-SPACE
    
  3. Retrieve and record a list of your bound apps by running:

    cf services
    

    Your apps are listed the bound apps column.

  4. For each bound app you recorded in the above 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 Tanzu SQL for VMs service instance.
  5. Do the procedure in Migrate Data to Destination Instance below.

Migrate Data to Destination Instance

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

  1. Log in to your BOSH deployment and target the destination org and space by doing steps 1 and 2 of Stop and Unbind Apps above.

  2. View and select an available Tanzu SQL for VMs v2 service plans by running:

    cf marketplace
    

    Tanzu SQL for VMs v2 service plans are under p.mysql.

  3. 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 selected above.

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

    Note: For debugging purposes, you can add the --no-cleanup flag to the above 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 migrating your data, you must verify that the data was successfully migrated by validating the data in the new Tanzu SQL for VMs v2.x service instance.

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

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

  1. Create an SSH tunnel to your Tanzu SQL for VMs v2.x service instance by doing the following procedures in Accessing Services with SSH:

    1. Push Your Host App
    2. Create Your Service Key
    3. Configure Your SSH Tunnel
    4. Access Your Service Instance
  2. From the MySQL shell, validate that the data that you expect to see has been imported into the Tanzu SQL for VMs v2.x service instance.

  3. Exit the MySQL shell and kill the SSH tunnel.

Rebind and Re-Stage Apps

To complete the migration, rebind and re-stage any bounds 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.

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.

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

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

  2. Re-stage the app by running:

    cf restage APP
    

    The app is now using your new Tanzu SQL for VMs v2.x service instance and should be operational again.

  3. (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 Tanzu SQL for VMs service instance.