About data migration in VMware SQL with MySQL for Tanzu Application Service

This topic explains how data migration works in VMware SQL with MySQL for Tanzu Application Service.

Read this topic before you do the procedures in Migrating Data in VMware Tanzu for MySQL.

The migrate command

You can use the mysql-tools CLI plug-in to migrate MySQL data with the migrate command. The migrate command does a streaming mysqldump and restore to migrate data from your source MySQL database to a destination VMware Tanzu for MySQL v2 instance.

The command supports connections over TLS. If TLS is configured in the source and destination MySQL instances, the data is securely streamed using TLS. For information about how to configure TLS in a VMware Tanzu for MySQL v2 service instance, see Using TLS.

During data migration, the migrate command:

1. Creates a new VMware Tanzu for MySQL v2 service instance in the destination space with the same name as the source MySQL service instance name.
2. Copies the source data over to the new service instance.
3. Appends -old to the source service instance name, regardless if the source is a VMware Tanzu for MySQL service instance or a user-provided service instance.

Use cases

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

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.

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 Multi-Site
Leader-Follower	HA Cluster Multi-Site
HA Cluster	Leader-Follower Single Node
Multi-Site	Single Node Leader-Follower
Off-Platform Database *	VMware Tanzu for MySQL v2
Availability Zone (AZ)	Another AZ
MySQL for Pivotal Cloud Foundry v1	VMware Tanzu for MySQL v2

* If your off-platform database has encryption at rest or the Percona PAM Authentication plug-in 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. For example, if you are migrating a database to a larger single node or leader-follower plan, 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, see 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 the same or larger size
Leader-Follower	Single Node of the same or larger size

Omitted data

Important

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 VMware Tanzu Support.

The migrate command skips the following system schemas:

• cf_metadata: This is metadata for binding information, such as database users mapped to GUIDs.
• information_schema: This is metadata for the MySQL database.
• mysql: This is a MySQL database that describes users, user accounts, and permissions. VMware Tanzu for MySQL uses it to authenticate users.
• performance_schema: This is metadata about performance and server execution.
• sys: This is a schema that summarizes performance_schema.

sys, performance_schema, and information_schema change dynamically with changes on a VMware Tanzu for MySQL service instance. These schemas are not migrated because the destination database has its' own version of the schemas. You do not need to back up these schemas.