常见日志（Log）

重做日志（Redo Log）

Redo Log简介

MySQL的重做日志（Redo Log）保证了事务的持久性，记录了对数据库中数据的修改操作，以便在数据库崩溃或故障切换时进行数据恢复。

Redo Log组成

组成	简介
重做记录（Redo Record）	表示数据库中进行一次变更操作（如插入、更新、删除等）所生成的记录
重做记录集（Redo Records）	表示一组Redo Record的集合，通常是指事务执行期间产生的所有Redo Record
重做日志缓冲区（Redo Log Buffer）	存储在内存中，用于暂存未持久化的重做记录集（Redo Records） Redo Log Buffer 中的内容会写入到 Redo Log Files中，以确保数据的持久性
重做日志文件（Redo Log Files）	存储在磁盘上，用于实际保存重做记录集（Redo Records） Redo Log Files由多个重做日志文件（通常是`ib_logfile0`和`ib_logfile1`）组成如果在数据库发生故障时，MySQL可以通过Redo Log Files中的记录来恢复数据的一致性

重做日志缓冲区（Redo Log Buffer）的大小，默认为 16M ，最大值是4096M，最小值为 1M ，参数设置innodb_log_buffer_size

1 2	# 查看Redo Log Buffer 的大小 show variables like '%innodb_log_buffer_size%';

假如没有使用重做日志

InnoDB引擎的主要内存区域是缓冲池（Buffer Pool），其中缓存了许多数据页。
当事务需要读取或修改某个数据页时，系统首先在缓冲池中查找该页。
如果该页在缓冲池中已经存在，则直接从缓冲池中读取或修改数据，不需要访问磁盘。
如果该页在缓冲池中不存在，则需要从磁盘中读取该页，并将其加入缓冲池中。
当事务修改了缓冲区的某个数据页时，修改后的数据页我们称为脏页。
缓冲池的脏页数据并非实时刷新，而是一定时间段后，通过后台线程刷新到磁盘文件（xxx.ibd），保证缓冲区与磁盘的数据一致
如果用户事务提交后，数据刚加入缓冲池中，数据库宕机了，但是已经提示用户成功，此时数据没有持久化到磁盘，没有保证事务的持久性的问题。

Buffer Pool缓冲池直接刷新磁盘

如果数据刚加入缓冲池（Buffer Pool）中，就直接刷新到磁盘文件（xxx.ibd），而不是等待一段时间再刷新，这样就解决了问题，但是这样会有一些新的问题

资源浪费：当数据页在缓冲池中被修改后（变成了脏页），从缓冲池到磁盘是以页（16KB）为单位进行的，即使只有一个字节被修改，整个页面也需要被刷新回磁盘，这造成了资源的浪费
随机I/O刷新较慢：当一个事务修改了大量的不相邻的页时，就会出现大量的随机I/O，随机IO比顺序IO要慢

使用重做日志后

在使用重做日志后，InnoDB引擎会在每次事务执行操作时，会将更改操作写入重做日志，只有日志写入成功，才算事务提交成功。
当重做日志缓冲区被填满时，会将缓冲区中的重做日志批量写入磁盘的重做日志文件（ib_logfile0和ib_logfile1）
由于重做日志文件是循环写入的，当文件写满时，MySQL会将最早的重做日志删除，以便将新的重做日志写入。
当MySQL发生异常崩溃等情况时，重做日志可以用来恢复数据。
在数据库恢复过程中，MySQL会先读取重做日志，再根据重做日志的记录，重现数据的更改过程。
数据库恢复完成后，MySQL会将重做日志文件中的已经提交的记录删除，只保留未提交的记录用于下次恢复。

Redo Log的刷新磁盘策略

Redo Log的刷盘策略是通过innodb_flush_log_at_trx_commit参数来控制的

取值为1的方式可以保证数据的一致性和完整性，但是因为会频繁写入磁盘，所以会降低系统性能。
取值为0或者2的方式可以提高性能，但是可能会导致数据的丢失。

取值	简介
0	表示不需要在每次事务提交时立即将重做日志写入磁盘，在每秒钟刷新一次重做日志缓冲区
1	表示每次事务提交时都将重做日志写入磁盘，以确保数据的持久性和一致性
2	表示每次事务提交时并不直接将重做日志写入磁盘，而是先将日志写入重做日志缓冲区，然后每秒钟将日志刷新到磁盘

