Migrating Data from v1 to v2

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

Prerequisites

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

  • An existing MySQL for PCF v1.x service instance with the data you want to migrate
  • MySQL for PCF v2.x installed in the same PCF environment as your PCF v1.x service instance
  • At least one MySQL for PCF v2.x service plan available in the same space as your MySQL for PCF v1.x service instance. The persistent disk size of this service plan must be three times larger than the maximum size of the data you store in it. For more information, see MySQL 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.

    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 MySQL for PCF v1.x 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 MySQL for PCF v1.x is located.

  2. Stop any traffic to the MySQL for PCF v1.x 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 v1.x service instance. Run the following command for each app bound to the instance:

     cf unbind-service APP V1-INSTANCE
    


    Where:

    • APP is the name of your app.
    • V1-INSTANCE is the name of your MySQL for PCF v1.x service instance.

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

Migrate Data

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

The migrate command of the plugin renames the MySQL for PCF v1.x 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 V1-INSTANCE V2-PLAN
    


    Where:

    • V1-INSTANCE is the name of your MySQL for PCF v1.x 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 v1.x 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 V1-INSTANCE


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

For example:

$ cf delete-service my-v1-instance