MySQL日志管理

樊杨杨

于 2025-08-19 22:27:59 发布

阅读量651

点赞数 24

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： mysql adb

本文链接：https://blog.csdn.net/Fyy20021007/article/details/150535930

数据库专栏收录该内容

11 篇文章

订阅专栏

MySQL日志管理

1 为什么需要日志

用于排错
用来做数据分析
了解程序的运行情况，了解MySQL的性能

2 日志作用

在数据库保存数据时，有时候不可避免会出现数据丢失或者被破坏，这样情况下，就必须保证数据的安全性和完整性，则需要使用日志来查看或者恢复数据。

3 日志文件查看方法

由于多种安装mysql的方法可能导致文件存储位置和名称不同，需要先通过mysql配置文件确定错误日志文件位置及名称

C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
……
datadir=C:/ProgramData/MySQL/MySQL Server 8.0\Data   # 注意此处
log-error="LEGION.err"       # 注意此处
# 位置：C:\ProgramData\MySQL\MySQL Server 8.0\Data\LEGION.err

查看mysql数据目录中日志文件: C:\ProgramData\MySQL\MySQL Server 8.0\Data

4 常见日志类型之错误日志

错误日志记录以下信息：
- mysql服务器启动\运行\关闭过程中的信息如配置文件语法错误、磁盘空间不足等。
查看本机错误日志

# 查看错误日志位置及文件名，一般以主机名.err方式命名
mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| log_error     | .\LEGION.err |
+---------------+--------------+
1 row in set, 1 warning (0.01 sec)

错误日志信息需要注意三类：[System]、[Warning]、[Error]
注意：my.ini中可以用log-error=file_name 选项来指定mysqld保存错误日志文件的位置。

5 常见日志类型之通用查询日志general_log log_error slow_query_log log_bin relay_log

记录所有客户端连接和执行的 SQL 语句，包括查询、插入、更新和删除等操作。这对于调试应用程序与数据库的交互问题非常有用，管理员可以查看具体执行了哪些 SQL 语句，以及执行的顺序和时间。默认不开启
查看设置状态

mysql> show  global variables like "%general_log%";
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| general_log      | OFF        |       # 默认关闭
| general_log_file | LEGION.log |       # 存储位置
+------------------+------------+

启动记录查询日志

mysql> set global general_log=on;
mysql> show  global variables like "%general_log%";
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| general_log      | ON         |
| general_log_file | LEGION.log |
+------------------+------------+
C:\ProgramData\MySQL\MySQL Server 8.0\Data\LEGION.log
# 注意这是临时更改，若永久更改则在my.ini中配置general-log=1，并重启服务
# 注意可以在my.ini中设置general_log_file=路径/文件名 的形式设置永久更改存储位置

设置日志记录类型（表/文本文件/空）

mysql> show  variables like "%log_output%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
# 注意可以在my.ini中设置log-output={TABLE|FILE|NONE}类型
当set  global log_output="TABLE";
通用日志记录在mysql.general_log表中执行语句时argument字段默认是以BLOB二进制存储的，可通过convert()来进行编码转换。
select  convert(argument using utf8mb4)  from mysql.general_log;

注意：
- 要启用通用查询日志，需要至少配置general-log=1，log-output={TABLE|FILE}
- general_log_file如果没有指定，默认名是：主机名.log
- 默认通用查询日志是不开启的，因为会消耗大量的磁盘空间、CPU以及内存，所以当需要通过查询日志还原操作场景准确定位问题时可以短时间开启

# 可以通过下面的查询测试查询日志的文件变化
mysql> use  mydb9_stusys
mysql> select student.sno,student.sname,student.ssex,student.sage,course.cno,teacher.tno,teacher.tname,score from student,sc,course,teacher where student.sno=sc.sno and sc.cno=course.cno and course.tno=teacher.tno;

C:\ProgramData\MySQL\MySQL Server 8.0\Data\LEGION.log  # 查看

关闭查询日志

mysql> set global general_log=0;

mysql> show  global variables like "%general_log%";
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| general_log      | OFF        |
| general_log_file | LEGION.log |
+------------------+------------+

6 常见日志类型之慢查询日志 slow_query_log

慢日志记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询
解释：若某条查询语句的执行时间过长，超过了设定的阈值则就会记录到慢日志中，可以对其进行后期select语句的优化
查看慢查询日志状态

mysql> show global  variables like "%slow_query_log%";
+---------------------+-----------------+
| Variable_name       | Value           |
+---------------------+-----------------+
| slow_query_log      | ON              |
| slow_query_log_file | LEGION-slow.log |
+---------------------+-----------------+

