注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

老狗的博客

尽管每一步都很微小,但我确认我在进步

 
 
 

日志

 
 
关于我
sky

认真生活,努力工作 热爱技术,关注DB,存储,分布式,中间层,java,c++,php

网易考拉推荐

mysql row 日志学习  

2015-08-12 11:38:32|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
1. create语句在row日志中以query的形式存在

create table t1(
c1 int unsigned primary key,
c2 int
)engine=innodb;


# at 190
#150812 11:01:20 server id 11215003 end_log_pos 321 Query thread_id=1246781 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1439348480/*!*/;
create table t1(
c1 int unsigned primary key,
c2 int
)engine=innodb
/*!*/;
DELIMITER ;

2. insert语句

mysql> insert into t1 values(1,1);

# at 431
#150812 11:02:54 server id 11215003 end_log_pos 431 Table_map: `test`.`t1` mapped to number 16083840
#150812 11:02:54 server id 11215003 end_log_pos 469 Write_rows: table id 16083840 flags: STMT_END_F

BINLOG '
XrfKVRObIKsAKgAAAK8BAAAAAIBr9QAAAAEABHRlc3QAAnQxAAIDAwAC
XrfKVRebIKsAJgAAANUBAAAAAIBr9QAAAAEAAv/8AQAAAAEAAAA=
'/*!*/;
### INSERT INTO test.t1
### SET
### @1=1
### @2=1

3. insert into select..

mysql> insert into t1_new select c1,c2 from t1;

# at 765
#150812 11:06:30 server id 11215003 end_log_pos 765 Table_map: `test`.`t1_new` mapped to number 16083841
#150812 11:06:30 server id 11215003 end_log_pos 807 Write_rows: table id 16083841 flags: STMT_END_F

BINLOG '
NrjKVRObIKsALwAAAP0CAAAAAIFr9QAAAAEABHRlc3QABnQxX25ldwADAwMDAAY=
NrjKVRebIKsAKgAAACcDAAAAAIFr9QAAAAEAA//4AQAAAAEAAAAAAAAA
'/*!*/;
### INSERT INTO test.t1_new
### SET
### @1=1
### @2=1
### @3=0

4. 建立trigger

delimiter //
create trigger t1_add_column after insert on t1
for each row
begin
insert into t1_new(c1,c2) values(NEW.c1, NEW.c2);
end;
//

# at 949
#150812 12:04:41 server id 11215003 end_log_pos 1166 Query thread_id=1246791 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1439352281/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
CREATE DEFINER=`xxx`@`%` trigger t1_add_column after insert on t1 for each row begin insert into t1_new(c1,c2) values(NEW.c1, NEW.c2); end
/*!*/;

5. 插入数据

# at 1361
#150812 12:10:29 server id 11215003 end_log_pos 1276 Table_map: `test`.`t1` mapped to number 16083842
#150812 12:10:29 server id 11215003 end_log_pos 1323 Table_map: `test`.`t1_new` mapped to number 16083841
#150812 12:10:29 server id 11215003 end_log_pos 1361 Write_rows: table id 16083842
#150812 12:10:29 server id 11215003 end_log_pos 1403 Write_rows: table id 16083841 flags: STMT_END_F

BINLOG '
NcfKVRObIKsAKgAAAPwEAAAAAIJr9QAAAAEABHRlc3QAAnQxAAIDAwAC
NcfKVRObIKsALwAAACsFAAAAAIFr9QAAAAEABHRlc3QABnQxX25ldwADAwMDAAY=
NcfKVRebIKsAJgAAAFEFAAAAAIJr9QAAAAAAAv/8AgAAAAIAAAA=
### INSERT INTO test.t1
### SET
### @1=2
### @2=2
NcfKVRebIKsAKgAAAHsFAAAAAIFr9QAAAAEAA//4AgAAAAIAAAAAAAAA
'/*!*/;
### INSERT INTO test.t1_new
### SET
### @1=2
### @2=2
### @3=0
# at 1403

触发器的影响还有两个map事件,后面跟两个event事件
6. 同时建立两个触发器

#150812 12:32:00 server id 11215003 end_log_pos 3285 Table_map: `test`.`t1` mapped to number 16083855
#150812 12:32:00 server id 11215003 end_log_pos 3332 Table_map: `test`.`t1_new` mapped to number 16083850
#150812 12:32:00 server id 11215003 end_log_pos 3379 Table_map: `test`.`t1_new` mapped to number 16083850
#150812 12:32:00 server id 11215003 end_log_pos 3421 Write_rows: table id 16083850
#150812 12:32:00 server id 11215003 end_log_pos 3459 Write_rows: table id 16083855
#150812 12:32:00 server id 11215003 end_log_pos 3501 Write_rows: table id 16083850 flags: STMT_END_F

# at 173778310
# at 173778369
# at 173778428
#150811 11:49:26 server id 191394732 end_log_pos 173778369 Table_map: `relation_2`.`relation_1` mapped to number 4513
#150811 11:49:26 server id 191394732 end_log_pos 173778428 Write_rows: table id 4513
#150811 11:49:26 server id 191394732 end_log_pos 173778488 Write_rows: table id 4512 flags: STMT_END_F

BINLOG '
xnDJVROsc2gLOwAAAMGlWwoAAKERAAAAAAEACnJlbGF0aW9uXzIACnJlbGF0aW9uXzEABQgIAQcI
AAA=
xnDJVResc2gLOwAAAPylWwoAAKERAAAAAAAABf/gIfypswAAAADDFTtHAAAAAADGcMlVce/cX/LD
DQA=
### INSERT INTO `relation_2`.`relation_1`
### SET
### @1=3014261793 /* LONGINT meta=0 nullable=0 is_null=0 */
### @2=1195054531 /* LONGINT meta=0 nullable=0 is_null=0 */
### @3=0 /* TINYINT meta=0 nullable=0 is_null=0 */
### @4=1439264966 /* TIMESTAMP meta=0 nullable=0 is_null=0 */
### @5=3874620455055217 /* LONGINT meta=0 nullable=0 is_null=0 */
xnDJVResc2gLPAAAADimWwoAAKARAAAAAAEABv/AIfypswAAAADDFTtHAAAAAADGcMlVce/cX/LD
DQAA
'/*!*/;
### Row event for unknown table #4512# at 173778488
#150811 11:49:26 server id 191394732 end_log_pos 173778515 Xid = 1953428818
COMMIT/*!*/;
# at 173778515
#150811 11:49:26 server id 191394732 end_log_pos 173778574 Query thread_id=130706251 exec_time=0 error_code=0
SET TIMESTAMP=1439264966/*!*/;


  评论这张
 
阅读(107)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018