抱歉，您的浏览器无法访问本站
本页面需要浏览器支持（启用）JavaScript
了解详情 >

日志管理

4.4k 字

16 分钟

次

条

摘要：本文学习了数据库常见的日志类型及其作用。

环境

Windows 10 企业版 LTSC 21H2
MySQL 5.7.40
Perl 5.40.2.1

1 日志类型

1.1 错误日志（Error log）

1.1.1 说明

错误日志主要记录服务器的启动和关闭，以及在运行过程中发生的错误和警告相关信息。

默认开启，将主机名作为错误日志文件名保存在数据目录。

1.1.2 配置

修改配置文件：

my.ini
1
2
3
[mysqld]
# 设置错误日志的位置
log_error=mysql.err

1.2 一般查询日志（General query log）

1.2.1 说明

一般查询日志又称为通用查询日志，是记录最详细的日志，当客户端建立连接或断开连接时，服务器将信息写入此日志，并记录客户端执行的每个SQL语句。

默认关闭，开启通用查询日志会增加很多磁盘IO，所以不建议开启，当需要调试排错时才会开启。

1.2.2 配置

修改配置文件：

my.ini
1
2
3
4
5
[mysqld]
# 设置为1启用一般查询日志，设置为0禁用一般查询日志，默认禁用
general_log=1
# 设置一般查询日志的位置
general_log_file=mysql.log

临时修改，不支持会话修改，只能全局修改：

sql
1
set global general_log=off;

查看配置：

sql
1
show variables like 'general_log%';

1.3 慢查询日志（Slow query log）

1.3.1 说明

慢查询日志用来记录执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。

默认关闭，一般建议开启，它对服务器性能的影响微乎其微，但是可以记录服务器上的慢查询语句，帮助定位性能问题。

1.3.2 配置

修改配置文件：

my.ini
1
2
3
4
5
6
7
8
9
[mysqld]
# 设置为1启用慢查询日志，设置为0禁用慢查询日志，默认禁用
slow_query_log=1
# 设置慢查询日志的方式，file表示存储在文件，table表示存储在mysql.slow_log表，支持同时设置两种，默认是file
log_output=file,table
# 设置慢查询日志的位置
slow_query_log_file=mysql-slow.log
# 设置超时时间，单位是秒，默认值是10秒
long_query_time=10

临时修改，不支持会话修改，只能全局修改：

sql
1
set global slow_query_log=off;

查看配置：

sql
1
show variables like 'slow_query_log%';

1.4 二进制日志（Binary log）

1.4.1 说明

二进制日志也叫作变更日志，主要用于记录修改数据或有可能引起数据改变的SQL语句，以事件形式记录并保存在二进制文件中。

默认关闭，通常用于数据恢复和主从复制。

1.4.2 配置

修改配置文件：

my.ini
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[mysqld]
# 设置服务器唯一标识，每台服务器必须不同
server-id=1
# 设置二进制日志的位置，会在名称后附加数字扩展名
log_bin=mysql-bin
# 设置二进制日志索引文件的位置，会在名称后附加.index扩展名
log_bin_index=mysql-bin
# 设置忽略的数据库
binlog-ignore-db=mysql
# 设置同步的数据库
binlog-do-db=test
# 设置二进制日志的最大值，默认值是1GB，并不能严格控制日志的大小，最后的事务可能超出
max_binlog_size=1G
# 设置二进制日志自动删除的过期天数，默认值为0，表示不会自动删除，取值范围是0到99
expire_logs_days=0

临时修改，不支持全局修改，只能会话修改：

sql
1
set sql_log_bin=off;

查看配置：

sql
1
show variables like 'log_bin%';

查看二进制日志列表：

sql
1
show master logs;

创建二进制日志：

  • 在日志内容超过最大值或者重启服务器后，自动滚动生成新的日志文件，并将数字扩展名累加。
  • 也可以通过命令手动滚动生成新的日志文件：
    sql
    1
    flush logs;

清除二进制日志：

sql
1
2
purge master logs to '文件名称';
purge master logs before '日期';

重置主库，清除二进制日志并重置序号，如果已经存在从库，会导致从库同步报错：

sql
1
reset master;

1.4.3 日志格式

