阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

244次阅读
没有评论

共计 3612 个字符,预计需要花费 10 分钟才能阅读完成。

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

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计3612字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中