【MySQL进阶】错误日志，二进制日志，mysql系统库

发布于：2025-07-06 ⋅ 阅读:(45) ⋅ 点赞:(0)

目录

 一.错误日志

1.1 配置错误日志

1.1.1 Windows的默认错误日志路径

1.1.2 Unix和Linux系统的默认错误日志路径

1.2 错误日志中事件的字段

1.2.1 核心错误事件字段

1.2.2.MySQL 错误消息的两种不同输出渠道

1.2.3 可选错误事件字段

1.3. 刷新错误日志文件和重命名

二. 二进制日志 

2.1.二进制日志与主从复制 

2.1.1.什么是主从复制

2.1.2.为什么要主从复制

2.1.3.二进制日志在主从复制的作用

2.2 选项和变量 - 查看二进制日志相关的系统变量

2.3.查看二进制日志

2.4.二进制日志格式

2.5.RedoLog和UndoLog

2.6 服务器日志维护

2.7 配置日志输出位置

三. mysql System Schema (mysql系统库)

3.1 数据字典

3.2 系统表

 

 一.错误日志

  • 错误日志一般会记录mysqld启动和关闭的次数、诊断消息，以及服务器运行期间发生的错误和警告；
  • 例如MySQL需要自动检查或修复一个表，就会在错误日志中写入一条记录。
  • 错误日志默认使用UTF-8(utf8mb3)编码格式，并使用英语生成记录。

1.1 配置错误日志

错误日志输出的位置，可以是控制台或指定文件，"控制台"表示 stderr 标准错误输出。

1.1.1 Windows的默认错误日志路径

在Windows系统中，mysqld使用 --log-error（文件路径） 和 --console（控制台） 选项来确定默认的错误日志目标是控制台还是文件，规则如下：

  • 如果指定了 --console 选项，默认在控制台输出错误日志，如果 --console 和 --log-error 同时指定，则 --console 优先级更高，并且 --log-error 将失效。
  • 如果没有指定 --log-error 或者没有指定具体的文件名，默认在数据目录中生成名为 host_name.err 的日志文件。
  • 如果明确指定--log-error的文件名，则在数据目录下以指定文件名生成错误日志（如果没有指定后缀名，则以.err为后缀），可以通过指定绝对路径，来更改默认的日志位置。
[mysqld]
log-error=D:/log/MySQL/Error/error_log.err # 自定义错误日志的路径


1.1.2 Unix和Linux系统的默认错误日志路径

在Unix和Linux系统中，mysqld使用--log-error选项来指定默认错误日志目标，可以指定控制台或是文件，如果是文件，规则如下：

  • 如果没有指定--log-error，默认输出是控制台。
  • 如果错误日志输出目标是控制台，则服务器将log_error系统变量设置为stderr。否则，将以文件形式输入错误日志，并以log_error的值为文件名。
  • 如果显示写出--log-error但没有指定具体文件，则默认路径是数据目录中host_name.err的文件；
  • 如果明确指定--log-error的文件名，则在数据目录下以指定文件名生成错误日志（如果没有指定文件后缀，则以.err为后缀）。
  • 可以通过指定绝对路径，来更改默认的日志位置。

我们看看默认的这个 

我们去我们的系统里面配置一下这个

vim /etc/mysql/my.cnf

 然后我们输入下面这个

[mysqld]
log-error=/var/log/mysql/error_log.err # 自定义错误日志的路径

接着重启一下mysql

systemctl restart mysql

 

1.2 错误日志中事件的字段

1.2.1 核心错误事件字段

字段名 含义说明 示例值
time 事件发生的时间戳，精确到微秒，包含时区信息 2023-07-27T14:15:59.267828+08:00
msg 事件的具体描述消息 Starting upgrade of data directory.
prio

事件优先级：
0 [System]——系统事件
1 [ERROR]——错误事件
2 [Warning]——警告事件
3 [Note]——通知/提示事件

值越小，优先级越高

 0 [System]
err_code MySQL 特有的错误代码，格式为 [前缀]-[数字] MY-011012
err_symbol 标准化的错误符号标识（需查询文档） ER_CANT_OPEN_FILE (示例)
SQL_state 标准 SQLSTATE 错误代码（5字符编码） HY000 (通用错误)
subsystem 发生事件的子系统模块 InnoDB, Server, Repl

  • MYSQL内部维护的一套错误代码的定义 ——err_code，err_symbol，SQL_state

多说无益，我们看些例子

2023-07-27T14:15:59.267828+08:00 0 [System] [MY-011012] [Server] Starting upgrade of data directory.

字段 值 说明
timestamp 2023-07-27T14:15:59.267828+08:00 事件发生的精确时间（东八区）
thread_id 0 主服务器线程（非优先级）
event_priority [System] 系统级事件（最高优先级）
error_code [MY-011012] MySQL特有错误代码
subsystem [Server] 事件发生的子系统模块
message Starting upgrade of data directory. 数据目录升级开始

完整解释：
主服务器线程（thread_id=0）在指定时间报告了一个系统级事件，错误代码MY-011012表示MySQL正在执行数据目录升级操作，这是版本更新或初始化过程中的标准操作。

2023-07-27T14:16:11.719225+08:00 2 [Warning] [MY-010772] [Server] db.opt file not found for binlog database. Using default Character set.

字段 值 说明
timestamp 2023-07-27T14:16:11.719225+08:00 事件发生的精确时间
thread_id 2 执行线程ID（非优先级）
event_priority [Warning] 警告级事件
error_code [MY-010772] MySQL特有错误代码
subsystem [Server] 服务器子系统
message db.opt file not found... binlog数据库字符集配置文件缺失

完整解释：
线程ID=2报告了一个警告事件，错误代码MY-010772表示在binlog数据库目录中找不到db.opt字符集配置文件。MySQL将使用默认字符集，可能导致二进制日志的字符编码不一致。

