Migrating database is never an easy task, I've recently migrated 29 databases from 5 different mysql instances into 3 AWS RDS mysql instances with the help of AWS Database Migration Service. I will record all the pain points I've met in this series.
The first problem I met is that DMS only migrate data + primary key. for all the other database objects (view/trigger/store procedure/function) , DMS does not migrate them automatically, I have to migrate them manually. for an old database instance, it's hard to find the the up to date DDL/DML, the best way is to extract these database objects from live database.
There is no way to like show create table statement to show how to create index, so I have to construct my own SQL to extract these type of information
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` ADD ',
IF(NON_UNIQUE = 1,
CASE UPPER(INDEX_TYPE)
WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
ELSE CONCAT('INDEX `',INDEX_NAME,'` USING ',INDEX_TYPE)
END,
CONCAT('UNIQUE INDEX `',INDEX_NAME,'` USING ',INDEX_TYPE)
),
' (',GROUP_CONCAT(DISTINCT CONCAT('`',COLUMN_NAME,'`') ORDER BY SEQ_IN_INDEX ASC SEPARATOR ','),')'
) as show_add_indexes
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA='<database_name>' AND INDEX_NAME <> 'PRIMARY'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME ASC, INDEX_NAME ASC;
To check if there is any view available for given database, we could use the following command to check
SELECT TABLE_NAME,TABLE_TYPE,ENGINE,VERSION
FROM information_schema.tables
WHERE table_schema='<database_name>';
then we could use SHOW CREATE VIEW <database_name>.<view_name> \G to extract the create view DDL
To check if there is any trigger for given database, we could use the following command to check
show triggers from <database_name> \G
then we could use SHOW CREATE TRIGGER <database_name>.<trigger_name> to extract the create trigger DDL
To check store procedures and functions we could use
SHOW PROCEDURE STATUS WHERE Db = '<database_name>' \G
SHOW FUNCTION STATUS WHERE Db = '<database_name>' \G
After we collect all the database object we could start the DB migration, it's recommeded to do full data load first, then pause and apply these DDL, then resume the CDC(ongoing replication) process until we swich all the application to target database.