Migrating Data in MySQL for PCF
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:
- Creates a new MySQL for PCF v2.x service instance in the target space.
- 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:
Run the following command:
cf install-plugin -r CF-Community "MysqlTools"
Ensure the plugin has installed successfully by running the following command:
cf mysql-tools version
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:
Do the procedure in Share Service Instances.
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:
- 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.
- 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.
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 databasename
: Name of source databaseusername
andpassword
: Database account credentialsport
: Typically3306
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 thecf 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:
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 thebound apps
column.- 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. Do the following for each app bound to the instance:
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
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
- Use the cf CLI to target and log in to your PCF deployment.
For example:
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 underp.mysql
.
- To view available MySQL for PCF v2 service plans, run
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:
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:
- Push Your Host App
- Create Your Service Key
- Configure Your SSH Tunnel
- Access Your Service Instance
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.
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:
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
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