2023-07-27T14:16:11.731900+08:00 1 [ERROR] [MY-013140] [Server] Invalid utf8 character string: 'C9F3C5'

字段 值 说明
timestamp 2023-07-27T14:16:11.731900+08:00 事件发生的精确时间
thread_id 1 执行线程ID（非优先级）
event_priority [ERROR] 错误级事件
error_code [MY-013140] MySQL特有错误代码
subsystem [Server] 服务器子系统
message Invalid utf8 character string: 'C9F3C5' 检测到非法UTF8字符序列

完整解释：
线程ID=1报告了一个错误事件，错误代码MY-013140表示在数据处理过程中检测到非法UTF8字符序列"C9F3C5"（十六进制表示）。这表明数据库中存在非标准UTF8编码的数据，可能由字符集配置错误或数据导入问题引起。

当MYSQL服务启动失败的时候，首先需要查看一下错误日志。

我们可以借助tail命令

tail -n 20 /var/log/mysql/error.log

 

 关于错误码，我们可以去官网看看：MySQL :: MySQL 8.0 Error Reference :: 2 Server Error Message Reference

这个是专门查服务端的错误码的，我们看左边，其实可以切换

假如我们要查MY-013140的含义，我们只需点击ctrl+f，然后

 

有没有发现err_code，err_symbol，SQL_state这里都有，最重要的其实是message。

此外呢，我们可以借助SQL客户端来看看这些错误码是什么意思

 可以通过以下SQL查看已定义的错误类型

SELECT *
 FROM performance_schema.events_errors_summary_global_by_error
 WHERE SUM_ERROR_RAISED <> 0\G

  1. performance_schema.events_errors_summary_global_by_error

    • 这是 MySQL 性能模式（Performance Schema）中的一个系统表

    • 功能：按错误类型聚合统计 MySQL 实例中发生的所有错误

    • 记录内容：每种错误代码的触发次数、首次/末次发生时间等

  2. WHERE SUM_ERROR_RAISED <> 0

    • SUM_ERROR_RAISED：记录每种错误发生的总次数

    • <> 0：筛选条件，表示"只显示至少发生过一次的错误"

    • 目的：过滤掉从未发生过的错误类型，仅关注实际出现的错误

  3. \G 结尾符

    • MySQL 命令行特有的格式化指令

    • 功能：将查询结果从表格格式转换为垂直格式（每行显示一个字段）

    • 效果：更易阅读大量字段的记录（尤其是宽表）

 

1.2.2.MySQL 错误消息的两种不同输出渠道

官网：MySQL :: MySQL 8.0 Error Reference :: 2 Server Error Message Reference

此外我们注意到官网有这么一句话

我看到它给了个例子

 这是啥意思呢？

其实这个例子是这个描述清晰地解释了 MySQL 错误消息的两种不同输出渠道

  1. 服务器端错误日志 (Server-side Error Log)

    • 写入位置：MySQL 服务器的错误日志文件（默认：/var/log/mysql/error.log）

    • 内容：服务器运行时的内部事件、状态、警告和错误

    • 受众：数据库管理员（DBA）和系统管理员

    • 特点：

      • 包含详细时间戳

      • 包含错误级别（Note/Warning/Error）

      • 包含 MySQL 特有错误代码（如 MY-010303）

      • 记录服务器生命周期事件（启动、关闭、配置变更等）

    示例解析：

    2018-10-28T13:01:32.735983Z 0 [Note] [MY-010303] [Server] Skipping 
generation of SSL certificates as options related to SSL are specified.

    • 2018-10-28T13:01:32.735983Z：UTC 时间戳（精确到微秒）

    • 0：主服务器线程

    • [Note]：通知级别事件（非错误）

    • [MY-010303]：MySQL 特有错误代码

    • [Server]：子系统模块

    • 消息：跳过 SSL 证书生成（因为配置了 SSL 相关选项）

  2. 客户端错误消息 (Client Error Messages)

    • 发送位置：直接返回给连接的客户端程序（如 mysql 命令行、应用程序）

    • 内容：与特定 SQL 查询执行相关的错误

    • 受众：应用程序开发者和数据库用户

    • 特点：

      • 简洁的错误描述

      • 标准 SQLSTATE 错误代码（5 字符）

      • MySQL 错误代码（数字）

      • 无时间戳

    示例解析：

    mysql> SELECT * FROM no_such_table;
ERROR 1146 (42S02): Table 'test.no_such_table' doesn't exist

    • ERROR 1146：MySQL 特有错误代码

    • (42S02)：标准 SQLSTATE 错误码

    • Table 'test.no_such_table' doesn't exist：人类可读的错误描述

1.2.3 可选错误事件字段

  • OS_errno：操作系统错误号；
  • OS_errmsg：操作系统错误消息；
  • label：与值对应的prio描述；
  • user：客户端用户；
  • host：客户端主机；
  • thread：产生错误事件的线程的ID；
  • query_id：查询ID。

1.3. 刷新错误日志文件和重命名

如果使用 FLUSH ERROR LOGS、 FLUSH LOGS 语句或 mysqladmin flush-logs 命令刷新错误日志，服务器会将正在写入的任何错误日志文件关闭并重新打开。

如果要手动重命名错误日志文件，可以在重命名操作之后执行刷新操作，服务器会以原文件名生成一个新的错误日志文件，例如日志文件名为 host_name.err，可以按以下步骤操作：

mv host_name.err host_name.err-old # 重命名日志文件
mysqladmin flush-logs # 刷新操作
mv host_name.err-old backup-directory # 把重命名的日志文件移动到备份目录

比喻解释

