mysql的binlog有2个主要作用:复制和恢复。其内容记录所有的数据变化(DDL & DML)事件。这里就讨论下其记录机制。
一般情况(insert,update,delete,create ,alter,drop)
创建一张表,使用mysqlbinlog查看binlog中的内容:
# at 107
#130223 11:47:24 server id 1 end_log_pos 193 Query thread_id=3 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1361591244/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0,@@session.unique_checks=1,@@session.autocommit=1/*!*/;
SET@@session.sql_mode=0/*!*/;
SET@@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET@@session.lc_time_names=0/*!*/;
SET@@session.collation_database=DEFAULT/*!*/;
create table t1(idint)
/*!*/;
在mysql命令中使用show binlog events查看:
点击(此处)折叠或打开
***************************2.row***************************
Log_name:mysql-bin.000002
Pos:107
Event_type:Query
Server_id:1
End_log_pos:193
Info:use`test`;create tablet1(idint)
对于正常的binlog文件,有2个特殊类型的事件Format_desc和Rotate。format_desc用于标示binlog文件的开始,记录环境信息,rotate用于表示binlog文件结束。
点击(此处)折叠或打开
***************************1.row***************************
Log_name:mysql-bin.000002
Pos:4
Event_type:Format_desc
Server_id:1
End_log_pos:107
Info:Serverver:5.5.28-log,Binlogver:4
点击(此处)折叠或打开
***************************3.row***************************
Log_name:mysql-bin.000002
Pos:193
Event_type:Rotate
Server_id:1
End_log_pos:236
Info:mysql-bin.000003;pos=4
无数据更新的更新操作。
点击(此处)折叠或打开
mysql>select*fromt;
Emptyset(0.00sec)
mysql>update tsetid=1whereid=2;
QueryOK,0rows affected(0.00sec)
Rowsmatched:0 Changed:0 Warnings:0
binlog中内容:
点击(此处)折叠或打开
# at 607
#130223 12:20:55 server id 1 end_log_pos 675 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1361593255/*!*/;
BEGIN
/*!*/;
# at 675
#130223 12:20:55 server id 1 end_log_pos 767 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1361593255/*!*/;
update t set id =1 where id=2
/*!*/;
# at 767
#130223 12:20:55 server id 1 end_log_pos 836 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1361593255/*!*/;
COMMIT
/*!*/;
1. 在binlog中这条update语句分为3个event来记录,这样可以避免slave在重放过程中中断带来的不一致,如果复制在一个事务分组某一点中断,恢复后会从这个事务的开始重新执行。
2. 在binlog_format=statement的模式下,上面这样的语句未匹配到数据,也没有更新数据,仍然会记录到binlog中。事务
如果事务正常提交,那么和一般情况一样,binlog会在commit之后记录这个事件;如果事务被回滚,binlog将不会记录事件。
点击(此处)折叠或打开
mysql>start transaction;
QueryOK,0rows affected(0.00sec)
mysql>update t1setid=3whereid=2;
QueryOK,1row affected(0.00sec)
Rowsmatched:1 Changed:1 Warnings:0
mysql>rollback;
QueryOK,0rows affected(0.01sec)
此时binlog记录没有这个事务的记录:
点击(此处)折叠或打开
# at 904
#130223 13:17:04 server id 1 end_log_pos 996 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1361596624/*!*/;
update t1 set id=2 where id=1
/*!*/;
# at 996
#130223 13:17:28 server id 1 end_log_pos 1023 Xid = 54
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
存储函数(procedure,function,trigger,event)
对于存储函数的create、alter、drop操作都会记录到binlog中(不区分binlog-formate),在具体的执行过程,不同的bin_format,记录方式会有差异。
1.statement
1)procedure
储存过程中执行的且有数据改变的sql语句将记录到binlog中.
点击(此处)折叠或打开
# at 2293
#130223 14:04:11 server id 1 end_log_pos 2469 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1361599451/*!*/;
CREATE DEFINER=`xchliu`@`%` PROCEDURE `test`()
BEGIN
update t1 set id =1;
select 123;
update t1 set id=2;
END
/*!*/;
# at 2469
#130223 14:04:16 server id 1 end_log_pos 2537 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1361599456/*!*/;
BEGIN
/*!*/;
# at 2537
#130223 14:04:16 server id 1 end_log_pos 2619 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1361599456/*!*/;
update t1 set id =1
/*!*/;
# at 2619
#130223 14:04:16 server id 1 end_log_pos 2646 Xid = 100
COMMIT/*!*/;
# at 2646
#130223 14:04:16 server id 1 end_log_pos 2714 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1361599456/*!*/;
BEGIN
/*!*/;
# at 2714
#130223 14:04:16 server id 1 end_log_pos 2795 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1361599456/*!*/;
update t1 set id=2
/*!*/;
2)trigger
记录产生数据更新的sql语句。对于调用触发器后产生的数据更新,并不记录到binlog中。如下面的情况,insert语句到达slave中后同样会调用slave上的触发器,这样就保证主从数据一致。
点击(此处)折叠或打开
# at 2822
#130223 14:06:47 server id 1 end_log_pos 3040 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1361599607/*!*/;
CREATE DEFINER=`xchliu`@`%` TRIGGER `test`.`tg_1` AFTER INSERT
ON `test`.`t1`
FOR EACH ROW BEGIN
insert into t select * from t1;
END
/*!*/;
# at 3040
#130223 14:07:26 server id 1 end_log_pos 3108 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1361599646/*!*/;
BEGIN
/*!*/;
# at 3108
#130223 14:07:26 server id 1 end_log_pos 3195 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1361599646/*!*/;
insert into t1 values(1)
/*!*/;
# at 3195
#130223 14:07:26 server id 1 end_log_pos 3222 Xid = 123
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
3)function
如果函数中有数据改变,那函数的调用语句(包括select语句)将记录到binlog中。
点击(此处)折叠或打开
***************************41.row***************************
Log_name:mysql-bin.000003
Pos:3222
Event_type:Query
Server_id:1
End_log_pos:3428
Info:use`test`;CREATE DEFINER=`xchliu`@`%`FUNCTION`test`.`f_test`()RETURNSint(11)
DETERMINISTIC
BEGIN
update tsetid=2;
return0;
END
***************************42.row***************************
Log_name:mysql-bin.000003
Pos:3428
Event_type:Query
Server_id:1
End_log_pos:3496
Info:BEGIN
***************************43.row***************************
Log_name:mysql-bin.000003
Pos:3496
Event_type:Query
Server_id:1
End_log_pos:3583
Info:use`test`;SELECT`test`.`f_test`()
***************************44.row***************************
Log_name:mysql-bin.000003
Pos:3583
Event_type:Xid
Server_id:1
End_log_pos:3610
Info:COMMIT/* xid=162 */
4)event:
event触发事件后,更新数据的sql语句将记入binlog,event功能默认是关闭的。在master上定义event后,slave同步event并将其标示为SLAVESIDE_DISABLED,表mysql.event的column:originator值为master的server ID.
点击(此处)折叠或打开
***************************51.row***************************
Log_name:mysql-bin.000003
Pos:5093
Event_type:Query
Server_id:1
End_log_pos:5341
Info:use`test`;ALTER DEFINER=`xchliu`@`%`EVENT`event_1`ON SCHEDULE EVERY10SECOND STARTS'2013-02-23 14:26:11'ON COMPLETION NOT PRESERVE ENABLE DOBEGIN
insertintot values(1);
END
***************************52.row***************************
Log_name:mysql-bin.000003
Pos:5341
Event_type:Query
Server_id:1
End_log_pos:5409
Info:BEGIN
***************************53.row***************************
Log_name:mysql-bin.000003
Pos:5409
Event_type:Query
Server_id:1
End_log_pos:5495
Info:use`test`;insertintot values(1)
***************************54.row***************************
Log_name:mysql-bin.000003
Pos:5495
Event_type:Xid
Server_id:1
End_log_pos:5522
Info:COMMIT/* xid=252 */
2.row
函数产生的数据更新以具体行的更新记录在binlog中,而不是记录相应的sql或者函数调用语句。在row模式下,slave上的trigger不会被触发,slave读取master的binlog上面进行重放。
t1的一个触发器after insert行为:insert into t select * from t1;
执行语句insert into t1 values(1)后产生的binlog event:
点击(此处)折叠或打开
mysql>show binlog eventsin'mysql-bin.000006'G
***************************1.row***************************
Log_name:mysql-bin.000006
Pos:4
Event_type:Format_desc
Server_id:1
End_log_pos:107
Info:Serverver:5.5.28-log,Binlogver:4
***************************2.row***************************
Log_name:mysql-bin.000006
Pos:107
Event_type:Query
Server_id:1
End_log_pos:175
Info:BEGIN
***************************3.row***************************
Log_name:mysql-bin.000006
Pos:175
Event_type:Table_map
Server_id:1
End_log_pos:216
Info:table_id:39(test.t1)
***************************4.row***************************
Log_name:mysql-bin.000006
Pos:216
Event_type:Table_map
Server_id:1
End_log_pos:256
Info:table_id:40(test.t)
***************************5.row***************************
Log_name:mysql-bin.000006
Pos:256
Event_type:Write_rows
Server_id:1
End_log_pos:290
Info:table_id:39
***************************6.row***************************
Log_name:mysql-bin.000006
Pos:290
Event_type:Write_rows
Server_id:1
End_log_pos:324
Info:table_id:40flags:STMT_END_F
***************************7.row***************************
Log_name:mysql-bin.000006
Pos:324
Event_type:Xid
Server_id:1
End_log_pos:351
Info:COMMIT/* xid=369 */
7rowsinset(0.00sec)
系统表
对系统库(mysql)中的表更新
1. 使用insert/update/delete语句,按照binlog-format来选择记录方式
2. 使用权限命令(grant,revoke等),直接记录相应的sql语句。
点击(此处)折叠或打开
***************************8.row***************************
Log_name:mysql-bin.000006
Pos:351
Event_type:Query
Server_id:1
End_log_pos:472
Info:use`test`;grantselecton test.*to test@'localhost'
statement 和row 模式下授权语句的binglog记录方式:
点击(此处)折叠或打开
# statement 模式
# at 107
#130409 12:14:03 server id 1 end_log_pos 242 Query thread_id=9 exec_time=0 error_code=0
use mysql/*!*/;
SET TIMESTAMP=1365480843/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
grant select on test.* to test@'localhost' identified by 'test'
/*!*/;
# row 模式
# at 242
#130409 12:18:58 server id 1 end_log_pos 373 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1365481138/*!*/;
grant select on test.* to test1@'localhost' identified by 'test'
/*!*/;
DELIMITER ;