支持三种格式：

  • statment：基于sql语句的日志记录，记录的是SQL语句，对数据进行修改的SQL都会记录在日志文件中。优缺点：
    • 优点：不需要记录每一行的变化，减少了日志量，节约了IO，从而提高了性能。
    • 缺点：在某些情况下的数据不一致，比如将now()等函数得到的结果插入数据库。
  • row：基于行的日志记录，记录的是数据变更，不记录每条SQL语句的上下文信息。优缺点：
    • 优点：不会出现某些情况下的数据不一致的问题。
    • 缺点：会产生大量的日志。
  • mixed：基于statment和row两种模式的混合，一般的复制使用statement模式，对于statement模式无法复制的操作使用row模式。

默认使用ROW格式。

修改配置文件：

my.ini
1
2
3
[mysqld]
# 设置二进制日志格式
binlog_format=row

临时修改：

sql
1
2
set binlog_format='row';
set global binlog_format='row';

查看配置：

sql
1
show variables like 'binlog_format';

1.4.4 刷盘策略

系统会为每个线程分配一块内存作为缓存，在执行SQL命令时，将日志写入缓存，在提交事务时，将缓存里的日志写入磁盘文件。

刷盘的时机可以通过sync_binlog参数控制：

  • 0：事务提交只写缓存，由系统自行判断什么时候刷盘。
  • 1：事务提交会执行刷盘。
  • N：事务提交只写缓存，当累计N个事务后才刷盘。

修改配置文件：

my.ini
1
2
3
[mysqld]
# 设置刷盘策略
sync_binlog=0

临时修改，不支持会话修改，只能全局修改：

sql
1
set global sync_binlog=0;

查看配置：

sql
1
show variables like 'sync_binlog';

1.5 中继日志（Relay log）

1.5.1 说明

中继日志保存了主服务器的二进制日志，SQL线程会根据中继日志的内容进行同步，从而使从服务器和主服务器的数据保持一致。

默认关闭，主要用于主从复制，需要联合使用二进制日志和中继日志。

1.5.2 配置

修改配置文件：

my.ini
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[mysqld]
# 设置中继日志的位置
relay_log=relay-bin
# 设置中继日志索引文件的位置，会在名称后附加.index扩展名
relay_log_index=relay-bin
# 设置主库二进制日志同步的数据，file表示存储在文件，table表示存储在mysql.slave_master_info表，支持同时设置两种
master_info_repository=table
# 设置从库中继日志同步的数据，file表示存储在文件，table表示存储在mysql.slave_relay_log_info表，支持同时设置两种
relay_log_info_repository=table
# 设置中继日志的最大值，默认值是1GB
max_relay_log_size=1G
# 设置是否自动清空中继日志，设置为1表示启用，默认启用
relay_log_purge=1
# 设置是否放弃未执行的中继日志，设置为0表示当从库宕机后重新从主库获取日志，保证完整性，默认关闭
relay_log_recovery=0

重置从库，清除中继日志，如果已经存在从库，会删除主库关联：

sql
1
reset slave;

1.6 DDL日志（DDL log）

DDL日志记录由DDL语句执行的元数据操作，使用此日志从元数据操作中间发生的崩溃中恢复。

DDL日志最多可容纳4GB的条目，超过此限制后必须先重命名或删除文件，然后才能执行DDL语句。

1.7 回滚日志（Undo log）

1.7.1 说明

回滚日志是InnoDB存储引擎在执行增删改的SQL命令时产生的逻辑日志，用来保证事务的原子性，主要用于事务回滚和多版本并发控制。

回滚日志是逻辑日志，记录数据被修改前的值。

回滚日志主要分为两种：

  • insert undo log：当执行insert的时候，产生的回滚日志只在回滚时需要，在事务提交后，可被立即删除。因为insert操作只是对本事务可见，其他事务不可见，所以当事务提交后，对应的回滚日志就会被系统直接删除回收，占用的链表也被释放。
  • update undo log：当执行update和delete的时候，产生的回滚日志不仅在事务回滚时需要，在多版本并发控制的快照读时也需要，所以不能在事务提交后马上删除，只在提交后放入链表，等待purge线程进行最后的删除。

1.7.2 原理

在执行增删改的SQL命令前，会在回滚日志中记录操作前的数据，并且会记录三个隐藏字段：

  • DB_TRX_ID：最近修改事务ID，记录最近插入或者修改这条记录的事务ID。
  • DB_ROLL_PTR：回滚指针，记录最近修改这条记录的上一版本，通过这个字段可以在回滚日志中找到最近的历史版本。
  • DB_ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