想象 MySQL 错误日志是一个正在被书写的日记本：

  1. 日记本正在使用中：作家（MySQL服务器）正在一个名为 host_name.err 的本子上写日记

  2. 想要更换新本子：您想保存当前日记本，让作家用新本子继续写

  3. 直接抢走会出问题：如果直接拿走本子，作家会生气（可能丢失内容）

安全更换日记本的步骤：

🖊️ 步骤 1：给旧日记本贴新标签

mv host_name.err host_name.err-old

  • 相当于：把正在书写的日记本封面贴上"旧日记"标签

  • 实际效果：

    • 文件系统看到的是：host_name.err-old

    • 但作家（MySQL）仍在往这本子里写，因为他的手还握着本子（文件句柄未释放）

🔄 步骤 2：请作家换新本子

mysqladmin flush-logs

  • 相当于：对作家说："请放下旧本子，拿个新本子继续写"

  • 实际效果：

    • 作家合上旧本子（关闭文件句柄）

    • 拿来一个全新的空白本子，命名为原名字 host_name.err

    • 从此开始在新本子上书写

📦 步骤 3：安全保存旧日记

mv host_name.err-old backup-directory/

  • 相当于：把贴了"旧日记"标签的本子移到书柜保存

  • 现在安全了：因为作家已经完全转用新本子

二. 二进制日志 

二进制日志包含数据库更改的"事件"，不会记录 SELECT 和 SHOW ，例如：记录表的创建操作或表数据的更改.

二进制日志还包含每个语句更新数据时花费的时间信息

启动二进制日志，对服务器性能稍微有些影响，因为涉及磁盘I/O

除了基于行的日志模式，它还包含可能进行更改数据的语句事件，例如 DELETE 操作没有匹配到查找到的行，我们这先不说

二进制日志的作用：

  • - 主从节点数据复制：从节点服务器读取主节点服务器上的二进制日志文件，并根据二进制日志中记录的事件在从节点上执行相同的操作，保证主从节点服务器上数据一致，实现数据复制功能。在主从复制专题中我们重点讲解复制过程；
  • - 数据恢复：从某个时间点恢复备份数据后，将重新执行备份时间点之后记录在二进制日志中的事件。这些事件使数据库从备份点更新到当前最新状态。

二进制日志的语句中如果涉及用户的密码，则由服务器进行加密，不会以纯文本形式出现。

2.1.二进制日志与主从复制 

2.1.1.什么是主从复制

MySQL 主从复制是一种异步（或半同步）的数据复制技术，其核心思想是：将一台 MySQL 数据库服务器（主节点/Master）上的数据变更，自动同步到一台或多台其他 MySQL 数据库服务器（从节点/Slave）上。

你可以把它想象成：

  1. 有一个“源头”（主节点）： 这是唯一接受应用程序写入（增、删、改）操作的数据库服务器。所有对数据的修改都发生在这里。

  2. 有多个“副本”（从节点）： 这些服务器不能直接接受写入（或者可以配置为只读，避免冲突），它们的主要任务是忠实地复制主节点上的数据变更。

  3. 复制机制：

    • 当主节点上的数据发生变更（执行了 INSERT, UPDATE, DELETE, DDL 等语句）后，这些变更会被记录到它的二进制日志 (Binary Log, Binlog) 中（我们之前重点讨论过它）。

    • 每个从节点通过其 I/O 线程 主动连接到主节点。

    • 从节点的 I/O 线程读取主节点的 Binlog 中记录的变更事件，并将这些事件按顺序复制到从节点本地的中继日志 (Relay Log) 中。

    • 从节点的 SQL 线程 读取本地的 Relay Log，解析其中的事件，并在从节点的数据库上重新执行这些变更操作（比如执行等效的 SQL 语句或直接应用行变更）。

  4. 最终结果： 经过一段短暂的延迟（复制延迟），从节点上的数据会变得与主节点上的数据基本一致（最终一致性）。

2.1.2.为什么要主从复制

主从复制不是为了取代数据库备份！它是一个强大的高可用性、可扩展性和性能优化方案，主要解决以下核心问题：

  1. 读写分离与负载均衡：

    • 问题： 在高并发场景下，单个数据库服务器既要处理大量的读请求（SELECT）又要处理写请求（INSERT/UPDATE/DELETE），很容易成为性能瓶颈，导致响应变慢甚至宕机。

    • 解决方案： 主节点只负责处理写操作。所有的读操作可以分发到一个或多个从节点上去执行。

    • 好处：

      • 显著提升读性能： 利用多个从节点分担读负载，轻松应对高并发查询。

      • 提升整体吞吐量： 释放主节点资源，使其更专注于处理写操作。

      • 优化用户体验： 减少查询等待时间。

  2. 高可用性与故障转移：

    • 问题： 任何单点服务器都可能因为硬件故障、软件崩溃、网络问题、维护等原因宕机，导致服务完全中断。

    • 解决方案： 当主节点发生故障时，可以快速地将其中一个从节点提升 (Promote) 为新的主节点。应用程序随后连接到这个新的主节点继续提供服务。

    • 好处：

      • 减少停机时间： 大大缩短数据库不可用的时间窗口（RTO - 恢复时间目标），提高业务连续性。

      • 灾难恢复： 提供热备节点，在主节点完全失效时接管服务。

      • 需要配合： 这通常需要额外的监控工具（如 MHA, Orchestrator, RDS/Aurora 的自动故障转移）和应用程序连接管理（如 ProxySQL, MySQL Router）来实现自动化切换。

  3. 数据备份与容灾：

    • 问题：

      • 直接在主节点上进行物理备份（如 mysqldump 全量、xtrabackup 热备）可能会带来性能影响或锁表。

      • 需要一种方式在不影响主库性能的情况下获取一致的数据快照用于恢复或分析。

      • 需要将数据副本存放在物理上分离的地点以防区域性灾难。

    • 解决方案：

      • 可以在从节点上进行备份操作，完全不影响主节点的性能。

      • 可以将一个从节点部署在异地机房，作为异地灾备节点。

    • 好处：

      • 零影响备份： 确保备份操作不会降低线上服务的性能。

      • 灾难恢复： 异地从节点在主节点所在机房发生灾难（火灾、断电、网络中断）时，可以成为恢复的基础（可能需要配合日志恢复）。

      • 数据安全性： 多一份实时（或近实时）的数据副本。

  4. 数据分析与报表：

    • 问题： 执行复杂的分析查询（如大数据量的 JOIN, GROUP BY, 聚合函数）或生成报表会消耗大量 CPU、内存和 I/O 资源，严重影响主库上 OLTP（在线交易处理）业务的性能。

    • 解决方案： 将这类资源密集型的分析查询或报表生成任务路由到专用的从节点上执行。

    • 好处：

      • 隔离 OLTP 与 OLAP： 确保核心交易业务不受分析查询的干扰，性能稳定。

      • 利用专用资源： 可以为分析从节点配置更强大的硬件或优化参数。

  5. 水平扩展（读方向）：

    • 问题： 当读请求的增长远超单个服务器（即使是强大的主库）的处理能力时。

    • 解决方案： 通过增加更多的从节点，可以近乎线性地提升整个系统的读吞吐量。

    • 好处： 轻松应对读请求的爆发式增长。

