Chinaunix首页 | 论坛 | 博客

smoothiexliu.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

devilkin0312

  • 博客访问： 277861
  • 博文数量： 52
  • 博客积分： 120
  • 博客等级： 民兵
  • 技术积分： 1189
  • 用 户 组： 普通用户
  • 注册时间： 2011-08-03 15:41
个人简介

MySQL DBA

文章分类

全部博文（52）

文章存档

2013年（51）

2011年（1）

我的朋友
最近访客
推荐博文
相关博文
mysql binlog的记录机制

分类： Mysql/postgreSQL

2013-03-07 14:12:20

mysql的binlog有2个主要作用：复制和恢复。其内容记录所有的数据变化（DDL & DML）事件。这里就讨论下其记录机制。
      
     一般情况(insert,update,delete,create ,alter,drop)

    创建一张表，使用mysqlbinlog查看binlog中的内容:


  1. # at 107
  2. #130223 11:47:24 server id 1 end_log_pos 193 Query thread_id=3 exec_time=0 error_code=0
  3. use test/*!*/;
  4. SET TIMESTAMP=1361591244/*!*/;
  5. SET @@session.pseudo_thread_id=3/*!*/;
  6. SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0,@@session.unique_checks=1,@@session.autocommit=1/*!*/;
  7. SET@@session.sql_mode=0/*!*/;
  8. SET@@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;
  9. /*!C utf8 *//*!*/;
  10. SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
  11. SET@@session.lc_time_names=0/*!*/;
  12. SET@@session.collation_database=DEFAULT/*!*/;
  13. create table t1(idint)
  14. /*!*/;


在mysql命令中使用show binlog events查看：

点击(此处)折叠或打开

  1. ***************************2.row***************************
  2. Log_name:mysql-bin.000002
  3. Pos:107
  4. Event_type:Query
  5. Server_id:1
  6. End_log_pos:193
  7. Info:use`test`;create tablet1(idint)

对于正常的binlog文件，有2个特殊类型的事件Format_desc和Rotate。format_desc用于标示binlog文件的开始，记录环境信息，rotate用于表示binlog文件结束。

点击(此处)折叠或打开

  1. ***************************1.row***************************
  2. Log_name:mysql-bin.000002
  3. Pos:4
  4. Event_type:Format_desc
  5. Server_id:1
  6. End_log_pos:107
  7. Info:Serverver:5.5.28-log,Binlogver:4

点击(此处)折叠或打开

  1. ***************************3.row***************************
  2. Log_name:mysql-bin.000002
  3. Pos:193

  5. Event_type:Rotate
  6. Server_id:1
  7. End_log_pos:236

  9. Info:mysql-bin.000003;pos=4

无数据更新的更新操作。


点击(此处)折叠或打开

  1. mysql>select*fromt;
  2. Emptyset(0.00sec)
  3. mysql>update tsetid=1whereid=2;
  4. QueryOK,0rows affected(0.00sec)
  5. Rowsmatched:0 Changed:0 Warnings:0


binlog中内容：


点击(此处)折叠或打开

  1. # at 607
  2. #130223 12:20:55 server id 1 end_log_pos 675 Query thread_id=4 exec_time=0 error_code=0
  3. SET TIMESTAMP=1361593255/*!*/;
  4. BEGIN
  5. /*!*/;
  6. # at 675
  7. #130223 12:20:55 server id 1 end_log_pos 767 Query thread_id=4 exec_time=0 error_code=0
  8. SET TIMESTAMP=1361593255/*!*/;
  9. update t set id =1 where id=2
  10. /*!*/;
  11. # at 767
  12. #130223 12:20:55 server id 1 end_log_pos 836 Query thread_id=4 exec_time=0 error_code=0
  13. SET TIMESTAMP=1361593255/*!*/;
  14. COMMIT
  15. /*!*/;



1.    在binlog中这条update语句分为3个event来记录，这样可以避免slave在重放过程中中断带来的不一致，如果复制在一个事务分组某一点中断，恢复后会从这个事务的开始重新执行。

