mysqlbinlog 使用详解（附应用实例 恢复数据 日常维护）

 

Mysql binlog使用详解

一、mysql常见日志。

可根据具体需求，对不同的日志进行分析。

日志类型	作用
错误日志	记录在启动，运行或停止mysqld时遇到的问题
通用查询日志	记录建立的客户端连接和执行的语句
二进制日志	记录更改数据的语句
中继日志	从复制主服务器接收的数据更改
慢查询日志	记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询
DDL日志（元数据日志）	元数据操作由DDL语句执行

二、binlog的作用及应用场景。

1、作用：

MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

2、应用场景：

（1）MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的

（2）数据恢复：通过使用 mysqlbinlog工具来使恢复数据。

 

三、binlog三种模式及特点。

binlog主要包括statement、row、mixed三种模式。

1、STATMENT模式（保存为sql语句）

基于SQL语句的复制，每一条会修改数据的sql语句会记录到binlog中。

优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。

缺点：在某些情况下会导致master-slave（主从复制模式）中的数据不一致(如sleep(暂停指定时间执行)函数， last_insert_id（自增）等情况下会出现问题)

2、ROW模式（保存每行数据的变化记录）

基于行的复制，不记录每一条SQL语句的上下文信息，仅记录哪条数据被修改了，修改后的结果是什么。

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。

缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。

3、MIXED模式（自动判断，混合使用）

混合模式的复制方式：如上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的相关操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。也有可能发生主从不一致的情况。

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，默认值是 ROW。日志格式通过 binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

 

4、使用建议

在磁盘和网络允许的情况下，可以使用row模式，毕竟他准确率比较高。如果使用1和3，有可能出现不一致的情况。比如

当在master上更新一条从库不存在的记录时，也就是id=2的记录，你会发现master是可以执行成功的。而slave拿到这个SQL后，也会照常执行，不报任何异常，只是更新操作不影响行数而已。并且你执行命令show slave status，查看输出，你会发现没有异常。但是，如果你是row模式，由于这行根本不存在，是会报1062错误的。

四、binlog常见操作

1、启用binlog。

开启binlog一般会有1%左右的性能损耗。可通过一下两种方式进行开启。

#第一种方式:

#开启binlog日志

log_bin=ON

#binlog日志的基本文件名

log_bin_basename=/var/lib/mysql/mysql-bin

#binlog文件的索引文件，管理所有binlog文件

log_bin_index=/var/lib/mysql/mysql-bin.index

#配置serverid

server-id=1

 

#第二种方式:

#此一行等同于上面log_bin三行

log-bin=/var/lib/mysql/mysql-bin

#配置serverid

server-id=1

 

启用后查看是否成功。执行show variables like '%log_bin%';

看到下图即可。

2、binlog配置项。

[mysqld]

#设置日志三种格式：STATEMENT、ROW、MIXED 。

binlog_format = mixed

#设置日志路径，注意路经需要mysql用户有权限写

log-bin = /data/mysql/logs/mysql-bin.log

#设置binlog清理时间

expire_logs_days = 7

#binlog每个日志文件大小

max_binlog_size = 100m

#binlog缓存大小

binlog_cache_size = 4m

#最大binlog缓存大小

max_binlog_cache_size = 512m

 

修改配置后重启mysql生效。

 

3、常用命令。

是否启用binlog日志

show variables like 'log_bin';

查看详细的日志配置信息

show global variables like '%log%';

mysql数据存储目录

show variables like '%dir%';

查看binlog的目录

show global variables like "%log_bin%";

查看当前服务器使用的binlog文件及大小

show binary logs;

查看主服务器使用的biglog文件及大小

查看最新一个binlog日志文件名称和Position

show master status;

事件查询命令

IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)

FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)

LIMIT [offset,] ：偏移量(不指定就是0)

row_count ：查询总条数(不指定就是所有行)

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

查看 binlog 内容

show binlog events;

查看具体一个binlog文件的内容 （in 后面为binlog的文件名）

show binlog events in 'master.000003';

设置binlog文件保存事件，过期删除，单位天

set global expire_log_days=3;

删除当前的binlog文件

reset master;

删除slave的中继日志

reset slave;

删除指定日期前的日志索引中binlog日志文件

purge master logs before '2019-03-09 14:00:00';

删除指定日志文件

purge master logs to 'master.000003';

 

4、binlog生成时机。

对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。

在MySQL 5.7.7之前，默认值 sync_binlog 是0，MySQL 5.7.7和更高版本使用默认值1，这是最安全的选择。一般情况下会设置为100或者0，牺牲一定的一致性来获取更好的性能。

