MySql Binlog statement row mixedの3つのモードの初期プローブ

30076 ワード

一言で紹介する
以下は主にMySql Binlogのrowフォーマットとstatementの内容と関連する知識点を示した.

ROWレコードはEVENT TYPEを含み、各行に基づいている.すなわち、binlogに記録されているのは具体的なこのsqlではなく、その文の各行または複数行のレコードに対してそれぞれレコードを生成し、同じsqlに対して主が下から異なる結果を生じることを効果的に回避することができる(force indxの例を参照).この方法は間違いなく最も安全だが、効率と空間的な消費が最も大きい.

STATAMENTはsql実行文(表示レコード)に基づいており、rowに比べて記憶領域が少ない.データの同期に使用する場合は慎重に、マスタスレーブマシン間の整合性(variablesパラメータ、Binlogログ形式パラメータ、テーブルエンジン、データ、インデックスなど)を保証する必要があります.保証できない場合は、データの復元に使用するシナリオは慎重に使用する必要があります(次のupdate where limit文の例を参照してください).

MIXEDフォーマットは、行と文を自動的に判断して自動的に切り替えるポリシーであり、自動である以上、サーバレベルで絶対的なセキュリティが達成されない限り、各ビジネスシーンに完全に適合することは保証されません.

環境およびパラメータの説明
環境パラメータ

mysql> select version();
+-----------------------------+
| version()                   |
+-----------------------------+
| 5.7.30-0ubuntu0.16.04.1-log |
+-----------------------------+

mysql> show variables like "%binlog%";
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 32768                |
| binlog_checksum                            | CRC32                |
| binlog_direct_non_transactional_updates    | OFF                  |
| binlog_error_action                        | ABORT_SERVER         |
| binlog_format                              | ROW                  |
| binlog_group_commit_sync_delay             | 0                    |
| binlog_group_commit_sync_no_delay_count    | 0                    |
| binlog_gtid_simple_recovery                | ON                   |
| binlog_max_flush_queue_time                | 0                    |
| binlog_order_commits                       | ON                   |
| binlog_row_image                           | FULL                 |
| binlog_rows_query_log_events               | OFF                  |
| binlog_stmt_cache_size                     | 32768                |
| binlog_transaction_dependency_history_size | 25000                |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER         |
| innodb_api_enable_binlog                   | OFF                  |
| innodb_locks_unsafe_for_binlog             | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 18446744073709547520 |
| max_binlog_size                            | 104857600            |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |
+--------------------------------------------+----------------------+

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000003 |     18117 |
| mysql-bin.000004 |       523 |
| mysql-bin.000005 |       523 |
| mysql-bin.000006 |       177 |
| mysql-bin.000007 |      4232 |
+------------------+-----------+

説明
具体的には、本明細書の操作とは強く関連していないが、すでに存在するbinlog、d現在の最新のファイル名はmysql-bin.000007 である.主な目的はコンテキストの境界線(id識別)を作ることです.

root@base2018:/var/log/mysql# ls -lt|grep mysql-bin
-rw-r----- 1 mysql mysql  4232 Jul  2 10:32 mysql-bin.000007
-rw-r----- 1 mysql mysql   160 Jul  2 10:24 mysql-bin.index
-rw-r----- 1 mysql mysql   177 Jul  2 10:23 mysql-bin.000006
-rw-r----- 1 mysql mysql   523 Jun 25 06:25 mysql-bin.000005
-rw-r----- 1 mysql mysql   523 Jun 24 06:25 mysql-bin.000004
-rw-r----- 1 mysql mysql 18117 Jun 23 06:25 mysql-bin.000003

show binlog events in 'mysql-bin.000007'\G

.##   ..
*************************** 32. row ***************************
   Log_name: mysql-bin.000007
        Pos: 2318
 Event_type: Write_rows
  Server_id: 1
End_log_pos: 2366
       Info: table_id: 120 flags: STMT_END_F
*************************** 33. row ***************************
   Log_name: mysql-bin.000007
        Pos: 2366
 Event_type: Xid
  Server_id: 1