需要注意的关键点

  • 异步性： 默认是异步复制。主节点提交事务后，不需要等待从节点确认就返回成功给客户端。这意味着在主节点写入成功后，从节点上可能短暂地看不到最新数据（复制延迟）。这可能导致“读己之所写”不一致的问题（在写入后立即去从库读可能读不到刚写入的数据）。半同步复制（需要显式配置）可以缓解但无法完全消除延迟。

  • 复制延迟： 网络延迟、从节点负载过高、大事务等都可能导致从节点数据滞后于主节点。监控复制延迟至关重要。

  • 最终一致性： 由于异步性和延迟，主从节点在任意瞬间的数据可能不完全相同，但从节点最终会追上主节点（最终一致性）。对强一致性要求极高的场景需要特殊处理。

  • 单点写入： 只有主节点接受写操作。从节点默认是只读的（read_only=1），防止意外写入导致数据不一致。

2.1.3.二进制日志在主从复制的作用

我们来深入探讨一下二进制日志（Binary Log，简称 Binlog）在 MySQL 主从复制（Replication） 中的核心作用。它确实是整个复制机制的基石。

核心作用：充当“操作指令流水账”

你可以将主节点上的二进制日志想象成一份极其精确、严格按照操作顺序记录的“数据库操作流水账”或“指令清单”。它忠实记录了对数据库内容进行修改的所有操作（INSERT, UPDATE, DELETE, DDL 等）以及这些操作执行时的上下文信息。

在主从复制中的具体作用流程：

  1. 主节点记录操作：

    • 当应用程序对主节点（Master）的数据库执行任何会修改数据（数据或结构）的语句（DML/DDL）时。

    • 主节点在执行完该语句后，不是直接记录 SQL 语句本身（除非使用 STATEMENT 格式），而是将该操作引起的数据变化以特定格式（STATEMENT, ROW, MIXED）编码成一个个“事件”（Event）。

    • 这些事件被严格按执行顺序追加写入到主节点的二进制日志文件中。每个事件都有一个唯一的、递增的位置标识（binlog position）或全局事务标识（GTID）。

  2. 从节点获取操作记录：

    • 从节点（Slave）上运行着一个专门的线程，称为 I/O Thread（I/O 线程）。

    • I/O 线程会主动连接到主节点。

    • 它向主节点发出请求：“请把从某个位置（比如上次同步到的位置，或者指定的 GTID）之后的所有二进制日志事件发送给我”。

    • 主节点上有一个专门的线程（Binlog Dump Thread）负责响应这个请求，将请求的二进制日志事件按顺序发送给从节点的 I/O 线程。

  3. 从节点接收并暂存操作记录：

    • 从节点的 I/O 线程接收到主节点发来的二进制日志事件后。

    • 它会将这些事件原样、按顺序地写入到从节点本地的文件，这个文件叫做 Relay Log（中继日志）。你可以把中继日志看作是从节点本地的、来自主节点的二进制日志副本。

  4. 从节点执行操作记录：

    • 从节点上运行着另一个专门的线程，称为 SQL Thread（SQL 线程）。

    • SQL 线程持续读取本地的 Relay Log 文件。

    • 它解析 Relay Log 中的每一个事件，理解这个事件代表什么操作（比如更新哪一行数据，更新成什么值）。

    • SQL 线程在从节点的数据库上，严格按照事件在 Relay Log 中出现的顺序，重新执行（Replay）这些操作。它是在从节点上执行等效的 SQL 或直接应用行变更。

  5. 数据同步达成：

    • 当从节点的 SQL 线程成功执行了 Relay Log 中的一个事件，就表示该事件对应的操作已经在从节点上完成。

    • 通过持续不断地重复步骤 2-4（I/O线程拉取新事件 -> 写入Relay Log -> SQL线程执行事件），从节点最终会执行主节点上记录在二进制日志中的所有数据修改操作。

    • 结果是：从节点上的数据状态逐渐与主节点保持一致（最终一致性）。

2.2 选项和变量 - 查看二进制日志相关的系统变量

show variables like '%bin%';

关于这些变量，我们都可以去官网看看： MySQL :: MySQL 8.0 Reference Manual :: 7.1.4 Server Option, System Variable, and Status Variable Reference

就比如说我要查询下面这个 