参数sync_binlog=[N]表示每写缓冲多次就同步到磁盘，设置为1，表示写入binlog的同时也写入磁盘，这种情况最安全，速度也最慢（mysql5.7后这是默认选项）。缓冲的次数越多，有可能丢的数据越多，但是性能越好。设置为0表示mysql不控制，由文件系统控制缓存的刷新。

 

5、查看分析binlog。

（1）命令行使用 show binlog events。

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

a、命令参数解释：

log_name：可以指定要查看的 binlog 日志文件名，如果不指定的话，表示查看最早的 binlog 文件。

pos：从哪个 pos 点开始查看，凡是 binlog 记录下来的操作都有一个 pos 点，这个其实就是相当于我们可以指定从哪个操作开始查看日志，如果不指定的话，就是从该 binlog 的开头开始查看。

offset：这是是偏移量，不指定默认就是 0。

row_count：查看多少行记录，不指定就是查看所有。

b、举例1：

show binlog events in 'javaboy_logbin.000001'; 下图是row格式的binlog。

c、举例2： binlog event分析

如果binlog格式设置为statement，会保存create、insert、update、delete等事件，结果如下：

如果binlog格式设置为row，会保存create、insert、update、delete等事件，结果如下：

这里如果要看具体的sql语句， 需要用mysqlbinlog 加上-v参数，会将row格式内容解析为sql。

（2）linux命令行使用mysqlbinlog。

a、基本格式：

mysqlbinlog [options] log_file ...

查看bin-log二进制文件（shell方式）：

mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.00000

查看bin-log二进制文件（带查询条件）：

mysqlbinlog -v --base64-output=decode-rows /var/lib/mysql/master.000003 \

--start-datetime="2019-03-01 00:00:00" \

--stop-datetime="2019-03-10 00:00:00" \

--start-position="5000" \

--stop-position="20000"

b、mysqlbinlog远程导出

mysqlbinlog -u username -p password -hl-db1.dba.beta.cn6.qunar.com -P3306 \

--read-from-remote-server --start-datetime='2013-09-10 23:00:00' --stop-datetime='2013-09-10 23:30:00' mysql-bin.000001 > t.binlog

c、举例：截取一段mysqlbinlog 输出的信息如下。

====================================================================

# at 21019

#190308 10:10:09 server id 1 end_log_pos 21094 CRC32 0x7a405abc Query thread_id=113 exec_time=0 error_code=0

SET TIMESTAMP=1552011009/*!*/;

BEGIN

/*!*/;

====================================================================

上面输出包括信息：

• position: 位于文件中的位置，即第一行的（# at 21019）,说明该事件记录从文件第21019个字节开始

• timestamp: 事件发生的时间戳，即第二行的（#190308 10:10:09）

• server id: 服务器标识（1）

• end_log_pos 表示下一个事件开始的位置（即当前事件的结束位置+1）

• thread_id: 执行该事件的线程id （thread_id=113）

• exec_time: 事件执行的花费时间

• error_code: 错误码，0意味着没有发生错误

• type:事件类型Query

c、binlog常见事件类型解释。