End_log_pos: 2397
       Info: COMMIT /* xid=62 */
33 rows in set (0.00 sec)

最後のrow idの詳細を見ると、Posが2366であるため、この33を覚えています.
造作を始める
表を作成してテストデータを挿入する

CREATE TABLE `binlog_demo` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;

insert into binlog_demo values(1,1,'2020-07-01');
insert into binlog_demo values(2,2,'2020-07-02');
insert into binlog_demo values(3,3,'2020-07-02');
insert into binlog_demo values(4,4,'2020-07-04');
insert into binlog_demo values(5,5,'2020-07-05');

sql(rowフォーマット)を実行

mysql> delete from binlog_demo /*comment*/  where a>=4 and t_modified<='2020-07-10' limit 1;
Query OK, 1 row affected (0.01 sec)

上の第33条に続いて見ます

*************************** 34. row ***************************
   Log_name: mysql-bin.000007
        Pos: 2397
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 2462
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 35. row ***************************
   Log_name: mysql-bin.000007
        Pos: 2462
 Event_type: Query
  Server_id: 1
End_log_pos: 2542
       Info: BEGIN
*************************** 36. row ***************************
   Log_name: mysql-bin.000007
        Pos: 2542
 Event_type: Table_map
  Server_id: 1
End_log_pos: 2599
       Info: table_id: 120 (test.binlog_demo)
*************************** 37. row ***************************
   Log_name: mysql-bin.000007
        Pos: 2599
 Event_type: Delete_rows
  Server_id: 1
End_log_pos: 2647
       Info: table_id: 120 flags: STMT_END_F
*************************** 38. row ***************************
   Log_name: mysql-bin.000007
        Pos: 2647
 Event_type: Xid
  Server_id: 1
End_log_pos: 2678
       Info: COMMIT /* xid=86 */

\Gを外して次の5行に対応して展開し、2397は前のbinlogのEnd_log_pos、つまりその33のend posであることに注意してください.

| mysql-bin.000007 | 2397 | Anonymous_Gtid |         1 |        2462 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                                                      |
| mysql-bin.000007 | 2462 | Query          |         1 |        2542 | BEGIN                                                                                                                                                                                                                                                     |
| mysql-bin.000007 | 2542 | Table_map      |         1 |        2599 | table_id: 120 (test.binlog_demo)                                                                                                                                                                                                                          |
| mysql-bin.000007 | 2599 | Delete_rows    |         1 |        2647 | table_id: 120 flags: STMT_END_F                                                                                                                                                                                                                           |
| mysql-bin.000007 | 2647 | Xid            |         1 |        2678 | COMMIT /* xid=86 */                                                                                                                                                                                                                                       |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
38 rows in set (0.00 sec)

2462から見たmysqlbinlog -vv mysql-bin.000007 --start-position=2462

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200702 10:24:14 server id 1  end_log_pos 123 CRC32 0xa97adc3a     Start: binlog v 4, server v 5.7.30-0ubuntu0.16.04.1-log created 200702 10:24:14 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
TkX9Xg8BAAAAdwAAAHsAAAABAAQANS43LjMwLTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABORf1eEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATrceqk=
'/*!*/;
# at 2462
#200702 11:26:00 server id 1  end_log_pos 2542 CRC32 0x2e66d2a1     Query    thread_id=14    exec_time=0    error_code=0
SET TIMESTAMP=1593660360/*!*/;
SET @@session.pseudo_thread_id=14/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 2542
#200702 11:26:00 server id 1  end_log_pos 2599 CRC32 0x8d66ca1a     Table_map: `test`.`binlog_demo` mapped to number 120
# at 2599
#200702 11:26:00 server id 1  end_log_pos 2647 CRC32 0x59777bf5     Delete_rows: table id 120 flags: STMT_END_F