2.    在binlog_format=statement的模式下，上面这样的语句未匹配到数据，也没有更新数据，仍然会记录到binlog中。
    事务

如果事务正常提交，那么和一般情况一样，binlog会在commit之后记录这个事件；如果事务被回滚，binlog将不会记录事件。


点击(此处)折叠或打开

  1. mysql>start transaction;
  2. QueryOK,0rows affected(0.00sec)
  3. mysql>update t1setid=3whereid=2;
  4. QueryOK,1row affected(0.00sec)
  5. Rowsmatched:1 Changed:1 Warnings:0
  6. mysql>rollback;
  7. QueryOK,0rows affected(0.01sec)


此时binlog记录没有这个事务的记录：


点击(此处)折叠或打开

  1. # at 904
  2. #130223 13:17:04 server id 1 end_log_pos 996 Query thread_id=4 exec_time=0 error_code=0
  3. SET TIMESTAMP=1361596624/*!*/;
  4. update t1 set id=2 where id=1
  5. /*!*/;
  6. # at 996
  7. #130223 13:17:28 server id 1 end_log_pos 1023 Xid = 54
  8. COMMIT/*!*/;
  9. DELIMITER ;
  10. # End of log file
  11. ROLLBACK /* added by mysqlbinlog */;
  12. /*!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中.


点击(此处)折叠或打开

  1. # at 2293
  2. #130223 14:04:11 server id 1 end_log_pos 2469 Query thread_id=5 exec_time=0 error_code=0
  3. SET TIMESTAMP=1361599451/*!*/;
  4. CREATE DEFINER=`xchliu`@`%` PROCEDURE `test`()
  5. BEGIN
  6. update t1 set id =1;
  7. select 123;
  8. update t1 set id=2;
  9. END
  10. /*!*/;
  11. # at 2469
  12. #130223 14:04:16 server id 1 end_log_pos 2537 Query thread_id=4 exec_time=0 error_code=0
  13. SET TIMESTAMP=1361599456/*!*/;
  14. BEGIN
  15. /*!*/;
  16. # at 2537
  17. #130223 14:04:16 server id 1 end_log_pos 2619 Query thread_id=4 exec_time=0 error_code=0
  18. SET TIMESTAMP=1361599456/*!*/;
  19. update t1 set id =1
  20. /*!*/;
  21. # at 2619
  22. #130223 14:04:16 server id 1 end_log_pos 2646 Xid = 100
  23. COMMIT/*!*/;
  24. # at 2646
  25. #130223 14:04:16 server id 1 end_log_pos 2714 Query thread_id=4 exec_time=0 error_code=0
  26. SET TIMESTAMP=1361599456/*!*/;
  27. BEGIN
  28. /*!*/;
  29. # at 2714
  30. #130223 14:04:16 server id 1 end_log_pos 2795 Query thread_id=4 exec_time=0 error_code=0
  31. SET TIMESTAMP=1361599456/*!*/;
  32. update t1 set id=2
  33. /*!*/;



2)trigger

记录产生数据更新的sql语句。对于调用触发器后产生的数据更新，并不记录到binlog中。如下面的情况，insert语句到达slave中后同样会调用slave上的触发器，这样就保证主从数据一致。


点击(此处)折叠或打开

  1. # at 2822

  3. #130223 14:06:47 server id 1 end_log_pos 3040 Query thread_id=5 exec_time=0 error_code=0
  4. SET TIMESTAMP=1361599607/*!*/;
  5. CREATE DEFINER=`xchliu`@`%` TRIGGER `test`.`tg_1` AFTER INSERT
  6. ON `test`.`t1`
  7. FOR EACH ROW BEGIN
  8. insert into t select * from t1;
  9. END
  10. /*!*/;
  11. # at 3040
  12. #130223 14:07:26 server id 1 end_log_pos 3108 Query thread_id=4 exec_time=0 error_code=0
  13. SET TIMESTAMP=1361599646/*!*/;
  14. BEGIN
  15. /*!*/;
  16. # at 3108
  17. #130223 14:07:26 server id 1 end_log_pos 3195 Query thread_id=4 exec_time=0 error_code=0
  18. SET TIMESTAMP=1361599646/*!*/;
  19. insert into t1 values(1)
  20. /*!*/;
  21. # at 3195
  22. #130223 14:07:26 server id 1 end_log_pos 3222 Xid = 123
  23. COMMIT/*!*/;
  24. DELIMITER ;
  25. # End of log file
  26. ROLLBACK /* added by mysqlbinlog */;
  27. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;


