You can use data migration in VMware Tanzu SQL with MySQL for VMs.
Read this topic before you do the procedures in Migrating Data in Tanzu SQL for VMs.
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 Tanzu SQL for VMs 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 Tanzu SQL for VMs v2 service instance, see Using TLS.
During data migration, the migrate command:
- Creates a new Tanzu SQL for VMs v2 service instance in the destination space with the same name as the source MySQL service instance name.
- Copies the source data over to the new service instance.
- Appends
-oldto the source service instance name, regardless if the source is a Tanzu SQL for VMs 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 |
|
| Leader-Follower |
|
| HA Cluster |
|
| Multi-Site |
|
| Off-Platform Database * | Tanzu SQL for VMs v2 |
| Availability Zone (AZ) | Another AZ |
| MySQL for Pivotal Cloud Foundry v1 | Tanzu SQL for VMs 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 Support](https://www.vmware.com/support/services.html).
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. Tanzu SQL for VMs uses it to authenticate users.performance_schema: This is metadata about performance and server execution.sys: This is a schema that summarizesperformance_schema.
The sys, performance_schema, and information_schema schemas change dynamically with changes on a Tanzu SQL for VMs 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.
