binlog（二进制归档日志）

binlog 二进制日志记录保存所有执行过的修改操作语句，不保存查询操作。如果 MySQL 服务意外停止，可通过二进制日志文件排查，用户操作或表结构操作，从而来恢复数据库数据。启动 binlog 记录功能，会影响服务器性能，但如果需要恢复数据或主从复制功能，则好处大于对服务器的影响。

查看binlog相关参数

上图显示了MySQL服务器中与二进制日志（binary log，简称binlog）相关的一些系统变量的状态。这些系统变量用于控制和配置MySQL的binlog行为。

1. log_bin：

   表示是否开启了binlog。值为OFF意味着二进制日志功能当前是关闭的。这意味着MySQL实例不会记录任何二进制日志，这将影响复制和数据恢复功能。

2. log_bin_basename：

   这个变量的值通常表示二进制日志文件的路径前缀。由于log_bin是OFF，这个路径可能没有被设置或者不被使用。

3. log_bin_index：

   这个变量表示所有二进制日志文件的索引文件名。这个索引文件包含了所有的binlog文件名。这里也因为二进制日志没有开启，所以这个值可能没有被设置。

4. log_bin_trust_function_creators：

   这个设置通常用于控制是否限制存储函数（如触发器、存储过程等）在没有明确声明二进制日志属性的情况下创建。OFF表示这种创建是受限的，这是一种安全措施。

5. log_bin_use_v1_row_events：

   这个设置决定是否使用版本1的行事件格式记录binlog。OFF表示使用的是更新版本的行事件格式。

6. sql_log_bin：

   这个变量控制当前会话是否记录二进制日志。值为ON意味着当前会话对于写操作会记录二进制日志，即使全局的log_bin设置为OFF。这个设置可以用来控制特定会话是否需要记录binlog，独立于全局设置。

综合来看，即便全局的binlog被关闭，当前的会话仍然可以通过sql_log_bin变量单独控制其binlog的记录行为。如果你需要使用MySQL复制或者需要binlog来支持数据恢复和审计等功能，你可能需要将log_bin设置为ON。

启用 binlog 功能

在 MySQL 5.7 版本中，binlog 默认是关闭的，8.0 版本默认是启用的。上图中 log_bin 的值是OFF代表关闭状态，启用 binlog 功能，需要修改配置文件 my.ini（windows）或 my.cnf（linux），然后重启数据库。

在配置文件中的【mysqld】部分，增加如下配置：

# 是 binlog 的名字前缀 可以在前面添加指定路径比如：D:\mysqlServer\mysql-5.7.27\Data\mysql-bin；不添加默认在Data文件夹下
log-bin=mysql-binlog
# Server Id 是数据库服务器id 这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
server-id=1
# 其他配置
binlog_format = row # 指定binlog的记录格式为行格式
expire_logs_days = 15 # 设置binlog文件的过期天数，默认15天。超过这个时间的binlog文件会被自动删除
max_binlog_size = 200M # 限制每个binlog文件的最大大小，默认为1GB。此设置避免单个文件过大，便于管理和传输

重启数据库后，我们再去看data数据目录会多出两个文件，第一个就是binlog日志文件，第二个是binlog文件的索引文件

当然也可以执行命令查看有多少binlog文件

show binary logs;

此命令会列出所有的binlog文件以及索引文件。索引文件中包含了所有binlog文件的列表，使得MySQL可以追踪和管理这些日志文件。

binlog 的日志格式

STATEMENT（基于SQL语句的复制）

• 描述正确。记录执行的SQL语句本身，对于大多数操作，这种模式下的日志量是最小的，因为它不记录每一行数据的变更，而是记录SQL操作。然而，对于使用了非确定性函数（如UUID()、NOW()）的SQL语句，这可能导致主从数据不一致的问题。

ROW（基于行的复制）

• 描述正确。记录对数据行的具体修改，而不是记录执行的SQL语句。这种方式下，即使是非确定性函数也不会引起主从不一致，因为复制的是数据变化的结果，而非执行的SQL语句。缺点是可能会产生大量的日志数据，尤其是当对多行数据进行操作时。

MIXED（混合模式复制）

• 描述正确。MySQL会智能地在STATEMENT和ROW模式之间切换，以尽可能地优化复制的效率和准确性。在大多数情况下使用STATEMENT模式来减少日志数据量，但在可能导致数据不一致的操作中使用ROW模式。MIXED模式在很多情况下提供了一个平衡的选择。

补充

• 对于选择哪种binlog格式，需要根据实际应用的需求和数据库操作的特点进行选择。ROW模式虽然在某些情况下会产生更多的日志数据，但它提供了最高的数据一致性保障，特别是在进行跨数据库分布式事务处理时。
• MIXED模式通常是一个折中的选择，对于大多数应用来说，它提供了一个既能确保数据一致性又不会产生过多日志数据的解决方案。