log_bin 

  • 作用： 最核心的开关，决定是否启用二进制日志功能。ON 表示启用，MySQL 会记录所有修改数据的语句（DDL 和 DML）。
  • 重要性： 主从复制和数据恢复的基础。必须为 ON。

我们进入官网，

我们按下ctrl+f，

 

然后输入log_bin，就自动寻找到了！！！

那么 其他那些系统变量我就先不讲解，知道怎么查询即可

我们接下来来查询一下关于二进制日志的状态变量

show status like '%bin%';

默认情况下是启用二进制日志的，即log_bin 系统变量为ON

禁用二进制日志，可以指定 --skip-log-bin 或 --disable-log-bin 选项。如果同时指定了 --log-bin 则后指定的选项优先；

注意：--skip-log-bin，--disable-log-bin，log_bin都没有值，直接写就行

选项 --log-bin[=base_name] 用于指定二进制日志文件的基本名称，如果不指定 --log-bin 选项，默认基本名称为 binlog ，建议为二进制日志指定一个基本名；

二进制日志文件默认情况下是/var/lib/mysql/里面带有binlog前缀的那些文件。

  --log-bin[=base_name]用于指定二进制日志文件的基本名称（前缀）和存储路径。MySQL 会在这个基本名称后面自动添加序号（如 binlog.000001, binlog.000002）生成实际的日志文件名。

二进制日志文件名是由基本名+数字扩展名组成的，服务器每次创建一个新的日志文件时，数字扩展名都会增加，从而保证有序的文件系列，发生以下事件时，服务器都会在创建一个新的日志文件：

  • - 服务器已启动或重新启动
  • - 服务器刷新日志
  • - 当前日志文件的大小达到 max_binlog_size (单个日志文件的最大字节数，最小值 4096 字节，最大值和默认值 1GB).
  • 二进制日志以事务为单位进行记录日志

二进制日志文件大小可能会超出 max_binlog_size 设定的值，因为二进制日志在记录事务时，会完整的记录整个事务，不存在把一个事务拆分的情况，如果遇到一个大事务时，即使记录整个事务会超过日志大小限制，也会保证事务的完整性

mysqld 还会创建一个包含二进制日志文件名的日志索引文件，默认情况下，这与二进制日志文件具有相同的基本名称，扩展名为 .index. 可以使用选项 --log-bin-index[=file_name] 修改索引文件名；

 

二进制日志文件和索引文件的默认位置是数据目录。可以使用 --log-bin[=file_name] 选项指定自定义路径， file_name 格式=绝对路径+基本名。--log-bin 对应的系统变量是 log_bin_basename;

注意：

  • 在Linux里面，数据目录默认是/var/lib/mysql/
  • 在Windows里面，数据目录默认是C:\ProgramData\MySQL\MySQL Server 8.0\Data\

MySQL5.7 中，启用二进制日志必须指定服务器 ID, 对应 server_id （集群使用的）选项，否则服务器将无法启动。在 MySQL8.0 中， server_id 系统变量默认设置为 1，在集群环境中，每台 MySQL 服务器必须有唯一的 server_id;

二进制日志记录事件支持三种格式类型：基于行的日志记录、基于语句的日志记录和混合日志记录，稍候具体介绍；

二进制日志记录在语句或事务完成之后，释放锁或在提交完成之前进行。这样做是为了确保按照提交顺序记录日志；

在一个未提交的事务中，对支持事务的表(如 InnoDB 表)的更改都会被缓存(UPDATE, DELETE 或 INSERT), 直到服务器收到 COMMIT 语句， mysqld 在执行 COMMIT 之前将整个事务写入二进制日志；

如果事务回滚，则在整个事务中记录一个 ROLLBACK 语句，但是对非事务性表(如 MyISAM 表)的修改不能回滚，所以这些修改将被复制到从节点；

对非事务表的更新在执行后立即存储在二进制日志中；

当处理事务的线程启动时，它会分配一个大小为 binlog_cache_size 的缓冲区来缓存语句。如果语句大小大缓冲区的值，线程则打开一个临时文件来存储事务，临时文件在线程结束时删除；

Binlog_cache_use 状态变量显示使用该缓冲区(可能还有临时文件)存储事务的数量；
Binlog_cache_disk_use 状态变量显示有多少事务实际上使用了临时文件。结合这两个变量可以把 binlog_cache_size 调优到一个足够大的值，从而避免使用临时文件；

系统变量 max_binlog_cache_size（默认值和最大值都是4GB，最小值为 4096）用于限制缓存区大小，如果事务语句大于这个值指定的字节数，事务将会失败并回滚；

如果使用基于行的日志记录方式，为了保证日志的准确性， CREATE ... SELECT 或 INSERT ... SELECT 语句的并发插入将转换为普通插入；如果使用基于语句的日志记录方式，则将原始语句写入日志。

由于服务器崩溃或其他原因，导致对二进制日志文件无法进行写入、刷新或者同步到磁盘。那么主从节点上的日志就会出现不一致，当遇到这种问题时，可以通过系统变量 binlog_error_action 控制处理方式：

默认值 ABORT_SERVER，服务器停止二进制日志记录并关闭，排查完问题并重启后，服务器按意外停止执行恢复操作；

IGNORE_ERROR 表示，服务器继续进行当前的事务并记录错误，然后停止日志记录，排查问题后，需要确认启用 log_bin ，然后再次启动服务器，对日志要求不高的场景可以设置此值，不推荐在集群环境使用；

默认情况下， sync_binlog=1 ，表示每个事务在写入缓存后立即同步到磁盘，也可以设置为其他值，比如 sync_binlog=N 表示N次事务提交到缓存之后再同步到磁盘，如果 sync_binlog=0 则MySQL不控制同步磁盘的频率，完全由操作系统控制。需要注意：如果当 sync_binlog 的值设置为 0 或 N 那么当服务器崩溃时，缓上存中的有些日志不能同步到磁盘，可能造成一些更改丢失，所以 sync_binlog=1 是最安全的，但同时效率也是最低的。

