LATEST VERSION: 2.1 - CHANGELOG

Migrating a Database to MySQL for PCF v2

This page provides instructions on how to migrate your MySQL for PCF v1.x databases to MySQL for PCF v2. To perform the migration, you use the backup and restore process as follows:

  1. Preemptively create a new v2 service instance to minimize downtime.
  2. Stop traffic to the old database and take a backup.
  3. Import the backup into your new service instance.
  4. Connect your app to the new database and restart the app.

Prerequisites

To minimize downtime while migrating, preemptively create a new service instance.

  • The application source code can use p.mysql as a valid key for accessing the database.
  • Create a new service instance of p.mysql with a persistent disk large enough for the data. If you do not have an instance, follow the instructions to create a new service instance in the same space as your existing service instance.

    Do not bind the new service instance to your application. We’ll do that later.

Back Up Your Current Database

If you are migrating from a MySQL for PCF v1.x database, please refer to the instructions below. If you are migrating from another MySQL provider, please refer to their documentation on taking a MySQL backup.

Back Up Your MySQL for PCF v1.x Instance

  1. Stop any traffic to your existing database by stopping the application:

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

  2. Unbind your application from the old service instance:

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

  3. Use a cf application to create an ssh tunnel to connect to your old service instance. The tunnel may appear as though it is hanging.

    Since your application is now stopped, you’ll need another application for the ssh tunnel. If you don’t have a running app, you can clone and cf push this sample app for tunneling. You can use any running app in the same Org/Space

    • An ssh tunnel is necessary as the MySQL service is only accessible within the Cloud Foundry network.
  4. Take a backup of the existing database in a separate shell:

    $ mysqldump --single-transaction --add-drop-table --add-locks --create-options --disable-keys --extended-insert --quick --set-charset --routines --flush-privileges -u USERNAME -p -h 0 -P 63306 DB_NAME > backup.sql
    

    • DB_NAME is the “name” property from your service-key
    • USERNAME is the “username” property from your service-key
    • 63306 is the available port you configured in the ssh tunnel on your local machine
    • You will be prompted for a password after entering the previous command which is the “password” property from your service-key
  5. Kill the ssh tunnel

Restore to Your New Database

This section provides steps to restore your backup to a new service instance of MySQL for PCF:

  1. Use cf ssh to create a tunnel to remotely access your new MySQL instance.

  2. In another shell, restore your backup to the new service instance via the ssh tunnel:

    $ mysql -u USERNAME -p -h 0 -P 63306 -D service_instance_db < backup.sql
    

    • USERNAME is the “username” property from your service-key
    • 63306 is the available port you configured in the ssh tunnel on your local machine
    • backup.sql is the path to the backup on your local machine
    • You will be prompted for a password after entering the previous command which is the “password” property from your service-key
  3. Validate that data that you expect to see has been imported into the new service instance by using the following command to enter a mysql shell.

    $ mysql -u USERNAME -p -h 0 -P 63306 -D service_instance_db
    

    • USERNAME is the “username” property from your service-key
    • 63306 is the available port you configured in the ssh tunnel on your local machine
    • You will be prompted for a password after entering the previous command which is the “password” property from your service-key
  4. Exit the mysql shell and kill the ssh tunnel

  5. Bind the application to the new service instance

    $ cf bind-service my-app my-new-instance
    Binding service my-new-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
    

  6. Restage the app to restart the app with the new changes

    $ cf restage my-app
    Restaging app my-app in org my-org / space my-space as user@example.com...
    [...]
    

  7. Your application is now using your new MySQL for Pivotal Cloud Foundry v2 database and should be operational again.

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