MySQL关于binlog简介

1.binlog概述

binlog是mysql server层维护的一种二进制日志，其记录了对mysql的更新操作信息，并以事件的形式保存在磁盘中

2.binlog作用

1. 复制：在主从复制中，master的binlog会被传递给slave并解析执行来达到slave-master的数据一致。
2. 数据恢复：可通过mysqlbinlog工具来恢复数据
3. 增量备份：通过备份+binlog的方式可实现mysql恢复到任一时刻的状态

3.binlog工作模式

binlog有三种工作模式

1. row模式

   日志中会记录每一行数据被修改的情况

   优点：能清楚的记录每一行数据修改的细节

   缺点：记录的数据量太大，导致日志文件大

2. statement模式

   每一条修改的sql都会被记录到日志中

   优点：减少了记录的日志量

   缺点：容易出现主从不一致，例如sql中存在获取时间或uuid等函数时会导致主从数据不一致

3. mixed模式

   混合模式，综合了row和statement，由mysql决定哪种情况下使用哪种模式

在mysql中，可通过binlog_format配置日志记录模式

binlog_format=row;

4.开启binlog

编辑mysql配置文件，linux下默认目录为/etc/my.cnf

vim /etc/my.cnf

在配置文件中添加如下配置

#开启binlog记录
log_bin=on 
#binlog文件名
log_bin_basename=/data/mysql/binlog/mysql-bin
#binlog索引文件
log_bin_index=/data/mysql/binlog/mysql-bin.index

5.常用binlog操作

1. 查看所有binlog文件列表

   show master logs;
   

2. 查看master状态，包含最后一个binlog的文件名和最后一个事务在binlog中的偏移位置

   show master status;
   

3. 刷新binlog，会生成一个新的binlog文件

   flush logs;
   

   注：当mysql重启时，会自动执行该命令

4. 删除binlog

   #方式一：清空所有binlog
   reset master;
   #方式二：删除编号000005之前的所有binlog
   purge master logs to 'mysql-bin.000005';
   #方式三：删除在指定时间之前的日志文件
   PURGE MASTER LOGS BEFORE '2024-02-27 10:02:30';
   #方式四：设置binlog过期天数，过期了自动删除
   在配置文件中添加expire_logs_day=n，n为保存天数，过期了自动删除
   

6.binlog使用

binlog由包含事件的文件组成，服务器以二进制格式写入这些文件，使用vim或cat查看都会乱码，需要使用mysqlbinlog程序

6.1mysqlbinlog介绍

mysqlbinlog是mysql提供的专门处理binlog文件的工具，利用它可以查看binlog，通过指定条件进行恢复等操作。

常用参数：

–start-datetime 分析的开始时间点

–stop-datetime 分析的结束时间点

–start-position 分析的开始偏移位置

–stop-position 分析的结束偏移位置

实例：

查看binlog文件的指定时间段的内容

mysqlbinlog --start-datetime="2024-02-27 10:00:00" --stop-datetime="2024-02-27 10:10:00" binlog.000003

6.2查看binlog的事件

通过以下指令查看指定binlog的事件

SHOW BINLOG EVENTS IN 'binlog.000003';

结果以表的形式展示：

包括了binlog文件中每个事件的开始结束位置的偏移量，事件类型等信息

可以根据该信息获取到指定事件的偏移量，再开始恢复

6.3恢复数据

mysqlbinlog能够将记录的binlog事件再次执行，利用数据备份+binlog可以将数据库还原到任意时刻（需要存在该时刻之前的备份以及备份时刻到目标时刻之间的binlog，如果binlog过期被清理了，那就无法还原了）

mysqlbinlog --start-position=1278 --stop-position=1621 binlog.000003 | mysql -uroot -p123456

7.binlog有关配置项

binlog_cache_size

设置binlog event的缓冲区大小，默认32KB

sync_binlog

该配置项用于控制binlog刷盘策略

sync_binlog=0：默认，表示mysql不控制binlog刷盘，由文件系统决定。性能最好，但mysql宕机会丢失binlog cache中的数据

sync_binlog=n>0：代表每n次事务提交，就会将binlog刷盘。最安全为n=1，表示每次事务提交都会将binlog刷盘。

binlog_checksum

默认CRC32算法，写binlog时，会为每个event计算并写入一个值，用于从库校验接收到的binlog是否正确

binlog_expire_logs_seconds

binlog过期时间，默认30天，如果与expire_logs_day都进行设置，会采用binlog_expire_logs_seconds

log_bin_trust_function_creators

是否允许不确定的函数写入，默认关闭，因为主从同步有一定的延迟，对于调用如now或uuid等不确定函数的sql，则会导致主从数据不一致

max_binlog_size

单个binlog大小，最大和默认都是1GB，在遇到大的事务时，为了保证事务完整性，可能即使超过文件大小阈值也会将该事务记录完整