#Migrate MySQL-PXC to MySQL-MGR

This guide provides comprehensive instructions for migrating from MySQL-PXC (Percona XtraDB Cluster) 5.7 to MySQL Group Replication (MGR) 8.0 on Alauda Container Platform.

#Background

#The Challenge

MySQL 5.7 reached End of Life (EOL) in October 2023, and MySQL-PXC is deprecated and removed starting from Alauda Database Service for MySQL v4.3.0. Organizations must migrate to MySQL 8.0 to continue receiving security updates and to leverage new features.

Migrating production databases involves complex considerations:

• Schema compatibility with MySQL 8.0 reserved keywords
• Character set and collation review
• Authentication plugin updates
• Ensuring data integrity during migration

#The Solution

This guide provides comprehensive, verified instructions for migrating MySQL-PXC 5.7 to MySQL-MGR 8.0:

• Proven Approach: Validated on ACP v4.0+ using Alauda Database Service for MySQL
• Complete Object Coverage: Migrates all standard MySQL objects (tables, views, routines, triggers, events, users, grants)
• Schema Compatibility: Automated checks and fixes for MySQL 8.0 compatibility issues
• Comprehensive Verification: Verification across 9 object categories
• Minimal Risk: Detailed rollback procedures and validation at each step

#Environment Information

#PXC vs MGR: Key Differences

#Common Use Cases

#Prerequisites

Before performing migration, ensure you have:

• ACP v4.0 or later with MySQL Operator v4.0 or later
• A healthy MySQL-PXC 5.7 cluster as source (GTID is not required on the source; @@gtid_mode may be OFF)
• Permission and capacity to create a new MySQL-MGR 8.0 cluster as the target (Step 3)
• Storage capacity of 2-3x the source database size
• Network connectivity from your local machine to both clusters

#Important Limitations

• Application downtime is required during export and import to ensure consistency
• Recommended maximum database size: 200GB (larger databases may require alternative approaches)
• The target cluster must be created before the data export in Step 4
• Storage performance on target should match or exceed the source

#Getting Started

#1. Set Variables

Every command in this guide uses the variables below. Run them first, and run the rest of the guide in the same terminal. If you open a new terminal, set them again.

Find the name and namespace of your PXC source:

kubectl get mysql -A

Set the source variables, and choose a name for the target that you will create in Step 3. Replace the example values:

# PXC 5.7 source
SOURCE_NAME="source"
SOURCE_NAMESPACE="your-namespace"
SOURCE_MYSQL_PASSWORD=$(kubectl get secret ${SOURCE_NAME} -n ${SOURCE_NAMESPACE} -o jsonpath='{.data.root}' | base64 -d)

# MGR 8.0 target
TARGET_NAME="mysql-8-target"
TARGET_NAMESPACE="your-namespace"

You set TARGET_MYSQL_PASSWORD in Step 3, after the target exists, and DATABASES in Step 4.

#2. Verify Source Status

kubectl get mysql ${SOURCE_NAME} -n ${SOURCE_NAMESPACE}
# Expected: STATE = ready, PXCSTATE = ready

kubectl get pod -n ${SOURCE_NAMESPACE} | grep ${SOURCE_NAME}
# Example output: source-pxc-0, source-pxc-1, source-pxc-2

#3. Run a Command on the Source

The source takes no container specifier:

kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
  mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -e "SELECT 1;"

The target needs -c mysql, as shown in Step 3.

#Execution Guide

#Step 1: Schema Compatibility Analysis

Perform this analysis one week before the planned migration.

Run the following commands to detect schema compatibility issues:

# Check for reserved keyword columns
kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
  mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -e "
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
    FROM information_schema.COLUMNS
    WHERE LOWER(COLUMN_NAME) IN ('rank','groups','function','tables','indexes','procedure','file','process');
  "

# Check for ZEROFILL columns
kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
  mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -e "
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE
    FROM information_schema.COLUMNS
    WHERE COLUMN_TYPE LIKE '%ZEROFILL%';
  "

# Check for invalid date defaults
kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
  mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -e "
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT
    FROM information_schema.COLUMNS
    WHERE COLUMN_DEFAULT = '0000-00-00'
       OR COLUMN_DEFAULT = '0000-00-00 00:00:00';
  "

