MySQL日志

原创于 2025-08-25 21:11:53 发布 · 797 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

MySQL日志是记录数据库活动的重要工具，用于故障排查、性能分析、安全审计和数据恢复。MySQL提供了多种类型的日志，每种日志都有特定的用途和配置方式。合理配置和使用这些日志可以提高数据库的可维护性和安全性。

一、错误日志（Error Log）

错误日志是MySQL中最基本的日志，记录了MySQL服务器启动、运行和关闭过程中的错误信息、警告信息和通知信息，是排查服务器启动故障的主要依据。

1. 记录内容

- 服务器启动和关闭过程的信息

- 服务器运行中的错误信息（如连接失败、存储引擎错误等）

- 服务器运行中的警告信息（如配置参数不推荐使用）

- 重要的通知信息（如日志滚动、备份相关信息）

2. 配置方法

[mysqld]
# 启用错误日志（默认启用）
log_error = /var/log/mysql/error.log

# 错误日志级别（5.7.2+）：debug, info, note, warning, error
log_error_verbosity = 3  # 3表示记录note、warning、error，默认值

3. 查看错误日志

-- 查看错误日志位置
SHOW VARIABLES LIKE 'log_error';

-- 查看最近的错误日志内容（Linux）
tail -n 100 /var/log/mysql/error.log

-- 实时查看错误日志（Linux）
tail -f /var/log/mysql/error.log

4. 日志轮转

错误日志会不断增长，需要定期轮转，避免占用过多磁盘空间：

-- 方法1：使用mysqladmin
mysqladmin -u root -p flush-logs

-- 方法2：使用SQL命令
FLUSH ERROR LOGS;

-- 方法3：配置logrotate（推荐）
# 创建配置文件 /etc/logrotate.d/mysql
/var/log/mysql/error.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    postrotate
        mysqladmin -u root -p密码 flush-logs
    endscript
}

二、通用查询日志（General Query Log）

通用查询日志记录MySQL服务器执行的所有SQL语句和连接信息，包括客户端连接、断开连接以及执行的所有SQL命令。由于会记录所有操作，对性能有一定影响，通常不建议在生产环境长期开启。

1. 记录内容

- 客户端连接和断开连接的信息（包括客户端IP、用户名）

- 客户端执行的所有SQL语句（包括SELECT、INSERT、UPDATE、DELETE等）

- 服务器端执行的 administrative 命令

2. 配置方法

[mysqld]
# 启用通用查询日志
general_log = 1

# 日志文件路径
general_log_file = /var/log/mysql/general.log

# 日志输出方式：FILE（文件）或 TABLE（mysql.general_log表）
log_output = FILE

3. 动态开启/关闭

-- 查看当前状态
SHOW VARIABLES LIKE 'general_log';
SHOW VARIABLES LIKE 'general_log_file';

-- 临时开启
SET GLOBAL general_log = 1;

-- 临时关闭
SET GLOBAL general_log = 0;

-- 临时修改日志文件
SET GLOBAL general_log_file = '/var/log/mysql/new_general.log';

4. 使用场景

- 调试特定问题时临时开启，追踪应用程序执行的SQL语句

- 审计特定时间段的数据库操作

- 排查应用程序与数据库的交互问题

三、慢查询日志（Slow Query Log）

慢查询日志记录执行时间超过指定阈值的SQL语句，是性能优化的重要工具，帮助识别需要优化的低效查询。

1. 记录内容

- 执行时间超过long_query_time阈值的SQL语句

- 未使用索引的SQL语句（需开启log_queries_not_using_indexes）

- 查询的执行时间、锁定时间、扫描行数、返回行数等信息

- 执行查询的用户和主机信息

2. 配置方法

[mysqld]
# 启用慢查询日志
slow_query_log = 1

# 慢查询日志文件路径
slow_query_log_file = /var/log/mysql/slow.log

# 慢查询时间阈值（秒），默认10秒
long_query_time = 2

# 记录未使用索引的查询
log_queries_not_using_indexes = 1

# 记录管理语句（如ALTER TABLE, ANALYZE TABLE等）
log_slow_admin_statements = 1

# 日志输出方式：FILE或TABLE（mysql.slow_log表）
log_output = FILE

3. 动态配置

-- 临时开启慢查询日志
SET GLOBAL slow_query_log = 1;

-- 修改慢查询阈值（单位：秒，支持小数）
SET GLOBAL long_query_time = 1.5;

-- 开启记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 1;

4. 分析慢查询日志

① 使用mysqldumpslow工具（MySQL自带）：

-- 查看使用最多的10条慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

-- 查看执行时间最长的10条慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

-- 查看扫描行数最多的10条慢查询
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

② 使用pt-query-digest（Percona Toolkit工具）：

pt-query-digest /var/log/mysql/slow.log > slow_query_analysis.txt

四、二进制日志（Binary Log）

二进制日志（binlog）记录所有数据修改操作（如INSERT、UPDATE、DELETE），不记录查询操作（如SELECT）。主要用于数据恢复和主从复制，是MySQL高可用架构的基础组件。

1. 记录内容

- 数据库的所有修改操作（CRUD中的CUD操作）

- 操作的时间、日志位置等元数据

