mysql event type_MySQL 【Row】下的 Event_type

最新推荐文章于 2021-02-26 19:23:51 发布

黄衔

最新推荐文章于 2021-02-26 19:23:51 发布

阅读量503

点赞数

文章标签： mysql event type

本文链接：https://blog.csdn.net/weixin_32512187/article/details/113987724

版权

在主库上执行一条 SQL [insert... select... ] 语句，导致从库延迟，以前执行都不会出现延迟的，肯定有蹊跷。所以当时就在从上执行 [show slave status\G;] 查看在执行到主的哪个position被”卡“住。发现：

zjy@192.168.1.243 : (none) 02:13:11>show binlog events in 'mysql-bin_3306.000011' from 143808509 limit 10;+-----------------------+-----------+------------+-----------+-------------+-------------------------------+

+-----------------------+-----------+------------+-----------+-------------+-------------------------------+

| mysql-bin_3306.000011 | 143808509 | Query | 3 | 143808577 | BEGIN |

| mysql-bin_3306.000011 | 143808577 | Table_map | 3 | 143808719 | table_id: 204 (test.a_view) |

| mysql-bin_3306.000011 | 143808719 | Write_rows | 3 | 143809744 | table_id: 204 |

| mysql-bin_3306.000011 | 143809744 | Write_rows | 3 | 143810738 | table_id: 204 |

| mysql-bin_3306.000011 | 143810738 | Write_rows | 3 | 143811587 | table_id: 204 |

| mysql-bin_3306.000011 | 143811587 | Write_rows | 3 | 143812439 | table_id: 204 |

| mysql-bin_3306.000011 | 143812439 | Write_rows | 3 | 143813325 | table_id: 204 |

| mysql-bin_3306.000011 | 143813325 | Write_rows | 3 | 143814193 | table_id: 204 |

| mysql-bin_3306.000011 | 143814193 | Write_rows | 3 | 143815048 | table_id: 204 |

| mysql-bin_3306.000011 | 143815048 | Write_rows | 3 | 143815906 | table_id: 204 |

+-----------------------+-----------+------------+-----------+-------------+-------------------------------+

发现从被”卡“住的点143808509 开始，Event_type 都是Write_rows，原来在昨天把该数据库的Binlog模式从mixed 改成了Row。mixed模式的二进制信息说明请见MySQL 二进制文件 (STATEMENT)浅谈，对于Row模式下比较不同的是Event_type 列：

1) QUERY_EVENT：与STATEMENT模式处理相同，存储的是SQL，主要是一些与数据无关的操作，eg: begin、drop table、truncate table 等；

2) TABLE_MAP_EVENT：记录了下一条事件所对应的表信息，在其中存储了数据库名和表名；(test.a_view)

3) WRITE_ROWS_EVENT：操作类型为insert；(insert)

4) UPDATE_ROWS_EVENT：操作类型为update；(update)

5) DELETE_ROWS_EVENT：操作类型为delete；(delete)

6) XID_EVENT，用于标识事务提交。

对应：

+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+

+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+

| mysql-bin.000001 | 106 | Query | 1 | 174 | BEGIN |

| mysql-bin.000001 | 174 | Table_map | 1 | 215 | table_id: 223 (test.t1) |

| mysql-bin.000001 | 215 | Write_rows | 1 | 249 | table_id: 223 flags: STMT_END_F |

| mysql-bin.000001 | 249 | Query | 1 | 318 | COMMIT |

| mysql-bin.000001 | 619 | Query | 1 | 687 | BEGIN |

| mysql-bin.000001 | 687 | Table_map | 1 | 728 | table_id: 223 (test.t1) |

| mysql-bin.000001 | 728 | Delete_rows | 1 | 762 | table_id: 223 flags: STMT_END_F |

| mysql-bin.000001 | 762 | Query | 1 | 831 | COMMIT |

| mysql-bin.000001 | 831 | Query | 1 | 899 | BEGIN |

| mysql-bin.000001 | 899 | Table_map | 1 | 940 | table_id: 223 (test.t1) |

| mysql-bin.000001 | 940 | Update_rows | 1 | 980 | table_id: 223 flags: STMT_END_F |

| mysql-bin.000001 | 980 | Query | 1 | 1049 | COMMIT |

+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+

从上面看出，ROW模式和STATEMENT模式还有明显的差别，而STATEMENT的效果如下：

+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+

+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+

| mysql-bin.000001 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.69-0ubuntu0.11.10.1-log, Binlog ver: 4 |

| mysql-bin.000001 | 106 | Query | 1 | 195 | use `test`; insert into t1 values(111) |

| mysql-bin.000001 | 195 | Query | 1 | 293 | use `test`; update t1 set id=1111 where id =111 |

| mysql-bin.000001 | 293 | Query | 1 | 385 | use `test`; delete from t1 where id =1111 |

+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+

Event_type是query，包含了insert、delete、update，并且显示相关的sql，而Row不能显示sql。

