一文搞懂MySQL各种日志

一、引言

在MySQL中，日志非常重要的一个组成部分，它记录了数据库运行状态的各种信息，包括错误信息、查询信息、事务信息等等，是进行异常排查、性能优化、数据恢复和备份的关键基础。

本文将重点介绍MySQL中常见的各种日志，解析MySQL日志的作用、底层原理和实现方法，并结合案例加以说明。使读者能够对MySQL中的各种日志有比较全面和深入的了解和认识，有助于读者更好地使用和管理MySQL。

二、MySQL日志的种类和作用范围

MySQL中的日志主要分为两种类型：Server层的日志和引擎层的日志（引擎层日志本文主要介绍InnoDB引擎日志）。

Server层日志简介

用于记录MySQL Server层的各种操作和异常情况

• 「Error Log （错误日志）：」 记录MySQL Server运行时出现的错误和警告
• 「Binary Log （二进制日志）：记录了MySQL Server层执行的所有修改操作」
• 「Relay Log （ 中继日志）：」 主从复制过程中使用的一种日志类型，在从服务器上记录主服务器上所有的二进制日志（Binary log）的信息
• 「DDL Log ：」 记录DDL操作的一种日志类型。
• 「General Log（查询日志）：」 记录MySQL Server层的所有查询语句
• 「Slow Query Log （慢查询日志）：」 记录执行时间过长的查询语句

InnoDB引擎日志简介

• 「Redo Log：」 记录事务过程中的修改操作，以保证事务的安全性。
• 「Undo Log：」 用于撤销与事务相关的修改操作，以保证事务的原子性。

三、Server层日志

Error Log 错误日志

错误日志（Error log）是MySQL在启动、运行过程中产生的错误和警告信息的记录。

Error log的作用是记录MySQL Server的异常状态和错误信息，帮助管理员或开发人员定位原因并进行问题排查。常见的错误信息包括数据库启动失败、连接错误、SQL语句错误、权限不足、磁盘空间不足等，以及由于系统和硬件等因素导致的程序崩溃和运行时错误。

在MySQL的内部实现中，错误日志是由Server层自动产生和记录的。MySQL Server启动时，会自动创建错误日志文件，并在运行过程中不断记录异常信息。当MySQL Server执行过程中出现异常情况时，会将详细的错误和警告信息记录在错误日志文件中，并为每个错误日志事件生成一个独立的错误号码，方便管理员通过错误号码进行管理员排查。

Binary Log 二进制日志

binlog（binary log）是MySQL中用于记录执行修改语句的一种二进制日志。其作用是记录MySQL Server所执行的修改操作（例如对数据表的插入、更新、删除等操作），并以二进制格式进行记录。binlog主要用于MySQL的数据恢复、备份和主从复制等方面。

binlog的生成和写入是在MySQL Server层完成的。在MySQL Server层，binlog被称为“复制日志”（Replication Log）或“事务日志”（Transaction Log），它记录了MySQL Server层执行的所有修改的操作（以二进制形式存储），而不是记录在底层的存储引擎之中。

在MySQL的执行过程中，所有的修改操作均被交给MySQL Server处理，并被记录在binlog中。当MySQL执行完所有的修改操作并提交事务之后，binlog会将修改操作写入磁盘中。此时，binlog中记录的修改操作就可以用于数据恢复、备份和主从复制等操作。

binlog的三种格式

• 「Statement格式：」 binlog记录 SQL 语句，也就是 SQL 语句本身被记到 binlog 中。优点是记录量较小，缺点是有些语句不确定性很大，例如：UUID() 函数等，或者执行的随机的函数等，可能不稳定。
• 「Row格式：」 每一行数据的变化被记录在 binlog 里面。优点是可以记录较为精确的修改信息，缺点是记录的数据量较大。
• 「Mixed格式：」 Statement 和 Row 格式的混合使用，MySQL 会自行判断采用哪种方式，使得记录 binlog 达到最优方案。