可以使用 RESET MASTER 语句删除所有二进制日志文件，或者使用 PURGE binary LOGS 删除一部分二进制日志文件，具体演示：帮我

# 重置⼆进⾏⽇志⽂件和索引⽂件为初始状态
mysql> RESET MASTER; 
# 删除指定⽇志⽂件之前的所有⽇志⽂件并更新索引
mysql> PURGE BINARY LOGS TO 'mysql-bin.010';   
# 删除指定时间之前的所有⽇志⽂件并更新索引
mysql> PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

两阶段提交时可能出现的问题及解决⽅法，将在详解两阶段提交章节详细介绍。

术语"⼆进制⽇志⽂件"通常表⽰包含单独编号的⽇志⽂件。术语"⼆进制⽇志"表⽰所有⼆进制⽇志 ⽂件和索引⽂件。

2.3.查看二进制日志

  • 使⽤客⼾端⼯具mysqlbinlog查看
mysqlbinlog 二进制日志名 > 导出的文件名

我们看个例子 

mysqlbinlog binlog.000001 > binlog.txt

 

这个错误表明 mysqlbinlog 工具无法识别 default-character-set=utf8mb4 参数。原因是在 MySQL 5.7+ 版本中该选项已被弃用

mysqlbinlog --no-defaults binlog.000001 > binlog.txt

这会跳过所有配置文件的加载，直接解析 binlog 文件。

  • 通过SQL语句查看
mysql> show binlog events in 'binlog.000007';

如果说我们不添加一些限制的话，那么就会显示很多东西。

我们得添加一些限制

mysql> show binlog events in 'binlog.000007' from 哪 limit 查询几条;

例如说 

show binlog events in 'binlog.000007' limit 10\G

这样子是不是就很好了！！！ 

我们来仔细看一条：

 📝 字段解析

  1. Log_name: binlog.000007

    • 含义：当前二进制日志文件的名称

    • 说明：显示正在解析的是哪个 binlog 文件（这里是第 7 个 binlog 文件）

  2. Pos: 4

    • 含义：该事件在 binlog 文件中的起始位置（字节偏移量）

    • 说明：Format Description 事件总是从位置 4 开始（前 4 字节是 binlog 的魔数头）

  3. Event_type: Format_desc

    • 含义：事件类型为"格式描述事件"

    • 说明：这是 binlog 文件的第一个事件，包含文件的元数据信息

  4. Server_id: 1

    • 含义：生成该 binlog 的 MySQL 服务器 ID

    • 说明：在主从复制中用于标识事件来源服务器（主库通常设为 1）

  5. End_log_pos: 126

    • 含义：该事件结束位置（下一个事件开始位置）

    • 计算：End_log_pos - Pos = 事件大小 → 126-4=122 字节

  6. Info: Server ver: 8.0.42, Binlog ver: 4

    • 核心信息：

      • Server ver: 8.0.42：生成 binlog 的 MySQL 服务器版本

      • Binlog ver: 4：二进制日志格式版本号

2.4.二进制日志格式

记录二进制日志时使用的格式有以下几种：

  • 基于语句的日志格式（执行了什么语句就存储什么语句），最初MySQL是基于SQL语句复制实现主从节点同步，通过指定选项--binlog-format=STATEMENT使用此格式。
  • 基于行的日志格式（默认）中，主节点将事件写入二进制日志，表示各个表的行受到的影响，可以通过指定选项--binlog-format=ROW使用此格式。
  • 混合日志记录格式，默认情况下使用基于语句的日志记录，如果MySQL认为基于语句的格式不能保证主从复制过程中的数据安全时，会自动切换到基于行的日志格式，比如主节点在语句中用了UUID()函数，那么日志文件中记录的是UUID生成的真实值而不是直接使用原始的SQL语句，使用混合日志格式中以指定选项--binlog-format=MIXED。

TIPS:设置二进制日志格式

--binlog-format=[STATEMENT|ROW|MIXED]

基于语句与基于行的区别：

#基于语句，记录执行的SQL语句
update student set age = 18 where id between 10 and 20;
#基于行，记录每一行的更改
update student set age = 18 where id = 10;
update student set age = 18 where id = 11;
update student set age = 18 where id = 12;
...
update student set age = 18 where id = 19;
update student set age = 18 where id = 20;

当使用MYSQL内部的函数的时候，必须以行的格式记录日志。  

 后续有关二进制日志的进一步讨论，我们在主从复制中专题继续讨论。

2.5.RedoLog和UndoLog

  • RedoLog：重做⽇志，⽤于恢复数据；
  • UndoLog：撤消⽇志⽤于回滚操作；

Redo Log（重做日志）

作用：崩溃恢复

  • 场景：当 MySQL 突然崩溃（如断电）时，确保已提交的事务数据不丢失。

  • 原理：

    1. 事务提交时，先写 Redo Log（顺序写入，速度快）；

    2. 后续再异步将数据刷到磁盘；

    3. 崩溃重启后，重放 Redo Log 恢复未落盘的数据。

  • 类比：

    像餐厅的“订单便签”——厨师先记便签（写 Redo Log），再做菜（写磁盘）。
    断电后，按便签继续做未完成的菜（崩溃恢复）。

Undo Log（撤销日志）

作用：回滚与多版本控制（MVCC）

  • 核心功能：

    1. 回滚事务：

      • 事务执行过程中，记录数据修改前的旧值；

      • 若事务回滚（ROLLBACK），用 Undo Log 恢复原数据。

    2. 实现 MVCC：

      • 其他事务读取数据时，通过 Undo Log 找到历史版本，保证读不阻塞写。

  • 类比：

    像文档编辑的“撤销功能”——每次修改前备份旧内容（写 Undo Log）。
    点击撤销（回滚）时，用备份还原；其他人可同时查看旧版本（MVCC）。

