在进行增量恢复数据时，需要先了解什么是binlog日志，此日志文件其实就是用于记录对数据库进行操作更改的语句信息的；
并且记录更改的语句信息以事件形式进行记录，但是需要注意的是查询相关的语句是不会被记录的，比如：select、show；
然而作为所有对数据库的改操作事件信息都会被记录，比如：insert、update、create、drop。。。
一、查看数据库binlog日志配置参数¶
进入到数据库服务系统环境中，可以使用命令进行查看binlog日志功能是否开启；
-- 未开启binlog日志功能时，查看系统binlog功能配置参数状态
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------+
| log_bin | ON |
| log_bin_basename | /data/3306/data/binlog |
| log_bin_index | /data/3306/data/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------+
6 rows in set (0.00 sec)
配置参数信息说明：
- log_bin：表示是否开启日志功能
- sql_log_bin：是否记录事务信息到日志中
- log_bin_basename：定义日志保存路径以及名称信息，没有特别指定路径，默认存放在数据目录中
- log_bin_index：定义日志索引文件存储路径以及名称信息，没有特别指定路径，默认存放在数据目录中
二、日志信息基本配置¶
-- 进行主从操作时，需要进行此信息配置；
server_id=6
-- 默认日志功能处于关闭状态
log_bin=ON
-- 定义日志文件存储的路径信息，建议日志文件路径与数据存放路径进行分离。8.0版本之后不需要指定log_bin=ON和log_bin_basename=/data/3306/logs/3306-binlog,直接指定log_bin=/data/3306/logs/3306-binlog一条即可
log_bin=/data/3306/logs/3306-binlog
# 配置信息简写方式：开启数据库binlog日志记录功能
[root@master ~]# vim /data/3306/data/my.cnf
-- 激活binlog日志记录功能，需要对数据库服务配置文件进行编辑修改
...
...
[mysqld]
server_id=6
log_bin=/data/3306/logs/3306-binlog
-- 进行binlog日志目录路径信息修改时，需要创建指定的目录并设置权限信息，最后需要重新启动数据库服务生效；
或者
log_bin=binlog
-- 只是设置日志名称信息，日志会自动保存到数据库服务指定的数据目录中；
-- 配置文件修改后需要重启数据库服务，加载配置文件改动的信息：
[root@master ~]# systemctl restart mysqld3306.service
-- 数据库服务重启后，已经可以在数据库的数据存储目录中，看到binlog日志文件的踪影
[root@db01-51 ~]# ll -h /data/3306/logs/
total 20K
-rw-r----- 1 mysql mysql 156 Mar 9 11:10 3306-binlog.000001
-rw-r----- 1 mysql mysql 35 Mar 9 11:10 3306-binlog.index
-rw-r----- 1 mysql mysql 2.2K Mar 9 11:10 general.log
-rw-r----- 1 mysql mysql 6.0K Mar 9 11:10 mysql3306.err
-- 查看binlog日志信息
[root@master ~]# mysqlbinlog /data/3306/logs/3306-binlog.000001
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#250309 11:10:21 server id 6 end_log_pos 125 CRC32 0x7b307927 Start: binlog v 4, server v 8.0.26 created 250309 11:10:21 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
nQbNZw8GAAAAeQAAAH0AAAABAAQAOC4wLjI2AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACdBs1nEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBJ3kwew==
'/*!*/;
# at 125
#250309 11:10:21 server id 6 end_log_pos 156 CRC32 0x529ddef5 Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
-- 查看binlog日志索引信息
[root@db01-51 ~]# cat /data/3306/logs/3306-binlog.index
/data/3306/logs/3306-binlog.000001
说明：企业真实环境，日志处于默认激活记录状态，可以使用日志信息进行灾难数据恢复，以及可以用于实现主从复制；
三、日志配置信息扩展¶
参数一：sync_binlog 表示刷新日志到磁盘策略
-- 在进行主从同步过程的双一标准的其中一个1的信息配置，主要是控制缓冲区里的binlog日志信息如何刷写到磁盘中；
mysql> select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
-- 此参数信息是有三种方式进行配置的：
-- 参数信息配置0：表示由操作系统缓存自己决定，什么时候刷新日志到磁盘中；
-- 参数信息配置1：表示每次事务提交，立即刷新日志到磁盘中；(此方式配置更安全)
-- 参数信息配置N：表示每组事务提交，按照组的事务次数定义，确定刷新日志到磁盘中的频次；（可以有效减少IO性能损耗）
-- 参数官方资料链接：https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html
参数二：binlog_format 定义binlog日志的格式信息
# 格式设置方式一：参数信息配置 statement（SBR）：语句格式记录binlog；
-- 配置 statement（SBR）
mysql> set global binlog_format='statement';
mysql> \q
[root@db01-51 ~]# mysql
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT |
+-----------------+
1 row in set (0.00 sec)
-- 新建数据库
mysql> create database db22;
-- 验证查看，DDL DCL语句只能使用statement 表示的就是原原本本的语句信息，即做什么就记录什么；
mysql> show binlog events in '3306-binlog.000002';
+--------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------+
| 3306-binlog.000002 | 4 | Format_desc | 6 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| 3306-binlog.000002 | 125 | Previous_gtids | 6 | 156 | |
| 3306-binlog.000002 | 156 | Anonymous_Gtid | 6 | 233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 3306-binlog.000002 | 233 | Query | 6 | 362 | create database test_binlog /* xid=7 */ |
| 3306-binlog.000002 | 362 | Anonymous_Gtid | 6 | 439 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 3306-binlog.000002 | 439 | Query | 6 | 579 | create table test_binlog.t1(id int,name char(20)) /* xid=18 */ |
| 3306-binlog.000002 | 579 | Anonymous_Gtid | 6 | 656 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 3306-binlog.000002 | 656 | Query | 6 | 764 | create database db22 /* xid=30 */ |
+--------------------+-----+----------------+-----------+-------------+----------------------------------------------------------------+
8 rows in set (0.00 sec)
# 格式设置方式二：参数信息配置 row（RBR）：行格式记录binlog（默认模式）
-- 配置row
mysql> set global binlog_format='row';
mysql> \q
[root@db01-51 ~]# mysql
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)
-- 测试
mysql> create database db23;
mysql> create table db23.t1(id int,name char(20));
mysql> insert into db23.t1(name) values (10);
-- 将DDL、DCL语句明文记录 DML语句信息以密文方式记录
mysql> show binlog events in '3306-binlog.000002';
...
...
| 3306-binlog.000002 | 1388 | Query | 6 | 1496 | create database db23 /* xid=45 */ |
| 3306-binlog.000002 | 1496 | Anonymous_Gtid | 6 | 1573 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 3306-binlog.000002 | 1573 | Query | 6 | 1699 | create table db23.t1(id int,name char(20)) /* xid=48 */ |
| 3306-binlog.000002 | 1699 | Anonymous_Gtid | 6 | 1778 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 3306-binlog.000002 | 1778 | Query | 6 | 1849 | BEGIN |
| 3306-binlog.000002 | 1849 | Table_map | 6 | 1905 | table_id: 90 (db23.t1) |
| 3306-binlog.000002 | 1905 | Write_rows | 6 | 1944 | table_id: 90 flags: STMT_END_F |
| 3306-binlog.000002 | 1944 | Xid | 6 | 1975 | COMMIT /* xid=51 */
-- 在进行主从同步数据恢复时，此参数配置可能会影响数据恢复的一致性问题；
-- 此参数信息是有三种方式进行配置的，确定了主从复制的级别，只针对DML语句的日志才有效；
# 格式设置方式二：参数信息配置 mixed（MBR）：混合格式记录binlog
-- 由数据库服务自行决定，是记录语句信息，还是记录行的变化信息；
SBR（statement-based replication）与RBR（Row-Based Replication）记录的优缺点分析：（面试常见问题）
| 记录方式 | 优点说明 | 缺点说明 |
|---|---|---|
| SBR | 可读性强，日志量相对较少； | 数据信息可能不准确，数据一致性不足 |
| RBR | 数据信息记录更准确，数据一致性更强 | 可读性弱，日志量相对较多，数据记录准确 |
| 举例说明 | update t1 set a=10 where id<10000; 记录一条语句即可 | insert into 随机数函数; |
| 举例说明 | update t1 set a=10 where id<10000; 记录多条语句修改信息，生成日志 | insert into 随机数函数; |
四、日志信息查看方法¶
可以通过查看方式，获取binlog日志里的数据信息，一般在数据库启动时，日志记录功能就开启了；
可以利用日志中记录信息，将数据库服务的数据信息恢复到指定的时间点，同时也可以支持主从数据复制（在其它机器上回放日志）；
对于binlog日志信息的查看，主要目的是为了日后日志事件信息的截取操作；
查看方式一：确认数据库binlog日志数量
-- 获取数据库服务运行过程中，使用的binlog日志的情况
mysql> show binary logs;
+--------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+--------------------+-----------+-----------+
| 3306-binlog.000001 | 156 | No |
+--------------------+-----------+-----------+
1 row in set (0.00 sec)
-- 可以执行flush刷新命令，从而生成新的binlog日志文件，类似于实现了日志切割功能；
mysql> flush logs;
Query OK, 0 rows affected (0.12 sec)
mysql> show binary logs;
+--------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+--------------------+-----------+-----------+
| 3306-binlog.000001 | 205 | No |
| 3306-binlog.000002 | 156 | No |
+--------------------+-----------+-----------+
2 rows in set (0.00 sec)
查看方式二：确认数据库binlog日志状态
-- 查看获取当前使用的binlog日志情况，以及产生的日志量字节大小；
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| 3306-binlog.000002 | 156 | | | |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
-- 模拟数据服务有修改操作
mysql> create database test_binlog;
mysql> select * from world.city limit 1;
-- 观察到Position(表示事务产生位置点信息)发生变化
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| 3306-binlog.000002 | 362 | | | |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
-- 可以看到binlog日志的存储量发生了变化，但是在做查询操作时，binlog日志的存储量并未发生变化
mysql> show binary logs;
+------------------+-------------+--------------+
| Log_name | File_size | Encrypted |
+------------------+-------------+--------------+
| binlog.000001 | 200 | No |
| binlog.000002 | 362 | No |
+------------------+-------------+--------------+
2 rows in set (0.00 sec)
show master status;回显字段信息内容说明：
- Position：表示事务产生位置点信息
- Binlog_Do_DB：设置白名单，定义哪些库事务操作信息需要记录到binlog文件
- Binlog_Ignore_DB：设置黑名单 定义哪些库事务操作信息不需要记录到binlog文件
- Executed_Gtid_Set：当前主库（Master）已执行并记录到二进制日志中的所有事务的全局事务标识符（GTID）集合
查看方式三：查看数据库binlog日志信息
说明：binlog日志信息只记录一些创建、插入等语句信息，像use、select信息不会记录在内。
-- 方式一：在mysql会话查看
mysql> show binlog events in '3306-binlog.000002';
+--------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| 3306-binlog.000002 | 4 | Format_desc | 6 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| 3306-binlog.000002 | 125 | Previous_gtids | 6 | 156 | |
| 3306-binlog.000002 | 156 | Anonymous_Gtid | 6 | 233 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| 3306-binlog.000002 | 233 | Query | 6 | 362 | create database test_binlog /* xid=7 */ |
+--------------------+-----+----------------+-----------+-------------+-----------------------------------------+
4 rows in set (0.00 sec)
-- binlog日志信息是以事件方式进行记录的，所以日志查看过程是查看事件信息
-- 一般binlog日志的前两行，表示日志格式头信息（日志简单的描述信息）
-- 一般binlog日志中的query信息，就是对数据库的操作语句，其中包含了创建数据库的语句；
-- 方式二：在命令行界面查看,如果配置文件中添加了default-character-set=utf8mb4字符编码信息，会造成无法查看binlog
[root@db01-51 ~]# mysqlbinlog /data/3306/logs/3306-binlog.000002
...
...
# at 156
#250309 11:20:46 server id 6 end_log_pos 233 CRC32 0x38b55cef Anonymous_GTIDlast_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1741490446395278 immediate_commit_timestamp=1741490446395278 transaction_length=206
# original_commit_timestamp=1741490446395278 (2025-03-09 11:20:46.395278 CST)
# immediate_commit_timestamp=1741490446395278 (2025-03-09 11:20:46.395278 CST)
/*!80001 SET @@session.original_commit_timestamp=1741490446395278*//*!*/;
/*!80014 SET @@session.original_server_version=80026*//*!*/;
/*!80014 SET @@session.immediate_server_version=80026*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 233
#250309 11:20:46 server id 6 end_log_pos 362 CRC32 0x97e36898 Query thread_id=8 exec_time=0 error_code=0 Xid = 7
SET TIMESTAMP=1741490446/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database test_binlog
/*!*/;
# at 362
#250309 13:15:55 server id 6 end_log_pos 439 CRC32 0x153348f4 Anonymous_GTIDlast_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1741497355710838 immediate_commit_timestamp=1741497355710838 transaction_length=217
# original_commit_timestamp=1741497355710838 (2025-03-09 13:15:55.710838 CST)
# immediate_commit_timestamp=1741497355710838 (2025-03-09 13:15:55.710838 CST)
/*!80001 SET @@session.original_commit_timestamp=1741497355710838*//*!*/;
/*!80014 SET @@session.original_server_version=80026*//*!*/;
/*!80014 SET @@session.immediate_server_version=80026*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 439
#250309 13:15:55 server id 6 end_log_pos 579 CRC32 0xcf1d0f3a Query thread_id=8 exec_time=0 error_code=0 Xid = 18
SET TIMESTAMP=1741497355/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table test_binlog.t1(id int,name char(20))
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
具体binlog事件信息：
具体binlog事件记录信息分析：
| 列号 | 列信息 | 解释说明 |
|---|---|---|
| 01 | Log_name | 表示指定查看的binlog日志文件名称信息 |
| 02 | Pos | 表示binlog日志事件开始的位置点，用于截取二进制日志信息标识 |
| 05 | End_log_pos | 表示binlog日志事件结束的位置点，用于截取二进制日志信息标识 |
| 06 | Info | 表示binlog中具体的事件内容信息 |
如果想取消事务事件日志记录到bin日志中，则可以通过下面参数进行去除
-- sql_log_bin是会话级别的，不是全局配置的，切记
mysql> set session sql_log_bin=0;
查看方式四：筛选数据库binlog日志事件
# 模拟生成binlog日志事件信息
mysql> source ~/world.sql;
mysql> drop database world;
mysql> source ~/world.sql;
# 获取删除数据库的事件信息：
# 筛选数据库日志方式一：
-- 获取指定事件信息产生的起点位置和终点位置信息；
[root@master data]# mysql -e "show binlog events in '3306-binlog.000002'"|grep "drop database"
binlog.000002 722789 Query 1 722896 drop database world /* xid=5363 */
# 筛选数据库日志方式二：
-- 在数据库中定义pager功能，数据库连接会话退出即失效；
mysql> pager less
-- 此时查看日志事件信息具有了翻页功能
mysql> show binlog events in '3306-binlog.000002';
-- 表示开启数据库pager的过滤功能
/drop database
| binlog.000002 | 722789 | Query | 1 | 722896 | drop database world /* xid=5363 */
-- 提前设置过滤项
mysql> pager grep "drop database"
PAGER set to 'grep "drop database"'
-- 再次查看binlog事件信息时，只过滤显示删除数据库的操作事件日志
mysql> show binlog events in '3306-binlog.000002';
| 3306-binlog.000002 | 1026 | Query | 6 | 1130 | drop database db23 /* xid=35 */ |
| 3306-binlog.000002 | 1207 | Query | 6 | 1311 | drop database db22 /* xid=37 */ |
23 rows in set (0.00 sec)
说明：在实际生产环境中，若binlog日志量比较大时，需要快速过滤关键日志事件行，可以使用以上查看日志方法；
获取数据库binlog日志记录信息异常：
进行数据库服务数据信息更改操作，随后查看binlog日志信息的变化：
# 进行数据库创建操作
mysql> create database aaa;
mysql> show databases;
# 查看获取binlog日志记录信息
-- 由于在数据库在客户端配置文件中添加了default-character-set=utf8mb4字符编码信息，因此造成无法查看binlog
[root@master ~]# mysqlbinlog /var/lib/mysql/binlog.000001
mysqlbinlog: unknown variable 'default-character-set=utf8mb4'
-- 可以临时调整先将客户端的字符编码配置信息注释，
[root@master ~]# cat /etc/my.cnf.d/client.cnf
[client]
#default-character-set=utf8mb4
[client-mariadb]
#default-character-set=utf8mb4
-- 在binlog日志文件中，已经记录了之前的创建master的更改操作记录信息
[root@master ~]# mysqlbinlog /var/lib/mysql/binlog.000001
... 省略部分信息 ...
# at 494
#220624 2:35:02 server id 1 end_log_pos 579 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1656009302/*!*/;
create database master
/*!*/;
... 省略部分信息 ...