通过 mysqlbinlog 和 grep 命令定位binlog文件中指定操作

1.binlog日志基本知识

mysql的二进制日志binlog可以说是mysql最重要的日志，它记录了所有的ddl和dml语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间。

binlog有三种格式：statement、row以及mixed。分别是：基于sql语句的复制(statement-based replication,sbr)、 基于行的复制(row-based replication,rbr)和混合模式复制(mixed-based replication,mbr)。

 

格式	说明	优点	缺点
statement模式	每一条会修改数据的sql语句都会记录到binlog中。	不需要记录每一行的变化，减少了binlog日志量，节约了io，提高性能。	缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数，last_insert_id()，以及user-defined functions(udf)等会出现问题)。
row模式	不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。 而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。 新版本的mysql中对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么会记录所有行的变更。	任何情况都可以被复制，这对复制来说是最安全可靠的。多数情况下，从服务器上的表如果有主键的话，复制就会快了很多。 复制以下几种语句时的行锁更少：(1)insert ... select;(2)包含 auto_increment 字段的 insert;(3)没有附带条件或者并没有修改很多记录的 update 或 delete 语句 执行insert，update，delete 语句时锁更少。从服务器上采用多线程来执行复制成为可能。	binlog 大了很多 复杂的回滚时 binlog 中会包含大量的数据 主服务器上执行 update 语句时，所有发生变化的记录都会写到 binlog 中，这会导致频繁发生 binlog 的并发写问题 udf 产生的大 blob 值会导致复制变慢 无法从 binlog 中看到都复制了写什么语句
mixed模式	实际上就是前两种模式的结合，在mixed模式下，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选一种。	在mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在statement和row之间选择一种。

2.查看row模式和statement模式的binlog

为了加深印象，下面我们看看两者模式下的binlog到底长什么样子。

2.1 测试案例

分别在row模式 和 statement模式 下执行以下语句。

创建一张表

        create table if not exists `tt` (
          `id` int(10) unsigned not null auto_increment,
          `name` varchar(16) not null,
          `sex` enum('m','w') not null default 'm',
          `age` tinyint(3) unsigned not null,
          `classid` char(6) default null,
          primary key (`id`)
         ) engine=innodb default charset=utf8;

向表中insert 5笔数据

 insert into zyyshop.tt(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');

用一条sql语句修改前两笔数据

update tt set name ='xxx' where id in (1,2);

在row模式下形成的binlog文件为：mysql3306_bin.000011

在statement模式下生成的binlog文件为：mysql3306_bin.000012。

2.2 用 show binlog events in 命令去查看分析2各个文件

查询row模式记录如下：

 查询statement模式记录如下：

 通过这个命令查看log，两者差距不大。

2.3 通过mysqlbinlog命令解析

执行的命令分别如下

指定路径/bin/mysqlbinlog  --no-defaults --base64-output=decode-rows -v 指定路径/mysql_log/mysql3306_bin.000011

指定路径/bin/mysqlbinlog  --no-defaults --base64-output=decode-rows -v 指定路径/mysql_log/mysql3306_bin.000012

我们可以看到row模式下的binlog记录丰富的多，例如针对update的那条语句。

row模式记录如下：

statement模式记录如下：

 

 总结:通过 show binlog events in 命令，查看 row模式下记录 和 statement模式下的记录，两者基本一致。通过 mysqlbinlog 可以查看binlog具体的信息。row模式下的binlog记录比 statement模式下丰富的多。

3. 通过 mysqlbinlog 和 grep 命令定位binlog文件中指定操作

既然binlog文件中有详细的操作信息，如果有人误操作，我们是否可以快速定位到对应操作信息呢？

快速定位可以帮助我们找到当时具体的操作是什么，也可以找到pos（position）点，方便精准恢复。

例如，书接上回，我们发现表 tt 不在了，被人删除了。error 1146 (42s02): table 'testbinlog2.tt' doesn't exist。

那么我们就可以在binlog查找drop相关的操作，命令如下：

指定路径/bin/mysqlbinlog  --no-defaults --base64-output=decode-rows -v 指定路径/mysql_log/mysql3306_bin.000012 | grep drop

可惜没有数据，这是什么情况呢？不应该啊！！！

会不会大小写的问题？那么命令修改如下：

 指定路径/bin/mysqlbinlog  --no-defaults --base64-output=decode-rows -v 指定路径/mysql_log/mysql3306_bin.000012 | grep -i drop

找到了，但是信息不是很完整，我们可不可以找到，这条命令的更信息信息呢？例如，drop 前后各10条数据。

指定路径/bin/mysqlbinlog  --no-defaults --base64-output=decode-rows -v 指定路径/mysql_log/mysql3306_bin.000012 | grep -i  -a 10 -b 10  drop

这正是我们想要的，完美！

4. 其它知识补充

4.1 mysqlbinlog工具

此处主要讲解用于查看binglog日志的部分参数，用于还原binlog的参数在此不细讲。

mysqlbinlog工具参数说明【用于查看的部分】
参数	说明
-base64-output	inlog输出语句的base64解码 分为三类： 默认是值auto ,仅打印base64编码的需要的信息，如row-based 事件和事件的描述信息。 never 仅适用于不是row-based的事件 decode-rows 配合--verbose选项一起使用解码行事件到带注释的伪sql语句
--verbose	重新构建伪sql语句的行信息输出， -v会增加列类型的注释信息。
--database=name	列出数据库的名称(仅限binlog文件存储在本地)

 

4.2 grep 命令

grep是一个强大的文本搜索工具命令，用于查找文件中符合指定格式的字符串，支持正则表达式。

grep命令常用参数说明
参数	说明
-a	除了显示符合条件的那一行之外，并显示该列之后的指定行的内容内容。
-b	除了显示符合条件的那一行之外，并显示该列之前的指定行的内容内容。
-c	计算符合结果的行数。
-i	忽略字符大小写
-v	反向查找
-e	按指定字符串查找
-e	按指定字符串指定的正则查找
-n	在显示符合条件的那一行前，标识出该行的行数标号。