チョットワカル Row-Based Replication・その４
sejimaこんにちわ。せじまです。
今回も replication の話をします。
はじめに
第四回です。
前回、 Row-Based Replication では Column の名前を意識していないことを確認しました。今回は Row-Based Replication で online schema change できないか、そのへん試してみましょう。
まずは厳重注意
これから記述する内容を本番環境で実施する場合、リスクをともないます。まずは検証環境で試しましょう。
いちおうまとめ
- master の方が Column が少なかった場合、 slave にしか存在していない Column の振る舞いは、 Worklog 5092 で規定されているようなものでした。デフォルトの値で更新されます。
- よって、 slave でtableの末尾に add column する程度なら動く気がします。公式ドキュメントにもそのような記述があります。16.4.1.10 Replication with Differing Table Definitions on Master and Slave
- ただ、 stored な generated column は、Row-Based Replication では binlog event で更新すべきようで、 slave で table の末尾に stored な generated column 追加しても、更新のたびに評価されるわけではないようです。
- RBRでは「式を評価するのは、式が発行されたmasterでのみ行う」って考え方なんでしょうね。
でははじめます
master/slave でこのような table があります
master
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
mysql> show create table takanori\G *************************** 1. row *************************** Table: takanori Create Table: CREATE TABLE `takanori` ( `id` int(11) NOT NULL AUTO_INCREMENT, `val1` int(11) NOT NULL, `val2` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 1 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 1 | 1 | 10 | | 2 | 100 | 1000 | | 3 | 1 | 24000 | | 4 | 100 | 2000 | | 5 | 1 | 10 | | 6 | 100 | 100 | +----+------+-------+ 6 rows in set (0.00 sec) mysql> |
slave
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 |
mysql> show create table takanori\G *************************** 1. row *************************** Table: takanori Create Table: CREATE TABLE `takanori` ( `id` int(11) NOT NULL AUTO_INCREMENT, `val1` int(11) NOT NULL, `val2` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 2 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 1 | 1 | 10 | | 2 | 100 | 1000 | | 3 | 1 | 24000 | | 4 | 100 | 2000 | | 5 | 1 | 10 | | 6 | 100 | 100 | +----+------+-------+ 6 rows in set (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.18.0.2 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: binlog.000029 Read_Master_Log_Pos: 155 Relay_Log_File: 8230d2822d2f-relay-bin.000012 Relay_Log_Pos: 363 Relay_Master_Log_File: binlog.000029 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 155 Relay_Log_Space: 622 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 385a832a-ef37-11e9-a332-0242ac120002 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) mysql> |
slave に add column して INSERT
slave で末尾に add column します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
mysql> alter table takanori add column val30 int not null; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table takanori\G *************************** 1. row *************************** Table: takanori Create Table: CREATE TABLE `takanori` ( `id` int(11) NOT NULL AUTO_INCREMENT, `val1` int(11) NOT NULL, `val2` int(11) NOT NULL, `val30` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 2 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+-------+ | id | val1 | val2 | val30 | +----+------+-------+-------+ | 1 | 1 | 10 | 0 | | 2 | 100 | 1000 | 0 | | 3 | 1 | 24000 | 0 | | 4 | 100 | 2000 | 0 | | 5 | 1 | 10 | 0 | | 6 | 100 | 100 | 0 | +----+------+-------+-------+ 6 rows in set (0.00 sec) mysql> |
master でカラム名指定で INSERT します
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
mysql> insert into takanori (val1,val2) values (2,20),(300,3000); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 1 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 1 | 1 | 10 | | 2 | 100 | 1000 | | 3 | 1 | 24000 | | 4 | 100 | 2000 | | 5 | 1 | 10 | | 6 | 100 | 100 | | 7 | 2 | 20 | | 8 | 300 | 3000 | +----+------+-------+ 8 rows in set (0.00 sec) mysql> |
slaveにreplicationできます。masterに存在しないColumnには、デフォルトの値が入りました。この振る舞いは、WL#5092のDEFAULT VALUESの節に書かれているとおりですね。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 2 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+-------+ | id | val1 | val2 | val30 | +----+------+-------+-------+ | 1 | 1 | 10 | 0 | | 2 | 100 | 1000 | 0 | | 3 | 1 | 24000 | 0 | | 4 | 100 | 2000 | 0 | | 5 | 1 | 10 | 0 | | 6 | 100 | 100 | 0 | | 7 | 2 | 20 | 0 | | 8 | 300 | 3000 | 0 | +----+------+-------+-------+ 8 rows in set (0.00 sec) mysql> |
（slave に add column した状態で）UPDATE
update し、 replication することもできます。 RBR での update は、「何番目のColumnを更新するか」を指定しているだけなので
master
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
mysql> update takanori set val2=val2+2000 where id=3; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 1 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 1 | 1 | 10 | | 2 | 100 | 1000 | | 3 | 1 | 26000 | | 4 | 100 | 2000 | | 5 | 1 | 10 | | 6 | 100 | 100 | | 7 | 2 | 20 | | 8 | 300 | 3000 | +----+------+-------+ 8 rows in set (0.00 sec) mysql> |
slave
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 2 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+-------+ | id | val1 | val2 | val30 | +----+------+-------+-------+ | 1 | 1 | 10 | 0 | | 2 | 100 | 1000 | 0 | | 3 | 1 | 26000 | 0 | | 4 | 100 | 2000 | 0 | | 5 | 1 | 10 | 0 | | 6 | 100 | 100 | 0 | | 7 | 2 | 20 | 0 | | 8 | 300 | 3000 | 0 | +----+------+-------+-------+ 8 rows in set (0.00 sec) mysql> |
slave に Generated column 追加して INSERT や UPDATE
最近のMySQLは Generated column 使えるので、試しに slaveで Generated column 使ってみます 13.1.8.2 ALTER TABLE and Generated Columns
slave
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
mysql> alter table takanori drop column val30; Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE takanori ADD COLUMN val30 INT GENERATED ALWAYS AS (val1 + val2) STORED NOT NULL; Query OK, 8 rows affected (0.08 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> show create table takanori\G *************************** 1. row *************************** Table: takanori Create Table: CREATE TABLE `takanori` ( `id` int(11) NOT NULL AUTO_INCREMENT, `val1` int(11) NOT NULL, `val2` int(11) NOT NULL, `val30` int(11) GENERATED ALWAYS AS ((`val1` + `val2`)) STORED NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 2 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+-------+ | id | val1 | val2 | val30 | +----+------+-------+-------+ | 1 | 1 | 10 | 11 | | 2 | 100 | 1000 | 1100 | | 3 | 1 | 26000 | 26001 | | 4 | 100 | 2000 | 2100 | | 5 | 1 | 10 | 11 | | 6 | 100 | 100 | 200 | | 7 | 2 | 20 | 22 | | 8 | 300 | 3000 | 3300 | +----+------+-------+-------+ 8 rows in set (0.00 sec) mysql> |
master から insert したり update したりします
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
mysql> insert into takanori (val1,val2) values (2,20),(300,3000); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 1 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 1 | 1 | 10 | | 2 | 100 | 1000 | | 3 | 1 | 26000 | | 4 | 100 | 2000 | | 5 | 1 | 10 | | 6 | 100 | 100 | | 7 | 2 | 20 | | 8 | 300 | 3000 | | 9 | 2 | 20 | | 10 | 300 | 3000 | +----+------+-------+ 10 rows in set (0.00 sec) mysql> |
slave
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 2 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+-------+ | id | val1 | val2 | val30 | +----+------+-------+-------+ | 1 | 1 | 10 | 11 | | 2 | 100 | 1000 | 1100 | | 3 | 1 | 26000 | 26001 | | 4 | 100 | 2000 | 2100 | | 5 | 1 | 10 | 11 | | 6 | 100 | 100 | 200 | | 7 | 2 | 20 | 22 | | 8 | 300 | 3000 | 3300 | | 9 | 2 | 20 | 0 | | 10 | 300 | 3000 | 0 | +----+------+-------+-------+ 10 rows in set (0.00 sec) mysql> |
master で update
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> update takanori set val2=val2+2000 where id=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update takanori set val2=val2+2000 where id=8; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select @@server_id,@@version;select * from takanori where id in (3,8); +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 1 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 3 | 1 | 28000 | | 8 | 300 | 5000 | +----+------+-------+ 2 rows in set (0.00 sec) mysql> |
slave
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> select @@server_id,@@version;select * from takanori where id in (3,8); +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 2 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+-------+ | id | val1 | val2 | val30 | +----+------+-------+-------+ | 3 | 1 | 28000 | 26001 | | 8 | 300 | 5000 | 3300 | +----+------+-------+-------+ 2 rows in set (0.00 sec) mysql> |
なるほど。Generated columnでは不整合が発生しました。
master で Generated column 追加して INSERT や UPDATE
STORED な generated column への更新は、RBRでは binlog の event として master から降ってこないといけないようですね。
slave で drop column したのち、 master から add column して試してみます。
slave
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
mysql> alter table takanori drop column val30; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 2 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 1 | 1 | 10 | | 2 | 100 | 1000 | | 3 | 1 | 28000 | | 4 | 100 | 2000 | | 5 | 1 | 10 | | 6 | 100 | 100 | | 7 | 2 | 20 | | 8 | 300 | 5000 | | 9 | 2 | 20 | | 10 | 300 | 3000 | +----+------+-------+ 10 rows in set (0.00 sec) mysql> show create table takanori\G *************************** 1. row *************************** Table: takanori Create Table: CREATE TABLE `takanori` ( `id` int(11) NOT NULL AUTO_INCREMENT, `val1` int(11) NOT NULL, `val2` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> |
master
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
mysql> ALTER TABLE takanori ADD COLUMN val3 INT GENERATED ALWAYS AS (val1 + val2) STORED NOT NULL; Query OK, 10 rows affected (0.10 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> show create table takanori\G *************************** 1. row *************************** Table: takanori Create Table: CREATE TABLE `takanori` ( `id` int(11) NOT NULL AUTO_INCREMENT, `val1` int(11) NOT NULL, `val2` int(11) NOT NULL, `val3` int(11) GENERATED ALWAYS AS ((`val1` + `val2`)) STORED NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 1 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+-------+ | id | val1 | val2 | val3 | +----+------+-------+-------+ | 1 | 1 | 10 | 11 | | 2 | 100 | 1000 | 1100 | | 3 | 1 | 28000 | 28001 | | 4 | 100 | 2000 | 2100 | | 5 | 1 | 10 | 11 | | 6 | 100 | 100 | 200 | | 7 | 2 | 20 | 22 | | 8 | 300 | 5000 | 5300 | | 9 | 2 | 20 | 22 | | 10 | 300 | 3000 | 3300 | +----+------+-------+-------+ 10 rows in set (0.00 sec) mysql> |
slave
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
mysql> show create table takanori\G *************************** 1. row *************************** Table: takanori Create Table: CREATE TABLE `takanori` ( `id` int(11) NOT NULL AUTO_INCREMENT, `val1` int(11) NOT NULL, `val2` int(11) NOT NULL, `val3` int(11) GENERATED ALWAYS AS ((`val1` + `val2`)) STORED NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 2 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+-------+ | id | val1 | val2 | val3 | +----+------+-------+-------+ | 1 | 1 | 10 | 11 | | 2 | 100 | 1000 | 1100 | | 3 | 1 | 28000 | 28001 | | 4 | 100 | 2000 | 2100 | | 5 | 1 | 10 | 11 | | 6 | 100 | 100 | 200 | | 7 | 2 | 20 | 22 | | 8 | 300 | 5000 | 5300 | | 9 | 2 | 20 | 22 | | 10 | 300 | 3000 | 3300 | +----+------+-------+-------+ 10 rows in set (0.00 sec) mysql> |
binlog_row_image=full
master
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
mysql> insert into takanori (val1,val2) values (2,20),(300,3000); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> update takanori set val2=val2+2000 where id=3; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 1 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+-------+ | id | val1 | val2 | val3 | +----+------+-------+-------+ | 1 | 1 | 10 | 11 | | 2 | 100 | 1000 | 1100 | | 3 | 1 | 30000 | 30001 | | 4 | 100 | 2000 | 2100 | | 5 | 1 | 10 | 11 | | 6 | 100 | 100 | 200 | | 7 | 2 | 20 | 22 | | 8 | 300 | 5000 | 5300 | | 9 | 2 | 20 | 22 | | 10 | 300 | 3000 | 3300 | | 11 | 2 | 20 | 22 | | 12 | 300 | 3000 | 3300 | +----+------+-------+-------+ 12 rows in set (0.01 sec) mysql> |
slave
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 2 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+-------+ | id | val1 | val2 | val3 | +----+------+-------+-------+ | 1 | 1 | 10 | 11 | | 2 | 100 | 1000 | 1100 | | 3 | 1 | 30000 | 30001 | | 4 | 100 | 2000 | 2100 | | 5 | 1 | 10 | 11 | | 6 | 100 | 100 | 200 | | 7 | 2 | 20 | 22 | | 8 | 300 | 5000 | 5300 | | 9 | 2 | 20 | 22 | | 10 | 300 | 3000 | 3300 | | 11 | 2 | 20 | 22 | | 12 | 300 | 3000 | 3300 | +----+------+-------+-------+ 12 rows in set (0.00 sec) mysql> |
mysqlbinlog -vv で確認すると
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
$ ./mysqlbinlog /var/lib/mysql/binlog.000031 -vv /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #191112 6:41:27 server id 1 end_log_pos 124 CRC32 0x80b81408 Start: binlog v 4, server v 8.0.18 created 191112 6:41:27 # Warning: this binlog is either in use or was not closed properly. BINLOG ' F1TKXQ8BAAAAeAAAAHwAAAABAAQAOC4wLjE4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA CgEIFLiA '/*!*/; # at 124 #191112 6:41:27 server id 1 end_log_pos 155 CRC32 0xec3c007c Previous-GTIDs # [empty] # at 155 #191112 6:41:45 server id 1 end_log_pos 234 CRC32 0x82ecf8c4 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1573540905364290 immediate_commit_timestamp=1573540905364290 transaction_length=311 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1573540905364290 (2019-11-12 06:41:45.364290 UTC) # immediate_commit_timestamp=1573540905364290 (2019-11-12 06:41:45.364290 UTC) /*!80001 SET @@session.original_commit_timestamp=1573540905364290*//*!*/; /*!80014 SET @@session.original_server_version=80018*//*!*/; /*!80014 SET @@session.immediate_server_version=80018*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 234 #191112 6:41:45 server id 1 end_log_pos 309 CRC32 0x08695b67 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1573540905/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1168113696/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=255/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; BEGIN /*!*/; # at 309 #191112 6:41:45 server id 1 end_log_pos 366 CRC32 0x7491f9eb Table_map: `test`.`takanori` mapped to number 101 # at 366 #191112 6:41:45 server id 1 end_log_pos 435 CRC32 0x0f184969 Write_rows: table id 101 flags: STMT_END_F BINLOG ' KVTKXRMBAAAAOQAAAG4BAAAAAGUAAAAAAAEABHRlc3QACHRha2Fub3JpAAQDAwMDAAABAQDr+ZF0 KVTKXR4BAAAARQAAALMBAAAAAGUAAAAAAAEAAgAE/wALAAAAAgAAABQAAAAWAAAAAAwAAAAsAQAA uAsAAOQMAABpSRgP '/*!*/; ### INSERT INTO `test`.`takanori` ### SET ### @1=11 /* INT meta=0 nullable=0 is_null=0 */ ### @2=2 /* INT meta=0 nullable=0 is_null=0 */ ### @3=20 /* INT meta=0 nullable=0 is_null=0 */ ### @4=22 /* INT meta=0 nullable=0 is_null=0 */ ### INSERT INTO `test`.`takanori` ### SET ### @1=12 /* INT meta=0 nullable=0 is_null=0 */ ### @2=300 /* INT meta=0 nullable=0 is_null=0 */ ### @3=3000 /* INT meta=0 nullable=0 is_null=0 */ ### @4=3300 /* INT meta=0 nullable=0 is_null=0 */ # at 435 #191112 6:41:45 server id 1 end_log_pos 466 CRC32 0x499d20f0 Xid = 274 COMMIT/*!*/; # at 466 #191112 6:41:51 server id 1 end_log_pos 545 CRC32 0xf86e4c18 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes original_committed_timestamp=1573540911064593 immediate_commit_timestamp=1573540911064593 transaction_length=321 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1573540911064593 (2019-11-12 06:41:51.064593 UTC) # immediate_commit_timestamp=1573540911064593 (2019-11-12 06:41:51.064593 UTC) /*!80001 SET @@session.original_commit_timestamp=1573540911064593*//*!*/; /*!80014 SET @@session.original_server_version=80018*//*!*/; /*!80014 SET @@session.immediate_server_version=80018*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 545 #191112 6:41:51 server id 1 end_log_pos 629 CRC32 0x7ff0c37e Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1573540911/*!*/; BEGIN /*!*/; # at 629 #191112 6:41:51 server id 1 end_log_pos 686 CRC32 0xd52a4759 Table_map: `test`.`takanori` mapped to number 101 # at 686 #191112 6:41:51 server id 1 end_log_pos 756 CRC32 0x04dd11e1 Update_rows: table id 101 flags: STMT_END_F BINLOG ' L1TKXRMBAAAAOQAAAK4CAAAAAGUAAAAAAAEABHRlc3QACHRha2Fub3JpAAQDAwMDAAABAQBZRyrV L1TKXR8BAAAARgAAAPQCAAAAAGUAAAAAAAEAAgAE//8AAwAAAAEAAABgbQAAYW0AAAADAAAAAQAA ADB1AAAxdQAA4RHdBA== '/*!*/; ### UPDATE `test`.`takanori` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2=1 /* INT meta=0 nullable=0 is_null=0 */ ### @3=28000 /* INT meta=0 nullable=0 is_null=0 */ ### @4=28001 /* INT meta=0 nullable=0 is_null=0 */ ### SET ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2=1 /* INT meta=0 nullable=0 is_null=0 */ ### @3=30000 /* INT meta=0 nullable=0 is_null=0 */ ### @4=30001 /* INT meta=0 nullable=0 is_null=0 */ # at 756 #191112 6:41:51 server id 1 end_log_pos 787 CRC32 0x06f73187 Xid = 275 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; $ |
binlog_row_image=minimal
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 |
mysql> set session binlog_row_image=minimal; Query OK, 0 rows affected (0.00 sec) mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 1 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+-------+ | id | val1 | val2 | val3 | +----+------+-------+-------+ | 1 | 1 | 10 | 11 | | 2 | 100 | 1000 | 1100 | | 3 | 1 | 30000 | 30001 | | 4 | 100 | 2000 | 2100 | | 5 | 1 | 10 | 11 | | 6 | 100 | 100 | 200 | | 7 | 2 | 20 | 22 | | 8 | 300 | 5000 | 5300 | | 9 | 2 | 20 | 22 | | 10 | 300 | 3000 | 3300 | | 11 | 2 | 20 | 22 | | 12 | 300 | 3000 | 3300 | +----+------+-------+-------+ 12 rows in set (0.00 sec) mysql> insert into takanori (val1,val2) values (2,20),(300,3000); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> update takanori set val2=val2+2000 where id=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 1 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+-------+ | id | val1 | val2 | val3 | +----+------+-------+-------+ | 1 | 1 | 10 | 11 | | 2 | 100 | 1000 | 1100 | | 3 | 1 | 32000 | 32001 | | 4 | 100 | 2000 | 2100 | | 5 | 1 | 10 | 11 | | 6 | 100 | 100 | 200 | | 7 | 2 | 20 | 22 | | 8 | 300 | 5000 | 5300 | | 9 | 2 | 20 | 22 | | 10 | 300 | 3000 | 3300 | | 11 | 2 | 20 | 22 | | 12 | 300 | 3000 | 3300 | | 13 | 2 | 20 | 22 | | 14 | 300 | 3000 | 3300 | +----+------+-------+-------+ 14 rows in set (0.00 sec) mysql> |
slave
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
mysql> select @@server_id, @@version;select * from takanori; +-------------+-----------+ | @@server_id | @@version | +-------------+-----------+ | 2 | 8.0.18 | +-------------+-----------+ 1 row in set (0.00 sec) +----+------+-------+-------+ | id | val1 | val2 | val3 | +----+------+-------+-------+ | 1 | 1 | 10 | 11 | | 2 | 100 | 1000 | 1100 | | 3 | 1 | 32000 | 32001 | | 4 | 100 | 2000 | 2100 | | 5 | 1 | 10 | 11 | | 6 | 100 | 100 | 200 | | 7 | 2 | 20 | 22 | | 8 | 300 | 5000 | 5300 | | 9 | 2 | 20 | 22 | | 10 | 300 | 3000 | 3300 | | 11 | 2 | 20 | 22 | | 12 | 300 | 3000 | 3300 | | 13 | 2 | 20 | 22 | | 14 | 300 | 3000 | 3300 | +----+------+-------+-------+ 14 rows in set (0.00 sec) mysql> |
mysqlbinlog -vv は
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
$ ./mysqlbinlog /var/lib/mysql/binlog.000032 -vv /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #191112 6:45:07 server id 1 end_log_pos 124 CRC32 0x1fff9ca8 Start: binlog v 4, server v 8.0.18 created 191112 6:45:07 # Warning: this binlog is either in use or was not closed properly. BINLOG ' 81TKXQ8BAAAAeAAAAHwAAAABAAQAOC4wLjE4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA CgGonP8f '/*!*/; # at 124 #191112 6:45:07 server id 1 end_log_pos 155 CRC32 0x0d01c6ed Previous-GTIDs # [empty] # at 155 #191112 6:45:30 server id 1 end_log_pos 234 CRC32 0x732171d2 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1573541130135794 immediate_commit_timestamp=1573541130135794 transaction_length=311 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1573541130135794 (2019-11-12 06:45:30.135794 UTC) # immediate_commit_timestamp=1573541130135794 (2019-11-12 06:45:30.135794 UTC) /*!80001 SET @@session.original_commit_timestamp=1573541130135794*//*!*/; /*!80014 SET @@session.original_server_version=80018*//*!*/; /*!80014 SET @@session.immediate_server_version=80018*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 234 #191112 6:45:30 server id 1 end_log_pos 309 CRC32 0xa2aaa020 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1573541130/*!*/; SET @@session.pseudo_thread_id=9/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1168113696/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C latin1 *//*!*/; SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=255/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; BEGIN /*!*/; # at 309 #191112 6:45:30 server id 1 end_log_pos 366 CRC32 0x45dbe94c Table_map: `test`.`takanori` mapped to number 101 # at 366 #191112 6:45:30 server id 1 end_log_pos 435 CRC32 0x0a0a27af Write_rows: table id 101 flags: STMT_END_F BINLOG ' ClXKXRMBAAAAOQAAAG4BAAAAAGUAAAAAAAEABHRlc3QACHRha2Fub3JpAAQDAwMDAAABAQBM6dtF ClXKXR4BAAAARQAAALMBAAAAAGUAAAAAAAEAAgAEDwANAAAAAgAAABQAAAAWAAAAAA4AAAAsAQAA uAsAAOQMAACvJwoK '/*!*/; ### INSERT INTO `test`.`takanori` ### SET ### @1=13 /* INT meta=0 nullable=0 is_null=0 */ ### @2=2 /* INT meta=0 nullable=0 is_null=0 */ ### @3=20 /* INT meta=0 nullable=0 is_null=0 */ ### @4=22 /* INT meta=0 nullable=0 is_null=0 */ ### INSERT INTO `test`.`takanori` ### SET ### @1=14 /* INT meta=0 nullable=0 is_null=0 */ ### @2=300 /* INT meta=0 nullable=0 is_null=0 */ ### @3=3000 /* INT meta=0 nullable=0 is_null=0 */ ### @4=3300 /* INT meta=0 nullable=0 is_null=0 */ # at 435 #191112 6:45:30 server id 1 end_log_pos 466 CRC32 0xfd10bcdf Xid = 301 COMMIT/*!*/; # at 466 #191112 6:45:42 server id 1 end_log_pos 545 CRC32 0x430100c7 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes original_committed_timestamp=1573541142240435 immediate_commit_timestamp=1573541142240435 transaction_length=301 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1573541142240435 (2019-11-12 06:45:42.240435 UTC) # immediate_commit_timestamp=1573541142240435 (2019-11-12 06:45:42.240435 UTC) /*!80001 SET @@session.original_commit_timestamp=1573541142240435*//*!*/; /*!80014 SET @@session.original_server_version=80018*//*!*/; /*!80014 SET @@session.immediate_server_version=80018*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 545 #191112 6:45:42 server id 1 end_log_pos 629 CRC32 0xe20b65a1 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1573541142/*!*/; BEGIN /*!*/; # at 629 #191112 6:45:42 server id 1 end_log_pos 686 CRC32 0xd9b9a4e2 Table_map: `test`.`takanori` mapped to number 101 # at 686 #191112 6:45:42 server id 1 end_log_pos 736 CRC32 0xe7c66ed1 Update_rows: table id 101 flags: STMT_END_F BINLOG ' FlXKXRMBAAAAOQAAAK4CAAAAAGUAAAAAAAEABHRlc3QACHRha2Fub3JpAAQDAwMDAAABAQDipLnZ FlXKXR8BAAAAMgAAAOACAAAAAGUAAAAAAAEAAgAEAQwAAwAAAAAAfQAAAX0AANFuxuc= '/*!*/; ### UPDATE `test`.`takanori` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### SET ### @3=32000 /* INT meta=0 nullable=0 is_null=0 */ ### @4=32001 /* INT meta=0 nullable=0 is_null=0 */ # at 736 #191112 6:45:42 server id 1 end_log_pos 767 CRC32 0x497cc9eb Xid = 302 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; $ |
補足
WL#3915: Fewer columns on slave という WorkLog を見ると、力強く次のように書いてあり
It should be possible to replicate a table that has more columns on the master
than on the slave, i.e., there are M columns in the table on the master and N
columns in the table on the slave and M > N.When replicating in this manner, the contents of the extra columns on the master
is lost, but replication will accept the situation and keep running.
WorkLog 3259 は公開されていないようですが、 WL#3915 には次のようにも書いてあるので
This work is related to WL#3259 (RBR with more columns on slave than on master)
in that implementation of this worklog is necessary to support on-line table
definition changes (a.k.a., "schema upgrade") when using circular replication.
Row-Based Replication で、 master と slave の Column の数が一致していなくても replication できるべきだと、 MySQL の Replication teamは考えているようです。
個人的見解のような補足
ここでもう一度 WL#5092: RBR: Options for writing partial or full row images in RBR events を振り返ってみましょう。次のような記述がありました。
|
165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 |
NOTE ---- As a side remark, there was another option considered: - reversible means PKE and changed columns in before and after image Nevertheless, it was decided that this will not be implemented for now, as there is no practical use for it (think Undo operations). So, for not risking to get the user confused, and because the 'noblob' option supersedes this one, we will put this on hold. Decision was made over Skype meeting: - attendees: Alfranio, Mats and Luís - date: 09/09/09 (11:00 am CEST) |
binlog_format=ROW な binlog には AI と BI があるので、 binlog 使ってデータを巻き戻すことが可能ではないか、という話ですが
- master と slave の Column の数が違っても replication できる
- master ないし slave にしか存在しない generated column があった場合、 binlog でその更新をケアできるのか
- MySQLのバージョンが古かった場合、型変換できるのか。あるいは、 binlog を適用使用しているバージョンで、更新対象のColumnの型はサポートされているか
- DMLはともかく、DDLはどうするのか
といったあたり踏まえると、binlog_format=ROW を熟知していないと、混乱を招く機能ではないかなぁと思います。
Alfranio、 Mats、Luís の三人は、reversible より noblob の方がユーザにとってメリットがあると考えてくれたのでしょう。個人的に、良い決断だったのではないかと思います。
今日はこれまで
今回はちょっとヤンチャなことをしてみました。 master と slave でテーブル定義が異なっていても、Row-based Replication はできるようですが、 STORED な Generated Column は、その限りではないようですね。
次回は、Row-based Replication で Column の型チェックが行われるところなどを見てみましょう。