11-2. 日志
〇. 日志介绍
对于线上数据库应用系统，突然遭遇数据库宕机怎么办?在这种情况下，定位宕机的原因就非常关键。我们可以查看数据库的错误日志。因为日志中记录了数据库运行中的诊断信息，包括了错误、警告和注释等信息。
比如:从日志中发现某个连接中的sQL操作发生了死循环，导致内存不足，被系统强行终止了。明确了原因，处理起来也就轻松了，系统很快就恢复了运行。
除了发现错误，日志在数据复制、数据恢复、操作审计，以及确保数据的永久性和一致性等方面，都有着不可替代的作用。
很多情况下，只有通过查看日志才能发现问题的原因，真正解决问题。
1. 日志类型
MySQL有不同类型的日志文件，用来存储不同类型的日志，分为二进制日志、错误日志、通用查询日志和慢查询日志，这也是常用的4种。MySQL8 又新增两种支持的日志：中继日志 和数据定义语句日志。使用这些日志文件，可以查看MySQL内部发生的事情。
这6类日志分别为：
- 慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。
- 通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。
- 错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。
- 二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
- 中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
- 数据定义语句日志：记录数据定义语句执行的元数据操作。
除二进制日志外，其他日志都是文本文件 。默认情况下，所有日志创建于MySQL数据目录中。
2. 日志的弊端
- 日志功能会 降低MySQL数据库的性能 。例如，在查询非常频繁的MysQL数据库系统中，如果开启了通用查询日志和慢查询日志，MySQL数据库会花费很多时间记录日志。
- 日志会 占用大量的磁盘空间 。对于用户量非常大、操作非常频繁的数据库，日志文件需要的存储空间设置比数据库文件需要的存储空间还要大。
Ⅰ. general log
general log是通用查询日志
通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。
1. 使用场景示例
在电商系统中，购买商品并且使用微信支付完成以后，却发现支付中心的记录并没有新增，此时用户再次使用支付宝支付，就会出现重复支付的问题。但是当去数据库中查询数据的时候，会发现只有一条记录存在。那么此时给到的现象就是只有一条支付记录，但是用户却支付了两次。
我们对系统进行了仔细检查，没有发现数据问题，因为用户编号和订单编号以及第三方流水号都是对的。可是用户确实支付了两次，这个时候，我们想到了检查通用查询日志，看看当天到底发生了什么。
查看之后，发现1月1日下午2点，用户使用微信支付完以后，但是由于网络故障，支付中心没有及时收到微信支付的回调通知，导致当时没有写入数据。1月1日下午2点30，用户又使用支付宝支付，此时记录更新到支付中心。1月1日晚上9点，微信的回调通知过来了，但是支付中心已经存在了支付宝的记录，所以只能覆盖记录了。
可以看到通用查询日志可以帮助我们了解操作发生的具体时间和操作的细节，对找出异常发生的原因极其关键。
2. 查看日志状态
默认该日志是关闭的，可以查看是否关闭以及文件地址
SHOW VARIABLES LIKE '%general%';
-- 返回信息示例
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | OFF |
| general_log_file | /var/lib/mysql/ali-1.log |
+------------------+--------------------------+
系统变量general_log的值是OFF，即通用查询日志处于关闭状态。
在MySQL中，这个参数的默认值是关闭的。因为一旦开启记录通用查询日志，MysQL会记录所有的连接起止和相关的SQL操作，这样会消耗系统资源并且占用磁盘空间。我们可以通过手动修改变量的值，在需要的时候开启日志。
通用查询日志文件的存储路径是/var/lib/mysql/，默认也是数据路径。文件名默认就是服务器的名字
3. 启用日志
分为临时启用和永久性启用
3.1 配置中永久启用
修改my.cnf配置文件来设置。
在[mysqld]组下加入log选项，并重启MySQL服务。格式如下：
[mysqld]
general_log=ON
# 日志文件所在目录路径，filename为日志文件名
general_log_file=[path[filename]]
如果不指定目录和文件名，通用查询日志将默认存储在MySQL数据目录中的hostname.log文件中，hostname表示主机名。
3.2 通过命令临时启用
# 开启通用查询日志
SET GLOBAL general_log=on;
# 设置日志文件保存位置
SET GLOBAL general_log_file='path/filename';
# 关闭通用查询日志
SET GLOBAL general_log=off;
4. 查看日志
通用查询日志是以文本文件的形式存储在文件系统中的，可以使用文本编辑器直接打开日志文件。
在通用查询日志里面，我们可以清楚地看到，什么时候开启了新的客户端登陆数据库，登录之后做了什么 SQL 操作，针对的是哪个数据表等信息。
5. 停止日志
停止的方式和开启的方式有关，也分为配置文件和命令两种方式。
# 配置文件，永久性方式，需要重启
[mysqld]
general_log=OFF # 改成OFF，或直接注释，默认就是OFF
# general_log=ON
-- 命令，临时性方式
SET GLOBAL general_log=off;
6. 日志文件的删除、刷新和归档
如果数据的使用非常频繁，那么通用查询日志会占用服务器非常大的磁盘空间。数据管理员可以删除很长时间之前的查询日志，以保证MySQL服务器上的硬盘空间。
6.1 手动删除文件
可以手动删除通用查询日志
SHOW VARIABLES LIKE 'general_log%'; -- 查到他在哪里
6.2 使用命令重新生成
使用如下命令重新生成查询日志文件，具体命令如下。刷新MySQL数据目录，发现创建了新的日志文件。前提一定要开启通用日志。
mysqladmin -uroot -p flush-logs
6.3 归档日志
如果希望备份旧的通用查询日志，就必须先将旧的日志文件复制出来或者改名，然后执行上面的mysqladmin命令。正确流程如下
#输入自己的通用日志文件所在目录
cd mysql-data-directory
#指明旧的文件名以及新的文件名
mv mysql.general.log mysql.general.log.old
# 重新生成
mysqladmin -uroot -p flush-logs
Ⅱ. error log
error log是错误日志，错误日志记录了MySQL服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示等。
通过错误日志可以查看系统的运行状态，便于即时发现故障、修复故障。如果MySQL服务出现异常，错误日志是发现问题、解决故障的首选。
1. 启动日志
在MySQL数据库中，错误日志功能是默认开启的。而且错误日志无法被禁止。
默认情况下，错误日志存储在MySQL数据库的数据文件夹下，名称默认为 mysqld.log （Linux系统）或hostname.err （mac系统）。如果需要定制文件名，则需要在my.cnf中做如下配置：
[mysqld]
# path为日志文件所在的目录路径，filename为日志文件名
log-error=[path/[filename]]
2. 查看日志
MySQL错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看。
查询错误日志的存储路径：
SHOW VARIABLES LIKE 'log_err%';
-- 返回示例
+----------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------+
| log_error | /var/log/mysql/mysqld.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
+----------------------------+----------------------------------------+
3. 错误日志的 删除、归档和重建
对于很久以前的错误日志，数据库管理员查看这些错误日志的可能性不大，可以将这些错误日志删除，以保证MySQL服务器上的硬盘空间。MySQL的错误日志是以文本文件的形式存储在文件系统中的，可以直接删除。
3.1 手动删除
手动将错误日志删除。
-- 手动删除
rm -f /var/log/mysql/mysqld.log
-- 先归档，再删除
mv /var/log/mysql/mysqld.log /var/log/mysql/mysqld.log.old
注意：在运行状态下删除错误日志文件后，MySQL并不会自动创建日志文件。
3.2 重建日志
mysqladmin -u root -p flush-logs
从MySQL 5.5.7开始，flush-logs只是重新打开日志文件，并不做日志备份和创建的操作。如果日志文件不存在，MySQL启动或者执行flush-logs时会自动创建新的日志文件。
也就是说该命令仅仅是touch了下mysqld.log，如果文件没有被删除，重建日志操作就毫无意义。
Ⅲ. bin log
bin log 即binary log 为二进制日志。也叫作变更日志（update log）。它记录了数据库所有执行的 DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。
是MySQL中比较重要的日志，在日常开发及运维过程中，经常会遇到。
1. binlog的应用场景
用于数据恢复：如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
是用于数据复制：由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到maer-slave数据一致的目的。
可以说MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。
2. 查看日志信息
查看记录二进制日志是否开启，在MySQL8中默认情况下，二进制文件是开启的。
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 | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-----------------------------+
- log_bin：定义binlog日志是否开启，默认是开启的
- log_bin_basename：是binlog日志的基本文件名，后面会追加标识来表示每一个文件
- log_bin_index：是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录
- log_bin_trust_function_creators：限制存储过程，因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后，需要限制存储函数的创建、修改、调用
- log_bin_use_v1_row_events：此只读系统变量已弃用，ON表示使用版本1二进制日志行，OFF表示使用版本2二进制日志行(MySQL 5.6的默认值为2)。
- sql_log_bin：是否记录SQL语句。
3. bin 日志参数设置
3.1 配置文件中永久修改
修改MySQL的 my.cnf，设置二进制日志的相关参数：
[mysqld]
#启用二进制日志
log-bin=ali-bin # 开启二进制日志，并指定日志的名字
binlog_expire_logs_seconds=600
max_binlog_size=100M
- log-bin：打开日志，日志的初始名字可以自定义
- 默认就是开启的，可以不设置改参数，logbin的文件名默认是logbin
- 这里也可以加上路径，如:/home/www/mysql_bin_log/binlog
- binlog_expire_logs_seconds：此参数控制二进制日志文件保留的时长，单位是秒，默认2592000
- max_binlog_size：控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换动作。此参数的最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，可能不做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束。一般情况下可采取默认值。
3.2 设置binlog的存放目录
数据库文件最好不要与日志文件放在同一个磁盘上这样，当数据库文件所在的磁盘发生故障时，可以使用日志文件恢复数据。
如果想改变日志文件的目录和名称，可以对my.cnf的log_bin参数修改如下：
[mysqld]
log-bin="/var/lib/mysql/binlog/sgugo-bin"
注意：新建的文件夹需要使用mysql用户，或者修改文件夹的归属。
chown -R -v mysql:mysql binlog
3.3 使用命令临时修改logbin参数
如果不希望通过修改配置文件并重启的方式设置二进制日志的话，还可以使用如下指令，需要注意的是在mysql8中只有会话级别的设置，没有了global级别的设置。
-- global 级别 会直接报错
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
-- session级别
SET sql_log_bin=0;
-- Query OK, 0 rows affected (0.01 秒)
4. binlog 文件的自动新建
当MySQL创建二进制日志文件时，会先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称、以“.000001”为后缀的文件。
MySQL服务重新启动一次 ，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件。
总结：重启一次或日志文件的容量达到上线就会自动创建一个新的日志文件，文件名从[basename].000001开始自增，basename默认是binglog，可以自定义。
可以使用命令，查看当前的二进制日志文件列表
SHOW BINARY LOGS;
-- 返回示例
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 489794 | No |
| binlog.000002 | 201 | No |
| binlog.000003 | 157 | No |
+---------------+-----------+-----------+
5. mysqlbinlog 查看日志
所有对数据库的修改都会记录在binglog中。但binlog是二进制文件，无法直接查看，想要更直观的观测它就要借助MySQL提供的命令行工具mysqlbinglog了。
PS：主义是命令行工具，要在命令行环境下执行，而不是MySQL客户端。
5.1 mysqlbinglog直接使用
直接查看binlog日志
mysqlbinlog /var/lib/mysql/binlog.000001
这种自接查看方式，只能读到设置相关的内容，无法读到SQL内容，如下
# 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
#240110 11:20:35 server id 1 end_log_pos 126 CRC32 0x601bc066 Start: binlog v 4, server v 8.0.32 created 240110 11:20:35
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
Aw2eZQ8BAAAAegAAAH4AAAABAAQAOC4wLjMyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYgAEGggAAAAICAgCAAAACgoKKioAEjQACigAAWbAG2A='
/*!*/;
# at 126
#240110 11:20:35 server id 1 end_log_pos 157 CRC32 0xbc40773f 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*/;
这是一个简单的日志文件，日志中记录了用户的一些操作，这里并没有出现具体的SQL语句，这是因为binlog关键字后面的内容是经过编码后的二进制日志
5.2 mysqlbinlog -v
加上 -v参数，可以解析SQL语句
mysqlbinlog -v /var/lib/mysql/binlog.000001
-- 返回示例
# at 485783
#231223 11:00:30 server id 1 end_log_pos 485898 CRC32 0x91f5b690 Write_rows: table id 189 flags: STMT_END_F
BINLOG '
Tk2GZRMBAAAAWgAAAJdpBwAAAL0AAAAAAAEABXJ1b3lpAA5zeXNfbG9naW5pbmZvcgAJCA8PDw8P
/g8SD8gAAAL8A8gAyAD+BPwDAP4BAQEAAgP8/wBtQuUW
Tk2GZR4BAAAAcwAAAApqBwAAAL0AAAAAAAEAAgAJ//8AAGYAAAAAAAAABWFkbWluCQAxMjcuMC4w
LjEIAOWGhee9kUlQCUNocm9tZSAxMgpXaW5kb3dzIDEwATAMAOeZu+W9leaIkOWKn5mx7rAekLb1
kQ==
'/*!*/;
### INSERT INTO `ruoyi`.`sys_logininfor`
### SET
### @1=102
### @2='admin'
### @3='127.0.0.1'
### @4='内网IP'
### @5='Chrome 12'
### @6='Windows 10'
### @7='0'
### @8='登录成功'
### @9='2023-12-23 11:00:30'
如上截取的片段，包含了DML的SQL语句。
5.3 mysqlbinlog -v --base64-output=DECODE-ROWS
前面的命令同时显示binlog格式的语句，使用如下命令仅显示编译后的日志。
mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/binlog.000001
-- 返回示例
# at 485693
#231223 11:00:30 server id 1 end_log_pos 485783 CRC32 0x16e5426d Table_map: `ruoyi`.`sys_logininfor` mapped to number 189
# has_generated_invisible_primary_key=0
# at 485783
#231223 11:00:30 server id 1 end_log_pos 485898 CRC32 0x91f5b690 Write_rows: table id 189 flags: STMT_END_F
### INSERT INTO `ruoyi`.`sys_logininfor`
### SET
### @1=102
### @2='admin'
### @3='127.0.0.1'
### @4='内网IP'
### @5='Chrome 12'
### @6='Windows 10'
### @7='0'
### @8='登录成功'
### @9='2023-12-23 11:00:30'
5.5 将binlog转为sql
可以使用mysqlbinlog命令，将binlog文件转为SQL文件，方便查看。
mysqlbinlog /var/lib/mysql/binlog.000001>binlog000001.sql
导出的sql文件可以直接打开查看，非常方便，如下
# at 1468
#231113 17:31:35 server id 1 end_log_pos 2203 CRC32 0x0b57b1f6 Query thread_id=10 exec_time=0 error_code=0 Xid = 105
use `security`/*!*/;
SET TIMESTAMP=1699867895/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
CREATE TABLE `user`(^M
`id` int(11) NOT NULL AUTO_INCREMENT,^M
`username` varchar(32) DEFAULT NULL COMMENT "用户名",^M
`password` varchar(255) DEFAULT NULL COMMENT "密码",^M
`enabled` tinyint(1) DEFAULT NULL COMMENT "是否可用",^M
`accountNonExpired` tinyint(1) DEFAULT NULL COMMENT "账号是否未过期",^M
`accountNonLocked` tinyint(1) DEFAULT NULL COMMENT "是否未锁定",^M
`credentialsNonExpired` tinyint(1) DEFAULT NULL COMMENT "凭证是否未过期",^M
PRIMARY KEY (`id`)^M
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
/*!*/;
- at：每个SQL片段都是以
#at 数字开始的，后面的数字其实是SQL的位置。 - #231113 17:31:35：事务开始的时间
- 结束时间就是下一个事务开启的时间
6. show binlog envents 查看日志
mysqlbinlog工具读取的binlog日志的全文内容比较多，不容易分辨查看到pos点信息，下面介绍一种更为方便的查询命令：
mysql> 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 :查询总条数（不指定就是所有行）
6.1 使用示例
-- 分号必须加上，只需给出文件名，无需给出目录
show binlog events in 'binlog.000001';
-- 返回示例：
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 |
| binlog.000001 | 123 | Previous_gtids | 1 | 154 | |
| binlog.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 219 | Query | 1 | 291 | BEGIN |
| binlog.000001 | 291 | Table_map | 1 | 339 | table_id: 113 (hist.t2) |
| binlog.000001 | 339 | Write_rows | 1 | 413 | table_id: 113 flags: STMT_END_F |
| binlog.000001 | 413 | Xid | 1 | 444 | COMMIT /* xid=16 */ |
| binlog.000001 | 444 | Anonymous_Gtid | 1 | 509 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 509 | Query | 1 | 581 | BEGIN |
| binlog.000001 | 581 | Table_map | 1 | 629 | table_id: 113 (hist.t2) |
| binlog.000001 | 629 | Update_rows | 1 | 845 | table_id: 113 flags: STMT_END_F |
| binlog.000001 | 845 | Xid | 1 | 876 | COMMIT /* xid=17 */ |
| binlog.000001 | 876 | Rotate | 1 | 923 | binlog.000002;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
13 rows in set (0.00 sec)
6.2 从指定pos点开始查
-- 从pos点417开始查询
show binlog events in 'binlog.000002' from 417;
-- 从pos点219开始查询，查询5条记录
show binlog events in 'binlog.000002' from 417 limit 5;
-- 从pos点219开始查询，查询5条记录，偏移2行
show binlog events in 'binlog.000002' from 417 limit 2,5;
6.3 返回的信息解析
- Log_name：当前事件所在的binlog文件名称；
- Pos：当前事件的开始位置
- 每个事件都占用固定的字节大小，结束位置(End_log_position)减去Pos，就是这个事件占用的字节数。
- 第一个事件位置并不是从0开始，而是从4。Mysql通过文件中的前4个字节，来判断这是不是一个binlog文件
- Event_type：表示事件的类型；
- Server_id：表示产生这个事件的mysql server_id，通过设置my.cnf中的server-id选项进行配置；
- End_log_position：下一个事件的开始位置；
- Info：当前事件的描述信息。
7. 使用日志恢复数据
如果MySQL服务器启用了二进制日志，在数据库出现意外丢失数据时，可以使用MySQLbinlog工具从指定的时间点或SQL位置开始恢复，并恢复到指定的时间点或SQL位置
警告
当需要恢复数据时，为了防止恢复数据后影响最新业务，需要执行flush logs，产生一个新的binlog文件，此时旧的binlog文件不会再有写入；
mysql>flush logs;
7.1 语法
mysqlbinlog [option] filename|mysql –uuser -ppass;
这个命令可以这样理解：使用mysqlbinlog命令来读取filename中的内容，然后使用mysql命令将这些内容恢复到数据库中。
- filename：是日志文件名。
- option：可选项，有4个参数是比较重要的
- --start-date和--stop-date：可以指定恢复数据库的起始时间点和结束时间点。
- --start-position和--stop-position：可以指定恢复数据的开始位置和结束位置。
使用mysqlbinlog命令进行恢复操作时，必须是编号小的先恢复，例如binlog.000001必须在binlog.000002之前恢复。
7.1 示例1：使用位置恢复
恢复时需要在binlog中找到两个位置：
- 数据恢复的起始位置
- 数据恢复的结束位置
可以先使用mysqlbinlog工具将binlog日志导出为sql格式，再查询要恢复的SQL节点所在的位置（at 后面的数字）。
mysqlbinlog -v --start-position=219 --stop-position=982 --database=ruoyi
/var/lib/mysql/binlog.000001 | mysql -uroot -p123456
- /var/lib/mysql/binlog.000001：要操作binlog文件
- --start-position=219：数据恢复的起始位置
- --stop-position=982：数据恢复的结束位置
- --database=ruoyi：数据库名字，可以忽略
- mysql -uroot -p123456：数据恢复需要登录数据库
7.1 示例2：使用日期恢复
只是将位置参数改成了日期参数
mysqlbinlog --start-datetime="2022-02-02 14:23:32" --stop-datetime="2022-02-03 14:44:22" /var/lib/mysql/binlog.000001
| /usr/bin/mysql -uroot -p123456
日期可以根据binlog日志查看。
可能出现一个事务执行时间过短，那么就是同样的时间，一秒内执行完成，此时我们找到下一个事务的开始时间即可，多计算一些时间就可以了。
8. 删除二进制日志
MySQL的二进制文件可以配置自动删除，同时MySQL也提供了安全的手动删除二进制文件的方法。
- PURGE MASTER LOGS：只删除指定部分的二进制日志文件
- RESET MASTER：删除所有的二进制日志文件
8.1 PURGE MASTER LOGS：删除指定日志文件
PURGE {MASTER | BINARY} LOGS TO '指定日志文件名'
PURGE {MASTER | BINARY} LOGS BEFORE '指定日期'
-- 示例：删除binlog.000003之前创建的日志
purge master logs to 'binlog.000003';
-- 示例：删除2023-01-01前创建的所有binlog日志文件
purge master logs before '20230101';
8.2 RESET MASTER：删除所有二进制日志文件
使用RESET MASTER语句，清空所有的binlog日志。
MySQL会重新创建二进制文件，新的日志文件扩展名将重新从000001开始编号。慎用!
RESET MASTER
执行完该语句后，原来的所有二进制日志已经全部被删除
9. 其他应用场景
二进制日志可以通过数据库的全量备份和二进制日志中保存的增量信息，完成数据库的无损失恢复。但是，如果遇到数据量大、数据库和数据表很多（比如分库分表的应用）的场景，用二进制日志进行数据恢复，是很有挑战性的，因为起止位置不容易管理。
在这种情况下，一个有效的解决办法是配置主从数据库服务器，甚至是一主多从的架构，把二进制日志文件的内容通过中继日志，同步到从数据库服务器中，这样就可以有效避免数据库故障导致的数据异常等问题。
Ⅳ. bin log 底层
1. 写入机制
binlog的写入时机也非常简单，事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。
我们可以通过binlog_cache_size参数控制单个线程binlog cache大小，如果存储内容超过了这个参数，就要暂存到磁盘(Swap) 。
1.1 binlog日志刷盘流程
上图的write，是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。上图的 fsync，才是将数据持久化到磁盘的操作
1.2 write和fsync的时机
参数 sync_binlog 可以控制write和fsync的时机，默认是0 。为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是机器宕机，page cache里面的binglog 会丢失。如下图：
为了安全起见，可以设置为 1 ，表示每次提交事务都会执行fsync，就如同redo log 刷盘流程一样。最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。
在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样的，如果机器宕机，会丢失最近N个事务的binlog日志。
2. binlog与redolog对比
- redo log 它是 物理日志 ，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生的。
- 而 binlog 是 逻辑日志 ，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。
虽然它们都属于持久化的保证，但是则重点不同。
- redo log让InnoDB存储引擎拥有了崩溃恢复能力。
- binlog 保证了MySQL集群架构的数据一致性。
3. 两阶段提交
在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的 写入时机 不一样。
3.1 不一致问题
redo log与binlog两份日志之间的逻辑不一致会出现一些问题。
以update语句为例，假设id=2的记录，字段c值是0，把字段c值更新成1，sQL语句为update T set c=1 whereid=2。
假设执行过程中写完redo log日志后，binlog日志写期间发生了异常，会出现什么情况呢?
由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。
因此，之后用binlog日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是0，而原库因为redo log日志恢复，这一行c值是1，最终数据不一致。
3.2 问题解决
为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。
原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。
使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MysQL根据redo log日志恢复数据时，发现redolog还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。
3.3 commit阶段异常
redo log设置commit阶段发生异常，并不会回滚事务，它会执行上图框住的逻辑，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。
Ⅴ. relay log
relay log是中继日志
中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。
从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。
搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。 文件名的格式是：从服务器名 -relay-bin.序号。 中继日志还有一个索引文件：从服务器名 -relay-bin.index，用来定位当前正在使用的中继日志。
1. 查看中继日志
中继日志与二进制日志的格式相同，可以用 mysqlbinlog 工具进行查看。
下面是中继日志的一个片段：
SET TIMESTAMP=1618558728/*!*/;
BEGIN
/*!*/;
# at 950
#210416 15:38:48 server id 1 end_log_pos 832 CRC32 0xcc16d651 Table_map:
`atguigu`.`test` mapped to number 91
# at 1000
#210416 15:38:48 server id 1 end_log_pos 872 CRC32 0x07e4047c Delete_rows: table id
91 flags: STMT_END_F -- server id 1 是主服务器，意思是主服务器删了一行数据
BINLOG '
CD95YBMBAAAAMgAAAEADAAAAAFsAAAAAAAEABGRlbW8ABHRlc3QAAQMAAQEBAFHWFsw=
CD95YCABAAAAKAAAAGgDAAAAAFsAAAAAAAEAAgAB/wABAAAAfATkBw==
'/*!*/;
# at 1040
这一段的意思是，主服务器（“server id 1”）对表 atguigu.test 进行了 2 步操作：
- 定位到表 atguigu.test 编号是 91 的记录，日志位置是 832；
- 删除编号是 91 的记录，日志位置是 872
2. 数据恢复的bug
如果从服务器宕机，有的时候为了系统恢复，要重装操作系统，这样就可能会导致你的服务器名称与之前不同，而中继日志里是包含从服务器名的。
在这种情况下，就可能导致你恢复从服务器的时候，无法从宕机前的中继日志里读取数据，以为是日志文件损坏了，其实是名称不对了。
解决的方法也很简单，把从服务器的名称改回之前的名称。