![\"\u8a73\u3057\u304f\u306f\u5f8c\u65e5\u3001\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u4ea4\u3048\u3064\u3064\u5225\u306e\u304b\u305f\u3061\u3067\u3054\u7d39\u4ecb\u3057\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002\"](\"https:\/\/image.slidesharecdn.com\/mysqlonec2-190911061459\/95\/mysql-52-638.jpg\")
<\/p>\n
\u300c\u8a73\u3057\u304f\u306f\u5f8c\u65e5\u3001\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u4ea4\u3048\u3064\u3064\u5225\u306e\u304b\u305f\u3061\u3067\u3054\u7d39\u4ecb\u3057\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002\u300d\u3068\u8a00\u3063\u3066\u3044\u305f\u4ef6\u3067\u3059\u3002<\/p>\n
Row-Based Replication \u306e\u8a71\u3092\u3057\u307e\u3059\u3002<\/p>\n
\u6628\u5e74\u306e8\u6708\u9803\u3001\u300c\u305d\u308d\u305d\u308d\u3001SBR\u304b\u3089RBR\u306b\u79fb\u884c\u3057\u3066\u826f\u3044\u6642\u671f\u304b\u306a\u3041\u3001\u3057\u306a\u3044\u3068\u5c06\u6765\u3081\u3093\u3069\u304f\u3055\u3044\u304b\u306a\u3041\u300d\u300c\u3067\u3082\u3001 SET GLOBAL \u3067 binlog_format \u5909\u66f4\u3067\u304d\u306a\u3044\u3068\u3001\u624b\u9593\u304b\u304b\u3063\u3066\u3057\u3087\u3046\u304c\u306a\u3044\u306a\u3041\u300d\u300c\u3058\u3083\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u8aad\u3080\u304b\u3041\u300d\u3068\u3044\u3046\u3053\u3068\u3067\u3001MySQL \u306e Replication \u306f\u305d\u3093\u306a\u306b\u5f97\u610f\u5206\u91ce\u3067\u3082\u306a\u3044\u306e\u3067\u3001\u4ed6\u306e\u4ed5\u4e8b\u306e\u5408\u9593\u306b\u3061\u3087\u304f\u3061\u3087\u304f\u8abf\u3079\u3066\u3001\u793e\u5185\u6587\u66f8\u306b\u307e\u3068\u3081\u3066\u307e\u3057\u305f\u3002<\/p>\n
\u516c\u958b\u3057\u3066\u3082\u826f\u3044\u306e\u3067\u306f\u3001\u3068\u601d\u3063\u305f\u5185\u5bb9\u306a\u306e\u3067\u3001\u4e00\u90e8\u4fee\u6b63\u3057\u3064\u3064\u3001\u304a\u5c4a\u3051\u3057\u307e\u3059\u3002<\/p>\n
\u306f\u3058\u3081\u306b\u65ad\u3063\u3066\u304a\u304d\u307e\u3059\u3068\u3001\u3053\u306e\u8a71\u306f\u9577\u3044\u3067\u3059\u30022-3\u56de\u3067\u306f\u7d42\u308f\u3089\u306a\u3044\u3068\u601d\u3044\u307e\u3059\u306e\u3067\u3001\u3058\u3063\u304f\u308a\u8170\u3092\u636e\u3048\u3066\u8aad\u3093\u3067\u9802\u304f\u306e\u304c\u3088\u308d\u3057\u3044\u304b\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n
\u89e3\u8aac<\/h2>\n\u524d\u63d0\uff1a \u305d\u3082\u305d\u3082 binary log \u306b\u3088\u308b replication \u3068\u306f<\/h3>\n
\u307e\u305a\u306f\u524d\u63d0\u3068\u3057\u3066\u3001\u3053\u306e\u3042\u305f\u308a\u306e\u30b9\u30e9\u30a4\u30c9\u3092\u8aad\u3093\u3067\u3082\u3089\u3048\u308b\u3068\u3001MYSQL_BIN_LOG::ordered_commit() \u3042\u305f\u308a\u306e\u5b9f\u88c5\u304c\u308f\u304b\u308a\u307e\u3059\u3002<\/p>\n
- \n
- binary log \u3068 2PC \u3068 Group Commit<\/a><\/li>\n
- MySQL5.7 GA \u306e Multi-threaded slave<\/a><\/li>\n<\/ul>\n
slave \u306e SQL_Thread\uff08\u6700\u8fd1\u306f Applyer thread \u3068\u3082\u3044\u3044\u307e\u3059\u3002 binlog_format=row \u304c\u5c0e\u5165\u3055\u308c\u305f\u3053\u3068\u306b\u3088\u3063\u3066\u3001 SQL \u3060\u3051\u3067\u306f\u306a\u304f SQLCOM_BINLOG_BASE64_EVENT \u306a\u3069\u3082\u5b9f\u884c\u3059\u308b\u3088\u3046\u306b\u306a\u3063\u305f\u3063\u3066\u3053\u3068\u3067\u3057\u3087\u3046\uff09\u306f\u6b74\u53f2\u7684\u306b\u30b7\u30f3\u30b0\u30eb\u30b9\u30ec\u30c3\u30c9\u3067\u3059\u3002
\n\u3088\u3063\u3066\u3001SQL_Thread \u304c\u30b7\u30f3\u30b0\u30eb\u30b9\u30ec\u30c3\u30c9\u3060\u3063\u305f\u6642\u4ee3\u306f\u3001 master \u306f\u8907\u6570\u306e\u30b9\u30ec\u30c3\u30c9\u3067\u540c\u6642\u306b\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u3092\u5b9f\u884c\u3067\u304d\u307e\u3057\u305f\u304c\u3001 slave \u306f\u4e00\u3064\u306eSQL_Thread\u3060\u3051\u3067\u4e00\u500b\u305a\u3064\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u3092\u5b9f\u884c\u3059\u308b\u3057\u304b\u306a\u304b\u3063\u305f\u306e\u3067\u3059\u3002
\n\u305d\u3046\u3067\u3042\u308b\u306a\u3089\u3070\u3001master \u304c\u8907\u6570\u306e\u30b9\u30ec\u30c3\u30c9\u3067\u5b9f\u884c\u3057\u3066\u3044\u305f\u8907\u6570\u306e\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u3092\u3001\u30b7\u30ea\u30a2\u30e9\u30a4\u30ba\u3057\u3066 binary log \u306b\u66f8\u3044\u3066\u3001\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u4e00\u500b\u4e00\u500b\u3092 SQL_Thread \u3067 replay \u3059\u308b\u3057\u304b\u306a\u3044\u308f\u3051\u3067\u3059\u3002<\/p>\n\u3068\u3044\u3046\u308f\u3051\u3067\u3001MYSQL_BIN_LOG::ordered_commit() \u306f\u3001\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u304c commit \u3055\u308c\u305f\u9806\u756a\u3067\u3001\u5404\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u3092 BEGIN \uff5e COMMIT \u3067\u631f\u3093\u3067\u3001\u30b7\u30ea\u30a2\u30e9\u30a4\u30ba\u3055\u308c\u305f\u72b6\u614b\u3067 binary log \u306b\u66f8\u304d\u51fa\u3057\u3066\u3044\u307e\u3059\u3002
\nInnoDB \u306e Isolation Level \u7684\u306b\u8003\u3048\u308b\u3068\u3001 READ UNCOMMITTED \u3067\u306a\u3044\u9650\u308a\u3001\u4ed6\u306e\u30b9\u30ec\u30c3\u30c9\u306fCOMMIT \u3055\u308c\u3066\u306a\u3044\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u306e\u7d50\u679c\u3092\u53c2\u7167\u3067\u304d\u307e\u305b\u3093\u3002
\n\u3088\u3063\u3066\u3001 master \u306e InnoDB \u3067\u540c\u6642\u306b\u8907\u6570\u306e\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u5b9f\u884c\u3055\u308c\u3066\u3044\u305f\u3068\u3057\u3066\u3082\u3001COMMIT \u3055\u308c\u306a\u3044\u9650\u308a\u3001\u305d\u306e\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u306b\u3088\u308b\u66f4\u65b0\u7d50\u679c\u3092\u4ed6\u306e\u30b9\u30ec\u30c3\u30c9\u306f\u53c2\u7167\u3067\u304d\u306a\u3044\u306e\u3067\u3001 COMMIT \u306e\u9806\u304c\u4fdd\u305f\u308c\u3066\u3044\u308c\u3070\u3001\u305d\u306e binary log \u3067\u30ec\u30d7\u30ea\u30b1\u30fc\u30b7\u30e7\u30f3\u3057\u3066\u3044\u308b slave \u3067\u898b\u3048\u3066\u3044\u308b\u7d50\u679c\u306f\u3001\uff08\u975e\u540c\u671f\u306a\u306e\u3067\u9045\u5ef6\u3057\u3066\u3044\u308b\u304c\uff09\u6574\u5408\u6027\u304c\u62c5\u4fdd\u3067\u304d\u308b\u306f\u305a\u3067\u3042\u308b\u3001\u3068\u3002\u305d\u3046\u3044\u3046\u308f\u3051\u3067\u3057\u3087\u3046\u3002<\/p>\n\uff08master \u3068 slave \u306e\u72b6\u614b\u304c\u7570\u306a\u3063\u3066\u3044\u308b\u5834\u5408\u3001 unsafe statement \u3068\u304b\u3067\u6574\u5408\u6027\u304c\u5d29\u308c\u3066\u3057\u307e\u3046\u30b1\u30fc\u30b9\u3082\u3042\u308a\u3048\u308b\u306e\u3067\u3059\u304c\u3001\u305d\u308c\u306b\u3064\u3044\u3066\u306f\u4eca\u56de\u8a00\u53ca\u3057\u306a\u3044\u3053\u3068\u306b\u3057\u307e\u3059\uff09<\/p>\n
binlog_format=ROW \u306b\u306a\u3063\u305f\u3068\u304d\u3001binlog event \u306f\u3069\u306e\u6642\u70b9\u3067 transaction cache \u306b\u66f8\u304d\u51fa\u3055\u308c\u308b\u306e\u304b<\/h3>\n
MySQL Musings:Binary Log Group Commit in MySQL 5.6 <\/a> \u304b\u3089\u629c\u7c8b\u3059\u308b\u3068<\/p>\n
\n As the server executes transactions the server will collect the changes done by the transaction in a per-connection transaction cache. If statement-based replication is used, the statements will be written to the transaction cache, and if row-based replication is used, the actual rows changed will be written to the transaction cache. Once the transaction commits, the transaction cache is written to the binary log as one single block.\n<\/p><\/blockquote>\n
\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u3092\u8aad\u3093\u3060\u307b\u3046\u304c\u65e9\u3044\u3067\u3057\u3087\u3046\u304b\u3089\u3001 MySQL 8.0.12 \u306e\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u3092\u3082\u3068\u306b\u89e3\u8aac\u3057\u307e\u3059\u3002<\/p>\n
\u203b\u5f53\u6642\u3001\u300c\u307c\u3061\u307c\u3061MySQL 8.0\u306e\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u8aad\u3093\u3069\u304f\u304b\u306a\u3041\u300d\u3068\u601d\u3063\u3066\u3044\u305f\u306e\u3067\u3001\u4ee5\u964d\u306e\u8aac\u660e\u306f MySQL 8.0.12 \u304c\u524d\u63d0\u3068\u306a\u3063\u3066\u3044\u307e\u3059\u3002<\/p>\n
\u3068\u308a\u3042\u3048\u305a\u3001\uff08UPDATE\u306f\u8aad\u307f\u3084\u3059\u3044\u304b\u306a\u3068\u601d\u3063\u305f\u306e\u3067\uff09 SQLCOM_UPDATE \u53d7\u3051\u3066\u304b\u3089 binlog_format=row \u3067 UPDATE_ROWS_EVENT \u66f8\u304f\u3068\u3053\u308d\u307e\u3067<\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/sql_parse.cc#L3218<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/sql_parse.cc#L3228<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/sql_select.cc#L469<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/sql_select.cc#L554<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/sql_update.cc#L1514<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/sql_update.cc#L265<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/sql_update.cc#L809<\/a><\/p>\n
} else {\n \/* Non-batched update *\/\n error =\n table->file->ha_update_row(table->record[1], table->record[0]);\n }\n if (error == 0)\n updated_rows++;<\/code><\/pre>\nhttps:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/handler.cc#L7490<\/a><\/p>\n
handler \u306e\u4e2d\u3067\u3001 binlog_log_row() \u3067 UPDATE_ROWS_EVENT \u3092 transaction cache \u306b\u66f8\u3044\u3066\u3044\u304d\u307e\u3059\u3002<\/p>\n
Log_func *log_func = Update_rows_log_event::binlog_row_logging_function;<\/code><\/pre>\n
MYSQL_TABLE_IO_WAIT(PSI_TABLE_UPDATE_ROW, active_index, error,\n { error = update_row(old_data, new_data); })\n\n if (unlikely(error)) return error;\n if (unlikely((error = binlog_log_row(table, old_data, new_data, log_func))))<\/code><\/pre>\nhttps:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/handler.cc#L7513<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/sql_update.cc#L953<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/binlog.cc#L11251<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/binlog.cc#L11277<\/a><\/p>\n
\u305d\u306e query \u304a\u308f\u3063\u305f\u3089\u3001 binlog_flush_pending_rows_event() \u3067 STMT_END_F \u66f8\u3044\u3066 UPDATE_ROWS_EVENT \u306e\u7d42\u308f\u308a\u3067\u3042\u308b\u3053\u3068\u3092\u793a\u3057\u307e\u3059\u3002<\/p>\n
\/*\n If we are not in prelocked mode, mysql_unlock_tables() will be\n called after this binlog_query(), so we have to flush the pending\n rows event with the STMT_END_F set to unlock all tables at the\n slave side as well.\n If we are in prelocked mode, the flushing will be done inside the\n top-most close_thread_tables().\n *\/\n if (this->locked_tables_mode <= LTM_LOCK_TABLES)\n if (int error = binlog_flush_pending_rows_event(true, is_trans))\n DBUG_RETURN(error);<\/code><\/pre>\n\u3061\u306a\u307f\u306b\u3001 binlog_format=statement \u306e\u5834\u5408\u306f<\/h3>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/sql_update.cc#L953<\/a><\/p>\n
\u3053\u3053\u3067\u6e21\u3057\u3066\u308b thd->query() \u304b\u3089<\/p>\n
if (thd->binlog_query(THD::ROW_QUERY_TYPE, thd->query().str,\n thd->query().length, transactional_table, false,\n false, errcode)) {<\/code><\/pre>\nhttps:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.12\/sql\/binlog.cc#L11344<\/a><\/p>\n
Query_log_event \u306einstance\u751f\u6210\u3057\u3066 mysql_bin_log.write_event() \u3059\u308b\u3060\u3051\u3067\u3059\u306d\u3002<\/p>\n
case THD::STMT_QUERY_TYPE:\n \/*\n The MYSQL_BIN_LOG::write() function will set the STMT_END_F flag and\n flush the pending rows event if necessary.\n *\/\n {\n Query_log_event qinfo(this, query_arg, query_len, is_trans, direct,\n suppress_use, errcode);\n \/*\n Binlog table maps will be irrelevant after a Query_log_event\n (they are just removed on the slave side) so after the query\n log event is written to the binary log, we pretend that no\n table maps were written.\n *\/\n int error = mysql_bin_log.write_event(&qinfo);\n binlog_table_maps = 0;\n DBUG_RETURN(error);\n }<\/code><\/pre>\n\u5177\u4f53\u7684\u306b\u78ba\u8a8d\u3059\u308b\u3068<\/h3>\n
\u4f8b\u3048\u3070\u3001MySQL8.0.18 \u3067 INSERT \u3067\u78ba\u8a8d\u3057\u3066\u307f\u307e\u3059\u3068\u3001<\/p>\n
mysql> show create table sejima\\G\n*************************** 1. row ***************************\n Table: sejima\nCreate Table: CREATE TABLE `sejima` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `rank` int(11) NOT NULL,\n PRIMARY KEY (`id`)\n1 row in set (0.00 sec)\n\nmysql> show create table sejima\\G\n*************************** 1. row ***************************\n Table: sejima\nCreate Table: CREATE TABLE `sejima` (\n `id` int(11) NOT NULL AUTO_INCREMENT,\n `rank` int(11) NOT NULL,\n PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=982865 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci\n1 row in set (0.00 sec)\n\nmysql> show table status like 'sejima'\\G\n*************************** 1. row ***************************\n Name: sejima\n Engine: InnoDB\n Version: 10\n Row_format: Dynamic\n Rows: 0\n Avg_row_length: 0\n Data_length: 16384\nMax_data_length: 0\n Index_length: 0\n Data_free: 0\n Auto_increment: 982865\n Create_time: 2019-10-15 11:28:18\n Update_time: NULL\n Check_time: NULL\n Collation: utf8mb4_0900_ai_ci\n Checksum: NULL\n Create_options:\n Comment:\n1 row in set (0.00 sec)\n\nmysql> show global status like 'InnoDB_R%';\n+-------------------------------+-------+\n| Variable_name | Value |\n+-------------------------------+-------+\n| Innodb_row_lock_current_waits | 0 |\n| Innodb_row_lock_time | 0 |\n| Innodb_row_lock_time_avg | 0 |\n| Innodb_row_lock_time_max | 0 |\n| Innodb_row_lock_waits | 0 |\n| Innodb_rows_deleted | 16 |\n| Innodb_rows_inserted | 37 |\n| Innodb_rows_read | 4679 |\n| Innodb_rows_updated | 341 |\n+-------------------------------+-------+\n9 rows in set (0.00 sec)\n\nmysql> insert into sejima (`rank`) values (3),(4);\nQuery OK, 2 rows affected (0.02 sec)\nRecords: 2 Duplicates: 0 Warnings: 0\n\nmysql> show global status like 'InnoDB_R%';\n+-------------------------------+-------+\n| Variable_name | Value |\n+-------------------------------+-------+\n| Innodb_row_lock_current_waits | 0 |\n| Innodb_row_lock_time | 0 |\n| Innodb_row_lock_time_avg | 0 |\n| Innodb_row_lock_time_max | 0 |\n| Innodb_row_lock_waits | 0 |\n| Innodb_rows_deleted | 16 |\n| Innodb_rows_inserted | 39 |\n| Innodb_rows_read | 4679 |\n| Innodb_rows_updated | 341 |\n+-------------------------------+-------+\n9 rows in set (0.00 sec)\n\nmysql> exit\nBye<\/code><\/pre>\n$ .\/mysqlbinlog \/var\/lib\/mysql\/binlog.000003 -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#191015 11:29:14 server id 1 end_log_pos 124 CRC32 0xd1fc759e Start: binlog v 4, server v 8.0.18 created 191015 11:29:14\n# Warning: this binlog is either in use or was not closed properly.\nBINLOG '\niq2lXQ8BAAAAeAAAAHwAAAABAAQAOC4wLjE4AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA\nCgGedfzR\n'\/*!*\/;\n# at 124\n#191015 11:29:14 server id 1 end_log_pos 155 CRC32 0x09c52830 Previous-GTIDs\n# [empty]\n# at 155\n#191015 11:30:19 server id 1 end_log_pos 234 CRC32 0x0b72fbd9 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1571139019517356 immediate_commit_timestamp=1571139019517356 transaction_length=357\n\/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*\/\/*!*\/;\n# original_commit_timestamp=1571139019517356 (2019-10-15 11:30:19.517356 UTC)\n# immediate_commit_timestamp=1571139019517356 (2019-10-15 11:30:19.517356 UTC)\n\/*!80001 SET @@session.original_commit_timestamp=1571139019517356*\/\/*!*\/;\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#191015 11:30:19 server id 1 end_log_pos 309 CRC32 0x49e79d20 Query thread_id=144 exec_time=0 error_code=0\nSET TIMESTAMP=1571139019\/*!*\/;\nSET @@session.pseudo_thread_id=144\/*!*\/;\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 309\n#191015 11:30:19 server id 1 end_log_pos 375 CRC32 0x282997b8 Rows_query\n# insert into sejima (`rank`) values (3),(4)\n# at 375\n#191015 11:30:19 server id 1 end_log_pos 428 CRC32 0x7d597706 Table_map: `test`.`sejima` mapped to number 91\n# at 428\n#191015 11:30:19 server id 1 end_log_pos 481 CRC32 0xd440841e Write_rows: table id 91 flags: STMT_END_F\n\nBINLOG '\ny62lXR0BAAAAQgAAAHcBAACAACppbnNlcnQgaW50byBzZWppbWEgKGByYW5rYCkgdmFsdWVzICgz\nKSwoNCm4lyko\ny62lXRMBAAAANQAAAKwBAAAAAFsAAAAAAAEABHRlc3QABnNlamltYQACAwMAAAEBAAZ3WX0=\ny62lXR4BAAAANQAAAOEBAAAAAFsAAAAAAAEAAgAC\/wBR\/w4AAwAAAABS\/w4ABAAAAB6EQNQ=\n'\/*!*\/;\n### INSERT INTO `test`.`sejima`\n### SET\n### @1=982865 \/* INT meta=0 nullable=0 is_null=0 *\/\n### @2=3 \/* INT meta=0 nullable=0 is_null=0 *\/\n### INSERT INTO `test`.`sejima`\n### SET\n### @1=982866 \/* INT meta=0 nullable=0 is_null=0 *\/\n### @2=4 \/* INT meta=0 nullable=0 is_null=0 *\/\n# at 481\n#191015 11:30:19 server id 1 end_log_pos 512 CRC32 0x732827f3 Xid = 305\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$<\/code><\/pre>\nhandler \u7d4c\u7531\u3067 InnoDB \u3092\u66f4\u65b0\u3057\u3066\u3066\u3001 handler API \u7d4c\u7531\u3067 record \u8ffd\u52a0\u3057\u305f\u3068\u304d\u306b WRITE_ROWS_EVENT \u3092 transaction cache \u306b\u66f8\u3044\u3066\u3044\u308b\u308f\u3051\u306a\u306e\u3067<\/p>\n
WRITE_ROWS_EVENT \u3067\u884c\u304c\u8ffd\u52a0\u3055\u308c\u3066\u3044\u308b\u3068\u3044\u3046\u3053\u3068\u306f\u3001\u305d\u308c\u3060\u3051 Innodb_rows_inserted \u3082\u5897\u3048\u3066\u3044\u307e\u3059\u3002
\n\u4f59\u8ac7\u3067\u3059\u304c\u3001WRITE_ROWS_EVENT \u3067 record \u4e00\u4ef6\u3042\u305f\u308a\u3069\u308c\u304f\u3089\u3044\u306e\u30c7\u30fc\u30bf\u304c\u8ffd\u52a0\u3055\u308c\u305d\u3046\u304b \u306f\u3001 Avg_row_length \u3092\u53c2\u8003\u306b\u3067\u304d\u308b\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\u3002\uff08Avg_row_length \u306f\u3061\u3087\u3046\u3056\u3063\u304f\u308a\u3057\u305f\u6982\u7b97\u306a\u306e\u3067\u3001\u3042\u304f\u307e\u3067\u53c2\u8003\u306b\u3057\u304b\u306a\u3089\u306a\u3044\u3067\u3059\u304c\uff09 \u3002<\/p>\n\u3061\u306a\u307f\u306b\u3001binlog_rows_query_log_events<\/a> \u3092\u6709\u52b9\u306b\u3059\u308b\u3068\u3001 binlog_format=ROW \u306e\u3068\u304d\u3067\u3082 statement \u3092 binary log \u306b\u30b3\u30e1\u30f3\u30c8\u3068\u3057\u3066\u8a18\u9332\u3059\u308b\u3053\u3068\u304c\u3067\u304d\u307e\u3059\u3002\u5177\u4f53\u7684\u306b\u306f\u3001 \u4e0a\u8a18\u306e\u4f8b\u3060 \u3068 Rows_query \u304c\u305d\u308c\u306b\u5f53\u305f\u308a\u307e\u3059\u3002<\/p>\n
\u4f8b\u3048\u3070\u3001 show binlog events \u3067\u898b\u308b\u3068\u6b21\u306e\u3088\u3046\u306b\u306a\u308a\u307e\u3059\u3002 Query \u306f binlog_format=STATEMENT \u3067\u5b9f\u884c \u3057\u305f query \u3067\u3001 Rows_query \u3084 Writes_rows \u304c\u3001 binlog_format=ROW \u3067\u5b9f\u884c\u3057\u305f\u7d50\u679c\u3067\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 binlog events in 'binlog.000003';\n+---------------+-----+----------------+-----------+-------------+----------------------------------------------+\n| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |\n+---------------+-----+----------------+-----------+-------------+----------------------------------------------+\n| binlog.000003 | 4 | Format_desc | 1 | 124 | Server ver: 8.0.18, Binlog ver: 4 |\n| binlog.000003 | 124 | Previous_gtids | 1 | 155 | |\n| binlog.000003 | 155 | Anonymous_Gtid | 1 | 234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |\n| binlog.000003 | 234 | Query | 1 | 309 | BEGIN |\n| binlog.000003 | 309 | Rows_query | 1 | 375 | # insert into sejima (`rank`) values (3),(4) |\n| binlog.000003 | 375 | Table_map | 1 | 428 | table_id: 91 (test.sejima) |\n| binlog.000003 | 428 | Write_rows | 1 | 481 | table_id: 91 flags: STMT_END_F |\n| binlog.000003 | 481 | Xid | 1 | 512 | COMMIT \/* xid=305 *\/ |\n+---------------+-----+----------------+-----------+-------------+----------------------------------------------+\n8 rows in set (0.00 sec)\n\nmysql><\/code><\/pre>\nRows_query \u304c\u3044\u3064 transaction cache \u306b\u66f8\u304b\u308c\u308b\u304b\u3068\u3044\u3046\u3068\u3001\u3082\u3046\u3044\u3061\u3069\u3001handler::ha_update_row() \u3042\u305f\u308a\u304b\u3089\u8aad\u3080\u3068\u308f\u304b\u308a\u3084\u3059\u3044\u3067\u3059\u3002<\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/\/mysql-8.0.12\/sql\/handler.cc#L7490<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/\/mysql-8.0.12\/sql\/handler.cc#L7513<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/\/mysql-8.0.12\/sql\/handler.cc#L7350<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/\/mysql-8.0.12\/sql\/handler.cc#L7298<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/\/mysql-8.0.12\/sql\/handler.cc#L7311<\/a><\/p>\n
binlog_rows_query_log_events \u304c true \u306a\u5834\u5408\u3001<\/p>\n
bool need_binlog_rows_query = thd->variables.binlog_rows_query_log_events;<\/code><\/pre>\nhttps:\/\/github.com\/mysql\/mysql-server\/blob\/\/mysql-8.0.12\/sql\/handler.cc#L7333<\/a><\/p>\n
int const error = thd->binlog_write_table_map(table, has_trans,\n need_binlog_rows_query);<\/code><\/pre>\nhttps:\/\/github.com\/mysql\/mysql-server\/blob\/\/mysql-8.0.12\/sql\/binlog.cc#L9376<\/a><\/p>\n
https:\/\/github.com\/mysql\/mysql-server\/blob\/\/mysql-8.0.12\/sql\/binlog.cc#L9397<\/a><\/p>\n
if (binlog_rows_query && this->query().str) {\n \/* Write the Rows_query_log_event into binlog before the table map *\/\n Rows_query_log_event rows_query_ev(this, this->query().str,\n this->query().length);\n if ((error = cache_data->write_event(this, &rows_query_ev)))\n DBUG_RETURN(error);\n }\n\n if ((error = cache_data->write_event(this, &the_event))) DBUG_RETURN(error);<\/code><\/pre>\n\u4e0a\u8a18\u306e show binlog events; \u306e\u3088\u3046\u306b\u3001 Table_map(TABLE_MAP_EVENT) \u3092\u66f8\u304f\u524d\u306bRows_query(ROWS_QUERY_LOG_EVENT) \u3092\u66f8\u3044\u3066\u3044\u307e\u3059\u3002<\/p>\n
TABLE_MAP_EVENT \u306b\u3064\u3044\u3066\u306f\u3001\u3053\u306e\u3042\u305f\u308a\u304c\u8a73\u3057\u3044\u3067\u3059\u3002<\/p>\n
- \n
- 20.10 Row-Based Binary Logging<\/a><\/li>\n
- 20.6 Event Meanings<\/a><\/li>\n<\/ul>\n
\n Used for row-based binary logging. This event precedes each row operation event. It maps a table definition to a number, where the table definition consists of database and table names and column definitions. The purpose of this event is to enable replication when a table has different definitions on the master and slave. Row operation events that belong to the same transaction may be grouped into sequences, in which case each such sequence of events begins with a sequence ofTABLE_MAP_EVENT events: one per table used by events in the sequence.\n<\/p><\/blockquote>\n
- \n
- 20.9 Event Data for Specific Event Types<\/a><\/li>\n<\/ul>\n
\u96d1\u306b\u8003\u3048\u308b\u3068\u3001 InnoDB \u306f\u5185\u90e8\u7684\u306b *.ibd \u3054\u3068\u306b table \u306e id \u3092\u5272\u308a\u5f53\u3066\u3066\u3044\u308b\u306e\u3067\u3059\u304c\u3001 table \u306e\u4f5c\u6210\u9806\u5e8f\u3084 table \u306e\u500b\u6570\u304c master \u3068 slave \u3067\u5b8c\u5168\u306b\u4e00\u81f4\u3059\u308b\u3068\u306f\u9650\u308a\u307e\u305b\u3093\u3002\u306a\u306e\u3067\u3001 master \u3068 slave \u3067 \uff08\u5185\u90e8\u7684\u306a\uff09 table \u306e id \u304c\u4e00\u81f4\u3059\u308b\u3068\u3082\u9650\u3089\u306a\u3044\u306e\u3067\u3001 database \u306e\u540d\u524d\u3068 table \u540d\u3001\u305d\u3057\u3066\u305d\u306e table \u306b\u5bfe\u3059\u308b\u4e00\u610f\u306a id \u3092 TABLE_MAP_EVENT \u3067\u5272\u308a\u5f53\u3066\u308b\u3053\u3068\u306b\u3088\u308a\u3001 TABLE_MAP_EVENT \u306e\u5f8c\u306b\u7d9a\u304f WRITE_ROWS_EVENT \u3067\u5bfe\u8c61\u306e\u30c6\u30fc\u30d6\u30eb\u3092\u66f4\u65b0\u3067\u304d\u308b\u3001\u3068\u3044\u3046\u3053\u3068\u3067\u306f\u306a\u3044\u3067\u3057\u3087\u3046\u304b\u3002<\/p>\n
\u4f8b\u3048\u3070\u4e0a\u8a18\u306e show binlog events \u3067\u3044\u3046\u3068\u3001 Anonymous_Gtid \u304b\u3089 Xid\uff08COMMIT\uff09
\n\u307e\u3067\u304c\u4e00\u9023\u306e\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u3068\u306a\u308b\u306e\u3067\u306f\u306a\u3044\u304b\u3068\u3002<\/p>\n| binlog.000003 | 155 | Anonymous_Gtid | 1 | 234 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |\n| binlog.000003 | 234 | Query | 1 | 309 | BEGIN |\n| binlog.000003 | 309 | Rows_query | 1 | 375 | # insert into sejima (`rank`) values (3),(4) |\n| binlog.000003 | 375 | Table_map | 1 | 428 | table_id: 91 (test.sejima) |\n| binlog.000003 | 428 | Write_rows | 1 | 481 | table_id: 91 flags: STMT_END_F |\n| binlog.000003 | 481 | Xid | 1 | 512 | COMMIT \/* xid=305 *\/ |<\/code><\/pre>\nROWS_QUERY_LOG_EVENT \u306b\u95a2\u3059\u308b WorkLog \u306a\u3069<\/h2>\n
Bug #50935 Record original SQL statement in RBR binlog<\/a> \u3068\u3044\u3046 Feature request \u304c\u4e8b\u306e\u767a\u7aef\u3067\u3042\u308b\u3088\u3046\u3067\u3059<\/p>\n
\n To aid in debugging replication issues, it would help to have the full
\n original SQL statement (including any embedded comments) recorded in the
\n binlog when in RBR mode.
\n The reconstructed pseudo-SQL is not always sufficient to uniquely
\n identify the source of the statement.\n<\/p><\/blockquote>\nbinary log \u4f7f\u3063\u3066 Point In Time Recovery \u3057\u305f\u308a duplicate \u3057\u305f event \u3092 skip \u3057\u305f\u308a\u3059\u308b\u3068
\n\u304d\u3001\u5143\u306e query \u304c log \u306b\u306a\u3044\u3068\u56f0\u308b\u3060\u308d\u3046\u3001\u3068\u3044\u3046\u3053\u3068\u3067\u3057\u3087\u3046\u3002<\/p>\n\u305d\u3057\u3066 MySQL5.6 \u3067 binlog_rows_query_log_events \u304c\u8ffd\u52a0\u3055\u308c\u3001<\/p>\n
- 20.6 Event Meanings<\/a><\/li>\n<\/ul>\n
- MySQL5.7 GA \u306e Multi-threaded slave<\/a><\/li>\n<\/ul>\n