binlog是MySQL Server中一个非常重要的工具，它对于完整记录和追踪数据库的修改操作以及主从复制等操作具有十分重要的作用。MySQL管理员在对MySQL Server进行运维管理和数据库维护时，需要充分掌握binlog的作用、原理以及使用技巧。

Relay Log 中继日志

relay log（中继日志）是MySQL数据库主从复制过程中的一种日志类型，主要用于记录备份服务器的中继信息。relay log 在从服务器上生成，其中记录了从主服务器中复制得到的 binlog 信息，并被存储在本地磁盘上。

relay log 的生成和写入是在从（Slave）服务器层完成的，在需要将 binlog 信息复制到备份服务器时，将记录在 master 的 binlog 转化为中继日志存储到备份服务器（Slave）的中继日志目录下，并将变更记录同步到备份服务器的数据表中。

通过 relay log 日志，实现了主从服务器之间的数据复制和同步。在 MySQL 数据库的主从架构中，使用 relay log 技术可以最大程度地保证数据一致性和正确性，使得主从服务器之间的数据同步得以保持。

DDL Log DDL操作日志

DDL（Data Definition Language）日志是MySQL数据库的一种用于记录数据定义语言操作的日志类型。DDL操作用于管理、维护和修改数据库对象的结构，例如创建和修改数据表、视图、索引和存储过程等。DDL日志记录了在数据库中进行DDL操作的所有细节和元数据信息，包括DDL语句本身、操作对象的元数据信息以及修改时间等。

General Log 查询日志

General 日志是 MySQL 中一种非常基本的日志类型，用于记录 MySQL Server 的访问和操作信息。当启用 General 日志后，MySQL Server 会将每个客户端连接到服务端的信息和每个操作事件都记录到指定的日志文件中，其中包括连接和断开连接信息、执行的查询和 SQL 语句等信息。

General 日志通常用于排错和调试 MySQL 服务器。它可以帮助管理员和开发人员追踪以及调试复杂的问题和查询，例如，查找用户活动、调试语法错误以及定位卡顿或内存问题等。

启用 General 日志会导致日志占用大量的磁盘空间，并会影响 MySQL 服务器的运行性能。因此，在正式环境中仅应在必要时启用 General 日志。在开发、调试、测试等环境中建议使用 General 日志，以便更好地跟踪 MySQL 服务器的运行状态并识别故障。

Slow Query Log 慢查询日志

慢查询日志（Slow Query Log）是MySQL数据库的一种日志记录方式。如其名，它是用来记录执行时间较慢的查询语句的，主要用于分析和优化查询效率。

慢查询的主要作用包括以下几点

1. 帮助管理员发现执行时间较长的SQL语句以及卡顿或性能问题。
2. 支持优化SQL查询，针对性地调整索引、优化语句结构等，从而提高数据库性能。
3. 贴近实际生产环境和访问习惯，以更好地了解SQL查询行为，并扩展数据库管理和性能优化的知识。

在 MySQL Server 运行过程中，如何判断一条 SQL 语句是否为慢查询，需要根据执行时间和配置参数中指定的时间的大小进行比较，若超过指定时间则被认定为执行时间较慢的查询语句。

当 MySQL Server 记录到执行时间较慢的查询语句之后，会将这些语句的语法和执行时间等信息记录到慢查询日志中（默认位置为/data/mysql/hostname-slow.log）。

慢查询日志会占用较大的磁盘空间和资源，因此在生产环境中需慎重使用，建议在需要查询和排查性能问题时才开启。

四、引擎层日志

MySQL 数据库中，每种存储引擎都会拥有自己的引擎层日志（engine-level log），用于记录存储引擎层的操作和信息。引擎层日志是针对具体存储引擎而言的。

一般来说，引擎层日志主要记录以下信息：

1） 事务信息：事务开始、提交、回滚等操作的信息。

2） 修改信息：对存储结构进行的操作，如对数据表的增删改等操作。

3）数据页信息：记录数据页的信息和相关的状态，包括数据页的读入、写入和修改等操作。

