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

日志管理

4k 字

14 分钟

次

条

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

环境

CentOS Linux release 7.6.1810
MySQL 5.7.40

1 日志类型

1.1 错误日志（Error log）

1.1.1 说明

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

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

1.1.2 配置

修改配置文件：

my.cnf
1
2
3
[mysqld]
# 设置错误日志的位置
log_error=/var/log/mysql/error.log

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

1.2.1 说明

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

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

1.2.2 配置

修改配置文件：

my.cnf
1
2
3
4
5
[mysqld]
# 设置为1启用一般查询日志，设置为0禁用一般查询日志，默认禁用
general_log=1
# 设置一般查询日志的位置
general_log_file=/var/log/mysql/query.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.cnf
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=/var/log/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.cnf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[mysqld]
# 设置服务器唯一标识，每台服务器必须不同
server-id=1
# 设置二进制日志的位置，会在名称后附加数字扩展名
log_bin=/var/log/mysql/mysql-bin
# 设置二进制日志索引文件的位置，会在名称后附加.index扩展名
log_bin_index=/var/log/mysql/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.cnf
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.cnf
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.cnf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[mysqld]
# 设置中继日志的位置
relay_log=/var/log/mysql/relay-bin
# 设置中继日志索引文件的位置，会在名称后附加.index扩展名
relay_log_index=/var/log/mysql/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命令前，会在回滚日志中记录操作前的数据，并且会在对应的行中使用roll_pointer隐藏字段记录最近修改这条记录的上一版本。

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

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

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.cnf
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 查看

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

语法：

bash
1
mysqldumpslow [选项] 日志位置

选项：

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

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

bash
1
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

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

bash
1
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

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

bash
1
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/slow.log

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

bash
1
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log | more

2.3 分析

使用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工具查看日志内容：

bash
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：设置数据库。

示例：

bash
1
mysqlbinlog --no-defaults --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001

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

3.3 恢复数据

使用mysqlbinlog工具恢复数据：

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

示例：

bash
1
mysqlbinlog --no-defaults --stop-position=1038 /var/log/mysql/mysql-bin.000001 | mysql -uroot -p

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

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

MySQL

评论