这两个⽇志将InnoDB存储引擎和事务专题详细讨论。

2.6 服务器日志维护

MySQL服务器可以创建多种不同的日志文件来帮助我们查看服务器的活动。

但是必须定期清理这些文件，以免日志占用过多的磁盘空间。

在启用日志的情况下，通常希望备份和删除旧的日志文件，并把日志写到新文件。

 默认二进制日志的过期时间为30天，过期后将自动删除，要指定自定义过期时间，可以使用系统变量 binlog_expire_logs_seconds=N单位为秒，在下一次启动服务器和刷新日志时删除过期日志文件；

 强制使用新的日志文件可以手动刷新日志，当执行 FLUSH LOGS 语句或 mysqladmin flush-logs、 mysqladmin refresh、 mysqldump --flush-logs、 mysqldump --master-data 命令时，会发生日志刷新。

此外当二进制日志文件大小达到max_binlog_size 系统变量指定的值时，服务器会自动刷新二进制日志。

 FLUSH LOGS 支持可选的修饰符以启用个别日志的选择性刷新：

FLUSH LOGS # 刷新所有日志
FLUSH BINARY LOGS # 刷新二进制日志
FLUSH ERROR LOGS # 刷新错误日志
FLUSH GENERAL LOGS # 刷新一般查询日志
FLUSH RELAY LOGS # 刷新中继日志
FLUSH SLOW LOGS # 刷新慢查询日志

 刷新一般查询日志、慢查询日志或错误日志只是关闭并重新打开日志文件，如果要备份可以先重命名再执行刷新操作，比如一般查询日志、慢查询日志或错误日志文件名分别为： mysql.log、mysql-slow.log 和 err.log，可以在命令行中使用如下一系列命令：

cd mysql-data-directory #进入日志目录
mv mysql.log mysql.log.old #重命名一般查询日志
mv mysql-slow.log mysql-slow.log.old #重命名慢查询日志
mv err.log err.log.old #重命名错误日志
mysqladmin flush-logs #刷新日志

要在运行时重命名一般查询日志或慢查询日志

首先连接到服务器并禁用日志：

SET GLOBAL general_log = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

在禁用日志的情况下，重命名日志文件，例如用mv命令从命令行执行重命名操作

再次启用日志：

SET GLOBAL general_log = 'ON';
SET GLOBAL slow_query_log = 'ON';

这种方法适用于任何平台且不需要重启服务器.

  • 注意：错误日志是关不掉的！！

关于日志是备份与恢复中的使用我们将在备份与恢复专题中继续介绍。

2.7 配置日志输出位置

[mysqld]                # 表示以下是 MySQL 服务器（mysqld）的配置
log-output=FILE         # 日志输出方式：文件（非表）
general-log=1           # 启用通用查询日志（记录所有 SQL 操作）
general_log_file=/var/log/mysql/general.log  # 通用日志存储路径

slow-query-log=1        # 启用慢查询日志
slow_query_log_file=/var/log/mysql/slow-query.log  # 慢日志存储路径
long_query_time=10      # 定义慢查询阈值（超过 10 秒的查询）

log-error=/var/log/mysql/error.err  # 错误日志存储路径

log-bin=/var/log/mysql/binlog  # 启用二进制日志（binlog）并设置路径
server-id=1             # 服务器唯一标识（主从复制必需）

 

三. mysql System Schema (mysql系统库)

Mysql Schema是一个系统库，表中存储了MySQL服务器运行时所需的信息。

广义上，mysql schema包含存储数据库对象元数据的数据字典和用于其他操作目的的系统表。

数据字典和系统表都是保存数据库对象属性的存储结构。 

数据字典表和系统表位于数据目录下一个名为mysql.ibd的表空间文件中，使用的是InnoDB存储引擎。

我们在安装完mysql之后，第一次登陆时，就会看到下面这4个数据库。

这4个就是mysql System Schema (mysql系统库)。

3.1 数据字典

MySQL的数据字典，用来存储有关数据库对象自身的信息，不可以随意修改，否则可能造成服务器无法运行：

以下列出几个常见的数据字典表：

  • character_sets：有关可用字符集的信息（我们指定为utfmb4）
  • check_constraints：有关表上定义的CHECK约束的信息
  • collations：每个字符集的排序规则信息
  • column_type_elements：列类型的信息
  • columns：有关表中列的信息
  • indexes：有关表索引的信息
  • tables：有关数据库中表的信息
  • tablespace_files：有关表空间使用的文件信息
  • tablespaces：有关活动表空间的信息
  • triggers：有关触发器的信息

数据字典表是受保护的，只能在调试版本中访问，在发行版中没有权限访问，如果在发行版本中查询表中的数据，出提示拒绝访问：

mysql> select * from mysql.character_sets;   # 访问可用字符集的信息
ERROR 3554 (HY000): Access to data dictionary table 'mysql.catalogs' is
rejected.

 

  • INFORMATION_SCHEMA 实现了某些数据字典的视图，可以通过视图查看某些数据字典的内容：
# 通过INFORMATION_SCHEMA中的视图访问可用字符集的信息
mysql> SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS;

 

3.2 系统表

系统表按功能大致可以分为以下几类：权限授予系统表、对象信息系统表、日志系统表、服务器端帮助系统表、时区系统表、复制系统表、优化器系统表、杂项系统表。

mysql系统库中的表如下所示：

use mysql;
show tables;

 

 

  • 权限授予系统表

