MySQL日志梳理（服务器层）
本文为个人学习笔记整理，仅供交流参考，非专业教学资料，内容请自行甄别。
文章目录
- 前言
- 一、服务器层日志
- 1.1、Error Log（错误日志）
- 1.2、SLOW QUERY LOG（慢查询日志）
- 1.2.1、检查状态
- 1.2.2、开启慢查询日志
- 1.2.3、分析慢查询日志
- 1.3、General Query Log（通用查询日志）
- 1.3.1、检查状态
- 1.3.2、开启公用查询日志
- 1.4、BINARY LOG（二进制日志）
- 1.4.1、检查状态
- 1.4.2、参数设置
- 1.4.3、查看二进制日志
- 1.5、RELAY LOG（中继日志）
前言
理解MySQL的日志，对于进行SQL调优，理解底层和集群架构设计，都是有益的，MySQL的日志，通常可以分为以下三大类：
- 服务器层日志：这类日志大部分是面向用户进行查询的，可用于定位错误以及进行SQL调优，包含了
慢查询日志，错误日志，通用查询日志，以及主从同步时需要的二进制日志和中继日志。 - InnoDB 存储引擎层日志：这类日志通常偏向于数据库底层的维护，包含了
重做日志，回滚日志。 - 内部辅助日志：包含了
DDL日志，记录 DDL 操作的元数据变更过程，用于DDL 操作的崩溃恢复。
一、服务器层日志
1.1、Error Log（错误日志）
它是是 MySQL 服务器必备的日志，默认开启，记录服务器启动、运行、停止过程中的错误信息、警告信息，以及 InnoDB 的严重故障等关键事件，用于排查服务器启动失败、运行异常等问题，是运维中最常用的日志之一。
查看错误日志，先确定错误日志的存储位置，再通过对应系统的工具查看日志内容，首先登录MySQL客户端，执行以下的命令：
SHOW VARIABLES LIKE 'log_error';
进入指定的目录即可进行查询。该路径也可以在/etc/my.cnf配置文件中进行设置：
log_error = /var/log/mysqld.log
如果需要控制日志的输出级别，需要在my.cnf配置文件中追加（默认是2）：
- 1：仅记录错误信息（Error）。
- 2：记录错误和警告信息（Error + Warning）。
- 3：记录错误、警告和通知信息（Error + Warning + Note，最详细）。
log_error_verbosity = 3
1.2、SLOW QUERY LOG（慢查询日志）
1.2.1、检查状态
慢查询日志是sql调优过程中的一个重要组成部分，可以定位到执行较慢的sql，但是默认是关闭的，可以通过以下命令查询是否开启：
SHOW VARIABLES LIKE 'slow_query_log';
同时，何为慢sql，也是在配置中进行设置的，通过以下命令进行阈值查询，默认是超过10s的sql称为慢sql。
SHOW VARIABLES LIKE 'long_query_time';
除此之外，还可以通过以下参数进行更加精细化的管理：
- log_queries_not_using_indexes：是否记录未使用索引的查询。
- log_slow_admin_statements：是否记录慢管理语句，如ALTER TABLE、CREATE INDEX等
- slow_query_log_file：慢查询日志的存储文件路径。
1.2.2、开启慢查询日志
开启慢查询日志，可以是临时开启也可以是永久开启，如果需要临时开启：
-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
-- 设置慢查询阈值（单位：秒，支持小数）
SET GLOBAL long_query_time = 2;
-- 可选：记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;
-- 可选：记录慢管理语句
SET GLOBAL log_slow_admin_statements = ON;
永久开启，则需要在my.cnf配置文件中进行设置，然后重启MySQL服务生效
# 开启慢查询日志
slow_query_log = ON
# 慢查询日志文件路径（Linux示例，Windows需指定绝对路径如C:/ProgramData/MySQL/MySQL Server 8.0/Data/slow.log）
slow_query_log_file = /var/log/mysql/slow.log
# 慢查询阈值（秒，支持小数，如2.0）
long_query_time = 2
# 可选：记录未使用索引的查询（建议仅调试时开启）
log_queries_not_using_indexes = ON
# 可选：记录慢管理语句
log_slow_admin_statements = ON
# 可选：记录慢查询的执行计划（MySQL 8.0.14+支持）
log_slow_extra = ON
MySQL 8.0 支持慢查询日志存储为文件（默认）或数据库表（mysql.slow_log），由log_output变量控制：
SHOW VARIABLES LIKE 'log_output';
查看日志文件的路径，可以通过以下的命令：
SHOW VARIABLES LIKE 'slow_query_log_file';
1.2.3、分析慢查询日志
MySQL 提供了mysqldumpslow工具（内置），可快速统计慢查询的 TOP 信息:
# 查看使用帮助
mysqldumpslow --help
# 统计执行时间最长的10条慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 统计访问次数最多的慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# 统计扫描行数最多的慢查询
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log
1.3、General Query Log（通用查询日志）
通用查询日志，用于记录所有客户端的连接 / 断开事件以及执行的所有 SQL 语句，默认处于关闭状态，因其会产生大量日志，仅建议在调试、审计等临时场景下开启。
1.3.1、检查状态
可以通过以下命令，检查是否开启：
SHOW VARIABLES LIKE 'general_log';
还可以通过以下参数检查存储路径，以及存储的形式（与慢查询日志公用该参数）
SHOW VARIABLES LIKE 'general_log_file';
SHOW VARIABLES LIKE 'log_output';
1.3.2、开启公用查询日志
如果需要临时开启，可以使用以下的命令：
-- 开启通用查询日志
SET GLOBAL general_log = ON;
-- 可选：指定日志文件（若需修改默认路径，需确保MySQL对该路径有读写权限）
SET GLOBAL general_log_file = '/var/log/mysql/general.log'; -- Linux示例
-- 可选：设置输出方式为文件+表（同时存储到文件和mysql.general_log表）
SET GLOBAL log_output = 'FILE,TABLE';
永久开启，同样是修改my.cnf文件，然后重启MySQL服务：
[mysqld]
# 开启通用查询日志
general_log = ON
# 日志文件路径（Linux示例，Windows需指定绝对路径如C:/ProgramData/MySQL/MySQL Server 8.0/Data/general.log）
general_log_file = /var/log/mysql/general.log
# 输出方式（FILE/TABLE/FILE,TABLE）
log_output = FILE
1.4、BINARY LOG（二进制日志）
通常bin log和relay log是配合使用的，两者是MySQL集群架构下的重要组成部分，当配置了主从复制之后，数据在同步时通常会经历以下的步骤（暂时不考虑 undo 和 redo log）
- 主节点增删改了某条数据，并且commit事务。
- 主节点先将事务对应的 bin log 事件写入 bin log 缓存。
- 主节点将 bin log 缓存中的事件刷入 磁盘 bin log 文件，事务提交完成，向客户端返回处理成功响应
- 从节点 IO 线程主动连接主节点 3306 端口，请求主节点发送
指定 binlog 文件 + 位置”之后的 bin log 事件，主节点接收请求后，为该从节点创建专属 dump 线程（一个从节点对应一个，断开连接后销毁），dump 线程读取主节点持久化到磁盘的 bin log中 “指定位置之后” 的事件，通过 TCP 连接发送给从节点 IO 线程。 - 从节点 IO 线程接收主节点 dump 线程发送的 bin log 事件后，将其按顺序写入本地的 relay log 文件
- 从节点的 SQL 线程（长期后台运行）实时读取 relay log 中的 bin log 事件，按顺序执行重演，并且更新位置。
上述的过程是异步复制。也就是主库不关心从库是否将数据同步成功，而是直接以bin log发送出去为准。
1.4.1、检查状态
可以通过以下的命令，检查bin log是否开启：
SHOW VARIABLES LIKE 'log_bin';
还有一些其他核心参数，也可以通过命令进行查询：
-- 查看binlog存储目录（默认与数据目录同路径）
SHOW VARIABLES LIKE 'datadir';
-- 查看binlog文件名前缀（默认binlog）
SHOW VARIABLES LIKE 'binlog_format';
-- 查看binlog格式（ROW/STATEMENT/MIXED，MySQL 8.0默认ROW）
SHOW VARIABLES LIKE 'binlog_format';
-- 查看binlog过期清理时间（默认30天）
SHOW VARIABLES LIKE 'expire_logs_days';
-- 查看当前binlog文件列表及大小
SHOW BINARY LOGS;
-- 查看当前正在写入的binlog文件
SHOW MASTER STATUS;
1.4.2、参数设置
这里推荐修改my.cnf文件，修改后重启MySQL服务
# 开启binlog（指定前缀，若仅写log_bin=ON则前缀为binlog）
log_bin = /var/lib/mysql/mysql-bin
# binlog格式（ROW/STATEMENT/MIXED，推荐ROW）
binlog_format = ROW
# binlog过期时间（单位：天，MySQL 8.0.23后推荐用binlog_expire_logs_seconds）
expire_logs_days = 7
# 或用秒级配置（优先级更高，如86400秒=1天）
binlog_expire_logs_seconds = 604800
# binlog文件大小上限（默认1GB，达到后自动切换新文件）
max_binlog_size = 1G
# 开启GTID（MySQL 8.0默认开启，依赖binlog）
gtid_mode = ON
enforce_gtid_consistency = ON
1.4.3、查看二进制日志
可以通过以下的命令进行查看：
SHOW BINARY LOGS
如果要查看当前主从同步正在使用的bin log，可以通过：
SHOW MASTER STATUS
binlog 为二进制格式，需用 MySQL 自带的mysqlbinlog工具解析查看，支持本地文件解析、远程服务器解析、指定时间 / 位置 / GTID 范围解析等：
mysqlbinlog /var/lib/mysql/binlog.000001 > binlog_001.txt
1.5、RELAY LOG（中继日志）
relay log是从节点进行数据同步的关键组件，采用与 binlog 相同的文件格式和事件结构，因此可以直接用mysqlbinlog工具解析，并且其记录的 SQL 操作、事务信息、时间戳等与主库对应 binlog 片段完全一致。
relay log默认是不开启的，当在 MySQL 实例上执行CHANGE MASTER TO配置主库信息，并执行START SLAVE/REPLICA启动复制线程（IO 线程和 SQL 线程）后，从库会自动创建中继日志文件。可以通过以下命令查看状态：
SHOW SLAVE STATUS
重点需要观察以下两个参数
