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

栏目: 数据库 · 发布时间: 5年前

内容简介:MySQL的二进制日志binlog可以说是MySQL最重要的日志,它记录了所有的DDL和DML语句(除了数据查询语句select),以事件形式记录,还包含语句所执行的消耗的时间。binlog有三种格式:Statement、Row以及Mixed。分别是:基于SQL语句的复制(statement-based replication,SBR)、 基于行的复制(row-based replication,RBR)和混合模式复制(mixed-based replication,MBR)。为了加深印象,下面我们看看两者

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模式 记录如下:

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

查询 Statement 模式记录如下:

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

通过这个命令查看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模式记录如下:

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

Statement模式记录如下:

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

总结:通过 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

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

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

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

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

这正是我们想要的,完美!

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 在显示符合条件的那一行前,标识出该行的行数标号。

Linux公社的RSS地址https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址: https://www.linuxidc.com/Linux/2019-04/158336.htm


以上所述就是小编给大家介绍的《通过 mysqlbinlog 和 grep 命令定位binlog文件中指定操作》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

区块链:定义未来金融与经济新格局

区块链:定义未来金融与经济新格局

张健 / 机械工业出版社 / 2016-6-18 / 49.00

从构建价值互联网的角度看,区块链的出现意味着从0到1。正因如此,本书章节结构与常见的体例不同,从第0章开始。第0章从文字与货币的起源出发,通过论述人类信息传递和价值传输手段的进步,说明区块链技术诞生的必然性。第1章用深入浅出的语言讲解区块链的本质、运行原理、颠覆性潜力以及区块链技术的现状与未来;第2章宏观讲述了区块链技术带来的新产品和新机遇,包括数字货币、互联网金融、物联网,以及新一代的基础设施;......一起来看看 《区块链:定义未来金融与经济新格局》 这本书的介绍吧!

HTML 压缩/解压工具
HTML 压缩/解压工具

在线压缩/解压 HTML 代码

Markdown 在线编辑器
Markdown 在线编辑器

Markdown 在线编辑器

正则表达式在线测试
正则表达式在线测试

正则表达式在线测试