包含有关用户帐户及帐户拥有的权限授权信息，主要的表有：

  • user：用户帐户、全局权限和其他列。
  • global_grants：为用户分配的动态全局权限；
  • db：数据库级权限。
  • tables_priv：表级权限。
  • columns_priv：列级权限。
  • procs_priv：存储过程和函数权限。
  • proxies_priv：代理用户权限。
  • default_roles：列出了用户连接和认证后要激活的默认角色。
  • role_edges：user 表的关联表，区分 user 表中某一行记录是帐户还是角色
  • password_history：密码更改的信息

我们来重点学习一下user表

desc user;

 我们也可以查询一下我们系统有几个用户

select * from user\G

 

 这些字段啥意思呢？

字段名称

含义

示例值

说明

Select_priv

查询权限

Y/N

是否允许执行 SELECT 查询

Insert_priv

插入权限

N

是否允许 INSERT 操作

Update_priv

更新权限

N

是否允许 UPDATE 操作

Delete_priv

删除权限

N

是否允许 DELETE 操作

Create_priv

创建权限

N

是否允许创建数据库/表

Drop_priv

删除权限

N

是否允许删除数据库/表

Alter_priv

修改权限

N

是否允许 ALTER TABLE

Super_priv

超级权限

N

是否允许管理操作（如 KILL 进程）

Execute_priv

执行权限

N

是否允许执行存储过程

Create_user_priv

创建用户

N

是否允许创建新用户

Trigger_priv

触发器权限

N

是否允许操作触发器

账户安全字段（Security）

字段名称

含义

示例值

说明

plugin

认证插件

caching_sha2_password

MySQL 8.0 默认认证方式

authentication_string

加密密码

$A$005$...

密码的加密存储（不可逆）

password_expired

密码过期

N

密码是否已过期

password_last_changed

最后修改时间

2025-05-08

密码最后修改时间戳

account_locked

账户锁定

Y

账户是否被锁定（禁止登录）

连接限制字段（Connection Limits）

字段名称

含义

示例值

说明

max_connections

最大连接数

0

每小时最大连接次数（0=无限制）

max_user_connections

用户最大连接

0

同时连接数限制（0=无限制）

max_questions

最大查询数

0

每小时最大查询数

max_updates

最大更新数

0

每小时最大更新操作数

SSL/TLS 安全字段

字段名称

含义

示例值

说明

ssl_type

SSL 类型

(空)

加密连接类型（如 SSL/X509）

ssl_cipher

加密算法

0x

使用的加密算法

x509_issuer

证书签发者

0x

X.509 证书签发者信息

x509_subject

证书主题

0x

X.509 证书主题信息

特殊权限字段

字段名称 含义 示例值 说明
Create_role_priv 创建角色 N 是否允许创建角色
Drop_role_priv 删除角色 N 是否允许删除角色
Create_tablespace_priv 创建表空间 N 是否允许创建表空间
Password_reuse_history 密码重用历史 NULL 禁止使用最近几次的密码
Password_reuse_time 密码重用时间 NULL 禁止使用多少天内的旧密码

 

  • 对象信息系统表

包含有关组件、可加载的服务器插件和函数的信息：

  • component：使用 INSTALL COMPONENT 安装的服务器组件，表中列出了在服务器启动期间安装的组件。
  • func：使用 CREATE FUNCTION 安装的可加载函数，表中列出了在服务器启动期间加载的函数。
  • plugin：使用 INSTALL PLUGIN 安装的服务器插件，表中列出了在服务器启动期间安装的插件。
  • 日志系统表

服务器使用日志系统表进行日志记录：

  • general_log：一般查询日志表。
  • slow_log：慢查询日志表。

日志表使用 CSV 存储引擎。

  • 服务器端帮助系统表

包含服务器端帮助信息：

  • help_category：有关帮助类别的信息。
  • help_keyword：与帮助主题关联的关键字。
  • help_relation：帮助关键字和主题之间的映射。
  • help_topic：帮助主题内容。
  • 时区系统表

包含时区信息：

  • time_zone：时区 ID 以及是否使用闰秒。
  • time_zone_leap_second：发生闰秒时如何修正。
  • time_zone_name：时区 ID 和名称之间的映射。
  • time_zone_transition，time_zone_transition_type：时区说明及偏移量。
  • 复制系统表

服务器使用以下系统表来支持复制，有关复制的内容我们在主从复制专题中详细介绍

  • gtid_executed：用于存储GTID的值。
  • ndb_binlog_index：用于NDB Cluster复制的二进制日志信息。只有在支持 NDB CLUSTER的服务器才会创建此表，我们的课程不讨论NDB的相关内容
  • slave_master_info, slave_relay_log_info, slave_worker_info：用于存储从节点服务器上的复制信息。

以上几张表都使用 InnoDB 存储引擎。

  • 优化器系统表

这些系统表供优化器使用：

  • innodb_index_stats, innodb_table_stats：用于 InnoDB的持久优化器统计信息。
  • server_cost, engine_cost：优化器成本模型使用的表，包含查询期间发生的操作成本估算信息。 server_cost：包含服务器操作的优化器成本估算。 engine_cost包含对特定存储引擎操作的估计。
  • 杂项系统表 

  • audit_log_filter, audit_log_user：如果安装了MySQL Enterprise Audit，存储审计日志过滤器定义和用户帐户。
  • firewall_group_allowlist, firewall_groups, firewall_membership, firewall_users, firewall_whitelist：如果安装了MySQL Enterprise Firewall，这些存储表防火墙的使用信息。
  • servers：由 FEDERATED 存储引擎使用。
  • innodb_dynamic_metadata：由 InnoDB 存储引擎用来存储快速变化的表元数据，例如自动递增计数器值和索引树损坏标志。

关于mysql系统库中表的具体结构，我们在系统数据库专题中详细介绍

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布