https:\/\/dev.mysql.com\/worklog\/task\/?id=5092<\/a><\/p>\n\u3060\u3044\u305f\u3044\u5168\u90e8\u91cd\u8981\u306a\u3093\u3067\u3059\u304c\u629c\u7c8b\u3057\u307e\u3059\u3068<\/p>\n
BACKGROUND\n==========\n \n Before and After Images\n -----------------------\n \n In row based replication row events may contain two copies for the\n row that they are changing. These are generally known as images. The\n first one, called *before image* (BI), contains data that existed on\n the row before it was actually changed. The second one, called\n *after image* (AI), contain the actual changes. Each, BI and AI,\n usage is confined to two different moments in the execution\n flow. The BI is used while the slave is searching for the row to be\n updated, while AI is used when replaying the changes in the row.\n \n Both have some restrictions:\n \n - BI: needs to hold a set of values that can be used by the\n slave to fetch the correct row. In other words, it should\n provide a set a values that *uniquely* identify the row to\n be changed;\n \n - AI: needs to hold values that are needed to replay all the\n changes, that were actually done during the original\n execution, in an identical set (meaning, same index\n structures, same engine, ...).\n \n Given that BI and AI have different usages, their usefulness can be\n mapped into the data modification row events:\n \n - Write_rows_log_event: *requires only AI*.\n \n There is no need for a BI because, we are adding a record,\n and not changing an existing one. The current\n implementation logs only the AI.\n \n - Delete_rows_log_event: *requires only BI*.\n \n There is no need for an AI because, the row ceases to\n exist, as it is removed. However, before removing it, one\n needs to find it, thence BI is required.\n \n - Update_rows_log_event: *requires both: AI and BI*.\n \n Both BI and AI, are required. The row needs to be found (BI\n comes to play) before being changed (AI comes to play).\n \n Summing up, BI must contain values that uniquely identifies rows,\n acting like a primary key equivalent (PKE), while AI must contain\n values that make possible changing the row according to the original\n execution.\n \n Primary Key Equivalent\n ----------------------\n \n Tables contain an index structure - mysql calls indexes keys. This\n structure holds information on which keys are declared, and these\n range from plain keys (K), to unique keys (UK), or even a primary\n key (PK).\n \n PK - When it comes to logging row based events, PK plays an\n important role, as it covers the BI requirement of uniquely\n identifying a given row just by searching using the PK\n value. Thence, if replicating the master contents, one should\n be fine by just logging the PK column. The slave would then use\n this value to search the correct row. Technically, \"A PRIMARY\n KEY is a unique index where all key columns must be defined as\n NOT NULL. If they are not explicitly declared as NOT NULL,\n MySQL declares them so implicitly (and silently). A table can\n have only one PRIMARY KEY.\" [1]\n \n UK - Unique keys share the same usefulness of the PK, except that\n they need to be declared without nullable parts. From the\n manual [1]: \"[...] For all engines, a UNIQUE index allows\n multiple NULL values for columns that can contain NULL.\" In\n fact, if there is no PK declared in a table and an application\n requests one, MySQL \"[...] returns the first UNIQUE index that\n has no NULL columns as the PRIMARY KEY.\" [1]\n \n K - Regular keys or nullable UK are of no particular interest when\n logging row events. Given that these are stripped from\n uniqueness property provided by UK and PK, these cannot be used\n to uniquely identify a row.\n \n There can be the case that a table does not declare any index at\n all, or all indexes are regular keys. In this case the master must\n ensure that the data provided in BI shall be enough to uniquely\n identify the row. As such, the alternative is to log the full\n row. This ensures that when searching (using the BI) and applying\n (using the AI) the next record to be fetched will be uniquely\n identified by all the fields in the BI. Should there be\n indistinguishable rows, searching and updating either one in any\n given order leads to a correct state.\n \n Consequently, a *Primary Key Equivalent* (PKE) is defined as:\n \n 1. If a PK exists, the PKE is equal to the PK.\n \n 2. Otherwise, if there exists a UK where all columns have the NOT\n NULL attribute, then that is the PKE (if there are more than\n one such UKs, then one is chosen arbitrarily).\n \n 3. Otherwise, the PKE is equal to the set of all columns.\n \n Hereafter, we will be considering to PK to be a subset of PKE and it\n shall map into items 1. and 2. of PKE definition. Furthermore, if no\n explicit primary key nor UK NOT NULL exists in the table, it is said\n that the table has no PK.\n \nPROBLEM STATEMENT\n=================\n \n Given the definition of PKE, one can have a smaller set of columns\n to be logged (instead of the full row), and still be able to find\n (BI) and update a record (AI), while replaying the row event.\n \n This can be useful to reduce bandwidth (less network traffic) and\n storage (samller binlogs) usage as well as mysqld memory\n footprint. It becomes even more important if tables contain large\n blobs that do not need to be logged as part of the BI. Currently, in\n MySQL 5.1 GA, PKE is always assumed to be the full row, ie, the\n index structure is ignored. Consequently, AI and BI are always\n logged with all their columns.\n \n It should be possible for the user to configure this behavior\n such that he could request that BI and AI always log full rows,\n or a PK when available - for the BI - and changed columns only\n - for AI.\n \nSOLUTION\n========\n \n MySQL shall provide an option and the different configurations\n should be:\n \n - minimal:\n Means PKE in the before image and changed columns\n in after image\n \n - full:\n Means all columns in both before and after image\n \n - noblob:\n Works as full but avoids sending blobs when these are not\n needed. Blobs are still replicated if:\n \n 1. In AI, if they have been changed.\n 2. In BI, if they are part of PK.\n \n It shall be named:\n \n --binlog-row-image={minimal,noblob,full}\n \n DEFAULT VALUE SHALL BE: 'FULL'.<\/code><\/pre>\n\u3053\u308c\u3092\u8aad\u3093\u3067\u3044\u305f\u3060\u304f\u3068\u3044\u308d\u3044\u308d\u308f\u304b\u308b\u3068\u601d\u3046\u306e\u3067\u3059\u304c\u3001\u3069\u3046\u3082UPDATE\u6642\u306b\u8a55\u4fa1\u3055\u308c\u3066\u3044\u308b\u306e\u306fPKE\u306e\u307f\u3067\u3042\u308a\u3001\u305d\u308c\u4ee5\u5916\u306eColumn\u306f\u898b\u3066\u306a\u3044\u3088\u3046\u3067\u3059\u3002<\/p>\n
binlog_format=ROW\u306e\u3068\u304d\u3001 {Write,Delete,Update}_rows_log_event \u306b\u306fColumn\u306e\u540d\u524d\u304c\u4fdd\u5b58\u3055\u308c\u3066\u3044\u307e\u305b\u3093\u3002\u7d14\u7c8b\u306b\u300c\u4f55\u756a\u76ee\u306eColumn\u3092\u66f4\u65b0\u5bfe\u8c61\u3068\u3059\u308b\u306e\u304b\u300d\u3068\u3057\u304b\u898b\u3066\u306a\u3044\u3088\u3046\u3067\u3059\u3002
\n\u3088\u3063\u3066\u3001RBR\u3067\u306fmaster\u3068slave\u3067\u30ab\u30e9\u30e0\u540d\u304c\u7570\u306a\u308btable\u3067\u3082replication\u3067\u304d\u308b\u3093\u3058\u3083\u306a\u3044\u3067\u3057\u3087\u3046\u304b\uff1f<\/p>\n
\u3067\u306f\u8a66\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n
\u4ee5\u4e0b\u3001\u672c\u756a\u74b0\u5883\u3067\u306f\u8a66\u3055\u306a\u3044\u3053\u3068\u3092\u63a8\u5968\u3059\u308b\u3001\u5b9f\u9a13\u7684\u306a\u8a66\u307f\u3092\u3057\u307e\u3059\u3002<\/h3>\n
\u6b21\u306e\u3088\u3046\u306aslave\u304c\u3042\u308b\u3068\u3057\u307e\u3059\u3002<\/p>\n
mysql> select @@version;\n+-----------+\n| @@version |\n+-----------+\n| 8.0.18 |\n+-----------+\n1 row in set (0.00 sec)\n\nmysql> show create table takanori\\G\n*************************** 1. row ***************************\n Table: takanori\nCreate Table: CREATE TABLE `takanori` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `val10` int(11) NOT NULL,\n `val20` int(11) NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci\n1 row in set (0.00 sec)\n\nmysql> show slave status\\G\n*************************** 1. row ***************************\n Slave_IO_State: Waiting for master to send event\n Master_Host: 172.18.0.2\n Master_User: repl\n Master_Port: 3306\n Connect_Retry: 60\n Master_Log_File: binlog.000012\n Read_Master_Log_Pos: 155\n Relay_Log_File: 8230d2822d2f-relay-bin.000024\n Relay_Log_Pos: 363\n Relay_Master_Log_File: binlog.000012\n Slave_IO_Running: Yes\n Slave_SQL_Running: Yes\n Replicate_Do_DB:\n Replicate_Ignore_DB:\n Replicate_Do_Table:\n Replicate_Ignore_Table:\n Replicate_Wild_Do_Table:\n Replicate_Wild_Ignore_Table:\n Last_Errno: 0\n Last_Error:\n Skip_Counter: 0\n Exec_Master_Log_Pos: 155\n Relay_Log_Space: 622\n Until_Condition: None\n Until_Log_File:\n Until_Log_Pos: 0\n Master_SSL_Allowed: No\n Master_SSL_CA_File:\n Master_SSL_CA_Path:\n Master_SSL_Cert:\n Master_SSL_Cipher:\n Master_SSL_Key:\n Seconds_Behind_Master: 0\nMaster_SSL_Verify_Server_Cert: No\n Last_IO_Errno: 0\n Last_IO_Error:\n Last_SQL_Errno: 0\n Last_SQL_Error:\n Replicate_Ignore_Server_Ids:\n Master_Server_Id: 1\n Master_UUID: 385a832a-ef37-11e9-a332-0242ac120002\n Master_Info_File: mysql.slave_master_info\n SQL_Delay: 0\n SQL_Remaining_Delay: NULL\n Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates\n Master_Retry_Count: 86400\n Master_Bind:\n Last_IO_Error_Timestamp:\n Last_SQL_Error_Timestamp:\n Master_SSL_Crl:\n Master_SSL_Crlpath:\n Retrieved_Gtid_Set:\n Executed_Gtid_Set:\n Auto_Position: 0\n Replicate_Rewrite_DB:\n Channel_Name:\n Master_TLS_Version:\n Master_public_key_path:\n Get_master_public_key: 0\n Network_Namespace:\n1 row in set (0.00 sec)\n\nmysql> select * from takanori;\n+----+-------+-------+\n| id | val10 | val20 |\n+----+-------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 8000 |\n| 4 | 100 | 2000 |\n+----+-------+-------+\n4 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\n\u6b21\u306e\u3088\u3046\u306amaster\u304c\u3042\u308b\u3068\u3057\u307e\u3059\u3002<\/p>\n
mysql> select @@version;\n+-----------+\n| @@version |\n+-----------+\n| 8.0.18 |\n+-----------+\n1 row in set (0.00 sec)\n\nmysql> show create table takanori\\G\n*************************** 1. row ***************************\n Table: takanori\nCreate Table: CREATE TABLE `takanori` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `val1` int(11) NOT NULL,\n `val2` int(11) NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci\n1 row in set (0.00 sec)\n\nmysql> select * from takanori;\n+----+------+------+\n| id | val1 | val2 |\n+----+------+------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 8000 |\n| 4 | 100 | 2000 |\n+----+------+------+\n4 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\nslave\u3067\u66f4\u65b0\u304b\u3051\u307e\u3059\u3002<\/p>\n
mysql> update takanori set val20=val20-2000 where id = 3;\nQuery OK, 1 row affected (0.01 sec)\nRows matched: 1 Changed: 1 Warnings: 0\n\nmysql> select * from takanori;\n+----+-------+-------+\n| id | val10 | val20 |\n+----+-------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 6000 |\n| 4 | 100 | 2000 |\n+----+-------+-------+\n4 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\nbinlog_row_image=FULL \u3067 master \u306b\u66f4\u65b0\u3092\u304b\u3051\u307e\u3059\u3002<\/p>\n
mysql> update takanori set val2=val2+2000 where id=3;\nQuery OK, 1 row affected (0.01 sec)\nRows matched: 1 Changed: 1 Warnings: 0\n\nmysql> select * from takanori;\n+----+------+-------+\n| id | val1 | val2 |\n+----+------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 10000 |\n| 4 | 100 | 2000 |\n+----+------+-------+\n4 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\nslave \u306b\u306f\u6b21\u306e\u3088\u3046\u306b\u66f4\u65b0\u304c\u53cd\u6620\u3055\u308c\u307e\u3059\u3002<\/p>\n
mysql> select * from takanori;\n+----+-------+-------+\n| id | val10 | val20 |\n+----+-------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 10000 |\n| 4 | 100 | 2000 |\n+----+-------+-------+\n4 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\nmaster \u306e binlog \u3092 mysqlbinlog \u3067\u898b\u308b\u3068\u3001\u6b21\u306e\u3068\u304a\u308a\u3067\u3059\u3002WHERE \u306b @3=8000 \u3068\u66f8\u3044\u3066\u3042\u308a\u307e\u3059\u304c\u3001\u3053\u308c\u306fPKE\u306b\u542b\u307e\u308c\u306a\u3044\u306e\u3067\u3001\u5b9f\u306f\u898b\u3066\u3044\u306a\u3044\u3088\u3046\u3067\u3059\u3002<\/p>\n
$ .\/mysqlbinlog \/var\/lib\/mysql\/binlog.000012 -vv\n\/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*\/;\n\/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*\/;\nDELIMITER \/*!*\/;\n# at 4\n#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\n# Warning: this binlog is either in use or was not closed properly.\nBINLOG '\nZoW5XQ8BAAAAeAAAAHwAAAABAAQAOC4wLjE4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA\nCgF\/BJTQ\n'\/*!*\/;\n# at 124\n#191030 12:43:18 server id 1 end_log_pos 155 CRC32 0x7f275b04 Previous-GTIDs\n# [empty]\n# at 155\n#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\n\/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*\/\/*!*\/;\n# original_commit_timestamp=1572439555300913 (2019-10-30 12:45:55.300913 UTC)\n# immediate_commit_timestamp=1572439555300913 (2019-10-30 12:45:55.300913 UTC)\n\/*!80001 SET @@session.original_commit_timestamp=1572439555300913*\/\/*!*\/;\n\/*!80014 SET @@session.original_server_version=80018*\/\/*!*\/;\n\/*!80014 SET @@session.immediate_server_version=80018*\/\/*!*\/;\nSET @@SESSION.GTID_NEXT= 'ANONYMOUS'\/*!*\/;\n# at 234\n#191030 12:45:55 server id 1 end_log_pos 318 CRC32 0x52116058 Query thread_id=571 exec_time=0 error_code=0\nSET TIMESTAMP=1572439555\/*!*\/;\nSET @@session.pseudo_thread_id=571\/*!*\/;\nSET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1\/*!*\/;\nSET @@session.sql_mode=1168113696\/*!*\/;\nSET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1\/*!*\/;\n\/*!\\C latin1 *\/\/*!*\/;\nSET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=255\/*!*\/;\nSET @@session.lc_time_names=0\/*!*\/;\nSET @@session.collation_database=DEFAULT\/*!*\/;\n\/*!80011 SET @@session.default_collation_for_utf8mb4=255*\/\/*!*\/;\nBEGIN\n\/*!*\/;\n# at 318\n#191030 12:45:55 server id 1 end_log_pos 387 CRC32 0x1e59d01a Rows_query\n# update takanori set val2=val2+2000 where id=3\n# at 387\n#191030 12:45:55 server id 1 end_log_pos 443 CRC32 0x34b2f531 Table_map: `test`.`takanori` mapped to number 90\n# at 443\n#191030 12:45:55 server id 1 end_log_pos 505 CRC32 0x6b5cc245 Update_rows: table id 90 flags: STMT_END_F\n\nBINLOG '\nA4a5XR0BAAAARQAAAIMBAACAAC11cGRhdGUgdGFrYW5vcmkgc2V0IHZhbDI9dmFsMisyMDAwIHdo\nZXJlIGlkPTMa0Fke\nA4a5XRMBAAAAOAAAALsBAAAAAFoAAAAAAAEABHRlc3QACHRha2Fub3JpAAMDAwMAAAEBADH1sjQ=\nA4a5XR8BAAAAPgAAAPkBAAAAAFoAAAAAAAEAAgAD\/\/8AAwAAAAEAAABAHwAAAAMAAAABAAAAECcA\nAEXCXGs=\n'\/*!*\/;\n### UPDATE `test`.`takanori`\n### WHERE\n### @1=3 \/* INT meta=0 nullable=0 is_null=0 *\/\n### @2=1 \/* INT meta=0 nullable=0 is_null=0 *\/\n### @3=8000 \/* INT meta=0 nullable=0 is_null=0 *\/\n### SET\n### @1=3 \/* INT meta=0 nullable=0 is_null=0 *\/\n### @2=1 \/* INT meta=0 nullable=0 is_null=0 *\/\n### @3=10000 \/* INT meta=0 nullable=0 is_null=0 *\/\n# at 505\n#191030 12:45:55 server id 1 end_log_pos 536 CRC32 0xd848947e Xid = 1222\nCOMMIT\/*!*\/;\nSET @@SESSION.GTID_NEXT= 'AUTOMATIC' \/* added by mysqlbinlog *\/ \/*!*\/;\nDELIMITER ;\n# End of log file\n\/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*\/;\n\/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*\/;\n$\n<\/code><\/pre>\n\u3082\u3046\u3061\u3087\u3063\u3068\u8a66\u3057\u3066\u307f\u307e\u3057\u3087\u3046\u3002<\/p>\n
slave \u3067\u3053\u3046\u3057\u307e\u3059\u3002<\/p>\n
mysql> update takanori set val10=100, val20=val20-2000 where id = 3;\nQuery OK, 1 row affected (0.02 sec)\nRows matched: 1 Changed: 1 Warnings: 0\n\nmysql> select * from takanori;\n+----+-------+-------+\n| id | val10 | val20 |\n+----+-------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 100 | 8000 |\n| 4 | 100 | 2000 |\n+----+-------+-------+\n4 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\nmaster \u3067\u3053\u3046\u3057\u307e\u3059\u3002<\/p>\n
mysql> select * from takanori;\n+----+------+-------+\n| id | val1 | val2 |\n+----+------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 10000 |\n| 4 | 100 | 2000 |\n+----+------+-------+\n4 rows in set (0.00 sec)\n\nmysql> update takanori set val2=val2+2000 where id=3;\nQuery OK, 1 row affected (0.01 sec)\nRows matched: 1 Changed: 1 Warnings: 0\n\nmysql> select * from takanori;\n+----+------+-------+\n| id | val1 | val2 |\n+----+------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 12000 |\n| 4 | 100 | 2000 |\n+----+------+-------+\n4 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\nslave \u306f\u3053\u3046\u306a\u308a\u307e\u3059\u3002<\/p>\n
mysql> select * from takanori;\n+----+-------+-------+\n| id | val10 | val20 |\n+----+-------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 12000 |\n| 4 | 100 | 2000 |\n+----+-------+-------+\n4 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\nbinlog_row_image=FULL<\/h3>\n\n- \u4e0a\u8a18\u306e\u4f8b\u304b\u3089\u308f\u304b\u308b\u3088\u3046\u306b\u3001 BI\uff08before image\uff09\u306f\u30ed\u30b0\u3068\u3057\u3066\u6b8b\u3063\u3066\u3044\u308b\u3060\u3051\u3067\u3001\u5fc5\u305a\u3057\u3082\u3001\u5b8c\u5168\u4e00\u81f4\u3067\u3042\u308b\u304b\u8a55\u4fa1\u3057\u3066\u3044\u308b\u308f\u3051\u3067\u306f\u306a\u3044\u3088\u3046\u3067\u3059\u3002<\/li>\n
- \u305d\u3046\u3067\u3042\u308b\u306a\u3089\u3070\u3001BI\u306b\u3059\u3079\u3066\u306e\u30c7\u30fc\u30bf\u304c\u4fdd\u5b58\u3055\u308c\u3066\u3044\u308b\u5fc5\u8981\u6027\u3063\u3066\u3042\u3093\u307e\u308a\u306a\u3044\u306e\u3067\u306f\uff1f\u3068\u3044\u3046\u6c17\u3082\u3057\u307e\u3059\uff08\u304c\u3001\u30ed\u30b0\u3068\u3057\u3066\u306f\u610f\u5473\u306e\u3042\u308b\u3082\u306e\u3067\u3059\uff09\u3002<\/li>\n
- AI\uff08after image\uff09 \u306b\u3059\u3079\u3066\u306e\u30c7\u30fc\u30bf\u304c\u4fdd\u5b58\u3055\u308c\u3066\u3044\u3066\u3001\u305d\u308c\u3067\u5f37\u5236\u7684\u306b\u4e0a\u66f8\u304d\u3055\u308c\u308b\u306e\u306f\u3001\u6574\u5408\u6027\u3092\u4fdd\u3064\u4e0a\u3067\u671b\u307e\u3057\u3044\u3053\u3068\u3060\u3068\u601d\u3044\u307e\u3059\u3002<\/li>\n<\/ul>\n
binlog_row_image=noblob<\/h3>\n\n- \u5fc5\u8981\u304c\u306a\u3051\u308c\u3070BI\u3068AI\u306bblob\u3092\u542b\u307e\u306a\u3044\u3068\u3044\u3046\u306e\u306f\u3001\u306a\u304b\u306a\u304b\u5408\u7406\u7684\u3067\u30d0\u30e9\u30f3\u30b9\u304c\u826f\u3044\u6c17\u3082\u3057\u307e\u3059\u3002<\/li>\n<\/ul>\n
binlog_row_image=minimal<\/h3>\n
\u3042\u308b\u610f\u5473\u3001 Statement-Based Replication \u3068\u4e00\u756a\u6319\u52d5\u304c\u8fd1\u3044\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u3002<\/p>\n
\u4f8b\u3048\u3070\u3001\u4e0a\u8a18\u306e master\/slave \u69cb\u6210\u3067\u8a66\u3057\u3066\u307f\u307e\u3059\u3068<\/p>\n
master<\/p>\n
mysql> select @@version;select * from takanori;\n+-----------+\n| @@version |\n+-----------+\n| 8.0.18 |\n+-----------+\n1 row in set (0.00 sec)\n\n+----+------+-------+\n| id | val1 | val2 |\n+----+------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 12000 |\n| 4 | 100 | 2000 |\n+----+------+-------+\n4 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\nslave<\/p>\n
mysql> select @@version;select * from takanori;\n+-----------+\n| @@version |\n+-----------+\n| 8.0.18 |\n+-----------+\n1 row in set (0.00 sec)\n\n+----+-------+-------+\n| id | val10 | val20 |\n+----+-------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 12000 |\n| 4 | 100 | 2000 |\n+----+-------+-------+\n4 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\nslave\u3067\u66f4\u65b0\u3057\u3066<\/p>\n
mysql> update takanori set val10=100, val20=val20-2000 where id = 3;\nQuery OK, 1 row affected (0.01 sec)\nRows matched: 1 Changed: 1 Warnings: 0\n\nmysql> select @@version;select * from takanori;\n+-----------+\n| @@version |\n+-----------+\n| 8.0.18 |\n+-----------+\n1 row in set (0.00 sec)\n\n+----+-------+-------+\n| id | val10 | val20 |\n+----+-------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 100 | 10000 |\n| 4 | 100 | 2000 |\n+----+-------+-------+\n4 rows in set (0.01 sec)\n\nmysql><\/code><\/pre>\nmaster \u3067 set session binlog_row_image=minimal; \u3057\u3064\u3064\u66f4\u65b0\u3059\u308b\u3068<\/p>\n
mysql> set session binlog_row_image=minimal;\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql> select @@version;select * from takanori;\n+-----------+\n| @@version |\n+-----------+\n| 8.0.18 |\n+-----------+\n1 row in set (0.00 sec)\n\n+----+------+-------+\n| id | val1 | val2 |\n+----+------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 12000 |\n| 4 | 100 | 2000 |\n+----+------+-------+\n4 rows in set (0.01 sec)\n\nmysql> update takanori set val2=val2+2000 where id=3;\nQuery OK, 1 row affected (0.01 sec)\nRows matched: 1 Changed: 1 Warnings: 0\n\nmysql> select @@version;select * from takanori;\n+-----------+\n| @@version |\n+-----------+\n| 8.0.18 |\n+-----------+\n1 row in set (0.00 sec)\n\n+----+------+-------+\n| id | val1 | val2 |\n+----+------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 14000 |\n| 4 | 100 | 2000 |\n+----+------+-------+\n4 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\nslave \u306f\u4e8c\u756a\u76ee\u306eColumn\u304c\u66f4\u65b0\u3055\u308c\u307e\u305b\u3093\u3002<\/p>\n
mysql> select @@version;select * from takanori;\n+-----------+\n| @@version |\n+-----------+\n| 8.0.18 |\n+-----------+\n1 row in set (0.00 sec)\n\n+----+-------+-------+\n| id | val10 | val20 |\n+----+-------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 100 | 14000 |\n| 4 | 100 | 2000 |\n+----+-------+-------+\n4 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\nbinlog_row_image=full \u306b\u3059\u308b\u3068\u66f4\u65b0\u3055\u308c\u307e\u3059<\/p>\n
master<\/p>\n
mysql> set session binlog_row_image=full;\nQuery OK, 0 rows affected (0.01 sec)\n\nmysql> update takanori set val2=val2+2000 where id=3;\nQuery OK, 1 row affected (0.01 sec)\nRows matched: 1 Changed: 1 Warnings: 0\n\nmysql> select @@version;select * from takanori;\n+-----------+\n| @@version |\n+-----------+\n| 8.0.18 |\n+-----------+\n1 row in set (0.00 sec)\n\n+----+------+-------+\n| id | val1 | val2 |\n+----+------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 16000 |\n| 4 | 100 | 2000 |\n+----+------+-------+\n4 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\nslave<\/p>\n
mysql> select @@version;select * from takanori;\n+-----------+\n| @@version |\n+-----------+\n| 8.0.18 |\n+-----------+\n1 row in set (0.00 sec)\n\n+----+-------+-------+\n| id | val10 | val20 |\n+----+-------+-------+\n| 1 | 1 | 10 |\n| 2 | 100 | 100 |\n| 3 | 1 | 16000 |\n| 4 | 100 | 2000 |\n+----+-------+-------+\n4 rows in set (0.01 sec)\n\nmysql><\/code><\/pre>\n\u4eca\u65e5\u306f\u3053\u308c\u307e\u3067<\/h2>\n
\u4eca\u56de\u306f\u5fdc\u7528\u7de8\u3063\u307d\u3044\u8a71\u3092\u3057\u307e\u3057\u305f\u3002 Worklog 5092 \u306f\u3068\u3066\u3082\u91cd\u8981\u306a\u3053\u3068\u304c\u66f8\u3044\u3066\u3042\u308b\u3068\u601d\u3044\u307e\u3059\u3002\u3053\u306e\u3042\u305f\u308a\u95a2\u5fc3\u3042\u308b\u65b9\u306f\u3001\u719f\u8aad\u3057\u305f\u4e0a\u3067\u3001\u3044\u308d\u3044\u308d\u52d5\u304b\u3057\u3066\u307f\u305f\u308a\u3001\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u3092\u8aad\u3093\u3067\u3044\u305f\u3060\u304f\u3068\u826f\u3044\u3067\u3057\u3087\u3046\u3002<\/p>\n
\u6b21\u56de\u306f\u3082\u3046\u3061\u3087\u3063\u3068\u8e0f\u307f\u8fbc\u3093\u3067\u3044\u304d\u307e\u3059\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u3053\u3093\u306b\u3061\u308f\u3002\u305b\u3058\u307e\u3067\u3059\u3002 \u5f15\u304d\u7d9a\u304d replication \u306e\u8a71\u3092\u3057\u307e\u3059\u3002 \u306f\u3058\u3081\u306b \u7b2c\u4e09\u56de\u3067\u3059\u3002 \u524d\u56de\u306f THD::decide_logging_format() \u3092\u901a\u3058\u3066\u3001 binlog_format=ROW \u306b\u3064\u3044 […]<\/p>\n","protected":false},"author":137,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[9],"tags":[17],"class_list":["post-19752","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-info","tag-mysql"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/19752","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/users\/137"}],"replies":[{"embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/comments?post=19752"}],"version-history":[{"count":3,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/19752\/revisions"}],"predecessor-version":[{"id":19754,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/19752\/revisions\/19754"}],"wp:attachment":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/media?parent=19752"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/categories?post=19752"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/tags?post=19752"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}