Migrating from MySQL for VMs to VMware SQL with MySQL for Kubernetes

Overview

This topic describes how to migrate data from VMware Tanzu SQL with MySQL for VMs on Tanzu Application Service (TAS) for VMs to VMware SQL with MySQL for Kubernetes.

The migration process involves backing up the MySQL for VMs instances using the utility mysqldump, and restoring them under MySQL for Kubernetes.

Note: Currently MySQL for VMs supports MySQL 5.7, while MySQL for Kubernetes supports MySQL 8

Prerequisites

Before following the migration steps, you must:

Refer to the changes relating to MySQL 8. For details refer to What Is New in MySQL 8.0.
Have a local machine with the following utilities installed, and ensure you have familiarity with these utilities:
- mysqldump 5.7, to connect to MySQL service instances externally, take a logical backup, and store the backup. Refer to How to install & run mysqldump for an example installation procedure. Install the utility relevant to your local machine operating system.
- mysql 8, to connect to MySQL on Kubernetes externally, and load the backup.
- The Cloud Foundry Command-Line Interface (cf CLI). See Installing the cf CLI.
Have the correct credentials to access the TAS service instances you will be migrating.
Review which schemas you wish to migrate. The default schema created for MySQL for VMs service instances is named service_instance_db. For details on schemas and databases to backup and restore, refer to Create a Tanzu SQL for VMs Logical Backup.
Review and complete the prerequisites for Creating and Deleting MySQL Instances in Kubernetes.

Backup MySQL for VM instances

Review the following important points before starting the backup process: - The MySQL service instance must be configured for external access (either using service gateway or CF SSH). - mysqldump 5.7 should be used against a MySQL service instance. - Take care to backup routines, triggers, events, and other objects as needed. - You may wish to shut down applications connecting to the MySQL service instance that may continue to change the data.

For each MySQL for VMs service instance, use mysqldump to create a logical backup. For details on how to manually create a logical backup with mysqldump, refer to create a logical backup

List existing users

Log in to TAS, and list the service keys for the MySQL service instance:
```
cf service-keys <SERVICE_INSTANCE_NAME>
```
where:
- SERVICE_INSTANCE_NAME is the name of the MySQL service instance

Fetch the database, username, and password for each service key:

cf service-key <SERVICE_INSTANCE_NAME> <SERVICE_KEY_NAME> | tail -n +3 | jq '{database: .name, username: .username, password: .password}'

where:

SERVICE_INSTANCE_NAME is the name of the MySQL service instance
SERVICE_KEY_NAME is the name of the MySQL service key

An example output could be:

cf service-key test-single-node key | tail -n +3 | jq '{database: .name, username: .username, password: .password}'
{
  "database": "service_instance_db",
  "username": "c67a09469fd349b5ac600893dff4cee0",
  "password": "apv1wll5zzv470w1"
}

Note which users have read-only access instead of the default write access.

Create MySQL on Kubernetes instances

Create the MySQL instances. For details, refer to Creating and Deleting MySQL Instances.

Create existing users

Access the mysql container on the Kubernetes cluster:
```
kubectl exec -it <POD_NAME> -c mysql -- bash
```
where:
- POD_NAME is the name of the MySQL pod
Log in as root in order to create objects as needed:
```
mysql -u root -p$(cat $MYSQL_ROOT_PASSWORD_FILE)
```

Create all necessary databases, and a user for each:

CREATE DATABASE <DATABASE_NAME>;
CREATE USER '<USERNAME>'@'%' IDENTIFIED BY '<PASSWORD>';
GRANT ALL PRIVILEGES ON <DATABASE_NAME>.* TO '<USERNAME>'@'%';
FLUSH PRIVILEGES;

where:

DATABASE_NAME is the name of the database
USERNAME is the mysql username
PASSWORD is the password for the user

Example:

CREATE DATABASE service_instance_db;
CREATE USER 'c67a09469fd349b5ac600893dff4cee0'@'%' IDENTIFIED BY 'apv1wll5zzv470w1';
GRANT ALL PRIVILEGES ON service_instance_db.* to 'c67a09469fd349b5ac600893dff4cee0'@'%';
FLUSH PRIVILEGES;

For more information on creating a user for the associated database refer to Create a Database and Privileged MySQL User for the App.

Use a mysql 8.0 client and ensure the MySQL server can be accessed, and you can connect with the newly created user.

Load the backup

There are several methods to connect to the MySQL server in order to load the backup:

Change the MySQL Kubernetes service to use an external IP. See Access the MySQL Server from an External IP Address. Once the restore is done, remember to remove the external access.
Use Port Forwarding to access the MySQL pod from a local port.
Use kubectl copy to copy the backup onto a pod that has the mysql 8 client. This method also requires kubectl exec permissions to get a shell for running a container on that pod.
If the backup is too large to send over a local port through the Kubernetes API server or copy onto a pod, the backup could be stored on cloud storage and streamed to mysql client.

Connect to the MySQL instance and load the backup file for each database, using the appropriate credentials:

mysql -h <HOSTNAME> -u <USERNAME> -P 3306 -p<PASSWORD> < <BACKUP_FILE>

where:

HOSTNAME is the address of the MySQL service
USERNAME is the mysql username
PASSWORD is the password for the user
BACKUP_FILE is the backup taken for the specific database

Example:

mysql -h a4dc8de1biefe13112-17761231.us-west-2.elb.amazonaws.com -u 'c67a09469fd349b5ac600893dff4cee0' -P 3306 -papv1wll5zzv470w1 < dump.sql

When the restore completes, confirm that the schemas and data are loaded correctly, and remove external access to the MySQL instances.