牛刀小试MySQL学习—The Binary Log

最新推荐文章于 2024-04-13 11:17:19 发布

原创最新推荐文章于 2024-04-13 11:17:19 发布 · 75 阅读

CC 4.0 BY-SA版权

本文介绍了MySQL的四种日志类型：错误日志、一般查询日志、二进制日志和慢查询日志，并重点讲解了二进制日志的工作原理、格式及应用场景。

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(这个是我对应的测试数据库的位置)添加如下脚本：

log-bin =mysql-bin
log-bin-index=mysql-bin.index

复制代码

其中，linux系统服务中存储的binary log的文件的前缀和mysql-bin一致，为mysql-bin.000001,

2,进入mysql服务器

mysql> reset master;（重做master日志，日志从mysql-bin.000001日志开始）
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;（查看master服务器，目前日志记录的位置，开始为107）
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> insert into user values(200);
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(300);
Query OK, 1 row affected (0.01 sec)
mysql> insert into user values(400);
Query OK, 1 row affected (0.00 sec)
mysql> show master status;（这里的position发生了改变，position为377）
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 377 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show binlog events\G;(这里这记录了上述发生的所有事情)
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 107
Info: Server ver: 5.5.2-m2-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000001
Pos: 107
Event_type: Query
Server_id: 1
End_log_pos: 197
Info: use `zsd`; insert into user values(200)
*************************** 3. row ***************************
Log_name: mysql-bin.000001
Pos: 197
Event_type: Query
Server_id: 1
End_log_pos: 287
Info: use `zsd`; insert into user values(300)
*************************** 4. row ***************************
Log_name: mysql-bin.000001
Pos: 287
Event_type: Query
Server_id: 1
End_log_pos: 377
Info: use `zsd`; insert into user values(400)
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;

mysql> flush logs ;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;(flush logs 会切换日志)
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

复制代码

再次查看show binlog events 里面多了一个行，就是记录刚刚切换flush_log的event

mysql> show binlog events\G;
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 107
Info: Server ver: 5.5.2-m2-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000001
Pos: 107
Event_type: Query
Server_id: 1
End_log_pos: 197
Info: use `zsd`; insert into user values(200)
*************************** 3. row ***************************
Log_name: mysql-bin.000001
Pos: 197
Event_type: Query
Server_id: 1
End_log_pos: 287
Info: use `zsd`; insert into user values(300)
*************************** 4. row ***************************
Log_name: mysql-bin.000001
Pos: 287
Event_type: Query
Server_id: 1
End_log_pos: 377
Info: use `zsd`; insert into user values(400)
*************************** 5. row ***************************
Log_name: mysql-bin.000001
Pos: 377
Event_type: Rotate (这里是使用了flush logs,指定了此事件的类型就为rotate)
Server_id: 1
End_log_pos: 420
Info: mysql-bin.000002;pos=4(切换到的日志的位置)
5 rows in set (0.00 sec)

复制代码

show binlog events \G只能查看第一个日志的events
show binlog events in 'mysql-bin.000003';可以查看具体位置日志的events

复制代码

4，mysqlbinlog可以在shell下查看日志的具体记录
语法：
shell> mysqlbinlog log_file | mysql -h server_name
例子：

[root@www mysql]# bin/mysqlbinlog ./data/mysql-bin.000001
# at 107（这里的107和上面的107如出一辙，只是表现的方式不一样，内容相同）
#120426 4:43:54 server id 1 end_log_pos 197 Query thread_id=9 exec_time=0error_code=0
use zsd/*!*/;
SET TIMESTAMP=1335429834/*!*/;（这个代表unix的时间戳）
insert into user values(200)
/*!*/;
# at 197
#120426 4:43:58 server id 1 end_log_pos 287 Query thread_id=9 exec_time=0error_code=0
SET TIMESTAMP=1335429838/*!*/;
insert into user values(300)
/*!*/;
# at 287
#120426 4:44:01 server id 1 end_log_pos 377 Query thread_id=9 exec_time=0error_code=0
SET TIMESTAMP=1335429841/*!*/;
insert into user values(400)
/*!*/;
# at 377
#120426 4:51:53 server id 1 end_log_pos 420 Rotate to mysql-bin.000002 pos: 4
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，脚本如下：

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
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