binlog写入磁盘机制

sync_binlog 参数

• 为0时：

  描述准确。当sync_binlog设置为0时，事务的binlog信息首先被写入操作系统的page cache，并不直接写入磁盘。这种方式依赖于操作系统的IO策略来决定何时将缓存中的数据刷新到磁盘，从而提高了写入性能，但在发生系统崩溃时存在数据丢失的风险。

• 为1时：

  描述准确。设置sync_binlog为1意味着每个事务的binlog信息在提交时都会立即通过fsync操作被刷新到磁盘，确保了数据的持久性和一致性，但这可能对性能产生影响，特别是在高事务率的系统中。

• 设置为N (N>1)时：

  描述准确。当sync_binlog设置为大于1的值时，MySQL将累积N个事务的binlog信息在内存中，然后一次性通过fsync操作写入磁盘。这是一种折中的策略，既考虑了数据的持久性也考虑了性能优化。但如果系统在fsync之前崩溃，最近的N个事务可能会丢失。

binlog日志文件的重新生成

• 服务器启动或重新启动：

  MySQL服务器的启动或重启会导致binlog日志文件的重新生成。

• 执行FLUSH LOGS命令：

  手动执行FLUSH LOGS命令会刷新日志文件，包括binlog日志文件，从而生成新的日志文件。

• 日志文件大小达到max_binlog_size：
  当前的binlog文件大小达到max_binlog_size设置的值时，MySQL会关闭当前的binlog文件，并创建一个新的binlog文件继续记录后续的日志信息。

删除 binlog

• RESET MASTER：删除当前的binlog文件

  此命令用于删除所有的binlog文件，并重置binlog索引。这个命令确实会删除当前服务器上存在的所有binlog文件，并从头开始创建新的binlog文件（如mysql-bin.000001）。这个命令在测试环境中可能很有用，但在生产环境中使用时需要非常谨慎，因为它会丢失所有的binlog日志。

• PURGE BINARY LOGS TO ‘mysql-bin.000006’：删除指定日志文件之前的所有日志文件

  它会删除指定日志文件之前的所有日志文件，但保留指定的日志文件及之后的所有文件。这是一种常用的binlog管理操作，用于释放磁盘空间。需要注意的是，命令中应使用BINARY LOGS而非MASTER LOGS，尽管在某些MySQL版本中两者可能都有效，但官方文档使用的是BINARY LOGS。

• PURGE BINARY LOGS BEFORE ‘YYYY-MM-DD HH:MM:SS’：删除指定日期前的日志索引中binlog日志文件

  它会删除指定日期前的所有binlog文件。这个命令用于根据时间点管理binlog文件，非常有用于定期清理老旧的binlog以释放磁盘空间。

总结：

• 命令中应使用PURGE BINARY LOGS而非PURGE MASTER LOGS。
• 在使用这些命令时需要特别小心，特别是在生产环境中，因为不当的操作可能会导致数据丢失或影响数据复制等机制。

查看 binlog

可以用mysql自带的命令工具 mysqlbinlog 查看binlog日志内容

# 查看bin-log二进制文件（命令行方式，不用登录mysql）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 

# 查看bin-log二进制文件（带查询条件）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 start-datetime="2023-01-21 00:00:00" stop-datetime="2023-02-01 00:00:00" start-position="5000" stop-position="20000"

查出来的binlog日志文件内容如下：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230127 21:13:51 server id 1  end_log_pos 123 CRC32 0x084f390f  Start: binlog v 4, server v 5.7.25-log created 230127 21:13:51 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#230127 21:13:51 server id 1  end_log_pos 154 CRC32 0x672ba207  Previous-GTIDs
# [empty]
# at 154
#230127 21:22:48 server id 1  end_log_pos 219 CRC32 0x8349d010  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#230127 21:22:48 server id 1  end_log_pos 291 CRC32 0xbf49de02  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1674825768/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1342177280/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#230127 21:22:48 server id 1  end_log_pos 345 CRC32 0xc4ab653e  Table_map: `test`.`account` mapped to number 99
# at 345
#230127 21:22:48 server id 1  end_log_pos 413 CRC32 0x54a124bd  Update_rows: table id 99 flags: STMT_END_F
### UPDATE `test`.`account`
### WHERE
###   @1=1
###   @2='lilei'
###   @3=1000
### SET
###   @1=1
###   @2='lilei'
###   @3=2000
# at 413
#230127 21:22:48 server id 1  end_log_pos 444 CRC32 0x23355595  Xid = 10
COMMIT/*!*/;
# at 444
。。。

binlog 恢复数据

用binlog日志文件恢复数据其实就是回放执行之前记录在binlog文件里的sql，举一个数据恢复的例子