BINLOG '
yFP9XhMBAAAAOQAAACcKAAAAAHgAAAAAAAEABHRlc3QAC2JpbmxvZ19kZW1vAAMDAxEBAAIaymaN
yFP9XiABAAAAMAAAAFcKAAAAAHgAAAAAAAEAAgAD//gEAAAABAAAAF7/VgD1e3dZ
'/*!*/;
### DELETE FROM `test`.`binlog_demo`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1593792000 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 2647
#200702 11:26:00 server id 1  end_log_pos 2678 CRC32 0xba0bb150     Xid = 86
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*/;

上記のフィールド値の簡単な説明

server_idは、サービス識別と相互所有を保証する際にbinlog

の循環導入を避けることができる.

CRC 32、すなわちshow variables like "%binlog%"にbinlog_checksumを設ける検査方式

Table_mapもイベントであるが、rowのフォーマットのみをサポートする(statement自体がsqlである)テーブルとidのマッピングであり、複数を操作すると複数の

に対応する.

Delete_rowsはROWS_EVENTの1つであり、よく見られるのはUPDATE_ROWS_EVENT、WRITE_ROWS_EVENT、その他の≪イベント｜Events｜ldap≫である.

whereはsql文のwhere条件の具体的な値

である.

Xidとcommitは最終ok

を表す
statementに切り替え

mysql>  SET SESSION binlog_format = 'STATEMENT';
Query OK, 0 rows affected (0.00 sec)

insert into binlog_demo values(6,66,'2020-07-06');

上の39行に続く

*************************** 40. row ***************************
   Log_name: mysql-bin.000007
        Pos: 2743
 Event_type: Query
  Server_id: 1
End_log_pos: 2830
       Info: BEGIN
*************************** 41. row ***************************
   Log_name: mysql-bin.000007
        Pos: 2830
 Event_type: Query
  Server_id: 1
End_log_pos: 2961
       Info: use `test`; insert into binlog_demo values(6,66,'2020-07-06')
*************************** 42. row ***************************
   Log_name: mysql-bin.000007
        Pos: 2961
 Event_type: Xid
  Server_id: 1
End_log_pos: 2992
       Info: COMMIT /* xid=114 */
42 rows in set (0.00 sec)

PS: End_log_posとshow master statusのデータposサイズは一貫しています
2743行から見るmysqlbinlog -vv mysql-bin.000007 --start-position=2743PS: mysqlbinlog一般的なパラメータ:

 --base64-output=decode-rows –v 
 --start-position --stop-position
 --start-time= --stop-time
 --read-from-remote-server

root@base2018:/var/log/mysql# mysqlbinlog -vv mysql-bin.000007 --start-position=2743
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200702 10:24:14 server id 1  end_log_pos 123 CRC32 0xa97adc3a     Start: binlog v 4, server v 5.7.30-0ubuntu0.16.04.1-log created 200702 10:24:14 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
TkX9Xg8BAAAAdwAAAHsAAAABAAQANS43LjMwLTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABORf1eEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATrceqk=
'/*!*/;
# at 2743
#200702 11:58:33 server id 1  end_log_pos 2830 CRC32 0xa47b4e54     Query    thread_id=10    exec_time=0    error_code=0
SET TIMESTAMP=1593662313/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 2830
#200702 11:58:33 server id 1  end_log_pos 2961 CRC32 0x48d934c7     Query    thread_id=10    exec_time=0    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1593662313/*!*/;
insert into binlog_demo values(6,66,'2020-07-06')
/*!*/;
# at 2961
#200702 11:58:33 server id 1  end_log_pos 2992 CRC32 0x39d367e0     Xid = 114
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とstatementが違うrow形式のBEGINとCOMMITとの間の内容は異なり、statementはinsert文(実行を表示していないuse testを含む)しか保存されていません.SET TIMESTAMP(この時間は現在の動作コンテキストの時間と理解され、主従同期時に時間的に一致しない問題、例えばCURRENT_TIME、NOW()が発生することを回避し、linuxのntpに類似していることを理解する)
delete文ケンはデータの不一致を引き起こす(statementモード)
上は挿入文で、比較的簡単です.特殊な削除状況を見てみましょう.コンテキスト関係をもう一度示します.
テーブルには6つのデータがあり、2つのインデックスがあります.