3)function

如果函数中有数据改变，那函数的调用语句（包括select语句）将记录到binlog中。


点击(此处)折叠或打开

  1. ***************************41.row***************************

  3. Log_name:mysql-bin.000003
  4. Pos:3222
  5. Event_type:Query
  6. Server_id:1
  7. End_log_pos:3428
  8. Info:use`test`;CREATE DEFINER=`xchliu`@`%`FUNCTION`test`.`f_test`()RETURNSint(11)
  9. DETERMINISTIC
  10. BEGIN
  11. update tsetid=2;
  12. return0;
  13. END
  14. ***************************42.row***************************
  15. Log_name:mysql-bin.000003
  16. Pos:3428
  17. Event_type:Query
  18. Server_id:1
  19. End_log_pos:3496
  20. Info:BEGIN
  21. ***************************43.row***************************
  22. Log_name:mysql-bin.000003
  23. Pos:3496
  24. Event_type:Query
  25. Server_id:1
  26. End_log_pos:3583
  27. Info:use`test`;SELECT`test`.`f_test`()
  28. ***************************44.row***************************
  29. Log_name:mysql-bin.000003
  30. Pos:3583
  31. Event_type:Xid
  32. Server_id:1
  33. End_log_pos:3610
  34. Info:COMMIT/* xid=162 */


4)event：


event触发事件后，更新数据的sql语句将记入binlog，event功能默认是关闭的。在master上定义event后，slave同步event并将其标示为SLAVESIDE_DISABLED，表mysql.event的column：originator值为master的server ID.


点击(此处)折叠或打开

  1. ***************************51.row***************************

  3. Log_name:mysql-bin.000003
  4. Pos:5093
  5. Event_type:Query
  6. Server_id:1
  7. End_log_pos:5341
  8. 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
  9. insertintot values(1);
  10. END
  11. ***************************52.row***************************
  12. Log_name:mysql-bin.000003
  13. Pos:5341
  14. Event_type:Query
  15. Server_id:1
  16. End_log_pos:5409
  17. Info:BEGIN
  18. ***************************53.row***************************
  19. Log_name:mysql-bin.000003
  20. Pos:5409
  21. Event_type:Query
  22. Server_id:1
  23. End_log_pos:5495
  24. Info:use`test`;insertintot values(1)
  25. ***************************54.row***************************
  26. Log_name:mysql-bin.000003
  27. Pos:5495
  28. Event_type:Xid
  29. Server_id:1
  30. End_log_pos:5522
  31. 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:


点击(此处)折叠或打开

  1. mysql>show binlog eventsin'mysql-bin.000006'G
  2. ***************************1.row***************************
  3. Log_name:mysql-bin.000006
  4. Pos:4
  5. Event_type:Format_desc
  6. Server_id:1
  7. End_log_pos:107
  8. Info:Serverver:5.5.28-log,Binlogver:4
  9. ***************************2.row***************************
  10. Log_name:mysql-bin.000006
  11. Pos:107
  12. Event_type:Query
  13. Server_id:1
  14. End_log_pos:175
  15. Info:BEGIN
  16. ***************************3.row***************************
  17. Log_name:mysql-bin.000006
  18. Pos:175
  19. Event_type:Table_map
  20. Server_id:1
  21. End_log_pos:216
  22. Info:table_id:39(test.t1)
  23. ***************************4.row***************************
  24. Log_name:mysql-bin.000006
  25. Pos:216
  26. Event_type:Table_map
  27. Server_id:1
  28. End_log_pos:256
  29. Info:table_id:40(test.t)
  30. ***************************5.row***************************
  31. Log_name:mysql-bin.000006
  32. Pos:256
  33. Event_type:Write_rows
  34. Server_id:1
  35. End_log_pos:290
  36. Info:table_id:39
  37. ***************************6.row***************************
  38. Log_name:mysql-bin.000006
  39. Pos:290
  40. Event_type:Write_rows
  41. Server_id:1
  42. End_log_pos:324
  43. Info:table_id:40flags:STMT_END_F
  44. ***************************7.row***************************
  45. Log_name:mysql-bin.000006
  46. Pos:324
  47. Event_type:Xid
  48. Server_id:1
  49. End_log_pos:351
  50. Info:COMMIT/* xid=369 */
  51. 7rowsinset(0.00sec)


