Cloud Migration: MySQL utf8mb4 Migration Run Book

Published Date: October 7, 2024
Audience: Everyone
Products Applicable:

Jama Connect®
Self-hosted
MySQL utf8mb4

Summary

Before migrating to the cloud, self-hosted customers must ensure their database collation is updated to utf8mb4. This runbook outlines the steps to safely check and migrate the database collation using the provided scripts.

Resolution

Step 1: Prepare the Environment

Ensure you have the following information:

Tenant database name
MySQL username
MySQL password

The migration scripts are located at:

/utils/mysqlcluster/collation_migration

Scripts included:

mysqldb_utf8mb4_collation_update.sh
sp_rename_dup_user.sql

Step 2: Review Available Script Options

Run the following command to view input parameters and available options:

./mysqldb_utf8mb4_collation_update.sh -h

Options include:

-t name → Tenant ID/database name
-u user → MySQL username
-p password → MySQL password
-d yes/no → Dry run (default: yes)
-h → Help

Step 3: Perform a Dry Run

Run the dry run to preview required changes:

./mysqldb_utf8mb4_collation_update.sh -t jamatenant1234 -u myuser -p mypassword

Expected Output Example:

Dry run: yes
jamatenant1234 has 1648 non-utf8mb4 items that need to be fixed
Dry Run - DB character set and collation remain unchanged

The dry run allows you to safely see what changes would occur without modifying the database.

Step 4: Execute Migration

Run the migration by specifying -d no to apply changes:

./mysqldb_utf8mb4_collation_update.sh -t jamatenant1234 -u myuser -p mypassword -d no

Log File Generated:

mysqldb_utf8mb4_migration.log

Step 5: Verify Migration Results

Check the log file to confirm migration success.

Successful Example for Tables:

jamatenant1234 table collation migration is successful!
All columns have been converted to utf8mb4 and Collation utf8mb4_0900_ai_ci

Successful Example for Routines/Functions/Procedures:

jamatenantXXXX routines collation migration is successful!
All procedures/functions converted to UTF-8 MB4 and Collation UTF-8 MB4_0900_ai_ci

Step 6: Handle Large Databases / Background Execution

For large databases, the migration may take hours. Use nohup to run in the background:

nohup ./mysqldb_utf8mb4_collation_update.sh -t jamatenant1234 -u myuser -p mypassword -d no &

This ensures the process continues even if the terminal session ends. The log file will still be generated.

Step 7: Resolve Duplicate Usernames

If duplicate usernames exist:

Dry Run Output Example:

2 duplicate user names are found! Need to fix ...
+-----------------+-----------------+
| userName        | duplicate_count |
+-----------------+-----------------+
| user1@abc.com   | 2               |
| user2@abc.com   | 2               |
+-----------------+-----------------+

Notes:

If both duplicate accounts are active, manually rename one before migration.
If duplicate_count > 2, manually rename extra accounts.

Additional Resources

Feedback:
We welcome your input! Please sign in to leave any comments, suggestions, or ideas for improvement below.

Related to