回滚日志（Undo Log）

回滚日志简介

MySQL的回滚日志（Undo Log）保证了事务的原子性和一致性，存储的是旧版本数据

回滚日志版本链

（1）在InnoDB存储引擎中，当创建一张表后，每一行数据都会增加额外的隐藏字段，包括DB_ROW_ID、DB_TRX_ID和DB_ROLL_PTR。

DB_ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。
DB_TRX_ID：最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。

（2）在事务执行修改操作之前，旧版本的数据会保存到Undo Log中，这个过程通常被称为写入撤销日志。

（3）在事务执行修改操作之后，新的数据版本会保存对应的回滚指针（DB_ROLL_PTR），指向Undo Log中存储的旧数据版本。

（4）Undo Log中存储的旧数据版本，会通过回滚指针连接，形成一个版本链，每个节点代表一个版本，记录了对应行数据的修改信息。

链首：指向最新的数据版本
链尾：指向最旧的数据版本。

（5）当需要回滚一个事务时，MySQL会遍历版本链，按照回滚指针逆向遍历各个节点，并将Undo Log中存储的旧版本数据重新应用到数据库中，从而实现回滚操作。

回滚日志工作原理

（1）在事务开始时创建 Undo Log：当一个事务开始时，会创建一个空的 Undo Log，用于记录整个事务执行过程中对数据库进行的所有修改操作

（2）执行事务修改操作：当事务执行 INSERT、UPDATE 或 DELETE 操作时，数据库会先将当前的数据页从磁盘读取到内存中，并将其标记为“脏页”。接着，会将修改操作写入事务的 Undo Log 中。

insert: 插入一条记录时，将这条记录的主键记录下来，回滚时根据这个主键删除即可。
delete: 删除一条记录时，将这条记录的内容记录下来，回滚时重新插入到表中即可。
update：修改一条记录时，将被更新的列的旧值记录下来，回滚时将这些值更新回去即可。

（3）执行提交（COMMIT）操作：当事务被提交时，系统会将所有该事务相关的数据页（包括 Undo Log）写回到磁盘中，并解除“脏页”标记。这表示事务已经完成，所有相关的操作已经永久生效，并且可以持久化到磁盘中

（4）执行回滚（ROLLBACK）操作：如果一个事务需要回滚，则系统会按照 Undo Log 中记录的操作，将数据恢复到事务开始之前的状态。这个过程通常由数据库引擎在内部完成，并不需要用户干预。

根据事务 ID 找到事务对应的 Undo Log 记录链；
逐条撤销 Undo Log 记录中记录的修改操作，即将修改记录中的初始值写回到对应数据页中；
更新 Undo Log 链的指针，以便处理多个事务的回滚操作。

二进制日志（Binary Log）

Bin Log简介

二进制日志（Binary Log）又称归档日志（Bin Log），以二进制形式存储在磁盘中的逻辑日志，记录了数据库所有DDL和DML操作，但不包括数据查询（SELECT、SHOW）操作。

Bin Log作用

二进制日志主要作用是记录数据库中的所有数据修改操作，包括插入、更新和删除等。可以用于数据恢复、数据迁移、主从复制等场景

数据修改记录：Binary Log 记录了数据库中的数据修改操作，包括插入、更新和删除等。每个数据修改操作都会被记录为一个事件（Event），并以二进制格式存储在 Binary Log 文件中。
数据恢复：Binary Log 可以记录数据库中所有的数据修改操作，包括插入、更新和删除等。通过定期备份 Binary Log，可以实现对数据库的增量备份，当数据库出现故障或数据丢失时，可以使用备份的 Binary Log 进行数据恢复，减少数据损失。
数据复制，Binary Log 是实现主从复制的核心组件。主服务器将所有的数据修改操作记录到 Binary Log 中，并将其发送给从服务器。从服务器通过解析和应用 Binary Log 中的数据修改操作，实现与主服务器的数据同步，达到数据复制和冗余的目的。
数据库迁移：通过使用二进制日志，可以将现有数据库的数据迁移到新的数据库实例中。将源数据库的二进制日志应用到新的数据库实例中，可以确保目标数据库与源数据库之间的数据一致性。

