MySQL日志总结

原创已于 2025-09-18 23:08:19 修改 · 959 阅读

14 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2024-03-19 22:40:59 首次发布

MySQL 专栏收录该内容

6 篇文章

订阅专栏

MySQL日志是数据库运行的重要组成部分，涉及错误日志、二进制日志、中继日志等，用于异常排查、性能优化、数据恢复和备份。日志产生流程包括Server层和引擎层，涉及Redo Log和Undo Log，保证事务的持久性和原子性。日志在数据备份、恢复、问题排查和性能优化中发挥关键作用。

一、日志简介

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

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

二、日志分类

①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引擎日志

用于InnoDB存储引擎支持事务

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

三、日志产生流程

一条更新语句的执行过程为：

①MySQL执行器负责执行，首先查询待更新的记录。

如果在Buffer Pool中，那么返回这条记录。
如果没在，那么查询数据，加载进Buffer Pool。

②开启事务，首先记录Undo Log。

③InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面。

④然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache。

⑤事务提交。采用两阶段的方式

prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘。
commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）。

四、Server层日志

①错误日志（Error Log）

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

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

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

②二进制日志(Binary Log)

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

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

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

应用场景

一是用于数据恢复，如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
二是用于数据复制，由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

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操作日志

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

⑤查询日志（General Log）

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

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

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

⑥慢查询(Slow Query Log)日志

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

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

帮助管理员发现执行时间较长的SQL语句以及卡顿或性能问题。
支持优化SQL查询，针对性地调整索引、优化语句结构等，从而提高数据库性能。

在 MySQL Server 运行过程中，如何判断一条 SQL 语句是否为慢查询，需要根据执行时间和配置参数中指定的时间的大小进行比较，若超过指定时间则被认定为执行时间较慢的查询语句。当 MySQL Server 记录到执行时间较慢的查询语句之后，会将这些语句的语法和执行时间等信息记录到慢查询日志中（默认位置为/data/mysql/hostname-slow.log）。慢查询日志会占用较大的磁盘空间和资源，因此在生产环境中需慎重使用，建议在需要查询和排查性能问题时才开启。

五、引擎层日志

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

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

事务信息：事务开始、提交、回滚等操作的信息。
修改信息：对存储结构进行的操作，如对数据表的增删改等操作。
数据页信息：记录数据页的信息和相关的状态，包括数据页的读入、写入和修改等操作。
锁信息：当前引擎使用的锁信息，它会记录锁定时间、锁定的类型、锁定的范围等。
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存储引擎中记录事务的日志的一种类型，记录了正在执行的每个事务所做的修改操作之前的状态信息，以实现对于事务的回滚。

MySQL会提前生成Undo Log日志，当事务提交的时候，并不会立即删除Undo Log，因为后面可能需要进行回滚操作，要执行回滚（ROLLBACK）操作时，从缓存中读取数据。Undo Log日志的删除是通过通过后台purge线程进行回收处理的。

Undo log可以提供以下作用

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

Undo log原理

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