MySQL日志

发表于 2022-12-22 更新于 2023-11-10 分类于数据库阅读次数：本文字数： 1.3k

MySQL日志机制探究

1.错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

mysqld 使用错误日志名 host_name.err 并默认在参数 DATADIR(数据目录)指定的目录中写入日志文件。

host_name 为主机名。

2.二进制日志

二进制日志(BINLOG) 记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言) 语句，但是不包括数据查询语句。语句以 “事件” 的形式保存，它描述了数据的更改过程。此日志对于灾难时的数据恢复起着极其重要的作用。

它两个重要的应用场景：主从复制、数据恢复。

// 查看binlog
show binary logs;

通过 MySQL 自带的 mysqlbinlog 工具可以快速解析大量的 binlog 日志文件，如：

mysqlbinlog --no-defaults --database=school --base64-output=decode-rows
-v --start-datetime='2021-05-01 00:00:00' --stop-datetime='2021-05-10 00:00:00'
mysql-bin.000001 | more

3.查询日志

查询日志记录了客户端的所有语句。由于上线项目 SQL 语句特别多，开启查询日志 IO 太多导致 MySQL 效率低，只有在调试时才开启，比如通过查看 SQL 语句发现热点数据进行缓存。

show global variables like "%genera%";

4.慢查询日志

MySQL 可以设置慢查询日志，当 SQL 执行的时间超过我们设定的时间，那么这些 SQL 就会被记录在慢查询日志当中，然后我们通过查看日志，用 explain 分析这些 SQL 的执行计划，来判定为什么效率低下，是没有使用到索引？还是索引本身创建的有问题？或者是索引使用到了，但是由于表的数据量太大，花费的时间就是很长，那么此时我们可以把表分成 n 个小表，比如订单表按年份分成多个小表等。

慢查询日志的相关参数如下所示：

mysql> show variables like '%slow_query%';
+---------------------+--------------------------------------------+
| Variable_name       | Value                                      |
+---------------------+--------------------------------------------+
| slow_query_log      | OFF                                        |
| slow_query_log_file | E:\Application\MySQL\data\XiaoXin-slow.log |
+---------------------+--------------------------------------------+
2 rows in set, 1 warning (1.26 sec)

慢查询日志记录了包含所有执行时间超过参数 long_query_time(单位：秒) 所设置值的 SQL 语句的日志，在 MySQL 上用命令可以查看，如下：

mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.01 sec)

这个值是可以修改的，如下：

SET long_query_time = 1;

现在修改成超过 1 秒的 SQL 都会被记录在慢查询日志当中！可以设置为 0.01 秒，表示 10 毫秒。慢查询日志，默认名称是 host_name-slow.log，存放在 MySQL 的数据路径下，内容格式显示大致如下：

Query_time: 0.012000 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 139
use tuluneducation;
SET timestamp=1534527397;
select id,author from subject where content like '%linux%' and title like '%c++
linux%';

通过查询慢查询日志，发现项目运行过程中，上面这条 SQL 语句的执行时间超过了设定的慢查询时间，那么接下来就需要用 explain 分析一下该 SQL 的执行细节了，根据具体情况找出 SQL 和MySQL 索引|索引该怎么去优化。

show profiles 命令可以查看 SQL 语句具体的运行时间，全局变量的名字是: profiling。

5.redo&undo

redo log

redo log 是重做日志，用于记录事务操作的变化，确保事务的持久性。redo log 是在事务开始后就开始记录，不管事务是否提交都会记录下来，在异常发生时（如数据持久化过程中掉电），InnoDB 会使用 redo log 恢复到掉电前的时刻，保证数据的完整性。

innodb_log_buffer_size 默认是 16M，就是 redo log 缓冲区的大小，它随着事务开始，就开始写 redo log，如果事务比较大，为了避免事务执行过程中花费过多磁盘 IO，可以设置比较大的 redo log 缓存，节省磁盘IO。InnoDB 修改操作数据，不是直接修改磁盘上的数据，实际只是修改 Buffer Pool 中的数据。InnoDB 总是先把 Buffer Pool 中的数据改变记录到 redo log 中，用来进行崩溃后的数据恢复。优先记录 redo log，然后再慢慢的将 Buffer Pool 中的脏数据刷新到磁盘上。

innodb_log_group_home_dir 指定的目录下的两个文件：ib_logfile0，ib_logfile1，该文件被称作重做日志。

buffer pool 缓存池的作用：加速读和加速写，直接操作 data page，写 redo log 修改就算完成，有专门的线程去做把 buffer pool中的 dirty page 写入磁盘。

undo log

undo log 是回滚日志，保存了事务发生之前的数据的一个版本，用于事务指向时的回滚操作，同时也是实现多版本并发控制(MVCC)下读操作的关键技术。

其过程如下图所示，其中 DB_TRX_ID 为事务 ID，DB_ROLL_PTR 为回滚指针：