mysql> select * from binlog_demo;
+----+----+---------------------+
| id | a  | t_modified          |
+----+----+---------------------+
|  1 |  1 | 2020-07-01 00:00:00 |
|  2 |  2 | 2020-07-02 00:00:00 |
|  3 |  3 | 2020-07-02 00:00:00 |
|  5 |  5 | 2020-07-05 00:00:00 |
|  6 | 66 | 2020-06-01 00:00:00 |
+----+----+---------------------+
5 rows in set

show index from binlog_demo;
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| binlog_demo |          0 | PRIMARY    |            1 | id          | A         |           5 | NULL     | NULL   |      | BTREE      |         |               |
| binlog_demo |          1 | a          |            1 | a           | A         |           5 | NULL     | NULL   | YES  | BTREE      |         |               |
| binlog_demo |          1 | t_modified |            1 | t_modified  | A         |           4 | NULL     | NULL   |      | BTREE      |         |               |
+-------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

デフォルトのインデックスはaです

mysql> select * from binlog_demo   where a>=4 and t_modified<='2020-07-10' limit 1;
+----+---+---------------------+
| id | a | t_modified          |
+----+---+---------------------+
|  5 | 5 | 2020-07-05 00:00:00 |
+----+---+---------------------+
1 row in set

mysql> explain select * from binlog_demo   where a>=4 and t_modified<='2020-07-10' limit 1;
+----+-------------+-------------+------------+-------+---------------+-----+---------+------+------+----------+------------------------------------+
| id | select_type | table       | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------------+------------+-------+---------------+-----+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | binlog_demo | NULL       | range | a,t_modified  | a   | 5       | NULL |    2 |      100 | Using index condition; Using where |
+----+-------------+-------------+------------+-------+---------------+-----+---------+------+------+----------+------------------------------------+

インデックスの使用を強制的に指定するt_modified

mysql> select * from binlog_demo use index(t_modified)  where a>=4 and t_modified<='2020-07-10' limit 1;
+----+----+---------------------+
| id | a  | t_modified          |
+----+----+---------------------+
|  6 | 66 | 2020-06-01 00:00:00 |
+----+----+---------------------+
1 row in set

mysql> explain select * from binlog_demo use index(t_modified)  where a>=4 and t_modified<='2020-07-10' limit 1;
+----+-------------+-------------+------------+-------+---------------+------------+---------+------+------+----------+------------------------------------+
| id | select_type | table       | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------------+------------+-------+---------------+------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | binlog_demo | NULL       | range | t_modified    | t_modified | 4       | NULL |    5 |    33.33 | Using index condition; Using where |
+----+-------------+-------------+------------+-------+---------------+------------+---------+------+------+----------+------------------------------------+
1 row in set

デフォルトインデックスで調べたデータidは5,force indexで調べたidは6であることがわかる.では、delete from binlog_demo where a>=4 and t_modified<='2020-07-10' limit 1;を実行すると、ライブラリから実行するときも必ずしも同じインデックス選択ポリシーに従っているとは限らず、不一致を招く可能性があると考えられます.だからrowフォーマットはこの問題を避けることができて、rowは操作の各行の情報を記録します.これで完全に一致することが保証されます.row形式

