Migrating Data in MySQL for PCF

Warning: MySQL for PCF v2.6 is no longer supported because it has reached the End of General Support (EOGS) phase as defined by the Support Lifecycle Policy. To stay up to date with the latest software and security updates, upgrade to a supported version.

Page last updated:

This topic describes how to migrate the data from any MySQL database to a MySQL for PCF v2.x service instance.

Warning: Highly available plans are currently in beta. HA clusters are for advanced use cases only.

Overview

The mysql-tools CLI plugin includes a migrate command.

To migrate data from a source MySQL instance to a new target MySQL instance in Pivotal Cloud Foundry (PCF), you run the migrate command from your target org and space. The command:

  1. Creates a new MySQL for PCF v2.x service instance in the target space.
  2. Copies the source data over to the new service instance.

Warning: The migrate command does not migrate all stored programs. The command only migrates views and does not migrate triggers, routines, or events. To manually migrate triggers, routines, or events, contact Pivotal Support.

Use Cases

The migrate command is used for most migration use cases. See Use Cases Requiring the migrate Command below.

However, many common migrations, such as from a small to a large database, can be done with the simpler update-service command. See Use Cases Not Requiring the migrate Command below.

If your use case is not listed in this topic, you might need to manually back up and restore your database to migrate your data. See Backing Up and Restoring with mysqldump

Use Cases Requiring the migrate Command

The following table lists migration use cases that must be done with the migrate command.

Use migrate for migrating from… To…
Single Node Highly Available (HA) Cluster
Leader-Follower HA Cluster
HA Cluster Leader-Follower
HA Cluster Single Node
Off-Platform Database * MySQL for PCF v2
Availability Zone (AZ) Another AZ
MySQL for Pivotal Cloud Foundry v1 MySQL for PCF v2

* If your off-platform database has encryption at rest or the Percona PAM Authentication plugin enabled, you cannot use the migrate command. Instead, you must follow the procedure in Restore from an Off-Platform Logical Backup.

Use Cases Not Requiring the migrate Command

Not all migrations require the migrate command. If you are migrating from a smaller to a larger plan of the same or similar toplogy, use the simpler update-service command.

The following table lists migration use cases that can be done with the update-service command. For instructions about using the update-service command, Update a Service Instance.

Use update-service for migrating from… To…
Single Node larger Single Node
Leader-Follower larger Leader-Follower
Single Node Leader-Follower of same or larger size
Leader-Follower Single Node of same or larger size

How the migrate Command Works

The migrate command performs a streaming mysqldump and restore to migrate data from your source MySQL database to a target MySQL for PCF v2 instance. This command skips the following system schemas:

  • cf_metadata
  • information_schema
  • mysql
  • performance_schema
  • sys

Additionally, the migration skips MySQL stored routines, events, and triggers. Users must migrate these types of data manually.

The migrate command creates a new MySQL for PCF v2 instance in the target space and gives it the same name as the source MySQL service instance name.

The command also appends -old to the source service instance name, whether it is another PCF MySQL service instance or a user-provided service created locally to help migrate an off-platform database.

Security

The migrate command supports connections over TLS. If TLS is configured in source and target MySQL instances, the channel through which data is streamed is secured via TLS.

For information on how to configure TLS in your service instance, see Securing Service Instance Credentials with Runtime Credhub.

Command Output

If migration is successful, the migrate command output includes a Migration completed successfully message, as shown in this 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...

If the migration is not successful, an error message appears.

Debugging

For debugging, you can add the optional --no-cleanup flag to the migrate command.

If a migration fails, this flag preserves the app that runs the migration task and the newly-created service instances. However, if a migration succeeds, the migration app is deleted even when the migrate command included --no-cleanup.

Prerequisites

To perform the procedures in this topic, you must have the following:

  • An existing MySQL database that is the data source you want to migrate from. This can be:
    • a MySQL for PCF v1 service instance
    • a MySQL for PCF v2 service instance
    • a non-Pivotal MySQL database
  • MySQL for PCF v2.x installed in the target PCF environment you want to migrate to.
  • A MySQL for PCF service plan available in the targeted Org and Space you want to migrate your data to. The service plan should have the correct topology and resources for your target database. Talk to your operator to determine which service plan is appropriate. For more information, see Persistent Disk Usage.

Note: To view available service plans, run cf marketplace. MySQL for PCF v2.x appears as p.mysql and MySQL for PCF v1.x appears as p-mysql.

Resource Planning

Make sure the MySQL for PCF v2 service plan that you use for your migration target has your preferred VM type and persistent disk size. When there is not enough space on disk to store the data, migration fails.

The service plan that you select for your target service instance should have a persistent disk that is:

  • Single-Node or Leader-Follower: Three times larger than the size of the source data.
  • HA Cluster: Two times larger than the size of the source data.

