LATEST VERSION: 2.5 - RELEASE NOTES

Migrating Data in MySQL for PCF

Page last updated:

WARNING: Highly available (HA) cluster service plans are currently in beta. HA clusters are for advanced use cases only.

This topic describes how to migrate the data from a MySQL for Pivotal Cloud Foundry (PCF) service instance to another MySQL for PCF service instance.

This topic addresses the following data migration scenarios:

  • Migrating from MySQL for PCF v1.x to MySQL for PCF v2.x.
  • Migrating from a single-node or leader-follower plan to highly available (HA) cluster plan.
  • Migrating from an HA cluster plan to single-node or leader-follower plan.
  • Migrating from one availability zone (AZ) to another.

Prerequisites

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

  • An existing MySQL for PCF service instance to source the data you want to migrate.
  • MySQL for PCF v2.x installed in the same PCF environment as your source service instance.
  • At least one MySQL for PCF service plan for typology you want to use available in the same space as your source service instance.

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.

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.

Stop and Unbind Apps

Do the following to stop and unbind any apps that use your source MySQL for PCF service instance:

  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. Stop any traffic to the MySQL for PCF database by stopping any apps that are bound to the service instance. Run the following command for each app bound to the instance:

     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 
    To retrieve a list of bound apps, run cf services.

  3. Unbind your app from the MySQL for PCF service instance. Run the following command for each app bound to the instance:

     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
      

Migrate Data

To migrate the data from the source MySQL for PCF service instance to a new destination MySQL for PCF v2.x service instance, use the cf mysql-tools plugin.

The migrate command of the plugin renames the MySQL for PCF service instance by appending -old and gives the original name of the instance to the newly created MySQL for PCF v2.x service instance.

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

Do the following to migrate data to an instance of the same name:

  1. Install the cf mysql-tools plugin. Run the following command:

    cf install-plugin -r CF-Community "MysqlTools"
    
  2. Use the plugin to migrate your data. Run the following command:

    cf mysql-tools migrate SOURCE-INSTANCE V2-PLAN
    


    Where:

    • SOURCE-INSTANCE is the name of your source MySQL for PCF service instance.
    • V2-PLAN is the name of the MySQL for PCF v2.x service plan to use for the new MySQL for PCF v2.x service instance. For example, db-small. To view available MySQL for PCF v2.x service plans, run cf marketplace and locate the plans under p.mysql.

      For debug purposes, optionally add the --no-cleanup flag. This flag preserves the app that runs the migration task and the newly created service instances if the migration is not successful. However, if a migration succeeds, the migration app is cleaned even if the --no-cleanup flag is provided.

      Note: An error message appears if the migration is not successful. If the migration is successful, a Migration completed successfully message appears in the messages returned to you after you run the migration command, as show in the example below. This message is the best indication that your migration process was successfully completed.

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

Developers can SSH into the app container to examine the logs.

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.

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.

To perform the deletion, run 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

Create a pull request or raise an issue on the source for this page in GitHub