事件类型	说明
UNKNOWN_EVENT	此事件从不会被触发，也不会被写入binlog中；发生在当读取binlog时，不能被识别其他任何事件，那被视为UNKNOWN_EVENT
START_EVENT_V3	每个binlog文件开始的时候写入的事件，此事件被用在MySQL3.23 – 4.1，MYSQL5.0以后已经被 FORMAT_DESCRIPTION_EVENT 取代
QUERY_EVENT	执行更新语句时会生成此事件，包括：create，insert，update，delete；
STOP_EVENT	当mysqld停止时生成此事件
ROTATE_EVENT	当mysqld切换到新的binlog文件生成此事件，切换到新的binlog文件可以通过执行flush logs命令或者binlog文件大于 max_binlog_size 参数配置的大小；
INTVAR_EVENT	当sql语句中使用了AUTO_INCREMENT的字段或者LAST_INSERT_ID()函数；此事件没有被用在binlog_format为ROW模式的情况下
LOAD_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL 3.23版本中使用
SLAVE_EVENT	未使用
CREATE_FILE_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0和4.1版本中使用
APPEND_BLOCK_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0版本中使用
EXEC_LOAD_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0和4.1版本中使用
DELETE_FILE_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0版本中使用
NEW_LOAD_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL4.0和4.1版本中使用
RAND_EVENT	执行包含RAND()函数的语句产生此事件，此事件没有被用在binlog_format为ROW模式的情况下
USER_VAR_EVENT	执行包含了用户变量的语句产生此事件，此事件没有被用在binlog_format为ROW模式的情况下
FORMAT_DESCRIPTION_EVENT	描述事件，被写在每个binlog文件的开始位置，用在MySQL5.0以后的版本中，代替了START_EVENT_V3
XID_EVENT	支持XA的存储引擎才有，本地测试的数据库存储引擎是innodb，所有上面出现了XID_EVENT；innodb事务提交产生了QUERY_EVENT的BEGIN声明，QUERY_EVENT以及COMMIT声明，如果是myIsam存储引擎也会有BEGIN和COMMIT声明，只是COMMIT类型不是XID_EVENT
BEGIN_LOAD_QUERY_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL5.0版本中使用
EXECUTE_LOAD_QUERY_EVENT	执行LOAD DATA INFILE 语句时产生此事件，在MySQL5.0版本中使用
TABLE_MAP_EVENT	用在binlog_format为ROW模式下，将表的定义映射到一个数字，在行操作事件之前记录（包括：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT）
PRE_GA_WRITE_ROWS_EVENT	已过期，被 WRITE_ROWS_EVENT 代替
PRE_GA_UPDATE_ROWS_EVENT	已过期，被 UPDATE_ROWS_EVENT 代替
PRE_GA_DELETE_ROWS_EVENT	已过期，被 DELETE_ROWS_EVENT 代替
WRITE_ROWS_EVENT	用在binlog_format为ROW模式下，对应 insert 操作
UPDATE_ROWS_EVENT	用在binlog_format为ROW模式下，对应 update 操作
DELETE_ROWS_EVENT	用在binlog_format为ROW模式下，对应 delete 操作
INCIDENT_EVENT	主服务器发生了不正常的事件，通知从服务器并告知可能会导致数据处于不一致的状态
HEARTBEAT_LOG_EVENT	主服务器告诉从服务器，主服务器还活着，不写入到日志文件中

（3）使用第三方分析工具binlog2sql

官方仓库：https://github.com/danfengcao/binlog2sql

快速上手：

a、安装依赖。

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

pip install -r requirements.txt

b、使用（如果不提供偏移位置或者起始时间，就从头开始显示所有信息）

# 使用偏移位置

python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' --start-file='mysqld-bin.000001' --start-pos=2425 -d aaaa

# 同样功能，使用时间戳

python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' --start-file='mysqld-bin.000001' --start-datetime='2018-08-02 10:00:00' -d aaaa

c、结果示例

USE aaaa;

-- 建表

create table aaa(id int, title varchar(100)); USE aaaa;

-- 插入初始化数据

insert into aaa(id, title) values (1, '测试1');

USE aaaa;

insert into aaa(id, title) values (2, '测试2');

USE aaaa;

insert into aaa(id, title) values (3, '测试3');

USE aaaa;

-- 更新/删除

update aaa set title='修改' where id=2;

USE aaaa;

delete from aaa where id=1;

 

五、应用实例。

1、使用mysqlbinlog查看sql执行历史。

（1）查看mysql是否开启binlog以及binlog文件的存储路径。

show variables like 'log_bin'; 如果没开启就该配置文件进行开启。

（2）查看binlog文件名。

show master status;

（3）进入到当前主机的mysqlbinlog安装目录。

find / -name "mysqlbinlog"

进入上述路径，然后保存binlog为文本，然后下载用文本分析。

./mysqlbinlog ./usr/local/mysql/data/mysql-bin.000007 -v /a.txt

（4）也可以直接在命令行进行分析，举例。

查询时间段内日志的执行内容

mysqlbinlog --start-datetime='2018-01-08 02:01:00' --stop-datetime='2018-01-08 02:30:10' -d test /var/lib/mysql/mysql-bin.000170 -v

查询时间段内日志中执行的删除语句

mysqlbinlog --start-datetime='2018-01-08 02:01:00' --stop-datetime='2018-01-08 02:30:10' -d test /var/lib/mysql/mysql-bin.000170 -v|grep DELETE -A 5

统计时间段内日志中执行的删除语句

mysqlbinlog --start-datetime='2018-01-08 02:01:00' --stop-datetime='2018-01-08 02:30:10' -d test /var/lib/mysql/mysql-bin.000170 -v|grep DELETE |wc -l

-v参数：重新构建伪SQL语句的行信息输出，-v -v会增加列类型的注释信息。如果查看insert语句，直接使用mysqlbinlog过滤，看不到具体insert的值，只能看到insert语句。

（5）将binlog导出为sql（具体使用百度mysqlbinlog 参数）

mysqlbinlog --no-defaults --database=db --base64-output=decode-rows -v --start-datetime='2018-01-08 02:01:00' --stop-datetime='2018-01-08 02:30:10' mysql-bin.000170 /tmp/binlog007.sql

 

