MySql Binlog事件介绍篇( 二 ) _MySql

15.USER_VAR_EVENT
执行包含了用户变量的语句产生此事件，此事件没有被用在binlog_format为ROW模式的情况下；
mysql> set @age=50;Query OK, 0 rows affected (0.00 sec) mysql> insert into btest (age,name)values(@age,'zhaohui');Query OK, 1 row affected (0.12 sec) mysql> show binlog events in 'bin-log.000003';+----------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+----------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+...... || bin-log.000003 | 615 | Query | 1 | 683 | BEGIN || bin-log.000003 | 683 | Intvar | 1 | 711 | INSERT_ID=4 || bin-log.000003 | 711 | User var | 1 | 756 | @`age`=50 || bin-log.000003 | 756 | Query | 1 | 869 | use `test`; insert into btest (age,name)values(@age,'zhaohui') || bin-log.000003 | 869 | Xid | 1 | 896 | COMMIT /* xid=70 */ |+----------------+-----+-------------+-----------+-------------+------------------------------------------------------------------+15 rows in set (0.00 sec)16.FORMAT_DESCRIPTION_EVENT
描述事件，被写在每个binlog文件的开始位置，用在MySQL5.0以后的版本中，代替了START_EVENT_V3
mysql> show binlog events in 'bin-log.000003';+----------------+-----+-------------+-----------+-------------+---------------------------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+----------------+-----+-------------+-----------+-------------+---------------------------------------------------------------+| bin-log.000003 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.29-log, Binlog ver: 4 |......17.XID_EVENT
支持XA的存储引擎才有，本地测试的数据库存储引擎是innodb，所有上面出现了XID_EVENT；innodb事务提交产生了QUERY_EVENT的BEGIN声明，QUERY_EVENT以及COMMIT声明，
如果是myIsam存储引擎也会有BEGIN和COMMIT声明，只是COMMIT类型不是XID_EVENT；
18.BEGIN_LOAD_QUERY_EVENT和EXECUTE_LOAD_QUERY_EVENT
执行LOAD DATA INFILE 语句时产生此事件，在MySQL5.0版本中使用；
mysql> LOAD DATA INFILE "D:/btest.sql" INTO TABLE test.btest FIELDS TERMINATED BY ',';Query OK, 1 row affected (0.11 sec)Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 mysql> show binlog events in 'bin-log.000003';+----------------+------+--------------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+----------------+------+--------------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+...... || bin-log.000003 | 896 | Query | 1 | 964 | BEGIN || bin-log.000003 | 964 | Begin_load_query | 1 | 1008 | ;file_id=3;block_len=21 || bin-log.000003 | 1008 | Execute_load_query | 1 | 1237 | use `test`; LOAD DATA INFILE 'D:/btest.sql' INTO TABLE `btest` FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\' LINES TERMINATED BY 'n' (`id`, `age`, `name`) ;file_id=3 || bin-log.000003 | 1237 | Xid | 1 | 1264 | COMMIT /* xid=148 */ |+----------------+------+--------------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+19 rows in set (0.00 sec)btest.sql内容如下：
999, 101, 'zhaohui'19.TABLE_MAP_EVENT
用在binlog_format为ROW模式下，将表的定义映射到一个数字，在行操作事件之前记录（包括：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT）；
mysql> insert into btest values(998,88,'zhaohui');Query OK, 1 row affected (0.09 sec) mysql> show binlog events in 'bin-log.000004';+----------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+----------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+...... || bin-log.000004 | 776 | Query | 1 | 844 | BEGIN || bin-log.000004 | 844 | Table_map | 1 | 892 | table_id: 33 (test.btest) || bin-log.000004 | 892 | Write_rows | 1 | 943 | table_id: 33 flags: STMT_END_F || bin-log.000004 | 943 | Xid | 1 | 970 | COMMIT /* xid=20 */ |+----------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+14 rows in set (0.00 sec)

MySql Binlog事件介绍篇( 二 )

推荐阅读

南宁@南宁虹桥路何时打通至长虹路？已提出3大设计方案，超长隧道嘛？

【阿轲】王者荣耀：当女英雄遇上“街头霸王”，看到伽罗和阿轲，网友：这谁顶得住？

「奇闻笔谈」短小精致，引人深思，网络上相互传抄的经典良言

24日美国疫情消息汇总：单日新增死亡超3100例

低价版Surface Laptop 曝光：十代酷睿+更小屏幕

太平洋电脑网 News+用户提供音频内容，苹果正计划为Apple

青年|“王者”配音师长啥样？大乔女神，云中君高颜值，看到玄策：意料之外！

离离原上草5看过这几点有点羡慕单眼皮了！幸亏没割，别再总是想着割双眼皮了

苹果|苹果中国官网又调整：清除256GB版iPhone SE 2

徐渭|史上最“懒”对联，下联一字不差照抄上联，文人：千古绝对

穷养的孩子更有出息？别听信传言，给他带来的心理伤害超乎想象

气质|女神的时尚搭配一双高跟鞋穿出魅力气质

张嫂■卡车夫妻档的爱情故事：是生活，更是浪漫！

一个扰打一成语扰打一成语是什么

「吴京等成为奥斯卡评委」奥斯卡“招新”：吴京和黄觉等成为奥斯卡评委

[孕妇专用文胸]孕妇专用文胸有用吗孕妇可以穿吗

黑茶为什么会有酸味,为什么红茶会出现酸味

[腾讯科技]硅谷如何毁掉了我们原本该有的朝九晚五的生活

吃了这么久的燕麦片，你知道哪种燕麦更好吗？

立白|立白旗下朝云集团赴港IPO 产品老化如何打破增长天花板