测试验证mysql开启binlog_rows_query_log_events参数前后的比较

时间:2022-12-30 21:48:52

1.基本信息

主库:
 IP:10.16.24.107  port:3376
 server-id = 1073377
 data_dir:/data/MySQL/mysql3376/data/
 base_dir:/usr/local/mysql
 版本:mysql  5.6.29-log
 binlog_format:ROW

从库:
 IP:10.16.24.108  port:3376
 server-id = 1083376
 data_dir:/data/mysql/mysql3376/data/
 base_dir:/usr/local/mysql
 版本:mysql  5.6.29-log
 binlog_format:ROW


2.场景一:主从库没有设置binlog_rows_query_log_events参数
binlog-rows-query-log_events=1
主库上查看:
(product)root@localhost [(none)]> show variables like 'binlog_rows_query_log_events';

0 row in set (0.00 sec)

从库上查看:
(product)root@localhost [(none)]> show variables like 'binlog_rows_query_log_events';
0 row in set (0.00 sec)

主库上查看状态:
(product)root@localhost [(none)]> show master status\G

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000064 |      960 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从库查看状态:
product)root@localhost [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.16.24.107
                  Master_User: repl
                  Master_Port: 3376
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000064
          Read_Master_Log_Pos: 960
               Relay_Log_File: relay-bin.000009
                Relay_Log_Pos: 482
        Relay_Master_Log_File: mysql-bin.000064
             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: 960
              Relay_Log_Space: 649
              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: 1073376

主库上执行如下操作:

(product)root@localhost [lots]> update test set id=3,name='zeng3' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

从库上查看表test记录:
(product)root@localhost [lots]> select * from test;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
|    3 | zeng3 |
|    5 | zeng5 |
+------+-------+
4 rows in set (0.00 sec)


主库上分析日志:
mysqlbinlog -vv  mysql-bin.000064 --start-position=960 --base64-output=DECODE-ROWS
BEGIN
/*!*/;
# at 1032
#160429 23:01:38 server id 1073376  end_log_pos 1082 CRC32 0xe2bc12fc   Table_map: `lots`.`test` mapped to number 70
# at 1082
#160429 23:01:38 server id 1073376  end_log_pos 1140 CRC32 0x5c3181dd   Update_rows: table id 70 flags: STMT_END_F
### UPDATE `lots`.`test`
### WHERE
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='zeng4' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='zeng3' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 1140
#160429 23:01:38 server id 1073376  end_log_pos 1171 CRC32 0xbdce4924   Xid = 63
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;

上面可看到发生update位置是在mysql-bin.000064的1140,记录了update操作的old row和new row记录。没有记录用户发出的update原始SQL。

我们再在从库上分析中继日志:
BEGIN
/*!*/;
# at 554
#160429 23:01:38 server id 1073376  end_log_pos 1082 CRC32 0xe2bc12fc   Table_map: `lots`.`test` mapped to number 70
# at 604
#160429 23:01:38 server id 1073376  end_log_pos 1140 CRC32 0x5c3181dd   Update_rows: table id 70 flags: STMT_END_F
### UPDATE `lots`.`test`
### WHERE
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='zeng4' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='zeng3' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 662
#160429 23:01:38 server id 1073376  end_log_pos 1171 CRC32 0xbdce4924   Xid = 63
COMMIT/*!*/;
DELIMITER ;
# End of log file

上面查看到update操作位置有两个position:1140和662,其中1140是从master读取binlog记录update操作位置,4662是update操作在relay log中位置。
记录了update操作的old row和new row记录,没有记录用户发出的update原始SQL。


3.场景二:主从库都有设置binlog-rows-query-log_events参数

主从库查看参数设置:
(product)root@localhost [(none)]> show variables like 'binlog_rows_query_log_events';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| binlog_rows_query_log_events | ON    |
+------------------------------+-------+
1 row in set (0.00 sec)

查看主库状态:
(product)root@localhost [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000067 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec

查看从库状态:
(product)root@localhost [(none)]> show slave status\G

*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 10.16.24.107
                  Master_User: repl
                  Master_Port: 3376
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000064
          Read_Master_Log_Pos: 1171
               Relay_Log_File: relay-bin.000009
                Relay_Log_Pos: 693
        Relay_Master_Log_File: mysql-bin.000064
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 1171
              Relay_Log_Space: 1289
              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: NULL
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: 0
                  Master_UUID: 36af7e42-f4fc-11e5-8b08-0050568a0bcb
             Master_Info_File: /data/mysql/mysql3376/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           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
1 row in set (0.00 sec)

主库上执行如下操作:
(product)root@localhost [lots]> select * from test;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
|    3 | zeng3 |
|    5 | zeng5 |
+------+-------+
4 rows in set (0.00 sec)
(product)root@localhost [lots]> update test set id=4,name='zeng4' where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

从库查看表test记录:
(product)root@localhost [lots]> select * from test;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
|    3 | zeng3 |
|    4 | zeng4 |
+------+-------+
4 rows in set (0.00 sec)
主库上分析日志:
mysqlbinlog -vv  mysql-bin.000067 --start-position=120 --base64-output=DECODE-ROWS
BEGIN
/*!*/;
# at 192
#160429 23:36:28 server id 1073376  end_log_pos 260 CRC32 0xd9288e2a    Rows_query
# update test set id=4,name='zeng4' where id=5
# at 260
#160429 23:36:28 server id 1073376  end_log_pos 310 CRC32 0xa38557d6    Table_map: `lots`.`test` mapped to number 70
# at 310
#160429 23:36:28 server id 1073376  end_log_pos 368 CRC32 0x31ed5f26    Update_rows: table id 70 flags: STMT_END_F
### UPDATE `lots`.`test`
### WHERE
###   @1=5 /* INT meta=0 nullable=1 is_null=0 */
###   @2='zeng5' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='zeng4' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 368
#160429 23:36:28 server id 1073376  end_log_pos 399 CRC32 0x381ffdd7    Xid = 9
COMMIT/*!*/;
DELIMITER ;
# End of log file
上面可看到发生update位置是在mysql-bin.000067的368,记录了update操作的old row和new row记录。并有记录用户发出的update原始SQL:update test set id=4,name='zeng4' where id=5

再分析从库relay log日志:
mysqlbinlog -vv  relay-bin.000016 --base64-output=DECODE-ROWS

BEGIN
/*!*/;
# at 355
#160429 23:36:28 server id 1073376  end_log_pos 260 CRC32 0xd9288e2a    Rows_query
# update test set id=4,name='zeng4' where id=5
# at 423
#160429 23:36:28 server id 1073376  end_log_pos 310 CRC32 0xa38557d6    Table_map: `lots`.`test` mapped to number 70
# at 473
#160429 23:36:28 server id 1073376  end_log_pos 368 CRC32 0x31ed5f26    Update_rows: table id 70 flags: STMT_END_F
### UPDATE `lots`.`test`
### WHERE
###   @1=5 /* INT meta=0 nullable=1 is_null=0 */
###   @2='zeng5' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='zeng4' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 531
#160429 23:36:28 server id 1073376  end_log_pos 399 CRC32 0x381ffdd7    Xid = 9
COMMIT/*!*/;
DELIMITER ;
# End of log file
上面查看到update操作位置有两个position:368和531,其中368是从master读取binlog记录update操作位置,531是update操作在relay log中位置,记录了update操作的old row和new row记录,并有记录用户发出的update原始SQL:update test set id=4,name='zeng4' where id=5