binlog基本概念
binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息（DDL，DCL，DML），不记录查询语句（select、show这类的操作）。
binlog特点
- 1.binlog是在MySQL的server层实现的，是所有存储引擎共用的；
- 2.binlog是逻辑日志，，记录的是一条SQL语句的原始逻辑；
- 3.binlog是追加写入的，不会覆盖之前的日志。
binlog使用场景
- 1.主从复制：详见主从复制篇；
- 2.数据恢复：除了redolog在数据库崩溃后恢复时会用xid向binlog确认事务完整性以外，还有通过mysqlbinlog工具来进行数据恢复的场景。经常使用的第三方工具reverse_sql也是通过binlog解析出回滚用的反向SQL。
binlog记录内容和模式
SQL语句记录内容
- DDL：原封不动的记录当前DDL（statement语句方式）
- DCL：原封不动的记录当前DCL（statement语句方式）
- DML：只记录已经提交的事务DML（insert update delete）
DML记录模式
记录模式由binlog_format(binlog的记录格式)参数影响
statement(5.6默认)SBR(statement based replication)：语句模式原封不动地记录当前DML
ROW(5.7默认)RBR(ROW based replication)：记录实际开机的变化，用户看不懂，需要工具分析
- mixed（混合）MBR(mixed based replication)：混合模式，一般情况用statement模式，对于statement无法复制的用ROW模式，一般不用混合模式。
- statement：优点是可读性较高，日志量少，减少io。但缺点是不够严谨，记录仅是每条修改数据的SQL（如果是批量修改，则记录的不是单条SQL，而是批量修改SQL语句的事件），在使用一些函数时会导致主从不一致。
- ROW：缺点是可读性很低，日志量大，但足够严禁，记录的是行变化。生产环境我们希望数据最大化的一致，因此建议使用ROW模式。
row模式优点是能清楚记录每一行数据修改细节，能完全实现(包括存储过程及触发器)主从数据同步和数据恢复。我们很多基于binlog的工具都要求必须采用ROW模式。
binlog写入流程
binlog文件结构
主要是log event，不同的修改操作对应不同的log event。
比较常见的log event有：query event，ROW event，XID event等等。
文件结构参考下图：
binlog落盘策略
binlog写入顺序：binlog cache(write) → OS cache → fsync()写入磁盘
binlog刷数据到磁盘是由一个参数控制的：sync_binlog
show variables like '%sync_binlog%';
- sync_binlog = 0：每次提交事务都只write，不fsync，不建议设置为0，丢失的数据不可控。
- sync_binlog = 1：每次提交事务都fsync，生产环境若要保证最高安全性则设为1。
- sync_binlog = n(n＞1)：每次提交事务都write，但积累n个事务才fsync到磁盘，一般设为100~1000中某个值，在崩溃时最多丢失n个事务，丢失量可控。
binlog写入流程描述
参考下图：
1.根据操作和记录模式触发event事件，生成log event；
2.事务执行过程中，先把日志写到binlog cache，每个事务都会被分配一个binlog cache内存空间。事务提交的时候，再把日志写到binlog文件当中。binlog文件是只有一份的，共用的。
3.事务提交，执行器会把binlog cache里的完整事务写入binlog文件，然后执行器会清空binlog cache。
配置和查看binlog
启用binlog
向配置文件my.cnf增加几个基本参数：
binlog_format=row
log_bin=/binlog/mysqlbin
log_bin_index=/binlog/mysql-bin.index
重启后生效。(8.0默认开启，且保存在数据目录下)
检查是否生效：
show variables like '%log_bin%';
这里补充常用的binlog和复制参数：
# 二进制日志文件的缓存大小
binlog_cache_size = 131072
# 验证binlog的完整性，默认CRC32，此处禁用校验
binlog_checksum=NONE
# 二进制日志文件过期时间，两参数配置一个即可
binlog_expire_logs_seconds=172800
expire_logs_days = 3
# 记录每一行数据被修改的形式
binlog_format=row
# 二进制日志记录的行数据格式
# binlog_row_image='minimal'
# 开启记录详细的行操作信息
binlog_rows_query_log_events =1
# 开启记录详细的行操作信息
enforce_gtid_consistency=ON
# 启用GTID
gtid_mode=ON
# 启用binlog的开关，并指定binlog名称。
log_bin=/binlog/mysqlbin
# 指定二进制日志的索引文件名称。
log_bin_index=/binlog/mysql-bin.index
# 从服务器上记录二进制日志的方式，记录所有从服务器的更新操作到主服务器，默认关闭
log_slave_updates=OFF
# 二进制日志中主服务器的相关信息存储位置，将主服务器的二进制日志信息存储在MySQL的数据表中。
master_info_repository=TABLE
# 只读，一般从节点会配置，MGR不用主动配置
read_only=1
# 设定从服务器的中继日志中的相关信息存储位置，将中继日志信息存储在MySQL的数据表中
relay_log_info_repository=TABLE
# 部分复制的参数
replicate-do-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
replicate-ignore-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
# 实例启动时不自动启动复制线程
skip_slave_start
# 指定从服务器在进行并行复制时的行为，使用逻辑时钟进行并行复制。这意味着从服务器会根据主服务器上的时间戳来执行复制操作，而不是物理时间。
slave_paralle_type=LOGICAL_CLOCK
# 从服务器在进行并行复制时使用的线程数
slave_paralle_workers=4
# 主从复制过程中从服务器可以自动跳过的错误号
slave_skip_errors = 1032,1062,1053,1146
# 每个事务提交时将二进制日志同步到磁盘
sync_binlog=1
使用SQL命令查看binlog
查看binlog文件列表：
show binary logs;
查看正在写入的binlog
show master status;
查看binlog文件信息
show binlog events;
其中
Log_name是当前事件所在binlog名称，
Pos是当前事件起始位置，即起始position号，
Event_type是事件类型，如Query、Table_map、Update_rows、Xid等等…
Server_id，
End_log_pos是当前事件结束位置，用End_log_pos减去Pos则是占用字节数，
Info是具体信息。
查看指定文件内容
show binlog events in 'mysqlbinlog.000001'\G
或使用mysqlbinlog命令来查看，专门用于查看binlog的工具。一般需要导出到文件。
mysqlbinlog mysqlbinlog.000001
mysqlbinlog mysql-bin.000002 >/tmp/a.sql
binlog与redolog
1.redolog是innodb特有的。而binlog是MySQL server层实现的，因此所有引擎都能使用。
2.redolog是物理日志，记录的是xxx表空间xx页在xxx偏移量做了什么修改，值是多少，记录内容相对较少。而binlog是逻辑日志，记录SQL语句相关信息。
3.redolog是循环写入，空间一定是会被用完的，会产生覆盖，需要write pos和checkpoint搭配使用。binlog是追加写，写到一定大小会切换到下一个日志文件，但不会覆盖之前的，日志是全量保存的。
4.redolog作为服务器异常宕机后事务数据自动恢复时使用，具备crash-safe能力（所有已提交事务在mysql宕机重启后仍然存在，所有没提交的事务数据自动回滚）。binlog可以在主从复制和数据恢复使用，但不具备crash-safe能力。
为什么崩溃恢复不使用binlog？
1.恢复机制：mysql可以根据redo日志中的各种信息，来确定恢复的起点和终点，redo是以哈希表的方式记录的。
2.binlog恢复：binlog主要用于人工恢复数据，redolog是对用户不可读的，是mysql自己使用的，用于保证在数据库崩溃时的事务持久性。当数据库崩溃后，想要恢复未刷盘但已经写入redo log和binlog的数据到内存时，binlog是无法恢复的，虽然binlog拥有全量的数据，但是没有一个标识让innodb判断哪些数据已经写入表（写入磁盘表空间），哪些数据还没写入。
比如，binlog记录了两条日志
- 记录1：给id=2这一行的c字段+1
- 记录2：给id=2这一行的c字段+1
在记录1入表之后，记录2还没有入表时，数据库挂了。重启后，如果只是通过binlog无法判断两条记录哪条写入磁盘了，哪条没写，不管是两条都恢复至内存还是都不恢复，都不对。
但如果有了redolog，只要刷入磁盘的数据都会从redolog抹除掉。数据库重启之后，直接把redolog中的数据都恢复到内存就可以了。
常用的binlog维护操作
日志滚动：
show binary logs; 查看binlog文件
flush logs; 手动触发滚动日志
或在linux命令行下
mysqladmin -uroot -p123 flush-logs
也触发滚动日志
binlog大小限制，默认为1个G，达到此值后会自动滚动
select @@max_binlog_size;
mysqldump -F 也会自动滚动
重启mysql也会自动滚动
日志删除：
谨记：不要使用rm命令删除，因为不是数据库行为，会造成一些异常
①自动删除：
select @@expire_logs_days; 默认是0，单位是天，代表永不删除
阈值设置多少天合适？
一般是设置刚好超过一个全备周期：7+1天，空间够的话生产数据库建议2个全备+1天
②手动删除
purge binary logs to 'mysql-bin.000006';
删除到6为止，实际删1至5
reset master;
全清空，编号也从1开始，此操作主从必宕，需要修复
获取binlog文件列表：
show binary logs;
基于时间点截取，以及查看具体sql内容参考文章：
https://blog.csdn.net/qq_44697035/article/details/102583859
例
mysqlbinlog --base64-output=decode-rows -v --start-datetime="2021-11-15 9:00:00" --stop-datetime="2021-11-15 9:50:00" /binlog/mysqlbin.000095 |more > /tmp/both118.sql
例2
mysqlbinlog --verbose --base64-output=decode-rows -vv --database=your_database_name binlog-file-name
还可以对其进行进一步过滤，比如：
grep -B0 -A27 -w 'DELETE FROM `kdb`.`t_diag_reportinfo`' mydiag.sql >diagreportinfo.sql
B即before，A即after，意思是取目标行前后多少行
在vim中过滤并打印到文件：
: 2, 200 w ./data_copy.csv