开启慢查询日志

mysql> set global slow_query_log=1;   # 0为关闭
C:\ProgramData\MySQL\MySQL Server 8.0\Data\LEGION-slow.log

# 注意：为了服务器调优，建议开启

慢日志的时间阈值

mysql> show  global variables like "long_query_time";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |  # 默认10秒最小为0,精度可以到微秒
+-----------------+-----------+
set  long_query_time=1 在当前会话生效 set global long_query_time=1;


mysql> create table passwd(id int,pwds varchar(50));  
delimiter  $$
create procedure proc02()  begin declare i int default 1;  while(i<10000)  do insert into passwd values(i, md5(i)); set i=i+1;  end while;  end  $$

delimiter ;
mysql> call proc02();

7 常见日志类型之撤销日志

Undo-log日志：即撤销日志或回滚日志，Undo即撤销的意思，在日常开发过程中，如果代码敲错了，一般会习惯性的按下Ctrl+Z撤销，而Undo-log的作用也是如此，但它是用来给MySQL撤销SQL操作的。
Undo-log日志记录内容：
- 若是insert插入操作，则生成一个对应的delete操作。
- 若是delete删除操作，在 InnoDB 存储引擎中，当使用 DELETE 操作时，实际上并不会立即从磁盘上物理删除数据，而是将隐藏字段 deleted_bit 标记为 1 ，表示该行数据已被逻辑删除。后续在合适的时机（如进行垃圾回收等操作）才会真正物理删除这些数据。如果你想要将标记为已删除（deleted_bit = 1）的行恢复为未删除状态（deleted_bit = 0）
- 若是update修改操作，如：将性别从男改成了女，则就生成一个从女改回男的操作
- 可以理解为：当一条写入类型的SQL执行时，都会在Undo-log日志中生成相应的反SQL放入到Undo-log中
从mysql8.0.20版本开始存储位置：C:\ProgramData\MySQL\MySQL Server 8.0\Data\undo_001

通常情况下，undo log 的内容不能直接查看，原因如下：

格式复杂：undo log 是以二进制格式存储在磁盘上的，其内部结构和编码方式是 InnoDB 存储引擎私有的，没有公开的规范说明如何解析这些二进制数据。
系统设计：undo log 主要是供 InnoDB 存储引擎内部使用，用于事务回滚和 MVCC 版本管理，数据库设计时并没有提供直接访问和查看其内容的接口。

虽然无法直接查看 undo log 的内容，但可以间接了解 undo log 的相关信息：

SELECT * FROM performance_schema.events_transactions_current;

8 常见日志类重做redo_log 缓存

InnoDB引擎在设计时是基于磁盘存储数据的，当MySQL启动后就会在内存中创建一个BufferPool，运行过程中会将大量操作汇集在内存中进行，比如写入数据时，先写到内存中，然后由后台线程再刷写到磁盘。
虽然使用BufferPool提升了MySQL整体的读写性能，但它是基于内存的，也就意味着随着机器的宕机、重启，其中保存的数据会消失，当向内存中写入数据后，MySQL突然宕机了，则这条未刷写到磁盘的数据会丢失，也正由于该原因，Redo-log应运而生！
Redo-log：重做日志，是一种预写式日志，即在向内存写入数据前，会先写日志，当后续数据未被刷写到磁盘、MySQL崩溃时，就可以通过日志来恢复数据，确保所有提交的事务都会被持久化。
mysql8中Redo-log存储在C:\ProgramData\MySQL\MySQL Server 8.0\Data#innodb_redo目录下，由32个文件组成有两种类型的redo log文件，一种是当前正在使用的 #ib_redoN；另一种是空闲的文件名为 #ib_redoN_tmp，多加了个 _tmp 后缀。

mysql> select * from performance_schema.innodb_redo_log_files\G
*************************** 1. row ***************************
       FILE_ID: 118
     FILE_NAME: .\#innodb_redo\#ib_redo118
     START_LSN: 386428928
       END_LSN: 389703680
 SIZE_IN_BYTES: 3276800
       IS_FULL: 0
CONSUMER_LEVEL: 0