在对同一条记录进行增删改操作时，会在回滚日志中生成一系列这条记录的版本链，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

当发生回滚时，根据记录的操作前的数据进行回滚。

1.8 重做日志（Redo log）

1.8.1 说明

重做日志是InnoDB存储引擎在执行增删改的SQL命令时产生的物理日志，用来保证事务的持久性，主要用于掉电等故障恢复。

重做日志是物理日志，记录的是物理数据页修改后的信息。

1.8.2 原理

在执行增删改的SQL命令后，会将重做日志写入到缓存中，记录数据被修改后的值。在提交事务前，会将缓存中的重做日志写入到磁盘上的日志文件，然后将提交事务并将改动保存到数据库。

重做日志采用循环写入机制，写满一个文件后就自动切换到下一个文件，直到所有文件都写满后又回到第一个文件，覆盖之前的内容。

1.8.3 两阶段提交

在提交事务时，需要记录重做日志和二进制日志，为了解决日志之间的逻辑一致问题，使用了两阶段提交的方案。

提交事务前的两阶段提交流程：

  • 将缓存里的重做日志写入到磁盘文件，并标记为prepare阶段。
  • 将缓存里的二进制日志写入到磁盘文件。
  • 将重做日志中的状态改为commit阶段。

如果没有使用两阶段提交，在宕机时会出现数据不一致问题：

  • 假设先写入重做日志，在写入二进制日志前发生宕机，重启后根据重做日志执行操作并提交事务，此时二进制日志没有记录该事务，数据不一致。
  • 假如先写入二进制日志，在写入重做日志前发生宕机，重启后根据重做日志没有执行操作，此时二进制日志已经记录该事务，数据不一致。

如果使用了两阶段提交，在宕机时就能避免数据不一致问题：

  • 假设在写入二进制日志前发生宕机，重启后根据重做日志执行操作，在二进制日志找不到对应记录，回滚事务，数据一致。
  • 假如在写入二进制日志后发生宕机，重启后根据重做日志执行操作，在二进制日志能找到对应记录，提交事务，数据一致。

1.8.4 刷盘策略

在将缓存的重做日志写入到磁盘上时，先将重做日志写入到操作系统的缓存中，然后由操作系统将重做日志写到日志文件。

刷盘的时机可以通过innodb_flush_log_at_trx_commit参数控制：

  • 0（延迟写）：事务提交不会写入系统缓存，而是每隔1秒写入系统缓存并调用操作系统刷盘。当系统崩溃时会丢失数据。
  • 1（实时写并且实时刷盘）：事务提交会写入系统缓存并调用操作系统刷盘。当系统崩溃时不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能差。
  • 2（实时写但是延时刷盘）：事务提交只写入系统缓存，每隔1秒调用操作系统刷盘。当系统崩溃时会丢失数据。

修改配置文件：

my.ini
1
2
3
[mysqld]
# 设置刷盘策略
innodb_flush_log_at_trx_commit=0

临时修改，不支持会话修改，只能全局修改：

sql
1
set global innodb_flush_log_at_trx_commit=0;

查看配置：

sql
1
show variables like 'innodb_flush_log_at_trx_commit';

2 慢查询分析

2.1 配置

慢查询分析是通过分析慢查询日志，结合explain进行全面分析。

查看慢查询日志的配置：

sql
1
show variables like 'slow_query_log%';

2.2 安装Perl

在生产环境中，手工查找分析SQL显然是个体力活，建议使用mysqldumpslow日志分析工具。

在Windows系统中使用mysqldumpslow需要先安装Perl环境，有ActiveState Perl和Strawberry Perl两种：

  • ActiveState Perl：ActiveState为Perl提供了一个免费的社区版本和一个商业支持的版本。
  • Strawberry Perl：专为Windows设计的100%开源的Perl实现，使用CPAN模块不需要二进制包。

为了使用方便，在Windows上安装Strawberry Perl，官网地址：http://strawberryperl.com

安装包分为安装版和便携版，安装版会在安装时自动配置环境变量，便携版需要手动配置环境变量，建议下载安装版并按提示进行安装。

安装结束后在cmd命令行执行perl -v命令查看版本：

