牛刀小试MySQL学习—The Binary Log

  1. 云栖社区>
  2. 博客>
  3. 正文

牛刀小试MySQL学习—The Binary Log

zhangshengdong 2012-07-06 11:33:05 浏览524
展开阅读全文
mysql server有四种类型的日志 (参考的文档是5.5-en.html)
The Error Log                           错误日志
The General Query Log          一般查询日志
The Binary Log                         二进制日志
The Slow Query Log                慢查询日志
其中:
          错误日志,顾名思义,包含了mysqld启动,关闭和服务器运行时发生了任何错误的信息都会写在error log上面。如果mysqld通知
表需要自动的检查和修复,也会把记录写在error log上面
          慢查询日志,可以查看mysql服务器中运行比较慢的sql,这个在日常工作中,会经常使用到。官方的,就是日志中包含了一些超过了参数 long_query_time 指定时间的sql statement。和 min_examined_row_limit最小检查行数限制。
        (两个参数,可以使用show variables like 'long_query_time'  查看,long_query_time的最小值和默认值是0和10)
          一般查询日志,是记录了mysqld正在做的事情,mysql服务器会记录客户端连接和不连接,以及来自客户端的每条sql statement记录信息,当然,如果你想准确得知道客户端到底传了什么东西给mysqld,这个日志非常的有用,但是它非常影响服务器的性能,所以,一般情况下,它是不开启的
           二进制日志,包含了”events“,这些events描述了数据库的改动,例如表创建的操作和数据的改动。它也包括了一些潜在的改动(例如:用了 一条delete语句,但是什么都没有删除掉),除非使用row-based logging,否则会包含所有改动数据的sql statement。二进制日志有两个重要的用途:

           For replication(这个是mysql replication,主从服务器,以后我会写一篇日志,关于他的介绍和安装配置)。二进制日志会记录master里面所有的statement,然后 slave会接受master的二进制日志,从而根据二进制日志,修改slave服务器上的数据。

           数据库的恢复操作需要the binary log,在对一个备份进行restore之后,记录在二进制日志中的events可以re-executed,这些events可以使数据库进行基于时间点的恢复(Point-in-Time (Incremental) Recovery Using the Binary Log


基本概念和理论讲述完毕,现在自己开始实战演练:

1,开启binary log
在/etc/my.cnf(这个是我对应的测试数据库的位置)添加如下脚本:

  1. log-bin =mysql-bin
  2. log-bin-index=mysql-bin.index
复制代码
    其中,linux系统服务中存储的binary log的文件的前缀和mysql-bin一致,为mysql-bin.000001,

2,进入mysql服务器

  1. mysql> reset master;(重做master日志,日志从mysql-bin.000001日志开始)
  2. Query OK, 0 rows affected (0.00 sec)
  3. mysql> show master status;(查看master服务器,目前日志记录的位置,开始为107)
  4. +------------------+----------+--------------+------------------+
  5. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  6. +------------------+----------+--------------+------------------+
  7. | mysql-bin.000001 |      107 |              |                  |
  8. +------------------+----------+--------------+------------------+
  9. 1 row in set (0.00 sec)
  10. mysql> insert into user values(200);
  11. Query OK, 1 row affected (0.00 sec)
  12. mysql> insert into user values(300);
  13. Query OK, 1 row affected (0.01 sec)
  14. mysql> insert into user values(400);
  15. Query OK, 1 row affected (0.00 sec)
  16. mysql> show master status;(这里的position发生了改变,position为377)
  17. +------------------+----------+--------------+------------------+
  18. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  19. +------------------+----------+--------------+------------------+
  20. | mysql-bin.000001 |      377 |              |                  |
  21. +------------------+----------+--------------+------------------+
  22. 1 row in set (0.00 sec)
  23. mysql> show binlog events\G;(这里这记录了上述发生的所有事情)
  24. *************************** 1. row ***************************
  25.    Log_name: mysql-bin.000001
  26.         Pos: 4
  27. Event_type: Format_desc
  28.   Server_id: 1
  29. End_log_pos: 107
  30.        Info: Server ver: 5.5.2-m2-log, Binlog ver: 4
  31. *************************** 2. row ***************************
  32.    Log_name: mysql-bin.000001
  33.         Pos: 107
  34. Event_type: Query
  35.   Server_id: 1
  36. End_log_pos: 197
  37.        Info: use `zsd`; insert into user values(200)
  38. *************************** 3. row ***************************
  39.    Log_name: mysql-bin.000001
  40.         Pos: 197
  41. Event_type: Query
  42.   Server_id: 1
  43. End_log_pos: 287
  44.        Info: use `zsd`; insert into user values(300)
  45. *************************** 4. row ***************************
  46.    Log_name: mysql-bin.000001
  47.         Pos: 287
  48. Event_type: Query
  49.   Server_id: 1
  50. End_log_pos: 377
  51.        Info: use `zsd`; insert into user values(400)
  52. 4 rows in set (0.00 sec)
复制代码
Format_desc:这是每一个binlog文件的头,是每一个binlog文件必有的第一个event,在这个event中,记录了一些诸如binary log格式版本,产生这个event的mysql server      版 本等等.
其中:
Log_name: event所在的binlog名称.这里是在mysql-bin.000001
Pos: event在当前binlog中的位置
Event_type: event的类型
Server_id: event是在哪个server上发生的..
End_log_pos:下一个event的位置.因此当前这个event的长度是End_log_pos-Pos.
Info:直观的可读的关于本条event的信息.

3,执行flush logs;

  1. mysql> flush logs ;
  2. Query OK, 0 rows affected (0.01 sec)
  3. mysql> show master status;(flush logs 会切换日志)
  4. +------------------+----------+--------------+------------------+
  5. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  6. +------------------+----------+--------------+------------------+
  7. | mysql-bin.000002 |      107 |              |                  |
  8. +------------------+----------+--------------+------------------+
  9. 1 row in set (0.00 sec)
复制代码

        再次查看show binlog events 里面多了一个行,就是记录刚刚切换flush_log的event
  1. mysql> show binlog events\G;
  2. *************************** 1. row ***************************
  3.    Log_name: mysql-bin.000001
  4.         Pos: 4
  5. Event_type: Format_desc
  6.   Server_id: 1
  7. End_log_pos: 107
  8.        Info: Server ver: 5.5.2-m2-log, Binlog ver: 4
  9. *************************** 2. row ***************************
  10.    Log_name: mysql-bin.000001
  11.         Pos: 107
  12. Event_type: Query
  13.   Server_id: 1
  14. End_log_pos: 197
  15.        Info: use `zsd`; insert into user values(200)
  16. *************************** 3. row ***************************
  17.    Log_name: mysql-bin.000001
  18.         Pos: 197
  19. Event_type: Query
  20.   Server_id: 1
  21. End_log_pos: 287
  22.        Info: use `zsd`; insert into user values(300)
  23. *************************** 4. row ***************************
  24.    Log_name: mysql-bin.000001
  25.         Pos: 287
  26. Event_type: Query
  27.   Server_id: 1
  28. End_log_pos: 377
  29.        Info: use `zsd`; insert into user values(400)
  30. *************************** 5. row ***************************
  31.    Log_name: mysql-bin.000001
  32.         Pos: 377
  33. Event_type: Rotate (这里是使用了flush logs,指定了此事件的类型就为rotate)
  34.   Server_id: 1
  35. End_log_pos: 420
  36.        Info: mysql-bin.000002;pos=4(切换到的日志的位置)
  37. 5 rows in set (0.00 sec)
复制代码
  1. show binlog events \G只能查看第一个日志的events
  2. show binlog events in 'mysql-bin.000003';可以查看具体位置日志的events
复制代码

4,mysqlbinlog可以在shell下查看日志的具体记录
语法:
shell> mysqlbinlog log_file | mysql -h server_name
例子:
  1. [root@www mysql]# bin/mysqlbinlog ./data/mysql-bin.000001
  2. # at 107(这里的107和上面的107如出一辙,只是表现的方式不一样,内容相同)
  3. #120426  4:43:54 server id 1  end_log_pos 197   Query   thread_id=9  exec_time=0error_code=0
  4. use zsd/*!*/;
  5. SET TIMESTAMP=1335429834/*!*/;(这个代表unix的时间戳)
  6. insert into user values(200)
  7. /*!*/;
  8. # at 197
  9. #120426  4:43:58 server id 1  end_log_pos 287   Query   thread_id=9  exec_time=0error_code=0
  10. SET TIMESTAMP=1335429838/*!*/;
  11. insert into user values(300)
  12. /*!*/;
  13. # at 287
  14. #120426  4:44:01 server id 1  end_log_pos 377   Query   thread_id=9  exec_time=0error_code=0
  15. SET TIMESTAMP=1335429841/*!*/;
  16. insert into user values(400)
  17. /*!*/;
  18. # at 377
  19. #120426  4:51:53 server id 1  end_log_pos 420   Rotate to mysql-bin.000002  pos: 4
  20. DELIMITER ;
复制代码
上述的日志,我进行了一些删改,不过这样可以很清楚的看出,binary log记录了数据库改动的信息。

二:Binary  Logging  Formats
Binary log有三种format:
--binlog-format=STATEMENT:基于statement,顾名思义,不管对数据库有没有影响数据的sql statement都会存储在binary log里面
--binlog-format=ROW:基于row,是对表的行数据有影响的sql statement 存储在binary log里面
--binlog-format=MIXED:基于mixed,默认情况下使用statement-based logging,但是某些场合下logging mode会自动转化为 row -based(这里官方文档的英文没有看懂,什么叫某些场合下自动转化,希望高手能指点一二

     Set The Binary Log Format
     这里有两种设置:GLOBAL和SESSION一个针对所有的client,一个针对个人的clinet,脚本如下

  1. mysql> SET GLOBAL binlog_format = 'STATEMENT';
  2. mysql> SET GLOBAL binlog_format = 'ROW';
  3. mysql> SET GLOBAL binlog_format = 'MIXED';
  4. mysql> SET SESSION binlog_format = 'STATEMENT';
  5. mysql> SET SESSION binlog_format = 'ROW';
  6. mysql> SET SESSION binlog_format = 'MIXED'
复制代码

官方文档有解释说,在 statement-based replication下,会有些statement具有不可靠性,不能保证可以replicated。Statement may not be safe to log in statement format.在statement模式下sql statement的不可靠)所以,要避免上述问题,就要使用MySQL's row-based replication

网友评论

登录后评论
0/500
评论
zhangshengdong
+ 关注