2、使用binlog恢复数据库。

最核心的一句：实际是将读出的binlog日志内容，通过管道符传递给mysql命令

mysqlbinlog –no-defaults –stop-datetime='2017-04-11 09:48:48'/data/mysql/mysql-bin.000001 |mysql –uroot –p123456

参考如下链接：

https://www.jb51.net/article/236583.htm

https://www.jb51.net/article/109588.htm

https://www.jb51.net/article/236600.htm

 

3、常见mysqlbinlog工具比对。

canal、maxwell、databus、阿里dts

引用自：https://www.jb51.net/article/208508.htm

Canal

定位：基于数据库增量日志解析，提供增量数据订阅&消费，目前主要支持了mysql。

原理：

• canal模拟mysql slave的交互协议，伪装自己为mysql slave，向mysql master发送dump协议

• mysql master收到dump请求，开始推送binary log给slave(也就是canal)

• canal解析binary log对象(原始为byte流)

 

 

 

整个parser过程大致可分为几步：

• Connection获取上一次解析成功的位置（如果第一次启动，则获取初始制定的位置或者是当前数据库的binlog位点）

• Connection建立连接，发生BINLOG_DUMP命令

• Mysql开始推送Binary Log

• 接收到的Binary Log通过Binlog parser进行协议解析，补充一些特定信息

• 传递给EventSink模块进行数据存储，是一个阻塞操作，直到存储成功

• 存储成功后，定时记录Binary Log位置

 

 

• 数据过滤：支持通配符的过滤模式，表名，字段内容等

• 数据路由/分发：解决1:n (1个parser对应多个store的模式)

• 数据归并：解决n:1 (多个parser对应1个store)

• 数据加工：在进入store之前进行额外的处理，比如join

 

Maxwell

canal 由Java开发，分为服务端和客户端，拥有众多的衍生应用，性能稳定，功能强大；canal 需要自己编写客户端来消费canal解析到的数据。

maxwell相对于canal的优势是使用简单，它直接将数据变更输出为json字符串，不需要再编写客户端。

 

Databus

Databus是一种低延迟变化捕获系统，已成为LinkedIn数据处理管道不可或缺的一部分。Databus解决了可靠捕获，流动和处理主要数据更改的基本要求。Databus提供以下功能：

• 源与消费者之间的隔离

• 保证按顺序和至少一次交付具有高可用性

• 从更改流中的任意时间点开始消耗，包括整个数据的完全引导功能。

• 分区消费

• 源一致性保存

 

阿里云的数据传输服务DTS

数据传输服务（Data Transmission Service，简称DTS）是阿里云提供的一种支持 RDBMS（关系型数据库）、NoSQL、OLAP 等多种数据源之间数据交互的数据流服务。DTS提供了数据迁移、实时数据订阅及数据实时同步等多种数据传输能力，可实现不停服数据迁移、数据异地灾备、异地多活(单元化)、跨境数据同步、实时数据仓库、查询报表分流、缓存更新、异步消息通知等多种业务应用场景，助您构建高安全、可扩展、高可用的数据架构。

优势：数据传输（Data Transmission）服务 DTS 支持 RDBMS、NoSQL、OLAP 等多种数据源间的数据传输。它提供了数据迁移、实时数据订阅及数据实时同步等多种数据传输方式。相对于第三方数据流工具，数据传输服务 DTS 提供更丰富多样、高性能、高安全可靠的传输链路，同时它提供了诸多便利功能，极大得方便了传输链路的创建及管理。

个人理解：就是一个消息队列，会给你推送它包装过的sql对象，可以自己做个服务去解析这些sql对象。

免去部署维护的昂贵使用成本。DTS针对阿里云RDS（在线关系型数据库）、DRDS等产品进行了适配，解决了Binlog日志回收，主备切换、VPC网络切换等场景下的订阅高可用问题。同时，针对RDS进行了针对性的性能优化。出于稳定性、性能及成本的考虑，推荐使用。

https://www.jb51.net/article/208508.htm

 

4、正确清理binlog

（1）手动清理

a、查看主库和从库正在使用哪个binlog。

show master status

show slave status

b、备份binlog，防止误删。

c、执行删除操作（时间和文件名不要写错，不要删除正在使用的binlog）。

purge master logs before'2016-09-01 17:20:00'; //删除指定日期以前的日志索引中binlog日志文件

purge master logs to'mysql-bin.000022'; //删除指定日志文件的日志索引中binlog日志文件

（2）自动清理

设置过期时间，系统自动删除binlog

show variables like 'expire_logs_days';

set global expire_logs_days = 30; #设置binlog多少天过期