Bin Log开启

MySQL中可以通过以下命令查看BinLog是否开启，默认情况下MySQL5.7的BinLog处于关闭状态：

1	show variables like '%log_bin%';

可以通过在MySQL配置文件[mysqld]中添加如下配置，然后重启MySQL服务，达到开启BinLog的目的

1 2	[mysqld] log-bin=mysql-bin

Bin Log查看

可以使用以下命令查看二进制日志列表

1	SHOW BINARY LOGS;

由于日志是以二进制方式存储的，不能直接读取，但可以通过二进制日志查询工具mysqlbinlog来查看，logfilename是二进制日志文件的路径和文件名

1	mysqlbinlog [参数选项] logfilename

以下是一些常用的 mysqlbinlog 参数选项：

-v 或 --verbose：显示详细的日志内容，包括操作语句和相关信息。
-d 或 --database=name：仅显示指定数据库的日志。
--start-datetime="YYYY-MM-DD HH:MM:SS"：指定查看日志的起始时间。
--stop-datetime="YYYY-MM-DD HH:MM:SS"：指定查看日志的结束时间。
--start-position=N：从指定的位置开始查看日志。
--stop-position=N：在指定位置停止查看日志。
-h 或 --host=hostname：指定要连接的MySQL服务器主机名。
-P 或 --port=port_num：指定要连接的MySQL服务器端口号。
-u 或 --user=username：指定用于连接到MySQL服务器的用户名。
-p 或 --password[=password]：提示输入密码或使用指定的密码连接到MySQL服务器。

Bin Log删除

查看所有二进制文件的文件名称

1	SHOW BINARY LOGS;

BinLog可以手动删除或设置自动清理，手动删除的命令如下

# 删除指定日志之前产生的所有二进制日志
PURGE BINARY LOGS TO 'binlog.000005';
# 清除指定日期之前的所有二进制日志文件
PURGE BINARY LOGS BEFORE '2022-02-22 00:00:00';
# 清除当前日期向前推7天内的所有二进制日志文件
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);
清除所有早于当前时间的二进制日志文件
# 清除所有早于当前时间的二进制日志文件
PURGE BINARY LOGS BEFORE NOW();
# 删除所有的二进制日志文件，并且将二进制日志文件名编号重置为 1，同时清除所有旧的复制信息和状态
RESET MASTER

自动清理可以通过设置 expire_logs_days 变量来启用，默认情况下，expire_logs_days 的值为 0，表示不启用过期自动删除功能。如果将 expire_logs_days 设置为大于 0 的值，超过指定天数的二进制日志文件将会被自动删除。当 MySQL 启动时或者执行 FLUSH LOGS 命令时，会自动触发删除过期的二进制日志文件。

1	expire_logs_days = <days>

Bin Log格式

MySQL有三种Bin Log格式，各有优劣，可以使用以下命令来查看当前MySQL服务器的二进制日志格式配置

1	SHOW VARIABLES LIKE 'binlog_format';

Statement 格式：基于SQL语句的日志记录格式，通过记录每个事务中的 SQL 语句来生成二进制日志。这种格式可以节省日志空间。
Row 格式：基于行的日志记录格式，以行的粒度记录数据修改前后的完整行数据。这种格式会占用更多的日志空间，但可以准确地记录每一行的变化
Mixed 格式：综合了 Statement 和 Row 两种格式的优点。MySQL 会根据具体的情况自动选择合适的格式。大多数情况下，它会使用 Statement 格式，只有一些特殊情况下才会使用 Row 格式。

若要更改二进制日志格式，可以编辑 MySQL 配置文件（如 my.cnf），在 [mysqld] 部分中添加或修改以下行：

1	binlog_format = <format>

查询日志（Query Log）

Query Log简介

二进制日志（Binary Log）不记录查询数据的SQL语句，查询数据的SQL语句是由查询日志（Query Log）记录。查询日志会记录 MySQL 服务器的启动和关闭信息、客户端的连接信息、更新、查询数据记录的 SQL 语句等。因为查询日志记录了所有语句，包括敏感信息，如用户名、密码等，所以在生产环境中开启查询日志需要谨慎考虑。除非必须使用，否则应该关闭查询日志，避免泄漏敏感信息。对于性能分析和优化SQL语句的需求，推荐使用MySQL的慢查询日志（Slow Query Log），它只记录执行时间超过指定阈值的SQL语句，避免了记录所有语句的安全问题，并且可以对执行时间较长的查询进行排查和优化。