root@base2018:/var/log/mysql# mysqlbinlog -vv mysql-bin.000007 --start-position=2992;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200702 10:24:14 server id 1  end_log_pos 123 CRC32 0xa97adc3a     Start: binlog v 4, server v 5.7.30-0ubuntu0.16.04.1-log created 200702 10:24:14 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
TkX9Xg8BAAAAdwAAAHsAAAABAAQANS43LjMwLTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABORf1eEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATrceqk=
'/*!*/;
# at 2992
#200702 12:19:31 server id 1  end_log_pos 3057 CRC32 0x10b875ba     Anonymous_GTID    last_committed=10    sequence_number=11    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3057
#200702 12:19:31 server id 1  end_log_pos 3144 CRC32 0x8f84f3a7     Query    thread_id=15    exec_time=0    error_code=0
SET TIMESTAMP=1593663571/*!*/;
SET @@session.pseudo_thread_id=15/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 3144
#200702 12:19:31 server id 1  end_log_pos 3302 CRC32 0x450cca73     Query    thread_id=15    exec_time=0    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1593663571/*!*/;
UPDATE `binlog_demo` SET `t_modified`='2020-06-01 00:00:00' WHERE (`id`='6')
/*!*/;
# at 3302
#200702 12:19:31 server id 1  end_log_pos 3333 CRC32 0x73ca56b1     Xid = 132
COMMIT/*!*/;
# at 3333
#200702 12:27:03 server id 1  end_log_pos 3398 CRC32 0xad1d9f72     Anonymous_GTID    last_committed=11    sequence_number=12    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3398
#200702 12:27:03 server id 1  end_log_pos 3478 CRC32 0xccf08199     Query    thread_id=14    exec_time=0    error_code=0
SET TIMESTAMP=1593664023/*!*/;
BEGIN
/*!*/;
# at 3478
#200702 12:27:03 server id 1  end_log_pos 3535 CRC32 0x9946e0d7     Table_map: `test`.`binlog_demo` mapped to number 120
# at 3535
#200702 12:27:03 server id 1  end_log_pos 3583 CRC32 0x86c26c2a     Delete_rows: table id 120 flags: STMT_END_F

BINLOG '
F2L9XhMBAAAAOQAAAM8NAAAAAHgAAAAAAAEABHRlc3QAC2JpbmxvZ19kZW1vAAMDAxEBAALX4EaZ
F2L9XiABAAAAMAAAAP8NAAAAAHgAAAAAAAEAAgAD//gFAAAABQAAAF8Ap4AqbMKG
'/*!*/;
### DELETE FROM `test`.`binlog_demo`
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2=5 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1593878400 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 3583
#200702 12:27:03 server id 1  end_log_pos 3614 CRC32 0xe2c7ad74     Xid = 140
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*/;

statement下deleteのwarning
Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.

mysql> SET SESSION binlog_format = 'STATEMENT';
Query OK, 0 rows affected (0.00 sec)

mysql> delete from binlog_demo  where a>=2 and t_modified<='2020-07-10' limit 1;
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1592
Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.
1 row in set (0.00 sec)

ROW形式ではwarningはありません

mysql> SET SESSION binlog_format = 'ROW';
Query OK, 0 rows affected (0.00 sec)

mysql> delete from binlog_demo  where a>=2 and t_modified<='2020-07-10' limit 1;
Query OK, 1 row affected (0.05 sec)

*************************** 52. row ***************************
   Log_name: mysql-bin.000007
        Pos: 3614
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 3679
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 53. row ***************************
   Log_name: mysql-bin.000007
        Pos: 3679
 Event_type: Query
  Server_id: 1
End_log_pos: 3766
       Info: BEGIN
*************************** 54. row ***************************
   Log_name: mysql-bin.000007
        Pos: 3766
 Event_type: Query
  Server_id: 1
End_log_pos: 3920
       Info: use `test`; delete from binlog_demo  where a>=2 and t_modified<='2020-07-10' limit 1
*************************** 55. row ***************************
   Log_name: mysql-bin.000007
        Pos: 3920
 Event_type: Xid
  Server_id: 1
End_log_pos: 3951
       Info: COMMIT /* xid=159 */
*************************** 56. row ***************************
   Log_name: mysql-bin.000007
        Pos: 3951
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 4016
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 57. row ***************************
   Log_name: mysql-bin.000007
        Pos: 4016
 Event_type: Query
  Server_id: 1
End_log_pos: 4096
       Info: BEGIN
*************************** 58. row ***************************
   Log_name: mysql-bin.000007
        Pos: 4096
 Event_type: Table_map
  Server_id: 1
End_log_pos: 4153
       Info: table_id: 120 (test.binlog_demo)
*************************** 59. row ***************************
   Log_name: mysql-bin.000007
        Pos: 4153
 Event_type: Delete_rows
  Server_id: 1
