DB Migration Checklist

This document provides a step-by-step checklist for migrating the MySQL database, OvalEdgeDB, to a new environment or server. It includes all essential tasks, from environment preparation and service shutdown to backup, validation, and restoration. The checklist is designed to ensure a smooth and successful migration with minimal downtime and data integrity risk.

Pre-Migration Checklist

1. Verify Virtual Machine (VM) Specifications

   • Confirm the RAM and hard disk (HDD) capacity of the source VM hosting the MySQL database.

2. Check Database Location

   • Ensure that the MySQL database is hosted locally on the source VM (VM1).

3. Stop Tomcat Service

   • Stop the Tomcat service to prevent application access and database changes during the migration.

Capture Current Database Metrics

Capture Table Row Counts

• Run the following SQL query to retrieve row counts and table sizes. Save the output or take a screenshot for post-migration comparison.

  USE information_schema;
  
  SELECT 
      TABLE_NAME AS "Table Name", 
      TABLE_ROWS AS "Row Count", 
      ROUND((COALESCE(DATA_LENGTH, 0) + COALESCE(INDEX_LENGTH, 0)) / 1024 / 1024) AS "Size (MB)"
  FROM tables
  WHERE table_schema = 'YOUR_DB_NAME'
  
  UNION ALL
  
  SELECT 
      "Total Count", 
      SUM(RowCount), 
      SUM(Size)
  FROM (
      SELECT 
          TABLE_NAME AS "Table Name", 
          TABLE_ROWS AS "RowCount", 
          ROUND((COALESCE(DATA_LENGTH, 0) + COALESCE(INDEX_LENGTH, 0)) / 1024 / 1024) AS "Size"
      FROM tables
      WHERE table_schema = 'YOUR_DB_NAME'
  ) summary;

📄 Reference SQL Script: All_Tables_RowCount.sql

Capture Database Size

• Run the following query to check the total size of the database:

  SELECT 
      table_schema AS "Database Name", 
      ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS "DB Size (MB)"
  FROM information_schema.tables 
  GROUP BY table_schema;

Verify Table Collation

• Ensure all tables use the utf8mb4_unicode_ci collation.

• Check Collation:

  SELECT 
      TABLE_SCHEMA, 
      TABLE_NAME, 
      TABLE_COLLATION 
  FROM information_schema.tables 
  WHERE TABLE_COLLATION != 'utf8mb4_unicode_ci';

• Update Collation (if needed):

  SELECT 
      TABLE_SCHEMA, 
      TABLE_NAME, 
      TABLE_COLLATION 
  FROM information_schema.tables 
  WHERE TABLE_COLLATION != 'utf8mb4_unicode_ci';

👉 Replace <table_name> with the actual table name that requires conversion.

Database Backup and Restoration

Database Backup

• Perform a full logical backup of the MySQL database using mysqldump or equivalent.

• Ensure the Tomcat service remains stopped during this process to maintain data consistency.

Target Environment Setup

Install MySQL 8.4.x

• Set up MySQL 8.4.x on the target server.

Create Database and User

• Execute the following SQL queries to create the target database and user with appropriate privileges:

  CREATE DATABASE ovaledgedb 
  CHARACTER SET utf8mb4 
  COLLATE utf8mb4_unicode_ci;
  
  CREATE USER 'ovaledge'@'localhost' IDENTIFIED BY '0valEdge!';
  CREATE USER 'ovaledge'@'%' IDENTIFIED BY '0valEdge!';
  
  GRANT ALL ON ovaledgedb.* TO 'ovaledge'@'localhost';
  GRANT ALL ON ovaledgedb.* TO 'ovaledge'@'%'; 

• To grant super privileges (if applicable):

  GRANT SYSTEM_USER ON *.* TO 'user_name'@'localhost';
  GRANT SYSTEM_USER ON *.* TO 'user_name'@'%';

Database Restoration and Validation

Restore Database

• Restore the MySQL 8.0.4 dump into the new MySQL 8.4.x environment.

Post-Restoration Validation

• Run the row count and database size queries again.

• Compare the results with the pre-migration values to ensure accuracy.

Post-Migration Tasks

Restart the Tomcat Service

Restart the Tomcat service after confirming that:

• The restored database is running properly.

• All data has been successfully migrated.

• Application connectivity is functional.

Conclusion

This checklist ensures that the MySQL database migration is performed with minimal risk and verified integrity. All steps must be followed sequentially, and each action should be documented with appropriate evidence (logs, screenshots, and outputs) for audit and rollback planning.

---

Copyright © 2025, OvalEdge LLC, Peachtree Corners, GA, USA.