DB Migration Checklist
Introduction
This document serves as a comprehensive checklist for migrating a MySQL database, specifically OvalEdgeDB, to a new environment or server. The checklist outlines all the necessary steps and queries required to ensure a smooth and successful migration process. It covers tasks such as checking VM size, stopping the Tomcat service, taking backups, checking table collations, creating the database and user in the new environment, and verifying the migration results.
Migration Checklist
Check VM Size
Verify the RAM and HDD size of the virtual machine (VM) where the MySQL database resides.
Check MySQL DB Location
Determine if the MySQL database is located locally on VM1.
Stop Tomcat
Stop the Tomcat service to prevent new entries from hitting the database during migration.
Take Table Row Count
Execute a SQL query to retrieve the row count of all tables and capture a screenshot for reference.
QUERY:
Use infromation_schema;
select TABLE_NAME as "TableName", 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') test;
https://ovaledge.s3.us-west-1.amazonaws.com/queries/All_Tables_RowCount.sql
Take DB Size
Execute a SQL query to determine the size of the OvalEdgeDB MySQL database.
QUERY:
SELECT table_schema "ovaledgedb",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;
Check Table Collations
Verify if all tables are using in utf8mb4_unicode_ci collation using the below query:
QUERY :
SELECT TABLE_SCHEMA , TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES where TABLE_COLLATION != 'utf8mb4_unicode_ci' ;
If the tables are not in utf8mb4_unicode_ci we need to alter the TABLE_COLLATION column with utf8mb4_unicode_ci
QUERY :
ALTER TABLE deepanalysisjobparams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Take DB Backup
Take a backup of the MySQL database, ensuring that the Tomcat service is stopped during the backup process.
Install MySQL 8.0.X
Install MySQL 8.0.X on another server and create the OvalEdgeDB database and user using SQL queries.
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'@'%';
Note : If you want a user that must have super privileges then use the below query .
GRANT SYSTEM_USER ON *.* TO 'user_name'@'localhost';
GRANT SYSTEM_USER ON *.* TO 'user_name'@'%';
Restore Database
Restore the MySQL 5.7 dump into the MySQL 8.0.X database and verify the database size and row count.
Verify Migration
Once MySQL 8.0.X is running smoothly, restart the Tomcat service and ensure proper connectivity to the database.