一、MySQL日志类型

MySQL主要有以下日志：

  • 错误日志（Error Log）
  • 二进制日志（Binary Log）
  • 中继日志（Relay Log）
  • 慢查询日志（Slow Query Log）
  • 一般查询日志（General Query Log）
  • 重做日志（Redo Log）
  • 回滚日志（Undo Log）

二、错误日志

作用

记录MySQL启动、运行、停止过程中的错误信息。

配置

1
2
3
4
5
-- 查看错误日志位置
SHOW VARIABLES LIKE 'log_error';

-- 查看错误日志级别
SHOW VARIABLES LIKE 'log_error_verbosity';

使用场景

  • 排查启动失败
  • 定位运行时错误
  • 查看警告信息

三、二进制日志

作用

记录所有修改数据的SQL语句，用于主从复制和数据恢复。

格式

STATEMENT：记录SQL语句
ROW：记录行数据变化
MIXED：混合模式

1
2
3
4
5
-- 查看binlog格式
SHOW VARIABLES LIKE 'binlog_format';

-- 设置格式
SET binlog_format = 'ROW';

配置

1
2
3
4
5
6
7
8
-- 开启binlog
SHOW VARIABLES LIKE 'log_bin';

-- binlog位置
SHOW VARIABLES LIKE 'log_bin_basename';

-- binlog保留时间
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

使用场景

主从复制：

  • 主库写入binlog
  • 从库读取并执行

数据恢复：

  • 全量备份 + binlog恢复到指定时间点

常用命令

1
2
3
4
5
6
7
8
-- 查看binlog列表
SHOW BINARY LOGS;

-- 查看binlog内容
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 使用mysqlbinlog工具
-- mysqlbinlog mysql-bin.000001

四、中继日志

作用

从库接收主库binlog后存储为中继日志。

配置

1
2
3
4
5
-- 中继日志位置
SHOW VARIABLES LIKE 'relay_log';

-- 中继日志索引
SHOW VARIABLES LIKE 'relay_log_index';

复制流程

  1. 主库写入binlog
  2. 从库I/O线程读取binlog写入relay log
  3. 从库SQL线程读取relay log执行

五、慢查询日志

作用

记录执行时间超过阈值的SQL语句。

配置

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 开启慢查询日志
SHOW VARIABLES LIKE 'slow_query_log';
SET slow_query_log = ON;

-- 慢查询阈值（秒）
SHOW VARIABLES LIKE 'long_query_time';
SET long_query_time = 1;

-- 日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 记录未使用索引的查询
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

分析工具

1
2
3
4
5
6
7
# 使用mysqldumpslow分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 参数说明
-s t：按查询时间排序
-s c：按查询次数排序
-t 10：显示前10条

使用场景

  • 发现慢SQL
  • 优化查询性能
  • 索引优化依据

六、一般查询日志

作用

记录所有客户端连接和执行的SQL语句。

配置

1
2
3
4
5
6
-- 开启一般查询日志
SHOW VARIABLES LIKE 'general_log';
SET general_log = ON;

-- 日志位置
SHOW VARIABLES LIKE 'general_log_file';

注意：会记录所有SQL，影响性能，生产环境慎用。

七、重做日志（Redo Log）

作用

保证事务持久性，崩溃恢复时重放操作。

特点

  • InnoDB特有
  • 循环写入，固定大小
  • WAL机制（Write-Ahead Logging）

配置

1
2
3
4
5
6
7
8
-- redo log文件位置
SHOW VARIABLES LIKE 'innodb_log_group_home_dir';

-- redo log文件大小
SHOW VARIABLES LIKE 'innodb_log_file_size';

-- redo log文件数量
SHOW VARIABLES LIKE 'innodb_log_files_in_group';

写入机制

  1. 事务修改数据
  2. 先写入redo log buffer
  3. 根据配置刷入磁盘
1
2
3
4
5
6
-- 刷盘策略
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- 0：每秒刷盘
-- 1：每次提交刷盘（推荐）
-- 2：每次提交写入os cache，每秒刷盘

刷盘时机

  • 事务提交时
  • log buffer空间不足
  • 后台线程定时刷盘
  • 正常关闭MySQL时
  • checkpoint时

八、回滚日志（Undo Log）

作用

保证事务原子性，用于回滚和MVCC。

功能

回滚：

  • 记录数据修改前的值
  • 事务回滚时恢复数据

MVCC：

  • 保存数据历史版本
  • 实现非阻塞读

存储位置

MySQL 5.6之前：共享表空间
MySQL 5.6之后：可配置独立表空间
MySQL 8.0之后：存储在undo表空间

1
2
-- 查看undo表空间
SHOW VARIABLES LIKE 'innodb_undo_tablespaces';

删除时机

当没有事务需要回滚读时，后台线程清理。

九、日志总结

Server层日志

日志 作用
错误日志 记录错误信息
二进制日志 主从复制、数据恢复
中继日志 从库复制
慢查询日志 性能优化
一般查询日志 审计（慎用）

InnoDB日志

日志 作用
Redo Log 崩溃恢复，保证持久性
Undo Log 回滚，保证原子性

写入顺序

  1. 数据修改时写入Redo Log和Undo Log
  2. 事务提交时写入Binlog
  3. 后台线程将内存数据刷入磁盘

十、总结

MySQL日志要点：

  • 错误日志：排查问题
  • Binlog：主从复制、数据恢复
  • Redo Log：崩溃恢复
  • Undo Log：回滚和MVCC
  • 慢查询日志：性能优化

理解各类日志的作用和使用场景，是数据库运维和优化的基础。