Query Log开启

默认情况下，查询日志是未开启的，如果需要开启查询日志，可以修改MySQL的配置文件 /etc/my.cnf 文件，添加如下内容可以开启：

[mysqld]
# 该选项用来开启查询日志，可选关闭（0）、开启（1）
general_log=1
# 设置日志的文件名，如果没有指定，默认的文件名为 host_name.log
general_log_file=/var/log/mysql/query.log

慢查询日志（Slow Query Log）

Slow Query Log简介

用来记录在 MySQL 中执行时间超过指定时间的查询语句，在 SQL 优化过程中会经常使用到。通过慢查询日志，我们可以查找出哪些查询语句的执行效率低，耗时严重

Slow Query Log查看

# 查看是否开启慢查询日志，默认值为禁用（OFF），设为 ON表示启用慢查询日志
show variables like "slow_query_log";
# 查看慢查询日志的阈值，单位为秒，执行时间超过该值的SQL语句会纳入慢查询日志
show variables like "slow_launch_time";
# 查看慢查询日志的存储位置，默认值为数据目录下的 主机名-slow.log文件
show variables like 'slow_query_log_file';

Slow Query Log开启

修改在MySQL的配置文件，开启慢查询日志（修改后需要重启服务）

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
# 记录管理员权限的SQL语句到慢查询日志
log_slow_admin_statements =1
# 记录没有使用索引的查询语句到慢查询日志
log_queries_not_using_indexes = 1

错误日志（Error Log）

错误日志简介

错误日志是MySQL中最重要的日志之一，主要记录了以下几个方面的信息：

服务器启动和停止过程中的信息：当MySQL服务器启动时，错误日志会记录启动过程中的详细信息，包括启动的时间戳、加载的配置文件、加载的插件和模块等。同样，当MySQL服务器停止时，错误日志也会记录相应的停止过程信息。
服务器在运行过程中的故障和异常情况：如果MySQL服务器在运行过程中遇到任何严重的错误、警告或异常情况，这些信息都会被记录到错误日志中。例如，数据库连接失败、权限错误、数据损坏、死锁、查询错误等都会在错误日志中留下相应的记录。
数据库服务相关的警告和通知：除了记录错误和异常情况外，错误日志还可以记录一些警告和通知信息。这些警告和通知可能与数据库性能、配置问题、潜在风险或建议等相关。

错误日志查看

该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为mysqld.log，查看错误日志的位置

1	SHOW VARIABLES LIKE 'log_error';

错误日志设置

设置错误日志相关的配置选项，可以在MySQL的配置文件（my.cnf或my.ini）中进行设置。以下是一些常见的错误日志配置选项：

[mysqld]
# 错误日志文件的路径和文件名
log_error=/path/to/error.log
# 错误日志的最大大小，超过该大小后会自动轮换日志文件
log_error_verbosity=2
# 是否记录重复错误消息（0-不记录，1-记录）
log_warnings=1

中继日志（Relay Log）

中继日志（Relay Log）是MySQL复制过程中从服务器上使用的一种日志。它记录了从主服务器读取的二进制日志事件，并在从服务器上进行重放以实现数据复制。在MySQL复制中，主服务器（Master）将写入的数据更改记录到二进制日志（Binary Log），而从服务器（Slave）通过读取主服务器的二进制日志，并将这些更改应用到自己的数据库中。中继日志在MySQL复制中的作用如下：

数据复制：当从服务器连接到主服务器时，它会请求并获取主服务器的二进制日志事件。这些事件会被写入中继日志文件，在从服务器上进行重放和应用，以确保从服务器的数据与主服务器保持同步。
容灾和故障恢复：中继日志使得从服务器能够在主服务器出现故障或不可用的情况下继续提供服务。通过使用中继日志，从服务器可以恢复丢失的数据并保持与主服务器的一致性。
延迟复制：中继日志还允许管理员对复制进行延迟。通过延迟复制，管理员可以设置从服务器稍后执行中继日志事件，从而实现数据的延迟复制。这在某些情况下可能是有用的，例如为了进行备份、测试或分析。