Mysql数据库binlog系列一-什么是binlog

发表于 | 评论数： | 阅读次数：

mysql的binlog日志使用二进制的数据格式，以事务的方式存储将要在mysql上执行的变更语句，并且通过磁盘进程持久化存储，通过binlog日志可以做到主从数据库直接的数据复制以及数据库的数据恢复

开启binlog配置

在my.inf文件中添加

1
2
3
log_bin=ON
log_bin_basename=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index

或者

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

然后重新启动

查看binlog配置

1
show variables like '%log_bin%';

字段说明

  • log_bin：binlog文件的开启状态
  • log_bin_basename：binlog文件的文件路径和前缀
  • log_bin_index：binlog文件的索引
  • log_bin_trust_function_creators：是否信任存储过程和函数

    该参数的官方说明(5.7)：This variable applies when binary logging is enabled. It controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log. If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation. This variable also applies to trigger creation. See Section 23.7, “Binary Logging of Stored Programs”.

  • log_bin_use_v1_row_events：控制binlog文件中Rows_log_event的格式，ON使用V1版本格式（5.6.6之前），默认为OFF使用V2版本格式
  • sql_log_bin：mysql里面执行的语句，记录在二进制日志

查看binlog文件列表

1
show binary logs;

查看二进制日志的状态信息

1
show master status;

把二进制日志解析为SQL

1
2
show binlog events in filename from position limit offset,row_count
show binlog events in 'mysql-bin.001177' from 1 limit 2,100;

查看binlog格式

1
show variables like 'binlog_format';

修改binlog格式

  1. 通过my.inf修改
  2. set global binlog_format=’’;

binlog格式分类

ROW

row模式会以每一条数据被修改的格式来记录，不记录上下文信息，只记录单条数据的修改，不会受存储过程、函数、触发器等等影响，导致数据不同步,但是遇到批量的修改操作会造成日志内容变多

Statement

Statement模式记录每次执行的sql以及执行上下文，减少了日志量，但是使用执行相同sql的方式进行数据同步，会收到函数的影响，导致同步失败，某些版本中部分系统函数在同步时也存在bug

MiXED

根据具体语句自动区分使用ROW模式或者Statement模式，例如DDL语句通过Statement模式同步，DML则通过ROW模式进行同步，避免了部分场景下ROW模式或者Statement模式的明显缺点，但是部分插件需要依赖ROW模式进行同步，无法观察到Statement模式下的变更

接触到这个需求是因为在某一个单表远超一千万级的表中，已经按照业务的唯一编号进行了一致性哈希后的存储，然而需要满足用户id为条件进行列表查询的场景时无法根据用户id查询到数据实际的表名，这时候常用有两种解决方案，一是新增存储关系表，查询后二次关联原数据表，二是存储时按照用户id进行全量的数据冗余存储，这两个方案本质上是空间时间互换的问题，然而在实际操作中发现因为热点用户问题明显，无论是存储关联表还是全量冗余都会产生数据分布不均，造成数据量偏少的表资源浪费，而数据量过大的表优化效果不明显的问题，所以打算把mysql的数据同步到ES上，binlog的认知是第一部分，目标是Mysql从库和ES数据的同步，并且会按照根据实际需求进行以天为单位和秒级内的数据同步，各位如果想讨论请私信我