好了，回到这次出现问题的地方。为什么会延迟呢？通过 show binlog events 得到的信息中，看到了大量的Write_rows 状态(50万+)，说明刚执行了一个大量的插入操作。想知道是怎么样的sql语句，Row模式不能通过 show binlog events 来看，只能通过mysqlbinlog 去看binlog日志:

mysqlbinlog -vv --start-position=143808509 mysql-bin_3306.000011 | more

发现是一个脚本在主上执行了一个 insert into tb select * from ta 的操作引发的。可能会有人问，为什么ROW下会出现这个情况，而STATEMENT下不会，这个问题请具体看：MySQL Binlog 【ROW】和【STATEMENT】选择，里面有详细说明。文章里也给出了建议：

set session binlog_format=mixed;

即在脚本里面添加：

#!/usr/bin/env python

importMySQLdb

conn= MySQLdb.connect(host='localhost',user='root',passwd='123456',db='test',port=3306,charset='utf8')

query= 'insert into t1 select userid from tb_0513'cursor=conn.cursor()

cursor.execute("set session binlog_format='mixed'")

cursor.execute(query)print query

注意：

通过命令 show binlog events in '' from pos limit x 得出:

+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+

+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+

| mysql-bin.000001 | 385 | Query | 1 | 477 | use `test`; alter table t1 engine =innodb |

| mysql-bin.000001 | 477 | Query | 1 | 545 | BEGIN |

| mysql-bin.000001 | 545 | Table_map | 1 | 586 | table_id: 224 (test.t1) |

| mysql-bin.000001 | 586 | Write_rows | 1 | 620 | table_id: 224 flags: STMT_END_F |

| mysql-bin.000001 | 620 | Xid | 1 | 647 | COMMIT /*xid=47052*/ |

| mysql-bin.000001 | 647 | Query | 1 | 715 | BEGIN |

| mysql-bin.000001 | 715 | Table_map | 1 | 756 | table_id: 224 (test.t1) |

| mysql-bin.000001 | 756 | Update_rows | 1 | 796 | table_id: 224 flags: STMT_END_F |

| mysql-bin.000001 | 796 | Xid | 1 | 823 | COMMIT /*xid=47053*/ |

| mysql-bin.000001 | 823 | Query | 1 | 891 | BEGIN |

| mysql-bin.000001 | 891 | Table_map | 1 | 932 | table_id: 224 (test.t1) |

| mysql-bin.000001 | 932 | Delete_rows | 1 | 966 | table_id: 224 flags: STMT_END_F |

| mysql-bin.000001 | 966 | Xid | 1 | 993 | COMMIT /*xid=47054*/ |

| mysql-bin.000001 | 993 | Query | 1 | 1085 | use `test`; alter table t1 engine =myisam |

| mysql-bin.000001 | 1085 | Query | 1 | 1153 | BEGIN |

| mysql-bin.000001 | 1153 | Table_map | 1 | 1194 | table_id: 225 (test.t1) |

| mysql-bin.000001 | 1194 | Write_rows | 1 | 1228 | table_id: 225 flags: STMT_END_F |

| mysql-bin.000001 | 1228 | Query | 1 | 1297 | COMMIT |

| mysql-bin.000001 | 1297 | Query | 1 | 1365 | BEGIN |

| mysql-bin.000001 | 1365 | Table_map | 1 | 1406 | table_id: 225 (test.t1) |

| mysql-bin.000001 | 1406 | Update_rows | 1 | 1446 | table_id: 225 flags: STMT_END_F |

| mysql-bin.000001 | 1446 | Query | 1 | 1515 | COMMIT |

| mysql-bin.000001 | 1515 | Query | 1 | 1583 | BEGIN |

| mysql-bin.000001 | 1583 | Table_map | 1 | 1624 | table_id: 225 (test.t1) |

| mysql-bin.000001 | 1624 | Delete_rows | 1 | 1658 | table_id: 225 flags: STMT_END_F |

| mysql-bin.000001 | 1658 | Query | 1 | 1727 | COMMIT |

+------------------+------+-------------+-----------+-------------+--------------------------------------------------------+

Mysql对Binlog的处理是以事件为单位的，每一次DML操作可能会产生多次事件，例如对于innodb存储引擎，会额外产生一条QUERY_EVENT(事务的begin语句)以及XID_EVENT(事务提交)。

在上面的结果中观察得到一个现象：不管表是MyISAM还是Innodb(支持或则不支持事务)，都执行了begin...commit的命令。所以通过events的信息中，要看是否是innodb表需要看 Event_type 是不是用Xid 去提交的，是则为innodb。

具体row的信息请看这里

黄衔

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql event type_MySQL 【Row】下的 Event_type

在主库上执行一条 SQL [insert... select... ] 语句，导致从库延迟，以前执行都不会出现延迟的，肯定有蹊跷。所以当时就在从上执行 [show slave status\G;] 查看在执行到主的哪个position被”卡“住。发现：zjy@192.168.1.243 : (none) 02:13:11>show binlog events in 'mysql-bin_33...
复制链接

扫一扫