mysqlbinlog 工具分析binlog日志

阳光穿透心脏的1/2处 2021-06-10 20:39 547阅读 0赞

MySQL的binlog 日志对于生产环境非常有用，任何时间对数据库的修改都会记录在binglog中；当数据发生增删改，创建数据库对象都会记录到binlog中，数据库的复制也是基于binlog进行同步数据；

和SQL SERVER 数据库开启完整模式的原理一样，每一次的数据的变动都会记录在案；（对数据库的select,show这些操作不会记录在binlog）

下面介绍mysqlbinlog工具查看 二进制日志内容；

检查工作：

1，查看是否开启binlog

show variables like 'log_bin';

[![image][]][image 1]

ON 代表开启；OFF 代表没有开启；

如果没开启检查my.cnf文件，我的binlog位置如下：

[![image][image 2]][image_image 2]

增加 log\_bin = /mysql/log//mysql-bin

2，查看binlog

show binary logs

[![image][image 3]][image_image 3]

可见，binlog已经开启了；

**示例：**

01- 创建数据库db;

create database db default character set utf8mb4;

02- 创建表t1

use db;
    create table `t1` (
      `id` int(11) default null,
      `name` varchar(20) default null
    ) engine=innodb default charset=utf8mb4;

03- 插入数据

insert into t1(id,name) select 101,'tome101';
    insert into t1(id,name) select 102,'tome102';
    insert into t1(id,name) select 103,'tome103';
    insert into t1(id,name) select 104,'tome104';
    insert into t1(id,name) select 105,'tome105';
    insert into t1(id,name) select 106,'tome106';
    insert into t1(id,name) select 107,'tome107';
    insert into t1(id,name) select 108,'tome108';

04- 更新数据

update t1 set name='jack101' where id=101;
    update t1 set name='jack103' where id=103;
    update t1 set name='jack105' where id=105;

05- 删除数据

delete from t1 where id=102;
    delete from t1 where id=104;
    delete from t1 where id=106;

查看当前mysqlbinlog位置

which mysqlbinlog

[![image][image 4]][image_image 4]

我是二进制自定义安装的mysql 路径不是默认位置；

完整路径为/mysql/app/mariadb/bin/mysqlbinlog

找到binlog位置,我的binlog位置如下：

[![image][image 5]][image_image 5]

把最后一个 binlog : 拷贝到 /tmp 目录下：

[![image][image 6]][image_image 6]

查看binlog 日志内容：

/mysql/app/mariadb/bin/mysqlbinlog --no-defaults --database=db  --start-datetime='2019-04-11 00:00:00' --stop-datetime='2019-04-11 15:00:00'  mysql-bin.000007 | more

[![image][image 7]][image_image 7]

发现BINLOG 处的内容我们看不明白。。。^\_^

增加 --base64-output=decode-rows –v 选项解析

（base64-output，可以控制输出语句输出base64编码的BINLOG语句;decode-rows：选项将把基于行的事件解码成一个SQL语句）

/mysql/app/mariadb/bin/mysqlbinlog --no-defaults --database=db  --base64-output=decode-rows -v --start-datetime='2019-04-11 00:00:00' --stop-datetime='2019-04-11 15:00:00'  mysql-bin.000007 | more

[![image][image 8]][image_image 8]

变成人类可以读懂的SQL语句了；

PS: 可以把binlog解析后的内容放到一个文件后，在分析，利用如下脚本

/mysql/app/mariadb/bin/mysqlbinlog --no-defaults --database=db  --base64-output=decode-rows -v --start-datetime='2019-04-11 00:00:00' --stop-datetime='2019-04-11 15:00:00'  mysql-bin.000007 >/tmp/binlog007.sql

也可以根据精确位置 -- start-position   --stop-position 进行解析；

[image]: /images/20210527/055d088235844ae69068895b88f30946.png
[image 1]: https://img2018.cnblogs.com/blog/642494/201904/642494-20190411142726811-1521800727.png
[image 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjQyNDk0LzIwMTkwNC82NDI0OTQtMjAxOTA0MTExNDI3Mjc5NzktNzcyMzgzNjI1LnBuZw?x-oss-process=image/format,png
[image_image 2]: /images/20210527/1eee0491514b4ad390ab4497608b49b7.png
[image 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjQyNDk0LzIwMTkwNC82NDI0OTQtMjAxOTA0MTExNDI3Mjg5MTYtNDE1ODUzODk2LnBuZw?x-oss-process=image/format,png
[image_image 3]: /images/20210527/04284e273578463bae8da757b2c4c3a4.png
[image 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjQyNDk0LzIwMTkwNC82NDI0OTQtMjAxOTA0MTExNTA4MzE5MjgtNTUzNzE2MDYucG5n?x-oss-process=image/format,png
[image_image 4]: /images/20210527/c22d56d41f2f400997d4dc95e5c64900.png
[image 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjQyNDk0LzIwMTkwNC82NDI0OTQtMjAxOTA0MTExNTA4MzMzODUtMTkwMjczNzA4Ni5wbmc?x-oss-process=image/format,png
[image_image 5]: /images/20210527/77aa3d174c284f8fabada3c18ef6a533.png
[image 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjQyNDk0LzIwMTkwNC82NDI0OTQtMjAxOTA0MTExNTA4MzQxNzYtMTUzMTQ4NDU1LnBuZw?x-oss-process=image/format,png
[image_image 6]: /images/20210527/097037dc3b9c42fd8f65887fb03f7f87.png
[image 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjQyNDk0LzIwMTkwNC82NDI0OTQtMjAxOTA0MTExNTA4MzYxMjQtMTYwMDQ0NjgzNy5wbmc?x-oss-process=image/format,png
[image_image 7]: /images/20210527/9a3b6ce246db41389aa4e40977302d45.png
[image 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjQyNDk0LzIwMTkwNC82NDI0OTQtMjAxOTA0MTExNTA4Mzc3NjItNDY5NzY5NjUwLnBuZw?x-oss-process=image/format,png
[image_image 8]: /images/20210527/eac42565ded24cceb4a793f4dea17f27.png