#Fix Schema Issues

These are examples. Replace the database, table and column names with the ones the checks found.

# Fix reserved keyword columns (example)
kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
  mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -e "
    USE db1;
    ALTER TABLE users CHANGE COLUMN rank user_rank INT;
  "

# Fix ZEROFILL columns (example)
kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
  mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -e "
    USE db1;
    ALTER TABLE products MODIFY COLUMN price DECIMAL(10,2);
  "

#Step 2: Character Set and Collation Analysis

Converting to utf8mb4 is optional. The dump keeps each table's character set and collation, so latin1 and utf8 tables can be migrated as they are. Convert only if the application needs utf8mb4, for example to store emoji. Do not convert a database that belongs to a third-party application, such as Nacos, unless its vendor supports the change.

Check for non-utf8mb4 tables:

kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
  mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -e "
    SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema','sys')
      AND TABLE_COLLATION NOT LIKE 'utf8mb4%';
  "

Keep this output. You need the original collations if you have to revert a conversion.

#Optional: Convert to utf8mb4

Before converting, check for these two problems:

• Case-sensitive collations: a _bin collation treats App.yaml and app.yaml as different values, but utf8mb4_unicode_ci does not. Converting fails with ERROR 1062 if both values exist. If they do not, the conversion succeeds and the unique index then rejects values that differ only by case. The commands below skip tables with a _bin table collation.
• Long indexes: utf8mb4 uses up to 4 bytes per character, and the index limit is 3072 bytes across all columns of the index (767 bytes per column for the COMPACT and REDUNDANT row formats).

This query lists the indexes that would exceed 3072 bytes. It estimates non-character columns at 8 bytes each:

kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
  mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -e "
    SELECT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME,
           IF(MAX(s.NON_UNIQUE) = 0, 'UNIQUE', 'NON-UNIQUE') AS kind,
           SUM(IF(c.CHARACTER_SET_NAME IS NULL, 8,
                  COALESCE(s.SUB_PART, c.CHARACTER_MAXIMUM_LENGTH) * 4)) AS utf8mb4_bytes
    FROM information_schema.STATISTICS s
    JOIN information_schema.COLUMNS c
      ON c.TABLE_SCHEMA = s.TABLE_SCHEMA AND c.TABLE_NAME = s.TABLE_NAME
     AND c.COLUMN_NAME = s.COLUMN_NAME
    WHERE s.TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema','sys')
      AND s.INDEX_TYPE NOT IN ('FULLTEXT','SPATIAL')
    GROUP BY s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
    HAVING utf8mb4_bytes > 3072;
  "

A table listed by this query fails to convert with ERROR 1071. Leave it as it is unless you can shorten its columns. See the "Specified key was too long" issue under Common Issues and Solutions.

To convert, list only the databases you have decided to convert. ALTER TABLE rebuilds each table, and on PXC it blocks writes across the cluster by default while it runs, so use a maintenance window.

CONVERT_DATABASES="db1 db2"

# Set the default for new tables
for db in ${CONVERT_DATABASES}; do
  kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
    mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -e "
      ALTER DATABASE ${db} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
    "
done

