mysql binlog_checksum_mysql binlog 笔记

写在前面

目前后端的业务数据都是存放在 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 用起来真香~

############################################

# 下列 docker 实例操作基于 mac 平台，windows 平台同理 #

############################################

# 1.1 事前准备(mac上执行)

mkdir -p /tmp/docker-volumn/mysql_binlog

# 1.2 添加 /tmp/docker-volumn/mysql_binlog/my.cnf 配置文件，配置一下内容

[mysqld]

user=mysql

character-set-server=utf8

default_authentication_plugin=mysql_native_password

max_connections=3000

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin=/usr/local/var/mysql/logs/mysql.binlog

expire-logs-days=14

max-binlog-size=500M

server-id=1

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

# my.cnf binlog 相关参数说明:

# log-bin=/usr/local/var/mysql/logs/mysql.binlog #是否启用记录二进制日志，或指定一个日志路径

# expire-logs-days=14 #binlog 日志超出时限自动删除

# max-binlog-size=500M #binlog 文件大小上限

# 更多 binlog 的配置请参阅 附录 -> my.cnf 中 binlog 相关配置

# 2.1下载 mysql:5.7 镜像

docker pull mysql:5.7

# 2.2 创建 mysql:5.7 容器

docker run \-p 3308:3306 \-d \-v=/tmp/docker-volumn/mysql_binlog/my.cnf:/etc/my.cnf \-v=/tmp/docker-volumn/mysql_binlog/db:/var/lib/mysql \-v=/tmp/docker-volumn/mysql_binlog/binlog:/usr/local/var/mysql/logs \--privileged=true \--name mysql_binlog \-e MYSQL_USER="binlog" \-e MYSQL_PASSWORD="123456" \-e MYSQL_ROOT_PASSWORD="123456" \mysql:5.7

# docker run 参数说明:

# -d docker容器后台运行

# -e MYSQL_USER="binlog"：添加binlog用户

# -e MYSQL_PASSWORD="123456"：设置普通用户密码

# -e MYSQL_ROOT_PASSWORD=”123456”：设置root的密码

# –character-set-server=utf8：设置字符集为utf8

# –collation-server=utf8_general_ci：设置字符比较规则为utf8_general_ci

# –restart always：开机启动

# –privileged=true：提升容器内权限

# -v=/mysqltest/config/my.cnf:/etc/my.cnf：映射配置文件

# -v=/mysqltest/data:/var/lib/mysql：映射数据目录

# 3.1 进入容器

docker exec -it zx-mysql bash

# 3.2 进入 mysql 命令行(密码123456)

mysql -uroot -p

# 3.3 允许远程访问

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

FLUSH PRIVILEGES;

# 3.4 创建临时数据库

create database binlog_db;

# 4.1 创建表

create table binlog_db.users

(

id int auto_increment,

name varchar(30) null,

age int null,

constraint users_pk primary key (id)

);

# 4.2 造数产生 binlog

insert into binlog_db.users(name,age) values ('ben', 28);

update binlog_db.users set age=25 where name='ben';

delete from binlog_db.users where name='ben';

# 5.0 binlog 初体验 - 查看 binlog 文件列表(更多 binlog 操作请见“常用的Binlog操作命令”章节)

mysql> show binlog events;

+--------------+-----+----------------+-----------+-------------+---------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+--------------+-----+----------------+-----------+-------------+---------------------------------------+

| mysql.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 |

| mysql.000001 | 123 | Previous_gtids | 1 | 154 | |

| mysql.000001 | 154 | Stop | 1 | 177 | |

+--------------+-----+----------------+-----------+-------------+---------------------------------------+

mysql 版本和 binlog 版本

binlog 版本区别请查阅官方说明：传送门

常用的Binlog操作命令

-- 是否启用binlog日志mysql> show variables like 'log_bin';

+---------------+-------+| Variable_name | Value |

+---------------+-------+| log_bin | ON |

+---------------+-------+1 row in set (0.01 sec)

-- 查看详细的日志配置信息mysql> show global variables like '%log%';

+--------------------------------------------+---------------------------------------------+| Variable_name | Value |

+--------------------------------------------+---------------------------------------------+| back_log | 650 |

| binlog_cache_size | 32768 |

| binlog_checksum | CRC32 |

| binlog_direct_non_transactional_updates | OFF |

| binlog_error_action | ABORT_SERVER |

# mysql数据存储目录

mysql> show variables like '%dir%';

+-----------------------------------------+----------------------------+| Variable_name | Value |

+-----------------------------------------+----------------------------+| basedir | /usr/ |

| binlog_direct_non_transactional_updates | OFF |

| character_sets_dir | /usr/share/mysql/charsets/ |

| datadir | /var/lib/mysql/ |

| ignore_db_dirs | |

| innodb_data_home_dir | |

| innodb_log_group_home_dir | ./ |

| innodb_max_dirty_pages_pct | 75.000000 |

| innodb_max_dirty_pages_pct_lwm | 0.000000 |

| innodb_tmpdir | |

| innodb_undo_directory | ./ |

| lc_messages_dir | /usr/share/mysql/ |

| plugin_dir | /usr/lib/mysql/plugin/ |

| slave_load_tmpdir | /tmp |

| tmpdir | /tmp |

+-----------------------------------------+----------------------------+15 rows in set (0.00 sec)

# 查看binlog的目录

mysql> show global variables like "%log_bin%";

+---------------------------------+---------------------------------------+| Variable_name | Value |

+---------------------------------+---------------------------------------+| log_bin | ON |

| log_bin_basename | /usr/local/var/mysql/logs/mysql |

| log_bin_index | /usr/local/var/mysql/logs/mysql.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

+---------------------------------+---------------------------------------+5 rows in set (0.00 sec)

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

mysql> show binary logs;

+--------------+-----------+| Log_name | File_size |

+--------------+-----------+| mysql.000001 | 177 |

| mysql.000002 | 3051710 |

| mysql.000003 | 1495 |

+--------------+-----------+3 rows in set (0.00 sec)

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

mysql> show master status;

+--------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+--------------+----------+--------------+------------------+-------------------+| mysql.000003 | 1495 | | | |

+--------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

# 事件查询命令

# 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';

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 事件，事件内容包括：库名、表名、字段类型、元数据等等，但注意不包括有字段名称。

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

触发命令

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内部类型的对应关系如下图所示

binlog 解析库(Java)

com.github.shyiko

mysql-binlog-connector-java

0.13.0

解析本地 binlog

import java.io.File;

import java.io.IOException;

public class BinlogUtil {

public static void main(String[] args) throws IOException {

String filePath = "/tmp/mysql-bin.000001";

File binlogFile = new File(filePath);

EventDeserializer eventDeserializer = new EventDeserializer();

eventDeserializer.setChecksumType(ChecksumType.CRC32);

BinaryLogFileReader reader = new BinaryLogFileReader(binlogFile, eventDeserializer);

for (Event event; (event = reader.readEvent()) != null; ) {

System.out.println(event.toString());

}

reader.close();

}

}

连接到 mysql 读取 binlog

BinaryLogClient client = new BinaryLogClient("hostname", 3306, "username", "password");

client.registerEventListener(new BinaryLogClient.EventListener() {

@Override

public void onEvent(Event event) {

}

});

client.connect();

附录

my.cnf 中 binlog 相关配置