系统表

对系统库（mysql）中的表更新

1.    使用insert/update/delete语句，按照binlog-format来选择记录方式

2.    使用权限命令（grant，revoke等），直接记录相应的sql语句。


点击(此处)折叠或打开

  1. ***************************8.row***************************
  2. Log_name:mysql-bin.000006
  3. Pos:351
  4. Event_type:Query
  5. Server_id:1
  6. End_log_pos:472
  7. Info:use`test`;grantselecton test.*to test@'localhost'


statement 和row 模式下授权语句的binglog记录方式：

点击(此处)折叠或打开

  1. # statement 模式
  2. # at 107
  3. #130409 12:14:03 server id 1 end_log_pos 242     Query    thread_id=9    exec_time=0    error_code=0
  4. use mysql/*!*/;
  5. SET TIMESTAMP=1365480843/*!*/;
  6. SET @@session.pseudo_thread_id=9/*!*/;
  7. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  8. SET @@session.sql_mode=0/*!*/;
  9. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  10. /*!\C utf8 *//*!*/;
  11. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
  12. SET @@session.lc_time_names=0/*!*/;
  13. SET @@session.collation_database=DEFAULT/*!*/;
  14. grant select on test.* to test@'localhost' identified by 'test'
  15. /*!*/;
  16. # row 模式
  17. # at 242
  18. #130409 12:18:58 server id 1 end_log_pos 373     Query    thread_id=10    exec_time=0    error_code=0
  19. SET TIMESTAMP=1365481138/*!*/;
  20. grant select on test.* to test1@'localhost' identified by 'test'
  21. /*!*/;
  22. DELIMITER ;



阅读(8921) | 评论(3) | 转发(0) |
0

上一篇：mysql 日志系统

下一篇：MySQL 程序汇总（一）

给主人留下些什么吧！~~

ning_lianjie2013-04-09 12:49:51

devilkin0312：感谢回复，对于授权语句，binlog中都直接记录语句的。文末补充的内容分别是statement和row模式下授权语句的binlog。

靠谱.
binlog会记录明文密码,不过在row模式下,有点意外,grant语句,居然跟statement一样,没有记录行的修改状态.

回复 | 举报

devilkin03122013-04-09 12:22:36

ning_lianjie：赞,文章写的很详细,从简单的sql语句到函数,触发器等记录binlog的不同机制.其中涉及的如下结论很重要,事务在提交之后,才会把数据写入binlog.由触发器引起的数据变动不会写入binlog,而是在从库重新调用触发器.关于授权语句,博主不妨测试一下,密码是否会在binlog中记录原文.

感谢回复，对于授权语句，binlog中都直接记录语句的。文末补充的内容分别是statement和row模式下授权语句的binlog。

回复 | 举报

ning_lianjie2013-04-09 10:11:00

赞,文章写的很详细,从简单的sql语句到函数,触发器等记录binlog的不同机制.其中涉及的如下结论很重要,事务在提交之后,才会把数据写入binlog.由触发器引起的数据变动不会写入binlog,而是在从库重新调用触发器.关于授权语句,博主不妨测试一下,密码是否会在binlog中记录原文.

回复 | 举报
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6