MySQL日志

MySQL数据库中的参数可以分为两类，动态参数和静态参数。静态参数是只读的，不能通过SET命令修改，只能改MySQL 的配置文件（my.cnf）。动态参数可以通过SET命令修改。

SET命令可指定修改参数的作用域，SESSION，指该会话有效；GLOBAL，当前MySQL服务下有效，重启后失效。想要永久修改有效，需更改配置文件。

https://dev.mysql.com/doc/refman/8.0/en/server-logs.html

错误日志（error log）

记录 MySQL 服务实例启动、运行、停止等等的过程信息，需要注意的是这里记录的不仅仅是错误信息，还有警告和正确的信息。
默认情况下，错误日志文件名会以 “主机名.err” 结尾。但是，需要知道，错误日志不会记录所有的错误信息，只有被 MySQL 声明为 “关键错误” 的事件发生时才会记录。

mysql> select @@log_error;
+----------------------------------------+
| @@log_error                            |
+----------------------------------------+
| F:\mysql-8.0.19-winx64\data\rufeng.err |
+----------------------------------------+

一般查询日志（general_log）

记录了所有对MySQL数据库的执行sql。对于查询日志来说，MySQL 发生了什么，就会记录什么。
通常来说，查询日志都是关闭的,默认也是关闭的。只有在调试或者特殊时期，需要追踪某些特殊的查询，可以临时打开查询日志。

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+

开启查询日志有两种方式，一种是修改配置文件，但是需要重启服务；另一种是在运行时通过 SET 命令，但是只能对当前的会话生效。general_log_file 参数则用于指定文件的位置（同样可以自行修改）

mysql> show variables like 'general_log_file';
+------------------+----------------------------------------+
| Variable_name    | Value                                  |
+------------------+----------------------------------------+
| general_log_file | F:\mysql-8.0.19-winx64\data\rufeng.log |
+------------------+----------------------------------------+

mysql> set global general_log = 'OFF';

慢查询日志（slow_query_log）
MySQL的慢查询日志记录响应时间超过参数long_query_time、检索结果达到min_examined_row_limit行的SQL语句。慢查询日志用于定位慢SQL并优化。使用mysqldumpslow命令来处理慢查询日志文件并汇总其内容。
需要注意的是：

  1. 默认环境下，慢查询日志是关闭的。
  2. 默认情况下，慢查询日志中不会记录管理语句，可通过设置 log_slow_admin_statements = on 让管理语句中的慢查询也会记录到慢查询日志中。
  3. 默认情况下，只要不超过long_query_time，便不会记录。如果我们想记录检索所有行的SQL语句，可通过配置log_queries_not_using_indexes = on让检索了所有行的SQL都被记录到慢查询日志中。即便使用了索引，也可能会被记录到日志中，因为索引不限制扫描行数。
  4. 慢查询日志只会记录执行成功的语句，这与查询日志不同。
  5. 通过slow_query_log_file指定慢查询日志文件名，默认为host_name-slow.log
mysql> SELECT @@slow_query_log, @@long_query_time, @@log_slow_admin_statements,@@log_queries_not_using_indexes;
+------------------+-------------------+-----------------------------+---------------------------------+
| @@slow_query_log | @@long_query_time | @@log_slow_admin_statements | @@log_queries_not_using_indexes |
+------------------+-------------------+-----------------------------+---------------------------------+
|                0 |          1.000000 |                           0 |                               0 |
+------------------+-------------------+-----------------------------+---------------------------------+

windows环境下使用mysqldumpslow分析慢查询日志

F:\mysql-8.0.19-winx64\data>perl ../bin/mysqldumpslow.pl rufeng-slow.log

Reading mysql slow query log from rufeng-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select * from slow_log

关于log_output参数
一般查询日志和慢查询日志输出位置。
该值有三个选项：

  • FILE，输出到文件
  • TABLE，输出到mysql数据库中的general_log和slow_query_log
  • NONE，不输出
    也可以同时输出到FILE和TABLE，逗号分隔