- 不记录SELECT、SHOW等只读操作

2. 二进制日志格式

① STATEMENT格式（基于语句）：

记录执行的SQL语句，日志体积小，但某些函数（如NOW()、UUID()）可能导致主从数据不一致。

② ROW格式（基于行）：

记录每行数据的变化，能保证主从数据一致，但日志体积较大。MySQL 5.7+默认使用该格式。

③ MIXED格式（混合模式）：

默认使用STATEMENT格式，对可能导致不一致的操作自动切换为ROW格式。

3. 配置方法

[mysqld]
# 启用二进制日志
log_bin = /var/log/mysql/binlog

# 服务器ID，主从复制必须配置，每个实例唯一
server-id = 1

# 二进制日志格式：STATEMENT, ROW, MIXED
binlog_format = ROW

# 二进制日志过期时间（天）
expire_logs_days = 7

# 单个二进制日志文件大小限制（默认1GB）
max_binlog_size = 100M

# 同步写入磁盘（安全性高，性能略低）
sync_binlog = 1

4. 二进制日志管理

-- 查看二进制日志列表
SHOW BINARY LOGS;

-- 查看当前使用的二进制日志
SHOW MASTER STATUS;

-- 查看二进制日志内容（需指定日志文件）
SHOW BINLOG EVENTS IN 'binlog.000001';

-- 刷新二进制日志（创建新的日志文件）
FLUSH BINARY LOGS;

-- 删除指定日志文件之前的所有日志
PURGE BINARY LOGS TO 'binlog.000005';

-- 删除指定日期之前的所有日志
PURGE BINARY LOGS BEFORE '2023-10-01 00:00:00';

-- 临时关闭二进制日志（当前会话）
SET SQL_LOG_BIN = 0;

-- 重新开启二进制日志（当前会话）
SET SQL_LOG_BIN = 1;

5. 二进制日志的应用

① 数据恢复：

-- 使用mysqlbinlog工具恢复数据
mysqlbinlog --start-datetime="2023-10-01 08:00:00" --stop-datetime="2023-10-01 09:00:00" /var/log/mysql/binlog.000001 | mysql -u root -p

-- 按位置恢复
mysqlbinlog --start-position=107 --stop-position=490 /var/log/mysql/binlog.000001 | mysql -u root -p

② 主从复制：二进制日志是主从复制的基础，主库通过二进制日志将数据变化同步到从库。

五、中继日志（Relay Log）

中继日志是从库（Slave）在主从复制过程中使用的日志，用于存储从主库（Master）获取的二进制日志内容，然后由从库的SQL线程应用这些日志。

1. 工作原理

- 从库的IO线程从主库读取二进制日志，写入本地中继日志

- 从库的SQL线程读取中继日志，执行其中的SQL语句，实现数据同步

- 中继日志格式与二进制日志完全相同

2. 配置方法

[mysqld]
# 中继日志文件路径
relay_log = /var/log/mysql/relaylog

# 中继日志索引文件
relay_log_index = /var/log/mysql/relaylog.index

# 中继日志自动删除（默认开启）
relay_log_purge = 1

# 从库延迟复制（秒）
delay_key_write = ALL

3. 中继日志管理

-- 查看从库状态（包含中继日志信息）
SHOW SLAVE STATUS\G

-- 手动删除中继日志
RESET SLAVE;  -- 停止复制并删除中继日志

-- 重置从库配置（保留中继日志）
RESET SLAVE ALL;  -- MySQL 5.5.16+

六、事务日志（InnoDB Logs）

InnoDB存储引擎有自己的事务日志，包括重做日志（Redo Log）和撤销日志（Undo Log），用于保证事务的ACID特性和崩溃恢复。

1. 重做日志（Redo Log）

重做日志记录InnoDB存储引擎的所有数据修改操作，用于崩溃恢复，确保已提交的事务不会丢失（持久性）。

（1）特点

- 物理日志，记录数据页的修改

- 循环写入，固定大小，不会无限增长

- 写入速度快，优先于数据文件写入

（2）配置方法

[mysqld]
# 重做日志文件路径（默认在数据目录）
innodb_log_group_home_dir = /var/lib/mysql/

# 单个重做日志文件大小
innodb_log_file_size = 500M

# 重做日志文件数量（通常为2）
innodb_log_files_in_group = 2

# 日志刷盘策略：1-事务提交时刷盘（最安全），0-每秒刷盘，2-提交时写入OS缓存
innodb_flush_log_at_trx_commit = 1

2. 撤销日志（Undo Log）

撤销日志记录数据修改前的状态，用于事务回滚和多版本并发控制（MVCC）。

（1）特点

- 逻辑日志，记录数据修改前的状态

- 用于事务回滚和读取历史版本数据

- MySQL 5.6+支持独立的撤销表空间

（2）配置方法

[mysqld]
# 启用独立撤销表空间（MySQL 5.6+）
innodb_undo_directory = /var/lib/mysql/undo/
innodb_undo_logs = 128  # 撤销日志段数量
innodb_undo_tablespaces = 4  # 独立撤销表空间文件数量

# 自动截断undo日志（MySQL 5.7+）
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 1G  # 触发截断的大小