mysql> show global status like '%innodb%redo%';
+-------------------------------------+------------+
| Variable_name                       | Value      |
+-------------------------------------+------------+
| Innodb_redo_log_read_only           | OFF        |
| Innodb_redo_log_uuid                | 3813940893 |
| Innodb_redo_log_checkpoint_lsn      | 386572482  |
| Innodb_redo_log_current_lsn         | 386572482  |
| Innodb_redo_log_flushed_to_disk_lsn | 386572482  |
| Innodb_redo_log_logical_size        | 512        |
| Innodb_redo_log_physical_size       | 3276800    |
| Innodb_redo_log_capacity_resized    | 104857600  |
| Innodb_redo_log_resize_status       | OK         |
| Innodb_redo_log_enabled             | ON         |
+-------------------------------------+------------+

9 常见日志类型之二进制日志–重要

二进制日志作用：
- 记录所有更改数据的语句（insert、update、delete等），不记录查询语句
- 用于主从复制，因为从服务器需要到主服务器里拷贝二进制日志，然后根据二进制日志的内容去执行SQL语句，从而达到主从服务器里的数据一模一样；
- 用于恢复数据
- 日志审计的场景：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击（mysql注入攻击：黑客可以提交一段数据库查询代码，根据程序返回的结果，获得某些想要得到的数据）

查看二进制日志状态

mysql> show  variables like "%log_bin%";

日志查看

# 查看有哪些二进制日志文件：
mysql> show binary logs;    # 显示名称、容量单位字节、加密与否
# 查看当前正在使用的是哪一个二进制日志文件
mysql> show master status;
# 查看二进制日志内容：
mysql> show binlog events in "LEGION-bin.000008";

# 使用命令mysqlbinlog查看二进制日志内容\linux /var/lib/mysql/mysql-bin.xxxxxxxx
# 打开命令提示符
C:\Users\Administrator>cd  C:\ProgramData\MySQL\MySQL Server 8.0\Data
C:\ProgramData\MySQL\MySQL Server 8.0\Data>mysqlbinlog  LEGION-bin.000008 > test.sql

分割日志：新的操作会记录的新的日志文件中

PS C:\Users\Administrator> mysqladmin flush-logs  -u root -p
Enter password: ******
PS C:\Users\Administrator>  mysql -u root -p 
Enter password: 

mysql> create database  mydb12_journal;   # 新建数据库
mysql> show master status;

# 打开命令提示符
C:\Users\Administrator>cd  C:\ProgramData\MySQL\MySQL Server 8.0\Data
C:\ProgramData\MySQL\MySQL Server 8.0\Data>mysqlbinlog   LEGION-bin.00000？

日志刷新
- 作用：通过刷新日志进行更新日志，对缓存数据进行磁盘I/O，并强制 mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）
- 由于日志的记录不是直接写入日志文件中而是使用日志缓存的方式，当频繁执行多条修改的sql语句时，为了避免对磁盘频繁I/O，会将日志记录写入到内存的特殊空间即日志缓存中，之后每隔一个固定时间间隔将缓存的日志写入到磁盘文件中
- 刷新日志命令格式
```
mysql> flush  logs  # 会产生新日志文件
mysql> show master status;
```
```
# 在shell中，通过mysqladmin命令执行日志刷新：
mysqladmin flush-logs  -u 账户名  -p
mysqladmin refresh   -u 账户名  -p
PS C:\Users\Administrator> mysqladmin flush-logs  -uroot -p
Enter password: ******

PS C:\Users\Administrator> mysql -u root -p
Enter password: ******
mysql> show master status;
```
不小心删库后应该跑路吗？
- 如果在线上真的删库了，哪就先别想着跑路，你跑不掉！
- bin-log日志中会记录执行SQL的连接会话信息，同时一般规模较大的企业，都会搭建完善的监控系统，会监控服务的网络连接
- 因此当你删库后，他们可以顺着bin-log → session → network-connection这条线确定执行删库SQL的IP！如果你还未断开连接，直接通过MySQL的命令就能定位到删库的IP，因此基本上删库了，是可以定位到责任人的！
- 因此当你删库后，可以直接去本地找Bin-log的日志文件，然后拷贝出来一份，再打开最后一个文件，把里面删库的记录手动移除，再利用mysqlbinlog工具导出xx.SQL文件，最后执行该SQL文件即可恢复删库前的数据。

10 常见日志类型之中继日志

Relay-log：中继日志用于暂存和转发数据或日志等信息的一种机制，在单库中是见不到的，该类型的日志仅存在主从架构中的从机上
主从架构中的从机，其数据基本上都是复制主机bin-log日志同步过来并放在relay-log日志中，中继日志的作用就跟它的名字一样，仅仅只是作为主从同步数据的“中转站”。