4） 锁信息：当前引擎使用的锁信息，它会记录锁定时间、锁定的类型、锁定的范围等。

5） I/O 操作：记录 I/O 操作的详细信息，如 I/O 耗时、I/O 操作的数据页、I/O 操作的数据块等。

InnoDB存储引擎则拥有 redo log 和 undo log 两种引擎层日志，用于在事务提交时保证数据的一致性和完整性。

Redo Log 重做日志

redo log（重做日志）是 MySQL 进行数据持久化时，记录的一种日志类型。redo log 记录的是 InnoDB 存储引擎中数据文件的修改操作，用于保证 MySQL 数据库在异常崩溃等情况下的数据一致性。redo log 是 MySQL 中 WAL（Write-Ahead Logging）机制的实现之一。

redo log 具有以下作用：

• 在系统崩溃或重启时恢复数据：redo log 记录了所有更改数据的操作，从而可以使 MySQL 在崩溃的情况下恢复所有未被落盘到磁盘上的更改，确保数据不被破坏且数据一致性得以维护。
• 减少随机写磁盘的次数：MySQL 写入磁盘的随机操作非常低效，redo log 机制可以将 MySQL 对于数据修改的写操作集中到一块更高效的地方，从而避免了每次操作都要进行磁盘随机读写的场景，提高性能。
• 实现 MySQL 中的多版本并发控制：多个事务操作同一个数据库时，MySQL 通过 redo log 等多种机制来实现事务的 ACID 和 MVCC 特性，保证多个事务之间的并发执行的安全性。

在 MySQL 数据库中，redo log 使用固定大小的循环缓冲区来实现存储。缓冲区大小可以通过参数进行配置，一旦运行日志满，会新创建一个日志文件，并继续写入操作记录。管理员可以手动启动或停止 redo log 功能来想要的灵活控制。

Undo Log 撤销日志

undo log（撤销日志）是MySQL在InnoDB存储引擎中记录事务的日志的一种类型，记录了正在执行的每个事务所做的修改操作之前的状态信息，以实现对于事务的回滚。

undo log 可以提供以下作用：

1. 支持事务回滚：当事务进行回滚操作时，可以使用 undo log 中的信息回滚到事务开始之前的状态。
2. 支持 MVCC：多版本并发控制需要对版本的表进行维护，而 undo log 可以保留之前版本的数据，在读同时进行写时不会出现数据的不一致性。
3. 保证数据的一致性：InnoDB 存储引擎使用 undo log 的方式，可以保证当 MySQL 服务在执行操作期间出现异常时不会在数据上引入不一致性。

「undo log 的原理：」

1. 在执行 SQL 语句之前，InnoDB 存储引擎将会为该语句开启一个事务，并为每个修改操作创建一个undo log记录。
2. 对于 DML（INSERT、UPDATE、DELETE）操作和数据定义操作（DDL SELECT等），InnoDB 在事务开始前就会生成 undo log 记录，记录主键、页编号、旧值、新值等信息，保存在与数据表相对应的 undo segment 中。
3. 当撤销操作出现时，InnoDB 会通过 undo log 中记录的操作来回滚已经提交的修改操作。
4. InnoDB 会为各个数据段的 undo log 创建回收队列，回收完成后释放相关资源，防止数据的大量积累。

MySQL日志的作用小结

1. 「保证数据的完整性和一致性」 MySQL采用了“redo log”和“undo log”来保证数据操作的ACID特性。
2. 「数据备份与恢复」 MySQL使用binlog作为最重要的日志之一，其用于记录MySQL Server层执行的所有修改操作。当MySQL Server因故障停机或者出现数据错误时，可以通过使用二进制日志进行数据恢复。
3. 「发现和解决问题」 MySQL Server层的各种日志（例如，Slow query log，Error Log）以及InnoDB数据库引擎日志（例如 redo log）可以用来诊断系统问题或质量问题。
4. 「性能分析和优化」 通过访问日志、查询日志以及慢查询日志等日志，可以根据记录的数据分析和优化MySQL Server的性能。