# 先执行刷新日志的命令生成一个新的binlog文件mysql-binlog.000008，后面我们的修改操作日志都会记录在最新的这个文件里
flush logs;
# 执行两条插入语句
INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('4', 'zhuge', '666');
INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('5', 'zhuge1', '888');
# 假设现在误操作执行了一条删除语句把刚新增的两条数据删掉了
delete from account where id > 3;

# 现在需要恢复被删除的两条数据，我们先查看binlog日志文件
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000008

文件内容如下：

。。。。。。
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#230127 23:32:24 server id 1  end_log_pos 291 CRC32 0x4528234f  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1674833544/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1342177280/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#230127 23:32:24 server id 1  end_log_pos 345 CRC32 0x7482741d  Table_map: `test`.`account` mapped to number 99
# at 345
#230127 23:32:24 server id 1  end_log_pos 396 CRC32 0x5e443cf0  Write_rows: table id 99 flags: STMT_END_F
### INSERT INTO `test`.`account`
### SET
###   @1=4
###   @2='zhuge'
###   @3=666
# at 396
#230127 23:32:24 server id 1  end_log_pos 427 CRC32 0x8a0d8a3c  Xid = 56
COMMIT/*!*/;
# at 427
#230127 23:32:40 server id 1  end_log_pos 492 CRC32 0x5261a37e  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 492
#230127 23:32:40 server id 1  end_log_pos 564 CRC32 0x01086643  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1674833560/*!*/;
BEGIN
/*!*/;
# at 564
#230127 23:32:40 server id 1  end_log_pos 618 CRC32 0xc26b6719  Table_map: `test`.`account` mapped to number 99
# at 618
#230127 23:32:40 server id 1  end_log_pos 670 CRC32 0x8e272176  Write_rows: table id 99 flags: STMT_END_F
### INSERT INTO `test`.`account`
### SET
###   @1=5
###   @2='zhuge1'
###   @3=888
# at 670
#230127 23:32:40 server id 1  end_log_pos 701 CRC32 0xb5e63d00  Xid = 58
COMMIT/*!*/;
# at 701
#230127 23:34:23 server id 1  end_log_pos 766 CRC32 0xa0844501  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 766
#230127 23:34:23 server id 1  end_log_pos 838 CRC32 0x687bdf88  Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1674833663/*!*/;
BEGIN
/*!*/;
# at 838
#230127 23:34:23 server id 1  end_log_pos 892 CRC32 0x4f7b7d6a  Table_map: `test`.`account` mapped to number 99
# at 892
#230127 23:34:23 server id 1  end_log_pos 960 CRC32 0xc47ac777  Delete_rows: table id 99 flags: STMT_END_F
### DELETE FROM `test`.`account`
### WHERE
###   @1=4
###   @2='zhuge'
###   @3=666
### DELETE FROM `test`.`account`
### WHERE
###   @1=5
###   @2='zhuge1'
###   @3=888
# at 960
#230127 23:34:23 server id 1  end_log_pos 991 CRC32 0x386699fe  Xid = 65
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
。。。。。。

找到两条插入数据的sql，每条sql的上下都有BEGIN和COMMIT，我们找到第一条sql BEGIN前面的文件位置标识 at 219(这是文件的位置标识)，再找到第二条sql COMMIT后面的文件位置标识 at 701

我们可以根据文件位置标识来恢复数据，执行如下sql：

mysqlbinlog  --no-defaults --start-position=219 --stop-position=701 --database=test D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009 | mysql -uroot -p123456 -v test

补充一个根据时间来恢复数据的命令，我们找到第一条sql BEGIN前面的时间戳标记 SET TIMESTAMP=1674833544，再找到第二条sql COMMIT后面的时间戳标记 SET TIMESTAMP=1674833663，转成datetime格式

mysqlbinlog  --no-defaults --start-datetime="2023-1-27 23:32:24" --stop-datetime="2023-1-27 23:34:23" --database=test D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009 | mysql -uroot -p123456 -v test

被删除数据被恢复！

注意：如果要恢复大量数据，比如程序员经常说的删库跑路的话题，假设我们把数据库所有数据都删除了要怎么恢复了，如果数据库之前没有备份，所有的binlog日志都在的话，就从binlog第一个文件开始逐个恢复每个binlog文件里的数据，这种一般不太可能，因为binlog日志比较大，早期的binlog文件会定期删除的，所以一般不可能用binlog文件恢复整个数据库的。

一般我们推荐的是每天(在凌晨后)需要做一次全量数据库备份，那么恢复数据库可以用最近的一次全量备份再加上备份时间点之后的binlog来恢复数据。

备份数据库一般可以用mysqldump 命令工具

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库
mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表
mysql -u root test < 备份文件名 #恢复整个数据库，test为数据库名称，需要自己先建一个数据库test