End_log_pos: 4201
       Info: table_id: 120 flags: STMT_END_F
*************************** 60. row ***************************
   Log_name: mysql-bin.000007
        Pos: 4201
 Event_type: Xid
  Server_id: 1
End_log_pos: 4232
       Info: COMMIT /* xid=165 */
60 rows in set (0.00 sec)



root@base2018:/var/log/mysql# mysqlbinlog -vv mysql-bin.000007 --start-position=3614
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200702 10:24:14 server id 1  end_log_pos 123 CRC32 0xa97adc3a     Start: binlog v 4, server v 5.7.30-0ubuntu0.16.04.1-log created 200702 10:24:14 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
TkX9Xg8BAAAAdwAAAHsAAAABAAQANS43LjMwLTB1YnVudHUwLjE2LjA0LjEtbG9nAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABORf1eEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
ATrceqk=
'/*!*/;
# at 3614
#200702 12:38:24 server id 1  end_log_pos 3679 CRC32 0x0937d47a     Anonymous_GTID    last_committed=12    sequence_number=13    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 3679
#200702 12:38:24 server id 1  end_log_pos 3766 CRC32 0x92b2ee24     Query    thread_id=14    exec_time=0    error_code=0
SET TIMESTAMP=1593664704/*!*/;
SET @@session.pseudo_thread_id=14/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 3766
#200702 12:38:24 server id 1  end_log_pos 3920 CRC32 0x22d50c86     Query    thread_id=14    exec_time=0    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1593664704/*!*/;
delete from binlog_demo  where a>=2 and t_modified<='2020-07-10' limit 1
/*!*/;
# at 3920
#200702 12:38:24 server id 1  end_log_pos 3951 CRC32 0x35efdb16     Xid = 159
COMMIT/*!*/;
# at 3951
#200702 12:42:08 server id 1  end_log_pos 4016 CRC32 0xd357905c     Anonymous_GTID    last_committed=13    sequence_number=14    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 4016
#200702 12:42:08 server id 1  end_log_pos 4096 CRC32 0xfbb008a4     Query    thread_id=14    exec_time=0    error_code=0
SET TIMESTAMP=1593664928/*!*/;
BEGIN
/*!*/;
# at 4096
#200702 12:42:08 server id 1  end_log_pos 4153 CRC32 0xc7c76464     Table_map: `test`.`binlog_demo` mapped to number 120
# at 4153
#200702 12:42:08 server id 1  end_log_pos 4201 CRC32 0xa7203e07     Delete_rows: table id 120 flags: STMT_END_F

BINLOG '
oGX9XhMBAAAAOQAAADkQAAAAAHgAAAAAAAEABHRlc3QAC2JpbmxvZ19kZW1vAAMDAxEBAAJkZMfH
oGX9XiABAAAAMAAAAGkQAAAAAHgAAAAAAAEAAgAD//gDAAAAAwAAAF78swAHPiCn
'/*!*/;
### DELETE FROM `test`.`binlog_demo`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1593619200 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 4201
#200702 12:42:08 server id 1  end_log_pos 4232 CRC32 0x21a1766f     Xid = 165
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について:BinlogはMySqlサーバ層の実装であり、いわゆるcommitもあるが、このcommitは具体的な実装エンジンとは無関係である(innodbのトランザクションログredo/undoと衝突しない).主に主従、バックアップ/リカバリ用です.

mixedについては、その名の通りです.両者の結合が最良であり、じどうけっていがプライマリ・スペアの不一致に影響を及ぼすかどうかである.

rowモードは行ごとに記録され、deleteの行には1行の記録があり、そのうちbinlog_row_imageは記録の数を決定します.

fullは、すべての列

を記録する

minimalレコード変化の列

noblobレコードblobとtext以外の列を除去するとrowフォーマットのbinlogが大きくなります.

leetcode 1031. 2つの非オーバーラップサブ配列の最大和(C++)

[線形ダイナミックプランニング][最長上昇/下降シーケンス][P 1001合唱隊形]線形ダイナミックプランニング問題の理解と総括