俺でもわかるDMSを実際にぶっ放す前に確認しておきたいtips -MySQL to MySQL編-

俺です。

DMSは便利ですが制約がいくつかあります。
Aurora MySQLをSource/Targetとしてチェックした時のtips俺カンペです。
あれどーなんだっけな？ってときにテストするのだるい方見てどうぞ。
MySQLをソースとした時に受ける制約はドキュメント覗いてどうぞ
MySQL以外のRDBMSはそれぞれドキュメント覗いてどうぞ。

外部参照キーとトリガーはDMSぶっ放す前に、呼吸と同時に止めておくことは、昔体で覚えて本能に刻み込んだので実機チェックしてません。

テストしたSource/Targetのエンジンバージョン

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| aurora_version          | 1.17.8                       |
| innodb_version          | 1.2.10                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.10-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)

テーブル作成のtips

Autoincrement属性

• source

mysql> create table profile_test  ( id int not null  auto_increment, profile_text varchar(255),primary key(id))
mysql> desc profile_test;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| profile_text | varchar(255) | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

• target

ターゲットにautoincrementは反映されないので予めCREATE TABLEするか、ALTER TABLEでauto_incrementを追加する。

mysql> desc profile_test;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | NO   | PRI | NULL    |       |
| profile_text | varchar(255) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE profile_test CHANGE id id int auto_increment;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc profile_test;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | NO   | PRI | NULL    |       |
| profile_text | varchar(255) | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

• log

2019-03-19T14:47:15 [SOURCE_CAPTURE ]I: set_columns_orig_type for toiret.profile_test (mysql_endpoint_metadata.c:539)
2019-03-19T14:47:15 [TARGET_APPLY ]I: Column 'id' from toiret'.'profile_test' was created as not nullable, part of primary key (provider_syntax_manager.c:1267)

Index操作のtips

Indexの追加

Index追加はレプリケーション追従されない。

SourceデータベースにIndexを作りたいけどデータ量が多くて
CREATE INDEXたたけないときはターゲット側で空テーブルにIndex作っておいて、DMSレプリケーション使えば良い

• source

mysql> ALTER TABLE profile_test ADD COLUMN profile_id int;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> CREATE INDEX idx_profile_id ON profile_test(profile_id);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC profile_test;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| profile_text | varchar(255) | YES  |     | NULL    |                |
| profile_id   | int(11)      | YES  | MUL | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)
mysql> show index from profile_test;
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| profile_test |          0 | PRIMARY        |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| profile_test |          1 | idx_profile_id |            1 | profile_id  | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

• target

Index作成は追従されないので明示的に作らないとダメ

mysql> DESC profile_test;
+--------------+--------------+------+-----+---------+-------+
| Field        | Type         | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| id           | int(11)      | NO   | PRI | NULL    |       |
| profile_text | varchar(255) | YES  |     | NULL    |       |
| profile_id   | int(11)      | YES  |     | NULL    |       |
+--------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> show index from profile_test;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| profile_test |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)
mysql>  CREATE INDEX idx_profile_id ON profile_test(profile_id);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from profile_test;
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| profile_test |          0 | PRIMARY        |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| profile_test |          1 | idx_profile_id |            1 | profile_id  | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

• log

出力なし

Indexの削除

mysql> DROP INDEX idx_profile_id ON profile_test;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

列操作のtips

列追加

ALTER TABLE toiret.profile_test ADD COLUMN test datetime;

• log

2019-03-19T14:07:19 [TARGET_APPLY ]I: Alter table. Action: 3, statement: ALTER TABLE `toiret`.`profile_test` ADD COLUMN `test` DATETIME(0) (odbc_endpoint_imp.c:5588)

列削除

ALTER TABLE toiret.profile_test DROP COLUMN test;

2019-03-19T14:38:34 [TARGET_APPLY ]I: Alter table. Action: 2, statement: ALTER TABLE `toiret`.`profile_test` DROP COLUMN `test` (odbc_endpoint_imp.c:5588)

列制約操作

ソースデータベース上で制約を変更してもターゲットへは反映されない。

• ALTER文

ALTER TABLE toiret.profile_test MODIFY COLUMN test datetime NOT NULL;

• log

2019-03-19T14:07:54 [TARGET_APPLY ]I: Alter table. Action: 3, statement: ALTER TABLE `toiret`.`profile_test` ADD COLUMN `test` DATETIME(0) (odbc_endpoint_imp.c:5588)

source/targetで列制約が異なる場合

• source

Nullableな列にINSERTする

INSERT INTO toiret.profile_test (id) VALUES (14487);
SELECT id,test FROM toiret.profile_test WHERE id = 14487;
+-------+--------------+
| id    | test         |
+-------+--------------+
| 14487 | NULL         |
+-------+--------------+
1 row in set (0.01 sec)

• target

Not Nullの列にはDefault値がsetされる

SELECT id,test FROM toiret.profile_test WHERE id = 14487;
+-------+---------------------+
| id    | test                |
+-------+---------------------+
| 14487 | 0000-00-00 00:00:00 |
+-------+---------------------+
1 row in set (0.01 sec)

DML

ターゲットへ先に重複するデータを挿入したときのレプリケーション挙動

ターゲットへ先につっこまれたデータが残る。
ソースデータはレプリケーションされない。

• target

mysql> INSERT INTO profile_test VALUES(3, "aaaあ");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM profile_test;
+----+--------------+
| id | profile_text |
+----+--------------+
|  1 | aaaあ        |
|  2 | bbbいあ      |
|  3 | aaaあ        |
+----+--------------+
3 rows in set (0.01 sec)

• source

mysql> INSERT INTO profile_test VALUES(1, "aaaあ");
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO profile_test VALUES(2, "bbbいあ");
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO profile_test (profile_text)VALUES("bbbう");
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM profile_test;
+----+--------------+
| id | profile_text |
+----+--------------+
|  1 | aaaあ        |
|  2 | bbbいあ      |
|  3 | bbbう        |
+----+--------------+
3 rows in set (0.01 sec)

• target

先にtargetにInsertしたデータが残る。

mysql> SELECT * FROM profile_test;
+----+--------------+
| id | profile_text |
+----+--------------+
|  1 | aaaあ        |
|  2 | bbbいあ      |
|  3 | aaaあ        |
+----+--------------+
3 rows in set (0.00 sec)

• log

CloudWatch Logsには特に残らない(Logオプションが悪かったかな)

DELETE

レプリケーション追従される

• source

mysql> DELETE FROM profile_test WHERE id < 3;
Query OK, 2 rows affected (0.01 sec)
mysql> SELECT * FROM profile_test;
+----+--------------+
| id | profile_text |
+----+--------------+
|  3 | bbbう        |
+----+--------------+
1 row in set (0.00 sec)

• target

mysql> SELECT * FROM profile_test;
+----+--------------+
| id | profile_text |
+----+--------------+
|  3 | aaaあ        |
+----+--------------+
1 row in set (0.00 sec)

TRUNCATE

レプリケーション追従される

• source

mysql> TRUNCATE TABLE profile_test;
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT * FROM profile_test;
Empty set (0.00 sec)
mysql> DESC profile_test;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| profile_text | varchar(255) | YES  |     | NULL    |                |
| profile_id   | int(11)      | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

• target

mysql> SELECT * FROM profile_test;
Empty set (0.01 sec)
mysql> DESC profile_test;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| profile_text | varchar(255) | YES  |     | NULL    |                |
| profile_id   | int(11)      | NO   | MUL | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)