cmd
1
2
3
4
5
6
7
8
9
10
This is perl 5, version 40, subversion 2 (v5.40.2) built for MSWin32-x64-multi-thread

Copyright 1987-2025, Larry Wall

Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.

Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl".  If you have access to the
Internet, point your browser at https://www.perl.org/, the Perl Home Page.

说明Perl环境安装成功。

2.3 查看SQL

进入MySQL安装目录的bin目录。

语法：

cmd
1
perl mysqldumpslow.pl [选项] 日志位置

选项：

  • -s 参数：表示按照何种方式排序。
  • -s c：表示按照访问次数排序。
  • -s l：表示按照锁定时间排序。
  • -s r：表示按照返回时间排序。
  • -s t：表示按照查询时间排序。
  • -s al：表示按照平均访问次数排序。
  • -s ar：表示按照平均返回时间排序。
  • -s at：表示按照平均查询时间排序。
  • -t n：表示返回前面n条的数据。
  • -g 表达式：后边搭配一个正则匹配模式，大小写不敏感。

查询返回结果最慢的10个SQL：

cmd
1
perl mysqldumpslow.pl -s t -t 10 D:\Work\MySQL\mysql-5.7.40\data\mysql-slow.log

查询访问次数最多的10个SQL：

cmd
1
perl mysqldumpslow.pl -s c -t 10 D:\Work\MySQL\mysql-5.7.40\data\mysql-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句：

cmd
1
perl mysqldumpslow.pl -s t -t 10 -g "left join" D:\Work\MySQL\mysql-5.7.40\data\mysql-slow.log

另外建议在使用这些命令时结合more使用，否则有可能出现爆屏情况：

cmd
1
perl mysqldumpslow.pl -s r -t 10 D:\Work\MySQL\mysql-5.7.40\data\mysql-slow.log | more

2.4 分析SQL

使用explain工具分析慢查询SQL的索引使用情况，根据结果优化查询，缩短查询时间。

3 误操作恢复

3.1 配置

查看二进制日志的配置：

sql
1
show variables like 'log_bin%';

查看日志列表：

sql
1
show master logs;

3.2 查看内容

3.2.1 使用命令查看

使用SQL命令查看日志内容：

sql
1
2
3
4
show binlog events
in '文件名'
[from 位置]
[limit 起始编号, 查询条数];

示例：

sql
1
2
3
4
show binlog events
in 'mysql-bin.000001'
from 123
limit 10;

结果中每条记录的Pos字段就是该记录的位置。

3.2.2 使用工具查看

使用mysqlbinlog工具查看日志内容：

cmd
1
mysqlbinlog [选项] 文件位置

选项：

  • –no-defaults：不要读取任何选项文件，如果使用时有如下报错mysqlbinlog: [ERROR] unknown variable可以使用该选项绕过。
  • –base64-output=decode-rows -v：将BINLOG语句解码为SQL语句进行显示。
  • –base64-output=decode-rows -vv：将BINLOG语句解码为SQL语句进行显示，同时显示字段数据类型和某些元数据的注释。
  • –start-position：设置开始位置。
  • –stop-position：设置结束位置。
  • –start-datetime：设置开始日期，优先级比位置低。
  • –stop-datetime：设置结束日期，优先级比位置低。
  • –database：设置数据库。

示例：

cmd
1
mysqlbinlog --no-defaults --base64-output=decode-rows -v D:\Work\MySQL\mysql-5.7.40\data\mysql-bin.000001

结果中每行以# at开头的数字就是该条记录的位置。

3.3 恢复数据

使用mysqlbinlog工具恢复数据：

cmd
1
mysqlbinlog [选项] 文件位置 | mysql –u用户名 -p[密码];

示例：

cmd
1
mysqlbinlog --no-defaults --stop-position=1038 D:\Work\MySQL\mysql-5.7.40\data\mysql-bin.000001 | mysql -uroot -p

设置结束时间和结束位置时，需要选择事务提交之后的记录，才能保证最后执行的SQL命令被提交到了服务器。

日志管理
https://momashanhe.com/posts/20250611/004203/
本文作者
墨码山河
发布于
2025年6月11日
更新于
2025年6月21日
许可协议
CC BY-NC-SA 4.0
博文难免存在纰漏，如有错误之处，无论大小，恳请读者指正，博主不胜感激。
数据库

MySQL

评论