牛刀小试MySQL学习—The Binary Log

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