测试验证mysql开启binlog_rows_query_log_events参数前后的比较
来源：互联网 发布：win10 磁盘100 优化 编辑：程序博客网 时间：2024/05/17 04:16
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。
- 测试验证mysql开启binlog_rows_query_log_events参数前后的比较
- 测试验证从库开启和关闭log_slave_updates参数的区别
- 验证日期的前后
- mysql的高并发测试验证
- MySQL日志文件的开启与参数详解
- 手机号码的前后台验证写法
- Linux workqueue_struct 前后版本的比较
- 比较两个时间的前后关系
- 日期时间进行比较日期的前后
- JAVA比较日期前后的方法
- JSON.tringify(results)的前后比较
- flex有参数的自定义事件，数据验证测试
- 使用反射前后的参数化工厂方法
- web前后台的参数交互方式
- 开启 MySQL 的远程登陆
- mysql存储过程参数比较
- mysql性能测试及不同版本的比较
- mysql性能测试及不同版本的比较
- Objective-c_Date 日期对象
- 剖析八种经典排序算法
- SICP ex1-10 order of Growth
- 深度学习算法简介
- 2016 UESTC Training for Data Structures D - 卿学姐与魔法 优先队列、构造
- 测试验证mysql开启binlog_rows_query_log_events参数前后的比较
- 在百度加固中正确使用ida的姿势
- html中调用本地exe程序 打开文件或文件夹
- 数学漫步百度云视频资源
- Beyond----光辉岁月
- 顺序容器——C++Primer第九章
- 小船翻了，路还得继续
- angularjs指令中的compile与link函数详解
- java利用递归解决八皇后问题
