binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的DDL和DML等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。
它以事件形式记录并保存在二进制文件中。通过这些信息我们可以再现数据更新操作的全过程。
如果想要记录所有语句（例如，为了识别有问题的查询），需要使用通用查询日志。
binlog主要应用场景：
一是用于数据恢复，如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
二是用于数据复制，由于日志的延续性和时效性，master把它的二进制日志传递给slave来达到master-slave数据一致的目的。
可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依赖binlog来同步数据，保证数据一致性。
【1】查看与操作
① 查看默认情况
Windows下：
show VARIABLES like '%log_bin%' log_bin ON log_bin_basename C:\ProgramData\MySQL\MySQL Server 8.0\Data\LAPTOP-GDNPUCQ7-bin log_bin_index C:\ProgramData\MySQL\MySQL Server 8.0\Data\LAPTOP-GDNPUCQ7-bin.index log_bin_trust_function_creators OFF log_bin_use_v1_row_events OFF sql_log_bin ON
Linux下如下所示：
mysql> show VARIABLES like '%log_bin%'; +---------------------------------+-----------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/binlog | | log_bin_index | /var/lib/mysql/binlog.index | | log_bin_trust_function_creators | ON | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-----------------------------+ 6 rows in set (0.01 sec)
log_bin_basename : 是binlog日志的基本文件名，后面会追加标识来表示每一个文件
log_bin_index：是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录
log_bin_trust_function_creators：限制存储过程。前面我们已经讲过了，这是因为二进制日志的一个中药功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启了二进制日志后，需要限制存储函数的创建、修改、调用。
log_bin_use_v1_row_events：此只读系统变量已弃用。ON表示使用版本1二进制日志行，OFF表示使用版本2二进制日志行（MySQL5.6的默认值为2）。
MySQL服务每重启一次就会生成一个新的binlog文件。
② 日志参数设置
① 方式1：永久性方式
修改MySQL的my.cnf 或 my.ini文件可以设置二进制日志的相关参数：
[mysqld] # 启用二进制日志 可自定义文件名也可以加上路径 这个对log_bin_basename log_bin_index有影响 log-bin=mysql-bin binlog_expire_logs_seconds=60000 max_binlog_size=300M
binlog_expire_logs_seconds：此参数控制二进制日志文件保留的时长，单位是秒，默认是2592000,30天。
max_binlog_size：控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换动作。此参数的最大和默认值是1GB，该设置并不能严格控制binlog的大小，尤其是binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，可能 不做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。一般情况下可采取默认值。
数据库文件最好不要与日志文件放在同一个磁盘上，这样当数据库文件所在的磁盘发生故障时，可以使用日志文件恢复数据。
② 方式2：临时性方式
如果不希望通过修改配置文件并重启的方式设置二进制日志的话，还可以使用如下指令，需要注意的是在mysql8中只有会话级别的设置，没有global级别的设置。
mysql> set global sql_log_bin=0; ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can't be used with SET GLOBAL mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.01 sec)
③ 查看日志
当MySQL创建二进制日志文件时，先创建一个以"filename"为名称，以".index"为后缀的文件，再创建一个以"filename"为名称、以".000001"为后缀的文件。
MySQL服务重新启动一次，以".000001"为后缀的文件就会增加一个，并且后缀名称按1递增。即日志文件的个数与MySQL服务启动的次数相同。如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件。
查看当前的二进制日志文件列表及大小：
mysql> show binary logs; +---------------+------------+-----------+ | Log_name | File_size | Encrypted | +---------------+------------+-----------+ | binlog.000006 | 66374693 | No | | binlog.000007 | 8162695 | No | | binlog.000008 | 205361347 | No | | binlog.000009 | 10917409 | No | | binlog.000010 | 1073743498 | No | | binlog.000011 | 469251392 | No | +---------------+------------+-----------+ 6 rows in set (0.21 sec)
所有对数据库的修改都会记录在binlog中。但binlog是二进制文件，无法直接查看，想要更直观的观测它就要借助mysqlbinlog命令工具了。指令如下：
mysqlbinlog "/var/lib/mysql/binlog.000011"
如果遇到如下错误：
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'.
尝试下面两种措施：
在MySQL的配置 my.cnf 中将default-character-set=utf8mb4 修改为 character-set-server = utf8mb4，但是这需要重启MySQL服务
增加加参数 --no-defaults ，mysqlbinlog --no-defaults "/var/lib/mysql/binlog.000011"
执行结果可以看到，这是一个比较简单的日志文件，日志中记录了用户的一些操作，这里并没有出现具体的SQL语句，这是因为binlog关键字后面的内容是经过编码后的二进制日志。
这里面一个update语句包含如下事件：
Query事件，负责开始一个事务（begin）
Table_map事件，负责映射需要的表
Update_rows事件，负责写入数据
Xid事件，负责结束事务
下面命令将行事件以伪SQL的形式表现出来( -v 选项)：
mysqlbinlog --no-defaults -v "/var/lib/mysql/binlog.000011"
前面的命令同时显式binlog格式的语句，使用如下命令可以隐藏binlog格式的语句：
mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog.000011"
关于mysqlbinlog工具的使用技巧还有很多，例如只解析对某个库的操作或者某个时间段内的操作等。下面是几个常用语句，更多操作可以参考官方文档。
# 可查看参数帮助 mysqlbinlog --no-defaults --help #查看最后100行 mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog.000011"|tail -100 #根据position查找 mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog.000011"|grep -A 20 '4939002'
上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：
show binlog events [in 'log_name'] [from pos] [limit [offset,] row_count];
in ‘log_name’ ：指定要查询的binlog文件名（不指定就是第一个binlog文件）
from pos：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
limit [offset]：偏移量（不指定就是0）
row_count：查询总条数（不指定就是所有行）
应用举例：
# 查询第一个最早的binlog日志 show binlog events; # 指定查询日志文件 show binlog events in 'binlog.000011'; #指定pos点 show binlog events in 'binlog.000011' from 2000; # 从pos点开始查询100条 show binlog events in 'binlog.000011' from 2000 limit 100; # 从pos掉开始，偏移2行（即中间跳过2行）查询100条 show binlog events in 'binlog.000011' from 2000 limit 2,100;
上面都是基于binlog的默认格式，我们可以查看binlog的格式：
mysql> show VARIABLES like '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec)
除此之外，binlog还有2种格式，分表是Statement和Mixed。
Statement
每一条会修改数据的SQL都会记录在binlog中。优点是不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
Row
5.1.5版本的MySQL才开始支持row level的复制，它不记录SQL语句上下文相关信息，仅保存哪条记录被修改。优点是row level的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。
Mixed
从5.1.8开始，MySQL提供了Mixed格式，实际上就是Statement与Row的结合。
④ 删除二进制日志
MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法。PURGE MASTER LOGS只删除指定部分的二进制日志文件，RESET MASTER删除所有的二进制日志文件。
① PURGE MASTER LOGS：删除指定日志文件
PURGE MASTER LOGS语法如下：
PURGE {MASTER | BINARY} LOGS TO '指定日志文件名' PURGE {MASTER | BINARY} LOGS BEFORE '指定日期'
举例：
#查看二进制日志文件列表 show binary logs; # 删除创建时间比binlog.000013早的日志 PURGE MASTER LOGS TO "binlog.000013" # 删除20230105前创建的所有日志文件 PURGE MASTER LOGS before "20230105"
② RESET MASTER：删除所有二进制日志文件
使用 RESET MASTER语句，清空所有的binlog日志。MySQL会重新创建二进制文件，新的日志文件扩展名将重新从000001开始编号。
# 删除所有日志文件 RESET MASTER;
⑤ 其他场景
二进制日志可以通过数据库的 全量备份 和二进制日志中保存的增量信息，完成数据库的无损失恢复。但是，如果遇到数据量大、数据库和数据表很多（比如分库分表的应用）的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止位置不容易管理。
在这种情况下，一个有效的解决办法是配置 主从数据库服务器，甚至是一主多从的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常问题。
⑥ binlog 与redo log对比
redo log是物理日志，记录内容是“在某个数据页上做了什么修改”，属于InnoDB存储引擎层产生的。
而binlog是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的C字段加1”，属于MySQL Server层。
虽然它们都属于持久化的保证，但是侧重点不同：
redo log 让InnoDB存储引擎拥有了崩溃恢复能力
binlog保证了MySQL集群架构的数据一致性。
【2】使用日志恢复数据
如果MySQL服务器启用了二进制日志，在数据库出现意外丢失数据时，可以使用mysqlbinlog工具从指定时间点开始（例如，最后一次备份）直到现在或另一个指定的时间点的日志中恢复数据。
mysqlbinlog恢复数据的语法如下：
mysqlbinlog [option] filename|mysql -uuser -ppass;
命令可以这样理解：使用mysqlbinlog命令来读取filename中的内容，然后使用mysql命令将这些内容恢复到数据库中。
filename：是日志文件名
option：可选项，比较重要的两对option参数是 --start-datetime、–stop-datetime 和 --start-position、–stop-position。
–start-datetime和–stop-datetime：可以指定恢复数据库的起始时间点和结束时间点
–start-position和–stop-position：可以指定恢复数据的开始位置和结束位置
注意：使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复。例如binlog.000011必须在binlog.000012之前恢复。
这里有个小细节需要注意的是，假设你要恢复的操作在binlog.000011文件中，那么建议先执行 flush logs; 这样会新生成一个binlog.000012，对当前数据库的修改操作会记录到新的binlog日志文件中，不会影响binlog.000011。
mysql> show binary logs; +---------------+------------+-----------+ | Log_name | File_size | Encrypted | +---------------+------------+-----------+ | binlog.000006 | 66374693 | No | | binlog.000007 | 8162695 | No | | binlog.000008 | 205361347 | No | | binlog.000009 | 10917409 | No | | binlog.000010 | 1073743498 | No | | binlog.000011 | 469251392 | No | +---------------+------------+-----------+ 6 rows in set (0.10 sec) mysql> flush logs; Query OK, 0 rows affected (0.34 sec) mysql> show binary logs; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000011 | 469251436 | No | | binlog.000012 | 156 | No | +---------------+-----------+-----------+ 2 rows in set (0.00 sec)
① 基于pos进行数据恢复
假设我们误删除了student表的数据：
# 1.首先插入两条数据 mysql> insert into student (name,class) values ('jane','一班'); Query OK, 1 row affected (0.01 sec) mysql> insert into student (name,class) values ('janus',' 二班); Query OK, 1 row affected (0.01 sec) mysql> select * from student; +----+-------+--------+ | id | name | class | +----+-------+--------+ | 5 | jane | 一班 | | 6 | janus | 二班 | +----+-------+--------+ 2 rows in set (0.00 sec) #2.误删除了数据 mysql> delete from student; Query OK, 2 rows affected (0.01 sec)
此时先执行flush logs;命令新生成一个binlog文件，然后我们查看上一个binlog文件。
mysql> flush logs; Query OK, 0 rows affected (0.03 sec) mysql> show binary logs; +---------------+-----------+-----------+ | Log_name | File_size | Encrypted | +---------------+-----------+-----------+ | binlog.000011 | 469251436 | No | | binlog.000012 | 200 | No | | binlog.000013 | 531 | No | | binlog.000014 | 179 | No | | binlog.000015 | 1146 | No | | binlog.000016 | 156 | No | +---------------+-----------+-----------+ 6 rows in set (0.01 sec)
即，这里我们要读取恢复的是binlog.000015文件。如果我们想依赖于pos进行恢复，可以使用下面语句查看：
mysql> show binlog events in 'binlog.000015'; +---------------+------+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +---------------+------+----------------+-----------+-------------+--------------------------------------+ | binlog.000015 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.26, Binlog ver: 4 | | binlog.000015 | 125 | Previous_gtids | 1 | 156 | | | binlog.000015 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000015 | 235 | Query | 1 | 315 | BEGIN | | binlog.000015 | 315 | Table_map | 1 | 382 | table_id: 96 (testindex.student) | | binlog.000015 | 382 | Write_rows | 1 | 434 | table_id: 96 flags: STMT_END_F | | binlog.000015 | 434 | Xid | 1 | 465 | COMMIT /* xid=1045 */ | | binlog.000015 | 465 | Anonymous_Gtid | 1 | 544 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000015 | 544 | Query | 1 | 624 | BEGIN | | binlog.000015 | 624 | Table_map | 1 | 691 | table_id: 96 (testindex.student) | | binlog.000015 | 691 | Write_rows | 1 | 744 | table_id: 96 flags: STMT_END_F | | binlog.000015 | 744 | Xid | 1 | 775 | COMMIT /* xid=1061 */ | | binlog.000015 | 775 | Anonymous_Gtid | 1 | 854 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | binlog.000015 | 854 | Query | 1 | 934 | BEGIN | | binlog.000015 | 934 | Table_map | 1 | 1001 | table_id: 96 (testindex.student) | | binlog.000015 | 1001 | Delete_rows | 1 | 1071 | table_id: 96 flags: STMT_END_F | | binlog.000015 | 1071 | Xid | 1 | 1102 | COMMIT /* xid=1069 */ | | binlog.000015 | 1102 | Rotate | 1 | 1146 | binlog.000016;pos=4 | +---------------+------+----------------+-----------+-------------+--------------------------------------+
基于pos的恢复关键点在于起始pos的选择。如下图所示我们红色箭头指向了三个事务（两个insert一个delete）的begin位置。那么我们只需要恢复两个insert即可。针对当前binlog文件，我们 起始pos可以设置为 --start-position=4 --stop-position=775 ，那么我们的恢复命令如下所示：
mysqlbinlog --start-position=4 --stop-position=775 --database=testindex /var/lib/mysql/binlog.000015|mysql -uroot -p123456 -v testindex
② 基于时间点进行数据恢复
那么基于时间点呢？这时候需要先使用如下命令找到时间点：
mysqlbinlog --no-defaults -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog.000015"
也就是我们将发生在这个时间区间[2023-01-08 15:31:53,2023-01-08 15:32:26]的事件再重新执行一遍。
mysqlbinlog --start-datetime="2023-01-08 15:31:53" --stop-datetime="2023-01-08 15:32:26" --database=testindex /var/lib/mysql/binlog.000015|mysql -uroot -p123456 -v testindex
可能出现一个事务执行时间过短，那么就是同样的时间，一秒内执行完成，此时我们找到下一个事务的开始时间即可，多计算一些时间就可以了。即利用binlog进行数据恢复的本质是找到需要恢复的事件起始时间或起始pos，然后重新执行即可。
【3】二进制日志写入机制
binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。
我们可以通过 binlog_cache_size 参数控制单个线程binlog cache大小，如果存储内容超过了这个参数，就要暂存到磁盘（swap）。binlog日志刷盘流程如下：
上图的write，是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快
上图的fsync，才是将数据持久化到磁盘的操作
write和fsync的时机，可以由参数 sync_binlog 控制，默认是0。为 0 的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync（等同redolog中innodb_flush_log_at_trx_commit=2的场景）。虽然性能得到提升，但是机器宕机，page cache里面的binlog会丢失。如下图所示：
为了安全起见，可以设置为1，表示每次提交事务都会执行fsync，就如同redolog innodb_flush_log_at_trx_commit=1的场景。
最后还有一种折中方式，可以设置为N（N>1），表示每次提交事务都write，但累积N个事务后才fsync。
为了安全起见，可以设置为1，表示每次提交事务都会执行fsync，就如同redolog innodb_flush_log_at_trx_commit=1的场景。
最后还有一种折中方式，可以设置为N（N>1），表示每次提交事务都write，但累积N个事务后才fsync。
【4】两阶段提交
在执行更新语句过程，会记录redo log 与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。
redo log 与binlog两份日志之间的逻辑不一致，会出现什么问题？
以update语句为例，假设id=2 的记录，字段c值是0，把字段c值更新成1，SQL语句为 update T set c=1 where id=2。
假设执行过程中写完redo log日志后，binlog日志写起见发生了异常，会出现什么问题？
由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。因此，之后用binlog日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是0，而原库因为redo log日志恢复，这一行c值是1，最终数据不一致。
为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。原理很简单，将redo log的写入拆成了两个步骤prepare 和commit，这就是两阶段提交。
使用两阶段提交后，写入binlog时发生异常也不会有影响。因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段并且没有对应binlog日志，就会回滚该事务。
那么另一个场景，redo log设置commit阶段发生异常，会不会回滚事务呢？
并不会回滚事务，它会执行上图中的逻辑。虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。
【5】中继日志（relay log）
① 概念介绍
中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。
搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。
文件名的格式是：从服务器名-relay-bin.序号 。中继日志还有一个索引文件：从服务器名-relay-bin.index ，用来定位当前正在使用的中继日志。也可以在从机指定中继日志文件名称。
② 查看中继日志
中继日志与二进制日志的格式相同，可以用mysqlbinlog工具进行查看。
mysqlbinlog --no-defaults -v mysql-relay.000002
③ 恢复的典型错误
如果从服务器宕机，有的时候为了系统恢复，要重装操作系统，这样就可能会导致你的服务器名称与之前不同。而中继日志里是包含从服务器名的，在这样情况下，就可能导致你恢复从服务器的时候，无法从宕机前的中继日志里读取数据，以为是日志文件损坏了，其实是名称不对了。
解决方法也很简单，把从服务器的名称改回之前的名称。
vim /etc/hostname
【6】详谈binlog的三种格式
① STATEMENT
也就是基于SQL语句的复制，statement-based replicaiton,SBR。
binlog_format=STATEMENT
每一条会修改数据的SQL语句会记录到binlog中。
① SBR的优点
- 历史悠久，技术成熟
- 不需要记录每一行的变化，减少了binlog日志量，文件较小
- binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全情况
- binlog可以用于实时的还原，而不仅仅用于复制
- 主从版本可以不一样，从服务器版本可以比主服务器版本高
② SBR的缺点
不是所有的update语句都能被复制，尤其是包含不确定操作的时候
使用以下函数的语句页无法被复制：LOAD_FILE() 、UUID() 、USER() 、FOUND_ROWS() 、SYSDATE()(除非启动时启用了 --sysdate-is-now选项)
insert…select会产生比RBR更多的行级锁
复制需要进行全表扫描（where语句没有使用到索引）的update时，需要比RBR请求更多的行级锁
对于有AUOT_INCREMENT 字段的InnoDB表而言，insert语句会阻塞其他insert语句
对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而RBR模式下，只会对那个发生变化的记录产生影响
执行复杂语句如果出错的话会消耗更多的资源
数据表必须几乎和主服务器保持一致才行，否则可能会导致出错
② ROW模式
5.1.5版本的MySQL才开始支持，不记录每条SQL语句的上下文信息，仅记录哪条数据被修改了，修改成什么样了。
① RBR的优点
任何情况都可以被复制，这对复制来说是最安全可靠的。（比如：不会出现某些特定情况下的存储过程、function、trigger的调用和触发无法被正确复制的问题）
多数情况下，从服务器上的表如果有主键的话，复制就会快了很多
复制以下几种语句时的行锁更少：insert…select 、包含auto_increment字段的insert、没有附带条件或者并没有修改很多记录的update或delete语句
执行insert、update、delete语句时锁更少
从服务器上采用多线程来执行复制成为可能
② RBR的缺点
- binlog大了很多
- 复杂的回滚时binlog中会包含大量的数据
- 主服务器上执行update语句时，所有发生变化的记录都会写到binlog中。而SBR只会写一次，这会导致频繁发生binlog的并发写问题
- 无法从binlog中看到都复制了写什么语句
③ MIXED模式（混合模式复制(mixed-based replication,MBR)）
从5.1.8版本开始，MySQL提供了mixed格式，实际上就是Statement与Row的结合。
在mixed模式下，一般的语句修改使用statement格式保存binlog。如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog。
MySQL会根据执行的每一条具体的SQL语句来区分对待记录的日志形式，也就是在statement和Row之间选择一种。
