Export Table Definitions from the source database
- Import Table Structures to the destination database
- Alter table Definitions in the destination database to use InnoDB storage engine and utf8mb4 characters set [1]
- Assign primary key to the tables those do not have one [2] [3]
- Export Table Data from the source database
- Import Table Data to the destination database
[1] SELECT CONCAT('ALTER TABLE ',TABLE_NAME,'
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;')
FROM INFORMATION_SCHEMA.TABLES
WHERE ENGINE='MyISAM' AND table_schema = 'database_name';
(Capture the output in the file ALTER_TABLE_DEFS.SQL)
SOURCE @ALTER_TABLE_DEFS.SQL
Example : alter table tbl1 engine=innodb default charset=utf8mb4;
[2] SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS k
ON (t.TABLE_NAME = k.TABLE_NAME
AND k.CONSTRAINT_SCHEMA = t.TABLE_SCHEMA
AND k.constraint_name = 'PRIMARY')
WHERE t.TABLE_SCHEMA NOT IN
('information_schema', 'performance_schema', 'mysql', 'sys')
AND t.TABLE_SCHEMA = '<database name>'
AND k.constraint_name IS NULL;
[3] ALTER TABLE ‘table_name’ ADD PRIMARY KEY ‘column_name’;
Recommended Readings :
- https://dev.mysql.com/doc/refman/8.0/en/mysqldump-definition-data-dumps.html
- https://dev.mysql.com/doc/refman/8.0/en/converting-tables-to-innodb.html
