今、お仕事でとあるIaaSからAWSへのインフラ移行をしていて、DMS(Database Migration Service)を使っててめちゃ便利で最高なんですけど、これインデックスやらプライマリーキーに設定されているAUTO_INCREMENT
属性やらもろもろ移行してくれないものがあります。
ただし、 AWS DMS はターゲットデータベース内にセカンダリインデックス、外部キー、ユーザーアカウントなどを自動的に作成しません。
列の AUTO_INCREMENT 属性は、ターゲットデータベース列に移行されません。
というわけでもろもろの属性を移行するためのSQLを書いたので覚書のため記載しておきます。
{SCHEMA_NAME}
は対象のDBスキーマに読み替えてください。
Index移行用SQL
SELECT CONCAT( "ALTER TABLE ", t1.table_name, " ADD INDEX ", t1.index_name, "(", ( SELECT GROUP_CONCAT(DISTINCT t2.column_name SEPARATOR ', ') FROM information_schema.statistics AS t2 WHERE t1.index_name = t2.index_name AND t1.table_name = t2.table_name ORDER BY t2.table_name, t2.index_name, t2.seq_in_index ), ");" ) AS alter_table_add_index_query FROM information_schema.statistics AS t1 WHERE table_schema = "{SCHEMA_NAME}" AND index_name != "PRIMARY" GROUP BY t1.table_name, t1.index_name ;
プライマリーキーのAUTO_INCREMENT属性移行用SQL
SELECT CONCAT( "ALTER TABLE ", TABLE_NAME, " CHANGE ", COLUMN_NAME, " ", COLUMN_NAME, " ", COLUMN_TYPE, " AUTO_INCREMENT;" ) as alter_auto_increment FROM information_schema.columns WHERE table_schema = "{SCHEMA_NAME}" and is_nullable = "NO" and column_key = "PRI" and extra = "auto_increment" ;
おわりに
あとはそれぞれ生成したALTER文を移行先のDBで流せばOKです! 動作責任は負いかねますが、どなたかの助けになれば嬉しいです!
おまけ
外部キーを貼っているテーブルに関する調査もしたのでその際に確認したコマンドも記載しておきます。
外部キーを貼っているテーブルの調査
select * from information_schema.table_constraints where table_schema = "{SCHEMA_NAME}" and constraint_type = "FOREIGN KEY";
特定のテーブルへの外部キーを貼っているテーブルの調査
select distinct(referenced_table_name) from information_schema.key_column_usage where constraint_schema = "{SCHEMA_NAME}";