解析mysqlbinlog日志_mysql binlog日志解析

1、什么是binlog

MySQL Server 有四种类型的日志——Error Log、General Query Log、Binary Log 和 Slow Query Log。

Error Log：错误日志，记录 mysqld 的一些错误。

General Query Log：一般查询日志，记录 mysqld 正在做的事情，比如客户端的连接和断开、来自客户端每条 Sql Statement 记录信息；如果你想准确知道客户端到底传了什么瞎 [哔哔] 玩意儿给服务端，这个日志就非常管用了，不过它非常影响性能。

Slow Query Log：是慢查询日志，记录一些查询比较慢的 SQL 语句——这种日志非常常用，主要是给开发者调优用的。

Binary Log：就是 Binlog 了，binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息。binlog包含了一些事件，这些事件描述了数据库的改动，如建表、数据改动等，也包括一些潜在改动，比如 DELETE FROM ran WHERE bing = luan，然而一条数据都没被删掉的这种情况。除非使用 Row-based logging，否则会包含所有改动数据的 SQL Statement。

显然，我们执行SELECT等不设计数据变更的语句是不会记录Binlog的，而涉及到数据更新则会记录。要注意的是，对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录Binlog。Binlog是在事务最终commit前写入的，binlog什么时候刷新到磁盘跟参数sync_binlog相关。如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新，而如果设置为不为0的值则表示每sync_binlog次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响，一般情况下会设置为100或者0，牺牲一定的一致性来获取更好的性能。

默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令(比如，cat，vi等)查看，而使用mysqlbinlog解析查看。

2.binlog的作用

当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的binlog文件里，这个文件就是上文说的binlog文件。使用mysqldump备份时，只是对一段时间的数据进行全备，但是如果备份后突然发现数据库服务器故障，这个时候就要用到binlog的日志了。

主要作用是用于数据库的主从复制及数据的增量恢复。

3、开启和停用Binlog

在mysql的配置文件my.cnf中，增加log_bin参数即可开启binlog日志

[mysqld]

log-bin=mysql-bin

可以通过赋值来指定binlog日志的文件名，实例如下：

[root@db02 ~]# mkdir /application/mysql/logs

[root@db02~]# chown -R mysql.mysql /application/mysql/logs

开启binlog

编辑/etc/my.cnf

[mysqld]

log_bin= /application/mysql/logs/dadong-bin

#这个需要重启MySQL服务。

重启:/etc/init.d/mysqld restart

[root@db02~]# ll /application/mysql/logs/total8

-rw-rw---- 1 mysql mysql 120 Jun 21 12:04 dadong-bin.000001

-rw-rw---- 1 mysql mysql 42 Jun 21 12:04 dadong-bin.index

为什么要刷新binlog?找到全备数据和binlog文件的恢复临界点.

如何刷新

每天晚上0点备份数据库

mysqldump-A -B -F >/opt/$(date +%F).sql

[root@db02~]# ll /application/mysql/logs/

-rw-rw---- 1 mysql mysql 168 Jun 21 12:06 dadong-bin.000001

-rw-rw---- 1 mysql mysql 168 Jun 21 12:06 dadong-bin.000002

-rw-rw---- 1 mysql mysql 210 Jun 21 12:07 dadong-bin.index

提示:每个库刷新一次.

可以使用SET SQL_LOG_BIN=0命令停止使用日志文件，然后可以通过SET SQL_LOG_BIN=1命令来启用。

4、Binlog的删除

mysql> show variables like 'expire_log_days';

mysql> set global expire_log_days=3; //过期删除

mysql> reset master; //删除master的binlog

mysql> reset slave; //删除slave的中继日志

mysql> purge master logs before '2016-10-20 16:25:00';//删除指定日期前的日志索引中binlog日志文件

mysql> purge master logs to 'binlog.000002';//删除指定日志文件

除了以上方法之外，也可以采用操作系统的本地命令删除文件。

5、Binlog文件的扩展

当遇到以下3种情况时会重新生成一个新的日志文件，文件序号递增：

MySQL服务器停止或重启时，MySQL会在重启时生成一个新的日志文件；

使用flush logs命令；

当binlog文件大小超过max_binlog_size系统变量配置的上限时；

注：binlog文件的最大值和默认值是1GB，该设置并不能严格控制binlog的大小，尤其是binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录到当前日志，直到事务结束。

6、Binlog的日志格式

binlog有三种格式：Statement, Row和Mixed.

基于SQL语句的复制(statement-based replication, SBR)

基于行的复制(row-based replication, RBR)

混合模式复制(mixed-based replication, MBR)

(1)Statement

每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 提高了性能。

缺点：由于记录的只是执行语