# Convert existing tables, skipping utf8mb4 and _bin tables
for db in ${CONVERT_DATABASES}; do
  TABLES=$(kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
    mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -N -e "
      SELECT TABLE_NAME FROM information_schema.TABLES
      WHERE TABLE_SCHEMA = '${db}' AND TABLE_TYPE = 'BASE TABLE'
        AND TABLE_COLLATION NOT LIKE 'utf8mb4%'
        AND TABLE_COLLATION NOT LIKE '%\_bin';
    ")

  for table in ${TABLES}; do
    echo "Converting ${db}.${table}..."
    kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
      mysql -uroot -p${SOURCE_MYSQL_PASSWORD} ${db} -e "
        ALTER TABLE ${table} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
      " || echo "FAILED: ${db}.${table}"
  done
done

#Step 3: Create Target MySQL-MGR 8.0 Instance

Create the target MySQL-MGR 8.0 instance shortly before the data migration phase.

Using Web Console:

1. Select MySQL version 8.0
2. Configure resources (recommend +10-20% memory over source cluster due to MySQL 8.0 overhead)
3. Set storage size to 2-3x source database size

Using Command Line:

STORAGE_SIZE="500Gi"

cat << EOF | kubectl -n ${TARGET_NAMESPACE} apply -f -
apiVersion: middleware.alauda.io/v1
kind: Mysql
metadata:
  name: ${TARGET_NAME}
  namespace: ${TARGET_NAMESPACE}
  labels:
    mysql/arch: mgr
spec:
  mgr:
    enableStorage: true
    image: {}
    members: 3
    monitor:
      enable: true
      exporter: {}
    resources:
      server:
        limits:
          cpu: "2"
          memory: 4Gi
        requests:
          cpu: "2"
          memory: 4Gi
    router:
      replicas: 1
      resources:
        limits:
          cpu: 500m
          memory: 512Mi
        requests:
          cpu: 500m
          memory: 512Mi
      svcRO:
        type: ClusterIP
      svcRW:
        type: ClusterIP
    strictSecurityModeEnabled: true
    upgradeOption: {}
    volumeClaimTemplate:
      metadata: {}
      spec:
        accessModes:
        - ReadWriteOnce
        resources:
          requests:
            storage: ${STORAGE_SIZE}
        storageClassName: dataservice-topolvmsc
      status: {}
  params:
    mysql: {}
    router:
      DEFAULT:
        max_total_connections: "200"
      logger:
        level: info
  upgradeOption:
    autoUpgrade: false
  version: "8.0"
EOF

Verify target cluster:

kubectl -n ${TARGET_NAMESPACE} get mysql ${TARGET_NAME} -w
# Expected: STATE = Ready, MGRSTATE = ready
# Press Ctrl+C to stop watching

Set the target password:

TARGET_MYSQL_PASSWORD=$(kubectl get secret mgr-${TARGET_NAME}-password -n ${TARGET_NAMESPACE} -o jsonpath='{.data.root}' | base64 -d)

kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
  mysql -uroot -p${TARGET_MYSQL_PASSWORD} -e "SELECT 1;"

#Step 4: Migrate Data, Users, and Privileges

Procedure:

1. Stop Application Writes: Scale the application to zero replicas:

   kubectl scale deployment <app-name> --replicas=0 -n <app-namespace>

2. Identify Databases to Migrate:

   # List user databases (DO NOT include: information_schema, mysql, performance_schema, sys)
   kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
     mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -N -e "
       SELECT SCHEMA_NAME FROM information_schema.SCHEMATA
       WHERE SCHEMA_NAME NOT IN ('information_schema','mysql','performance_schema','sys');
     "

   Set DATABASES to the ones you are migrating, separated by spaces:

   DATABASES="db1 db2 db3"

3. Export and Import Data:

   for db in ${DATABASES}; do
     echo "Migrating database: ${db}..."
   
     if (
       set -o pipefail
   
       # Export from PXC source
       kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
         mysqldump -uroot -p${SOURCE_MYSQL_PASSWORD} \
           --single-transaction \
           --quick \
           --lock-tables=false \
           --set-gtid-purged=OFF \
           --routines \
           --events \
           --triggers \
           --databases ${db} \
         | kubectl exec -i ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
           mysql -uroot -p${TARGET_MYSQL_PASSWORD}
     ); then
       echo "Migrated: ${db}"
     else
       echo "FAILED: ${db}"
     fi
   done

   If any database prints FAILED, read the error above it and fix the problem before you continue. Migrated only means that the commands reported no error. A broken connection can still leave a table short of rows, so Step 5 compares the data.

   Keep set-gtid-purged OFF

   Do not change this flag to ON or AUTO:

   • On a source with @@gtid_mode = OFF (common on legacy PXC instances), --set-gtid-purged=ON makes mysqldump print Error: Server has GTIDs disabled. and still exit with status 0. A 5.7 mysqldump prints it after exporting the data, so the import is complete despite the message. An 8.0 mysqldump prints it before any data, so nothing is imported while the pipeline reports no failure and the loop prints Migrated. Either way, a clean run of the loop does not prove that data was migrated.
   • On a source with GTID enabled, ON/AUTO prepend SET @@SESSION.SQL_LOG_BIN= 0; to the dump. Applied on the MGR primary, that keeps the whole import out of the binary log, so the other group members never receive the data. All members still show ONLINE, and the first later write to that data makes the secondaries fail and leave the group. The operator may then rebuild them by clone from the primary, which also copies any other data that exists only on the primary.

   This migration is a logical dump and restore with no replication channel, so the source's GTID state is not needed on the target.

4. Migrate Users and Privileges:

   # List non-system users from PXC source
   USERS=$(kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
     mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -N -e "
       SELECT CONCAT('''', User, '''@''', Host, '''')
       FROM mysql.user
       WHERE User NOT IN ('root','mysql.sys','mysql.session','mysql.infoschema',
                          'monitor','operator','clustercheck','xtrabackup','replication')
         AND User NOT LIKE 'mysql.%'
         AND Host != 'localhost';
     ")
   
   # For each user, create user and apply grants on target
   for user_host in ${USERS}; do
     echo "Migrating user: ${user_host}..."
   
     # Get CREATE USER statement (includes auth plugin and password hash)
     CREATEUSER=$(kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
       mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -N -e "SHOW CREATE USER ${user_host};")
   
     # Create user on target
     kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
       mysql -uroot -p${TARGET_MYSQL_PASSWORD} -e "${CREATEUSER};"
   
     # Get GRANT statements from source
     GRANTS=$(kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
       mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -N -e "SHOW GRANTS FOR ${user_host};")
   
     # Apply grants on target
     echo "${GRANTS}" | while read grant; do
       kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
         mysql -uroot -p${TARGET_MYSQL_PASSWORD} -e "${grant};"
     done
   done
   
   # Flush privileges on target
   kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
     mysql -uroot -p${TARGET_MYSQL_PASSWORD} -e "FLUSH PRIVILEGES;"
   TIP

   SHOW CREATE USER preserves the original authentication plugin and password hash, so users can log in with their existing passwords after migration. If you need to switch to mysql_native_password for client compatibility, run:

   kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
     mysql -uroot -p${TARGET_MYSQL_PASSWORD} -e "
       ALTER USER '<user>'@'<host>' IDENTIFIED WITH mysql_native_password BY '<password>';
     "

#Step 5: Verify Migration

for db in ${DATABASES}; do
  echo "=== Verifying database: ${db} ==="

  # Compare table counts
  echo "Tables:"
  kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
    mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -N -e "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = '${db}' AND TABLE_TYPE = 'BASE TABLE';"

  kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
    mysql -uroot -p${TARGET_MYSQL_PASSWORD} -N -e "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = '${db}' AND TABLE_TYPE = 'BASE TABLE';"

  # Verify views execute correctly
  kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
    mysql -uroot -p${TARGET_MYSQL_PASSWORD} -N -e "
      SELECT TABLE_NAME FROM information_schema.VIEWS WHERE TABLE_SCHEMA = '${db}';
    " | while read view; do
      kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
        mysql -uroot -p${TARGET_MYSQL_PASSWORD} ${db} -e "SELECT 1 FROM ${view} LIMIT 1;" > /dev/null 2>&1 \
        && echo "✓ View ${view} OK" \
        || echo "✗ View ${view} FAILED"
    done
done

Compare row counts on every member. A broken connection can leave a table short of rows without any error, so check every table:

for db in ${DATABASES}; do
  TABLES=$(kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
    mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -N -e "
      SELECT TABLE_NAME FROM information_schema.TABLES
      WHERE TABLE_SCHEMA = '${db}' AND TABLE_TYPE = 'BASE TABLE';
    ")

  for table in ${TABLES}; do
    SOURCE_COUNT=$(kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
      mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -N -e "SELECT COUNT(*) FROM \`${db}\`.\`${table}\`;")

    # Adjust the member numbers if your target has a different size
    for member in 0 1 2; do
      TARGET_COUNT=$(kubectl exec ${TARGET_NAME}-${member} -n ${TARGET_NAMESPACE} -c mysql -- \
        mysql -uroot -p${TARGET_MYSQL_PASSWORD} -N -e "SELECT COUNT(*) FROM \`${db}\`.\`${table}\`;")

      if [ "${SOURCE_COUNT}" = "${TARGET_COUNT}" ]; then
        echo "OK: ${db}.${table} on ${TARGET_NAME}-${member}: ${TARGET_COUNT}"
      else
        echo "MISMATCH: ${db}.${table} on ${TARGET_NAME}-${member}: source ${SOURCE_COUNT}, target ${TARGET_COUNT}"
      fi
    done
  done
done

Every table should print OK on every member. A member may briefly lag behind, so run the comparison again before you act on a single MISMATCH.

• Low or missing on every member: the import did not finish. See "Network Interruption During Migration" under Common Issues and Solutions.
• Present on the primary but missing on a secondary: the import was applied with binary logging disabled (see the warning under Step 4). Keep the application stopped and re-run the import with --set-gtid-purged=OFF, which repairs the secondaries as long as nothing has written to that data yet. After such a write the secondaries leave the group. Check whether the operator has rebuilt them: all members ONLINE with matching counts. If not, recreate the target instance and repeat the migration.

Verification Checklist:

• Same number of tables in each database
• Same row counts per table on every member
• Same number of views
• All views execute successfully
• All stored procedures and functions exist
• All triggers and events migrated
• All user accounts exist
• All grants migrated

#Step 6: Post-Migration Optimization

#1. Update Table Statistics

for db in ${DATABASES}; do
  echo "Analyzing tables in ${db}..."
  TABLES=$(kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
    mysql -uroot -p${TARGET_MYSQL_PASSWORD} -N -e "
      SELECT TABLE_NAME FROM information_schema.TABLES
      WHERE TABLE_SCHEMA = '${db}' AND TABLE_TYPE = 'BASE TABLE';
    ")

  for table in ${TABLES}; do
    kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
      mysql -uroot -p${TARGET_MYSQL_PASSWORD} ${db} -e "ANALYZE TABLE ${table};" 2>&1 | grep -v "Table"
  done
done

#2. Check Fragmentation

# Build properly-quoted comma-separated list
DB_LIST=$(echo $DATABASES | sed "s/ /','/g")
DB_LIST="'$DB_LIST'"

kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
  mysql -uroot -p${TARGET_MYSQL_PASSWORD} -e "
    SELECT TABLE_SCHEMA, TABLE_NAME,
           ROUND(DATA_FREE / 1024 / 1024, 2) AS 'Fragmentation (MB)'
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA IN ($DB_LIST)
      AND DATA_FREE > 0
    ORDER BY DATA_FREE DESC;
  "

If significant fragmentation found (>100MB), rebuild tables:

OPTIMIZE TABLE db1.orders;

#Application Cutover

#Step 7: Switch Application to Target

#1. Verify Application is Stopped

# Ensure application is scaled down
kubectl scale deployment <app-name> --replicas=0 -n <app-namespace>

# Verify no active connections on source
kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
  mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -e "SHOW PROCESSLIST;" | grep -v "Sleep"

#2. Update Application Connection String

# Update ConfigMap or environment variables
kubectl patch configmap <app-config> -n <app-namespace> --type=json \
  -p='[{"op": "replace", "path": "/data/database-host", "value":"'${TARGET_NAME}'-read-write.'${TARGET_NAMESPACE}'.svc.cluster.local"}]'

kubectl patch configmap <app-config> -n <app-namespace> --type=json \
  -p='[{"op": "replace", "path": "/data/database-port", "value":"3306"}]'

#3. Restart Application

# Scale up application
kubectl scale deployment <app-name> --replicas=<original-replica-count> -n <app-namespace>

# Wait for pods to be ready
kubectl -n <app-namespace> rollout status deployment <app-name>

#4. Verify Application Functionality

# Test database connectivity
kubectl exec <app-pod> -n <app-namespace> -- \
  mysql -h ${TARGET_NAME}-read-write.${TARGET_NAMESPACE}.svc.cluster.local \
    -uroot -p${TARGET_MYSQL_PASSWORD} -e "SELECT 1 AS test;"

# Check application logs for errors
kubectl logs -n <app-namespace> <app-pod> --tail=100 | grep -i error

#Monitoring

Monitor the migrated instance for 24-48 hours:

# Check MySQL 8.0 instance health
kubectl -n ${TARGET_NAMESPACE} get mysql ${TARGET_NAME} -w

# Monitor error logs
kubectl logs -n ${TARGET_NAMESPACE} ${TARGET_NAME}-0 -c mysql --tail=100 -f

#Disaster Recovery

#Rollback Plan

If critical issues are discovered after the cutover:

# 1. Stop application
kubectl scale deployment <app-name> --replicas=0 -n <app-namespace>

# 2. Update connection string back to source
kubectl patch configmap <app-config> -n <app-namespace> --type=json \
  -p='[{"op": "replace", "path": "/data/database-host", "value":"'${SOURCE_NAME}'-proxysql.'${SOURCE_NAMESPACE}'.svc.cluster.local"}]'

# 3. Restart application
kubectl scale deployment <app-name> --replicas=<original-replica-count> -n <app-namespace>

# 4. Verify connectivity
kubectl exec <app-pod> -n <app-namespace> -- \
  mysql -h ${SOURCE_NAME}-proxysql.${SOURCE_NAMESPACE}.svc.cluster.local \
    -uroot -p${SOURCE_MYSQL_PASSWORD} -e "SELECT 1 AS test;"

#Common Issues and Solutions

These commands use the variables from Getting Started. Where a command uses ${db} or ${table}, first set them to the affected database and table, for example db="db1".

#Issue: GTID_PURGED Error

Symptoms:

ERROR 3911 (HY000) at line XX: Cannot update GTID_PURGED with the Group Replication plugin running

Solution: The dump was produced with --set-gtid-purged=ON or AUTO. Re-run the export with --set-gtid-purged=OFF as shown in Step 4, which emits no GTID_PURGED statement.

#Issue: Server has GTIDs disabled

Symptoms:

Error: Server has GTIDs disabled.

mysqldump still exits with status 0. With a 5.7 mysqldump the message appears after the data has been exported and the import is complete. With an 8.0 mysqldump the dump contains no data, so this message may be the only sign of the failure. Check the target to see which applies.

Solution: The source has @@gtid_mode = OFF and the dump was started with --set-gtid-purged=ON. Use --set-gtid-purged=OFF as shown in Step 4. Do not enable GTID on the source for this migration; it is not needed.

#Issue: Character Set Conversion Errors

Symptoms:

ERROR 1366 (HY000): Incorrect string value

Solution:

kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
  mysql -uroot -p${TARGET_MYSQL_PASSWORD} -e "
    ALTER DATABASE ${db} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
  "

#Issue: Specified key was too long

Symptoms:

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

Solution: The table has an index that does not fit in utf8mb4. The table is unchanged and can be migrated as it is, so the simplest fix is not to convert it. If the database belongs to a third-party application, such as older Nacos schemas with the config_info_aggr table, also revert any of its tables that were already converted. See the "Case-sensitive values are rejected" issue below.

If the table must use utf8mb4, first check that the data fits in shorter columns with SELECT MAX(CHAR_LENGTH(column_name)) FROM table_name;, then reduce the column lengths and convert again. Do not shorten a UNIQUE index with a prefix such as column_name(191), because values that differ only after the prefix then count as duplicates.

#Issue: Case-sensitive values are rejected

Symptoms:

ERROR 1062 (23000): Duplicate entry 'app.yaml-...' for key '...'

The error appears during CONVERT TO, or afterwards when the application writes a value that differs from an existing one only by case.

Solution: The table had a case-sensitive _bin collation and was converted to utf8mb4_unicode_ci. Convert it back to the original character set and collation from the Step 2 output, and restore the database default. Replace utf8 and utf8_bin if the originals were different:

kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
  mysql -uroot -p${SOURCE_MYSQL_PASSWORD} ${db} -e "
    ALTER TABLE ${table} CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
    ALTER DATABASE ${db} CHARACTER SET = utf8 COLLATE = utf8_bin;
  "

Any 4-byte characters written since the conversion do not fit in utf8, so check for them and take a backup first. TEXT columns remain MEDIUMTEXT after the revert, which is harmless.

#Issue: Network Interruption During Migration

Symptoms: The connection drops during Step 4, the loop prints FAILED, or Step 5 prints MISMATCH.

A broken stream can look like a successful import. If the stream stops between statements, or at a point that still forms valid SQL, mysql exits with status 0 and the table is missing or short of rows. Always run the row count comparison in Step 5, even if every database printed Migrated.

Solution, before the application writes to the target:

1. Keep the application stopped, so the source does not change.
2. When the network is back, check that the source is healthy and that all target members are ONLINE:

   kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
     mysql -uroot -p${TARGET_MYSQL_PASSWORD} -e "
       SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE
       FROM performance_schema.replication_group_members;
     "
   The primary may have changed. The commands in this guide import through ${TARGET_NAME}-0, so if it is no longer the primary, use the pod that is.
3. Set DATABASES to the databases that did not finish or that Step 5 reported, then re-run the export and import loop in Step 4. You do not need to drop anything first, because the dump drops and recreates each table, view, routine and event that it contains.
4. Run Step 5 again. Continue only when every table prints OK.

If the interruption happened while migrating users, re-run that step as well. ERROR 1396 for a user that already exists is expected, but confirm that the other users and all grants are present.

If the interruption happened during a schema fix or a utf8mb4 conversion on the source, check the state of that table and re-run the statement if it did not apply.

Solution, after the application has written to the target:

Do not re-run the dump, because it replaces the tables and removes those writes. Fix the network and continue on the target, or follow the Rollback Plan under Disaster Recovery. Rolling back loses anything written to the target since the cutover.

To reduce the risk:

• Run the migration from a host close to the cluster, inside tmux or screen, so that a dropped session does not stop it.
• Rehearse in staging to learn how long the stream runs. A proxy or load balancer in front of the Kubernetes API server may close long kubectl exec sessions.
• For large databases, list one database in DATABASES at a time, so that a failure costs less to repeat.

#Issue: DEFINER Privilege Errors

Symptoms:

ERROR 1449 (HY000): The user specified as a definer ('user'@'host') does not exist

Solution:

# Find all objects with missing definers
kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
  mysql -uroot -p${TARGET_MYSQL_PASSWORD} -e "
    SELECT DISTINCT DEFINER
    FROM information_schema.VIEWS
    WHERE TABLE_SCHEMA = '${db}';
  "

#Issue: Authentication Plugin Errors

Symptoms:

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded

Solution:

kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
  mysql -uroot -p${TARGET_MYSQL_PASSWORD} -e "
    ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
    FLUSH PRIVILEGES;
  "

#Troubleshooting

#Diagnostic Commands

Check Migration Progress:

kubectl exec ${TARGET_NAME}-0 -n ${TARGET_NAMESPACE} -c mysql -- \
  mysql -uroot -p${TARGET_MYSQL_PASSWORD} -e "SHOW PROCESSLIST;"

kubectl exec ${SOURCE_NAME}-pxc-0 -n ${SOURCE_NAMESPACE} -- \
  mysql -uroot -p${SOURCE_MYSQL_PASSWORD} -e "SHOW PROCESSLIST;"

Verify Data Integrity:

Use the row count comparison in Step 5. Do not compare TABLE_ROWS from information_schema.TABLES, because it is only an estimate for InnoDB tables.

Check MySQL 8.0 Error Logs:

kubectl logs -n ${TARGET_NAMESPACE} ${TARGET_NAME}-0 -c mysql --tail=100 -f | grep -i error

#Best Practices

#Pre-Migration Planning

• Test in Staging: Always perform a test migration in non-production first
• Schema Cleanup: Fix all schema compatibility issues before production migration
• Character Set Migration: If you choose to convert to utf8mb4, do it well in advance (3-5 days before). Do not convert third-party application databases
• Backup Strategy: Ensure recent backups are available before migration
• Maintenance Window: Schedule adequate downtime based on database size

#During Migration

• Stop Application Writes: Ensure no writes during export/import for consistency
• Monitor Progress: Track export/import progress at regular intervals
• Keep Source Running: Don't delete source until migration is verified

#Post-Migration

• Comprehensive Testing: Thoroughly test application functionality
• Performance Monitoring: Monitor query performance for 24-48 hours
• Keep Source for Rollback: Maintain source cluster for 24-48 hours for rollback window
• Update Documentation: Update connection strings and monitoring dashboards

#Reference

#Size vs Time Estimates

#mysqldump Flags Reference

#Useful Links

• Create MySQL-MGR Instance
• MySQL 8.0 Release Notes
• MySQL 8.0 Upgrade Guide