mysql> select @@log_output;
+--------------+
| @@log_output |
+--------------+
| FILE,TABLE   |
+--------------+

二进制日志（binlog）

Binlog 用于记录数据库的所有变化，简单的说，所有可能涉及数据变动的操作，都会记录下来。（例如select和show就不能被记录）。
查看当前系统的所有 Binlog 日志，可以查看 log_bin_index 参数，.index文件是日志文件的索引文件。

mysql> SHOW VARIABLES LIKE 'log_bin_index';
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| log_bin_index | F:\mysql-8.0.19-winx64\data\binlog.index |
+---------------+------------------------------------------+

Binlog 日志的记录格式一共有三种：ROW、STATMENT 以及 MIXED

  • ROW（记录行的内容，记两条，更新前和更新后都有）
    说明：基于行的复制，不会记录每一条 SQL 语句的上下文信息，仅仅会保存哪条记录被修改
    优点：记录了每一行数据的修改细节
    缺点：日志量巨大，特别是批量修改的情况
  • STATMENT（记sql语句）
    说明：基于 SQL 语句的复制，记录每一条修改数据的 SQL，是 Binlog 的默认格式
    优点：日志量小，节约磁盘 IO
    缺点：在某些情况下会导致 Master-Slave 不一致，例如：执行 Sleep 函数
    其中，MIXED 格式是 ROW 和 STATMENT 两种格式的混合，普通的复制使用的是 STATMENT 格式，对于 STATMENT 无法复制的操作则会使用 ROW 格式去记录。使用 MIXED 格式时，会由 MySQL 来决定使用哪种格式记录日志。该情况下，影响零行的SQL可能也会被写入日志。

以下三种情况，会产生新的Binlog文件

  • MySQL服务启动了
  • 日志刷新
  • binlog文件达到max_binlog_size

随着系统在不断运行，日志量也会逐步增加，所以，自然也就会有清理日志的工作。MySQL 提供了两种方式来清理 Binlog，一种是手动的，一种是自动的。手动清理 Binlog 是通过 PURGE 命令，它会同时删除 Binlog 文件和 Binlog 索引文件记录。自动清理 Binlog 是通过配置 MySQL 的 expire_logs_days 参数（在 my.cnf 中配置）。例如，设置 expire_logs_days = 10，表示系统保留10天的 Binlog，第11天将会删除第1天的 Binlog。

重做日志（redo_log）

redo_log文档 https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html

redo_log配置 https://dev.mysql.com/doc/refman/8.0/en/innodb-init-startup-configuration.html#innodb-startup-log-file-configuration
redo_log 是物理日志，一种基于磁盘的数据结构，记录的是更新的数据用于在崩溃恢复期间纠正不完整事务写入的数据。当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了（还未写入磁盘）。InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面。注意，事务开始之后就开始写入redo log文件，事务提交并不等于更新到磁盘中。

默认情况下，ib_logfile0，ib_logfile1两个文件表示重做日志，MySQL采用循环的方式写入日志（一个写满了从头写另一个），旧的日志可能会丢失。修改日志文件的大小使用innodb_log_file_size参数。

为什么会binlog和redo log日志都有恢复功能呢？

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现。

这两种日志有以下三点不同。

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

  2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”，所以redo log的恢复比binlog快。

  3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

关于redo log和binlog的一致性问题，参考该文章
简单来说，当系统挂掉，redo log里面可能包含无效数据(待回滚数据)，当系统再恢复后，首先会去处理binlog，根据binlog会回滚redo log（处理完后，redo log的数据都是有效数据），最后才会读取redo log恢复 bufferpool，即以binlog日志为准

回滚日志（undo log）

保存了事务发生之前的数据的一个版本，可以用于回滚。存的是操作，逻辑日志。实现MVCC的基础。

参考：https://zhuanlan.zhihu.com/p/266592787

Q.E.D.

一切很好，不缺烦恼。