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多少天过期
