目錄

?一.錯誤日志

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_errorWHERE 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系統庫中表的具體結構，我們在系統數據庫專題中詳細介紹