![1c9edd2b7044e8e1150d65fe975d1ebc.png](https://img-blog.csdnimg.cn/img_convert/1c9edd2b7044e8e1150d65fe975d1ebc.png)
写在前面
目前后端的业务数据都是存放在 mysql 数据库,数据平台使用 sqoop 定时脚本每天凌晨全量同步后端的所有库。全量同步可以解决 sqoop 无法同步 update、delete 修改表数据的问题,全量同步在业务发展初期确实能初步解决同步问题,但是随着数据表越来越多、数据量越来越大,全量同步的问题日渐明显:性能消耗多、同步耗时长、实时性满足不了数据分析需求。实时同步已经推上日程,经过调研,mysql binlog 是实现实时同步的入口。下面是从实时同步方案整理出来关于 binlog 的笔记。下文所有操作均基于 mysql 5.7。
什么是 binlog?binlog 有什么作用?
顾名思义,binlog 是一种二进制日志文件,用于记录表数据的各种操作行为(即 DDL 和 DML 语句)。我们无法直接查看文件内容,需要通过一些方法手段才能获取日志的内容信息。既然通过 binlog 我们就可以知道数据库里头所有发生过的操作,我们就可以按 binlog 的操作行为,依次作用在另外一个数据库,即可复制出两个一模一样的数据库。mysql的主从同步、增量备份正是基于这个原理实现的。
binlog 初体验
不管你是 mac 用户还是 windows 用户,强烈安利你使用 docker 安装 mysql。docker 上安装 mysql 非常简单,只是一条命令的事;而且你还可以同时安装多个版本的 mysql(如果你有这种需求,捂脸.jpg);用完即走,删除 mysql 也只是一条命令的事,而且不留任何垃圾文件。嗯,docker 用起来真香~
############################################
mysql 版本和 binlog 版本
![ec54b75fe3289b68163ea2dd01de8d9f.png](https://img-blog.csdnimg.cn/img_convert/ec54b75fe3289b68163ea2dd01de8d9f.png)
binlog 版本区别请查阅官方说明:传送门
常用的Binlog操作命令
-- 是否启用binlog日志
Binlog 的日志格式分类
STATEMENT
简介:基于SQL语句的复制(statement-based replication, SBR),每一条会修改数据的sql语句会记录到binlog中。
优点:不需要记录每一条SQL语句与每行的数据变化,这样子binlog的日志也会比较少,减少了磁盘IO,提高性能。
缺点:在某些情况下会导致master-slave中的数据不一致(如sleep()函数, last_insert_id(),以及user-defined functions(udf)等会出现问题)
ROW
简介:基于行的复制(row-based replication, RBR):不记录每一条SQL语句的上下文信息,仅需记录哪条数据被修改了,修改成了什么样子了。
优点:不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
缺点:会产生大量的日志,尤其是alter table的时候会让日志暴涨。
MIXED
简介:混合模式复制(mixed-based replication, MBR):以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。
官方对 Binlog Formats 说明:传送门
binlog 事件
管理事件
START_EVENT_V3
FORMAT_DESCRIPTION_EVENT
STOP_EVENT
ROTATE_EVENT
SLAVE_EVENT
INCIDENT_EVENT
HEARTBEAT_EVENT
Statement 事件
QUERY_EVENT
INTVAR_EVENT
RAND_EVENT
USER_VAR_EVENT
XID_EVENT
Row 事件(重点关注)
TABLE_MAP_EVENT
定义:表结构发生改变时将会触发 TABLE_MAP_EVENT 事件,事件内容包括:库名、表名、字段类型、元数据等等,但注意不包括有字段名称。
![d2efff73bd03f8981498c58227b0b912.png](https://img-blog.csdnimg.cn/img_convert/d2efff73bd03f8981498c58227b0b912.png)
MySQL 5.1.0 to 5.1.15
DELETE_ROWS_EVENTv0
UPDATE_ROWS_EVENTv0
WRITE_ROWS_EVENTv0
MySQL 5.1.15 to 5.6.x
DELETE_ROWS_EVENTv1
UPDATE_ROWS_EVENTv1
WRITE_ROWS_EVENTv1
MySQL 5.6.x
DELETE_ROWS_EVENTv2
UPDATE_ROWS_EVENTv2
WRITE_ROWS_EVENTv2
触发命令
![29af5e7a5357e050868f8435cb811803.png](https://img-blog.csdnimg.cn/img_convert/29af5e7a5357e050868f8435cb811803.png)
LOAD
LOAD_EVENT
CREATE_FILE_EVENT
APPEND_BLOCK_EVENT
EXEC_LOAD_EVENT
DELETE_FILE_EVENT
NEW_LOAD_EVENT
BEGIN_LOAD_QUERY_EVENT
EXECUTE_LOAD_QUERY_EVENT
Binlog 文件以及扩展
- 二进制日志索引文件(文件名后缀为.index)用于记录所有有效的的二进制文件
- 二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML语句事件
binlog是一个二进制文件集合,每个binlog文件以一个4字节的魔数开头,接着是一组Events: 魔数:0xfe62696e对应的是0xfebin; Event:每个Event包含header和data两个部分;header提供了Event的创建时间,哪个服务器等信息,data部分提供的是针对该Event的具体信息,如具体数据的修改; 第一个Event用于描述binlog文件的格式版本,这个格式就是event写入binlog文件的格式; 其余的Event按照第一个Event的格式版本写入; 最后一个Event用于说明下一个binlog文件; binlog的索引文件是一个文本文件,其中内容为当前的binlog文件列表
SQL数据类型和MySQL内部类型的对应关系如下图所示
![4d5fca4af51b7424a94454eb3e5a8035.png](https://img-blog.csdnimg.cn/img_convert/4d5fca4af51b7424a94454eb3e5a8035.png)
binlog 解析库(Java)
Github主页
<!-- https://mvnrepository.com/artifact/com.github.shyiko/mysql-binlog-connector-java -->
解析本地 binlog
import
连接到 mysql 读取 binlog
BinaryLogClient
附录
my.cnf 中 binlog 相关配置
![da824b93ac0aeeb53d283cb68765dad1.png](https://img-blog.csdnimg.cn/img_convert/da824b93ac0aeeb53d283cb68765dad1.png)
参考: https:// dev.mysql.com/doc/refma n/5.7/en/show-binlog-events.html https:// baijiahao.baidu.com/s? id=1613946893411582653&wfr=spider&for=pc https:// dev.mysql.com/doc/inter nals/en/table-map-event.html https:// dev.mysql.com/doc/inter nals/en/binlog-event.html