チョットワカル Row-Based Replication・その３
sejimaこんにちわ。せじまです。
引き続き replication の話をします。
はじめに
第三回です。
前回は THD::decide_logging_format() を通じて、 binlog_format=ROW についてぼちぼち学びました。今回は binlog_row_image に関する話を踏まえつつ、ちょっと応用編っぽい話もします。
解説
binlog_format=ROW についてさらに学ぶべく、まずはMySQL Internals Manualを読んでみましょう。
ただ、
MySQL Internals Manual / The Binary Log / Event Data for Specific Event Types をみると
Write_rows_log_event/WRITE_ROWS_EVENT には次のように書いてあります。
[TODO: following needs verification; it's guesswork]
なるほど、binlog_format=ROW について込み入ったことが知りたいなら、最終的には自分でソースコード読んだ方が早そうですね。
で、 Rows_log_event::print_verbose() や Rows_log_event::print_verbose_one_row() を読むと、「なるほど binlog_format=ROW の中身はこんなものか」と想像はつきます。
Rows_log_event::print_verbose_one_row() を呼び出す mysqlbinlog -vv を実行すると、update takanori set val2=val2+2000 where id=3 のような SQLは、次のように表示されます。
|
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 |
$ ./mysqlbinlog /var/lib/mysql/binlog.000010 -vv /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #191030 12:22:01 server id 1 end_log_pos 124 CRC32 0x4ca752a3 Start: binlog v 4, server v 8.0.18 created 191030 12:22:01 # Warning: this binlog is either in use or was not closed properly. BINLOG ' aYC5XQ8BAAAAeAAAAHwAAAABAAQAOC4wLjE4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA CgGjUqdM '/*!*/; # at 124 #191030 12:22:01 server id 1 end_log_pos 155 CRC32 0xa2f9fb41 Previous-GTIDs # [empty] # at 155 #191030 12:22:10 server id 1 end_log_pos 234 CRC32 0xad194110 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1572438130895076 immediate_commit_timestamp=1572438130895076 transaction_length=381 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1572438130895076 (2019-10-30 12:22:10.895076 UTC) # immediate_commit_timestamp=1572438130895076 (2019-10-30 12:22:10.895076 UTC) /*!80001 SET @@session.original_commit_timestamp=1572438130895076*//*!*/; /*!80014 SET @@session.original_server_version=80018*//*!*/; /*!80014 SET @@session.immediate_server_version=80018*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 234 #191030 12:22:10 server id 1 end_log_pos 318 CRC32 0x3ba27e42 Query thread_id=526 exec_time=0 error_code=0 SET TIMESTAMP=1572438130/*!*/; SET @@session.pseudo_thread_id=526/*!*/; 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 318 #191030 12:22:10 server id 1 end_log_pos 387 CRC32 0xfb566c82 Rows_query # update takanori set val2=val2+2000 where id=3 # at 387 #191030 12:22:10 server id 1 end_log_pos 443 CRC32 0x0018856b Table_map: `test`.`takanori` mapped to number 89 # at 443 #191030 12:22:10 server id 1 end_log_pos 505 CRC32 0x2dea17b0 Update_rows: table id 89 flags: STMT_END_F BINLOG ' coC5XR0BAAAARQAAAIMBAACAAC11cGRhdGUgdGFrYW5vcmkgc2V0IHZhbDI9dmFsMisyMDAwIHdo ZXJlIGlkPTOCbFb7 coC5XRMBAAAAOAAAALsBAAAAAFkAAAAAAAEABHRlc3QACHRha2Fub3JpAAMDAwMAAAEBAGuFGAA= coC5XR8BAAAAPgAAAPkBAAAAAFkAAAAAAAEAAgAD//8AAwAAAAEAAADQBwAAAAMAAAABAAAAoA8A ALAX6i0= '/*!*/; ### 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=2000 /* 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=4000 /* INT meta=0 nullable=0 is_null=0 */ # at 505 #191030 12:22:10 server id 1 end_log_pos 536 CRC32 0x90da88a7 Xid = 1089 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 67 |
$ ./mysqlbinlog /var/lib/mysql/binlog.000011 -vv /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #191030 12:23:12 server id 1 end_log_pos 124 CRC32 0xf250a38b Start: binlog v 4, server v 8.0.18 created 191030 12:23:12 # Warning: this binlog is either in use or was not closed properly. BINLOG ' sIC5XQ8BAAAAeAAAAHwAAAABAAQAOC4wLjE4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA CgGLo1Dy '/*!*/; # at 124 #191030 12:23:12 server id 1 end_log_pos 155 CRC32 0x6c1666a1 Previous-GTIDs # [empty] # at 155 #191030 12:23:16 server id 1 end_log_pos 234 CRC32 0x9f079791 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1572438196622460 immediate_commit_timestamp=1572438196622460 transaction_length=365 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1572438196622460 (2019-10-30 12:23:16.622460 UTC) # immediate_commit_timestamp=1572438196622460 (2019-10-30 12:23:16.622460 UTC) /*!80001 SET @@session.original_commit_timestamp=1572438196622460*//*!*/; /*!80014 SET @@session.original_server_version=80018*//*!*/; /*!80014 SET @@session.immediate_server_version=80018*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 234 #191030 12:23:16 server id 1 end_log_pos 318 CRC32 0x1ac997c3 Query thread_id=530 exec_time=0 error_code=0 SET TIMESTAMP=1572438196/*!*/; SET @@session.pseudo_thread_id=530/*!*/; 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 318 #191030 12:23:16 server id 1 end_log_pos 387 CRC32 0xb0fa914b Rows_query # update takanori set val2=val2+2000 where id=3 # at 387 #191030 12:23:16 server id 1 end_log_pos 443 CRC32 0x8e8f0fb9 Table_map: `test`.`takanori` mapped to number 89 # at 443 #191030 12:23:16 server id 1 end_log_pos 489 CRC32 0x4325f2a3 Update_rows: table id 89 flags: STMT_END_F BINLOG ' tIC5XR0BAAAARQAAAIMBAACAAC11cGRhdGUgdGFrYW5vcmkgc2V0IHZhbDI9dmFsMisyMDAwIHdo ZXJlIGlkPTNLkfqw tIC5XRMBAAAAOAAAALsBAAAAAFkAAAAAAAEABHRlc3QACHRha2Fub3JpAAMDAwMAAAEBALkPj44= tIC5XR8BAAAALgAAAOkBAAAAAFkAAAAAAAEAAgADAQQAAwAAAABwFwAAo/IlQw== '/*!*/; ### UPDATE `test`.`takanori` ### WHERE ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### SET ### @3=6000 /* INT meta=0 nullable=0 is_null=0 */ # at 489 #191030 12:23:16 server id 1 end_log_pos 520 CRC32 0x0a1f2359 Xid = 1105 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*/; $ |
そして、おそらく、 Row-based replication や binlog_row_image=minimal について最も詳細な資料は、Worklog 5092 であり、上記の binlog の中身を説明するものでもあります。
Worklog 5092
https://dev.mysql.com/worklog/task/?id=5092
だいたい全部重要なんですが抜粋しますと
|
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 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 |
BACKGROUND ========== Before and After Images ----------------------- In row based replication row events may contain two copies for the row that they are changing. These are generally known as images. The first one, called *before image* (BI), contains data that existed on the row before it was actually changed. The second one, called *after image* (AI), contain the actual changes. Each, BI and AI, usage is confined to two different moments in the execution flow. The BI is used while the slave is searching for the row to be updated, while AI is used when replaying the changes in the row. Both have some restrictions: - BI: needs to hold a set of values that can be used by the slave to fetch the correct row. In other words, it should provide a set a values that *uniquely* identify the row to be changed; - AI: needs to hold values that are needed to replay all the changes, that were actually done during the original execution, in an identical set (meaning, same index structures, same engine, ...). Given that BI and AI have different usages, their usefulness can be mapped into the data modification row events: - Write_rows_log_event: *requires only AI*. There is no need for a BI because, we are adding a record, and not changing an existing one. The current implementation logs only the AI. - Delete_rows_log_event: *requires only BI*. There is no need for an AI because, the row ceases to exist, as it is removed. However, before removing it, one needs to find it, thence BI is required. - Update_rows_log_event: *requires both: AI and BI*. Both BI and AI, are required. The row needs to be found (BI comes to play) before being changed (AI comes to play). Summing up, BI must contain values that uniquely identifies rows, acting like a primary key equivalent (PKE), while AI must contain values that make possible changing the row according to the original execution. Primary Key Equivalent ---------------------- Tables contain an index structure - mysql calls indexes keys. This structure holds information on which keys are declared, and these range from plain keys (K), to unique keys (UK), or even a primary key (PK). PK - When it comes to logging row based events, PK plays an important role, as it covers the BI requirement of uniquely identifying a given row just by searching using the PK value. Thence, if replicating the master contents, one should be fine by just logging the PK column. The slave would then use this value to search the correct row. Technically, "A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY." [1] UK - Unique keys share the same usefulness of the PK, except that they need to be declared without nullable parts. From the manual [1]: "[...] For all engines, a UNIQUE index allows multiple NULL values for columns that can contain NULL." In fact, if there is no PK declared in a table and an application requests one, MySQL "[...] returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY." [1] K - Regular keys or nullable UK are of no particular interest when logging row events. Given that these are stripped from uniqueness property provided by UK and PK, these cannot be used to uniquely identify a row. There can be the case that a table does not declare any index at all, or all indexes are regular keys. In this case the master must ensure that the data provided in BI shall be enough to uniquely identify the row. As such, the alternative is to log the full row. This ensures that when searching (using the BI) and applying (using the AI) the next record to be fetched will be uniquely identified by all the fields in the BI. Should there be indistinguishable rows, searching and updating either one in any given order leads to a correct state. Consequently, a *Primary Key Equivalent* (PKE) is defined as: 1. If a PK exists, the PKE is equal to the PK. 2. Otherwise, if there exists a UK where all columns have the NOT NULL attribute, then that is the PKE (if there are more than one such UKs, then one is chosen arbitrarily). 3. Otherwise, the PKE is equal to the set of all columns. Hereafter, we will be considering to PK to be a subset of PKE and it shall map into items 1. and 2. of PKE definition. Furthermore, if no explicit primary key nor UK NOT NULL exists in the table, it is said that the table has no PK. PROBLEM STATEMENT ================= Given the definition of PKE, one can have a smaller set of columns to be logged (instead of the full row), and still be able to find (BI) and update a record (AI), while replaying the row event. This can be useful to reduce bandwidth (less network traffic) and storage (samller binlogs) usage as well as mysqld memory footprint. It becomes even more important if tables contain large blobs that do not need to be logged as part of the BI. Currently, in MySQL 5.1 GA, PKE is always assumed to be the full row, ie, the index structure is ignored. Consequently, AI and BI are always logged with all their columns. It should be possible for the user to configure this behavior such that he could request that BI and AI always log full rows, or a PK when available - for the BI - and changed columns only - for AI. SOLUTION ======== MySQL shall provide an option and the different configurations should be: - minimal: Means PKE in the before image and changed columns in after image - full: Means all columns in both before and after image - noblob: Works as full but avoids sending blobs when these are not needed. Blobs are still replicated if: 1. In AI, if they have been changed. 2. In BI, if they are part of PK. It shall be named: --binlog-row-image={minimal,noblob,full} DEFAULT VALUE SHALL BE: 'FULL'. |
これを読んでいただくといろいろわかると思うのですが、どうもUPDATE時に評価されているのはPKEのみであり、それ以外のColumnは見てないようです。
binlog_format=ROWのとき、 {Write,Delete,Update}_rows_log_event にはColumnの名前が保存されていません。純粋に「何番目のColumnを更新対象とするのか」としか見てないようです。
よって、RBRではmasterとslaveでカラム名が異なるtableでもreplicationできるんじゃないでしょうか？
では試してみましょう。
以下、本番環境では試さないことを推奨する、実験的な試みをします。
次のような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 |
mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.18 | +-----------+ 1 row 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, `val10` int(11) NOT NULL, `val20` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row 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.000012 Read_Master_Log_Pos: 155 Relay_Log_File: 8230d2822d2f-relay-bin.000024 Relay_Log_Pos: 363 Relay_Master_Log_File: binlog.000012 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> select * from takanori; +----+-------+-------+ | id | val10 | val20 | +----+-------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 8000 | | 4 | 100 | 2000 | +----+-------+-------+ 4 rows 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 |
mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.18 | +-----------+ 1 row 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=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select * from takanori; +----+------+------+ | id | val1 | val2 | +----+------+------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 8000 | | 4 | 100 | 2000 | +----+------+------+ 4 rows in set (0.00 sec) mysql> |
slaveで更新かけます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> update takanori set val20=val20-2000 where id = 3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from takanori; +----+-------+-------+ | id | val10 | val20 | +----+-------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 6000 | | 4 | 100 | 2000 | +----+-------+-------+ 4 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 |
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 * from takanori; +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 10000 | | 4 | 100 | 2000 | +----+------+-------+ 4 rows in set (0.00 sec) mysql> |
slave には次のように更新が反映されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select * from takanori; +----+-------+-------+ | id | val10 | val20 | +----+-------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 10000 | | 4 | 100 | 2000 | +----+-------+-------+ 4 rows in set (0.00 sec) mysql> |
master の binlog を mysqlbinlog で見ると、次のとおりです。WHERE に @3=8000 と書いてありますが、これはPKEに含まれないので、実は見ていないようです。
|
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 |
$ ./mysqlbinlog /var/lib/mysql/binlog.000012 -vv /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #191030 12:43:18 server id 1 end_log_pos 124 CRC32 0xd094047f Start: binlog v 4, server v 8.0.18 created 191030 12:43:18 # Warning: this binlog is either in use or was not closed properly. BINLOG ' ZoW5XQ8BAAAAeAAAAHwAAAABAAQAOC4wLjE4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA CgF/BJTQ '/*!*/; # at 124 #191030 12:43:18 server id 1 end_log_pos 155 CRC32 0x7f275b04 Previous-GTIDs # [empty] # at 155 #191030 12:45:55 server id 1 end_log_pos 234 CRC32 0xd3d10e90 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1572439555300913 immediate_commit_timestamp=1572439555300913 transaction_length=381 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=1572439555300913 (2019-10-30 12:45:55.300913 UTC) # immediate_commit_timestamp=1572439555300913 (2019-10-30 12:45:55.300913 UTC) /*!80001 SET @@session.original_commit_timestamp=1572439555300913*//*!*/; /*!80014 SET @@session.original_server_version=80018*//*!*/; /*!80014 SET @@session.immediate_server_version=80018*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 234 #191030 12:45:55 server id 1 end_log_pos 318 CRC32 0x52116058 Query thread_id=571 exec_time=0 error_code=0 SET TIMESTAMP=1572439555/*!*/; SET @@session.pseudo_thread_id=571/*!*/; 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 318 #191030 12:45:55 server id 1 end_log_pos 387 CRC32 0x1e59d01a Rows_query # update takanori set val2=val2+2000 where id=3 # at 387 #191030 12:45:55 server id 1 end_log_pos 443 CRC32 0x34b2f531 Table_map: `test`.`takanori` mapped to number 90 # at 443 #191030 12:45:55 server id 1 end_log_pos 505 CRC32 0x6b5cc245 Update_rows: table id 90 flags: STMT_END_F BINLOG ' A4a5XR0BAAAARQAAAIMBAACAAC11cGRhdGUgdGFrYW5vcmkgc2V0IHZhbDI9dmFsMisyMDAwIHdo ZXJlIGlkPTMa0Fke A4a5XRMBAAAAOAAAALsBAAAAAFoAAAAAAAEABHRlc3QACHRha2Fub3JpAAMDAwMAAAEBADH1sjQ= A4a5XR8BAAAAPgAAAPkBAAAAAFoAAAAAAAEAAgAD//8AAwAAAAEAAABAHwAAAAMAAAABAAAAECcA AEXCXGs= '/*!*/; ### 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=8000 /* 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=10000 /* INT meta=0 nullable=0 is_null=0 */ # at 505 #191030 12:45:55 server id 1 end_log_pos 536 CRC32 0xd848947e Xid = 1222 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*/; $ |
もうちょっと試してみましょう。
slave でこうします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> update takanori set val10=100, val20=val20-2000 where id = 3; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from takanori; +----+-------+-------+ | id | val10 | val20 | +----+-------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 100 | 8000 | | 4 | 100 | 2000 | +----+-------+-------+ 4 rows 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 |
mysql> select * from takanori; +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 10000 | | 4 | 100 | 2000 | +----+------+-------+ 4 rows in set (0.00 sec) 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 * from takanori; +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 12000 | | 4 | 100 | 2000 | +----+------+-------+ 4 rows in set (0.00 sec) mysql> |
slave はこうなります。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> select * from takanori; +----+-------+-------+ | id | val10 | val20 | +----+-------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 12000 | | 4 | 100 | 2000 | +----+-------+-------+ 4 rows in set (0.00 sec) mysql> |
binlog_row_image=FULL
- 上記の例からわかるように、 BI（before image）はログとして残っているだけで、必ずしも、完全一致であるか評価しているわけではないようです。
- そうであるならば、BIにすべてのデータが保存されている必要性ってあんまりないのでは？という気もします（が、ログとしては意味のあるものです）。
- AI（after image） にすべてのデータが保存されていて、それで強制的に上書きされるのは、整合性を保つ上で望ましいことだと思います。
binlog_row_image=noblob
- 必要がなければBIとAIにblobを含まないというのは、なかなか合理的でバランスが良い気もします。
binlog_row_image=minimal
ある意味、 Statement-Based Replication と一番挙動が近いかもしれません。
例えば、上記の master/slave 構成で試してみますと
master
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> select @@version;select * from takanori; +-----------+ | @@version | +-----------+ | 8.0.18 | +-----------+ 1 row in set (0.00 sec) +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 12000 | | 4 | 100 | 2000 | +----+------+-------+ 4 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 |
mysql> select @@version;select * from takanori; +-----------+ | @@version | +-----------+ | 8.0.18 | +-----------+ 1 row in set (0.00 sec) +----+-------+-------+ | id | val10 | val20 | +----+-------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 12000 | | 4 | 100 | 2000 | +----+-------+-------+ 4 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> update takanori set val10=100, val20=val20-2000 where id = 3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select @@version;select * from takanori; +-----------+ | @@version | +-----------+ | 8.0.18 | +-----------+ 1 row in set (0.00 sec) +----+-------+-------+ | id | val10 | val20 | +----+-------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 100 | 10000 | | 4 | 100 | 2000 | +----+-------+-------+ 4 rows in set (0.01 sec) mysql> |
master で set session 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 |
mysql> set session binlog_row_image=minimal; Query OK, 0 rows affected (0.00 sec) mysql> select @@version;select * from takanori; +-----------+ | @@version | +-----------+ | 8.0.18 | +-----------+ 1 row in set (0.00 sec) +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 12000 | | 4 | 100 | 2000 | +----+------+-------+ 4 rows in set (0.01 sec) 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 @@version;select * from takanori; +-----------+ | @@version | +-----------+ | 8.0.18 | +-----------+ 1 row in set (0.00 sec) +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 14000 | | 4 | 100 | 2000 | +----+------+-------+ 4 rows in set (0.00 sec) mysql> |
slave は二番目のColumnが更新されません。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> select @@version;select * from takanori; +-----------+ | @@version | +-----------+ | 8.0.18 | +-----------+ 1 row in set (0.00 sec) +----+-------+-------+ | id | val10 | val20 | +----+-------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 100 | 14000 | | 4 | 100 | 2000 | +----+-------+-------+ 4 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 |
mysql> set session binlog_row_image=full; Query OK, 0 rows affected (0.01 sec) 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 @@version;select * from takanori; +-----------+ | @@version | +-----------+ | 8.0.18 | +-----------+ 1 row in set (0.00 sec) +----+------+-------+ | id | val1 | val2 | +----+------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 16000 | | 4 | 100 | 2000 | +----+------+-------+ 4 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 |
mysql> select @@version;select * from takanori; +-----------+ | @@version | +-----------+ | 8.0.18 | +-----------+ 1 row in set (0.00 sec) +----+-------+-------+ | id | val10 | val20 | +----+-------+-------+ | 1 | 1 | 10 | | 2 | 100 | 100 | | 3 | 1 | 16000 | | 4 | 100 | 2000 | +----+-------+-------+ 4 rows in set (0.01 sec) mysql> |
今日はこれまで
今回は応用編っぽい話をしました。 Worklog 5092 はとても重要なことが書いてあると思います。このあたり関心ある方は、熟読した上で、いろいろ動かしてみたり、ソースコードを読んでいただくと良いでしょう。
次回はもうちょっと踏み込んでいきます。