Warning: Migrating large datasets can take several hours. Migration of the data 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 will take 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.

Install the mysql-tools CF CLI Plugin

Pivotal recommends that operators and developers use the mysql-tools cf CLI plugin to migrate data from a source MySQL database to a MySQL for PCF v2.x service instance.

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

Do the following to install the mysql-tools cf CLI plugin:

  1. Run the following command:

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

  2. Ensure the plugin has installed successfully by running the following command:

    cf mysql-tools version

  3. You can run the following command to view plugin usage details:

    cf mysql-tools -h

    An example output: 

    $ cf mysql-tools -h
NAME:
   mysql-tools - Plugin to migrate mysql instances
    


    USAGE:
   cf mysql-tools migrate [-h] [--no-cleanup] <v1-service-instance> <plan-type>
   cf mysql-tools version

Set Up 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 target, as follows:

  • 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 target, enable access through service instance sharing. See Source Access across Spaces, below.
  • Migrating from Off-Platform: If the source database is in a different PCF foundation from the target, or not on any PCF foundation, create a user-provided service that accesses 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 target org and space, you can migrate your data by sharing the service instance to the target org and space. Service instance sharing is enabled by default.

To share a source MySQL service instance with your target 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 PCF foundation, or not deployed on PCF at all, 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, do the following:

  1. Ensure your off-platform MySQL database allows inbound and outbound network traffic to your target PCF foundation. You may need to modify firewall rules for your off-platform MySQL. Talk to your platform operator for assistance.
  2. If your off-platform MySQL database requires connections over TLS, make sure that your PCF foundation is configured to recognize the CA that the MySQL server certificate is signed with.

  3. Collect the address, credentials, and other values needed to access your off-platform database. These typically include the following:

    • hostname: Domain name or IP address of the off-platform source database
    • name: Name of source database
    • username and password: Database account credentials
    • port: Typically 3306

  4. Create a Cloud Foundry user-provided service instance that accesses 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 above.

    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.

After seeing that your user-provided service instance was created, continue to Migrate Data, below.

Migrate Data

When your source database is accessible to your destination space, perform the following steps to migrate your data:

  1. If your source MySQL service instance is running on PCF, do the following to stop all traffic to the service instance, by stopping and unbinding all apps that use it:

    Note: To retrieve a list of bound apps, run cf services and see the bound apps column.

    1. Use the cf CLI to target and log in to your PCF deployment. For example: 
      $ cf target api.example.com
$ cf login
      When prompted, enter your credentials and target the org and space where the service instance is located.

    2. Do the following for each app bound to the instance:

      1. Stop the app by running the following command:

          cf stop APP

        Where APP is the name of your app.

        For example: 

          $ cf stop my-app
  Stopping app my-app in org my-org / space my-space as user@example.com...
  OK

      2. Unbind the app by running the following command:

          cf unbind-service APP SOURCE-INSTANCE

        Where:

        • APP is the name of your app.
        • SOURCE-INSTANCE is the name of your source MySQL for PCF service instance.

        For example: 

          $ cf unbind-service my-app my-instance
  Unbinding app my-app from service my-instance in org my-org / space my-space as user@example.com...
  OK

  2. Choose a service plan for your target MySQL for PCF v2 instance. For example, db-small.

    • To view available MySQL for PCF v2 service plans, run cf marketplace and refer to the plans under p.mysql.

  3. Migrate your data by running the following command:

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

Validate Data

After migrating your data, you must verify that the data has successfully migrated by validating the data in the new MySQL for PCF v2.x service instance. You can validate the data by creating an SSH tunnel to gain direct command line access to the new MySQL for PCF v2.x service instance.

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

  1. Create an SSH tunnel to your MySQL for PCF v2.x service instance. To do this, perform the steps in the following sections of 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 MySQL for PCF v2.x service instance.

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

Rebind and Restage Apps

To complete the migration, rebind and restage any apps that had been bound to the original MySQL for PCF service instance.

Warning: If a developer rebinds an app to the MySQL for PCF 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.

Do the following to rebind and restage your apps:

  1. Bind the app to the new service instance. Run the following command:

    cf bind-service APP V2-INSTANCE

    Where:

    • APP is the name of your app.
    • V2-INSTANCE is the name of your MySQL for PCF 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. Restage the app. For example: 

    $ cf restage my-app
Restaging app my-app in org my-org / space my-space as user@example.com...
[...]
    The app is now using your new MySQL for PCF v2.x service instance and should be operational again.

Delete the Old Database

After rebinding and restaging your apps to confirm that migration was successful, Pivotal recommends saving resources by deleting the old database instance.

If your source database is deployed on PCF, perform the deletion by running the following command:

  cf delete-service SOURCE-INSTANCE

Where SOURCE-INSTANCE is the name of your old database instance.

For example:

$ cf delete-service my-instance