【Day 33】Linux-Mysql日志

一、數據庫日志的核心作用

故障恢復：當數據庫因崩潰（如斷電、進程異常）、誤操作（如刪表）導致數據丟失時，通過日志恢復數據至一致狀態。
數據一致性保障：確保事務的 ACID 特性（尤其是原子性、持久性），避免部分操作未提交或重復執行。
審計與追溯：記錄用戶對數據庫的關鍵操作（如登錄、刪改數據），用于安全審計或定位 “誰在何時做了什么”。
性能診斷：通過日志分析慢查詢、鎖等待、資源瓶頸等問題，優化數據庫性能。

二、主流數據庫日志分類與詳解

1. 二進制日志（Binary Log）

記錄規則：二進制日志僅記錄增刪改數據（DML）、修改庫表結構（DDL）等寫操作，不記錄 SELECT、SHOW 等純查詢操作；
路徑建議：需提前規劃 binlog 存儲路徑，推薦單獨掛載磁盤存放（遠離系統盤和數據盤），避免空間不足或 IO 沖突，同時配置自動清理規則，保障穩定可用。

//建議將二進制日志存放到不同的存儲設備上。

（1）格式：二進制格式（非明文），需通過 mysqlbinlog?工具解析查看。

（2）作用：

主從復制：主庫通過 binlog 將修改同步到從庫，保證主從數據一致。
時間點恢復（PITR）：當數據誤刪 / 誤改時，可通過 binlog 恢復到指定時間點或操作前的狀態。

（3）修改日志配置:

//_id=1...2^32；_bin=文件名自定

（4）二進制日志的記錄格式

MySQL 的binlog_format 配置用于指定二進制日志的記錄格式，決定如何記錄數據庫的修改操作。

格式	特點
STATEMENT	直接記錄執行的 SQL 語句，不記錄數據行的具體變化。
ROW	不記錄SQL語句本身，記錄數據行的 “修改前后變化”
MIXED	自動根據 SQL 語句類型選擇STATEMENT/ROW 簡單 SQL 用 STATEMENT，復雜 SQL用 ROW

格式

特點

STATEMENT

直接記錄執行的 SQL 語句，不記錄數據行的具體變化。

ROW

不記錄SQL語句本身，記錄數據行的 “修改前后變化”

MIXED

自動根據 SQL 語句類型選擇STATEMENT/ROW

簡單 SQL 用 STATEMENT，復雜 SQL用 ROW

# at 1780：當前事件在 binlog 文件中的起始字節位置（1780 字節處開始）。
#250825 16:19:01：事件發生的時間戳（2025 年 8 月 25 日 16:19:01）。
server id 11：生成該事件的 MySQL 服務器 ID
end_log_pos 1841：當前事件在 binlog 中的結束字節位置（到 1841 字節處結束）。
CRC32 0x727df518：事件內容的 CRC32 校驗值（用于驗證日志完整性，防止篡改或損壞）。
Table_map:?sul123.store?mapped to number 97：事件類型為Table_map（表映射），表示后續操作的表是庫名sul123.表名store，MySQL 內部為其分配的臨時 ID 是 97（復制時從庫通過該 ID 匹配本地表）。
# has_generated_invisible_primary_key=0：附加屬性，說明該表沒有生成 “不可見主鍵”（MySQL 8.0 + 特性，自動為無主鍵表添加隱藏主鍵，此處為 0 表示未啟用）。
SET @@SESSION.GTID_NEXT= 'AUTOMATIC'：mysqlbinlog工具自動添加的語句，臨時寫。
# End of log file：標識當前 binlog 文件解析結束。

（5）常用操作：

>SHOW BINARY LOGS;? ? ? ?//查看當前 MySQL 服務器上所有二進制日志文件的列表
>SHOW?MASTER STATUS;? ? //查看當前主庫的二進制日志狀態，主要用于主從復制場景
>SHOW?BINLOG EVENTS IN '日志文件名' [?FROM 位置] [ LIMIT 行數];? ? ? ?//查看指定二進制日志文件中的具體事件（操作記錄）
# mysqlbinlog /Log/master.000002? ? //直接解析指定的二進制日志文件
# mysqlbinlog --start-datetime="2025-08-25 08:00:00" --stop-datetime="2025-08-25 11:00:00"? 二進制日志文件? ? ? ? //按時間范圍篩選日志內容，只輸出兩個時間點之間的事件
# mysqlbinlog --start-position=13 --stop-position=63 二進制日志文件
//按日志位置（偏移量）篩選內容，只輸出從?313?到?463?位置之間的事件。
# mysqlbinlog binlog 二進制日志?>binlog.txt? ?? // 將二進制日志內容解析并保存到文本文件
# mysqlbinlog --start-datetime="2024-05-01 00:00:00" --stop-datetime="2024-05-01 12:00:00" 二進制日志文件 | mysql -uroot -p? ?//基于 binlog 恢復數據

2. 錯誤日志（Error Log）

// 故障排查核心

錯誤日志記錄 MySQL 服務器的啟動 / 關閉過程、嚴重錯誤（如崩潰、連接失敗）、警告信息，是定位數據庫無法啟動、進程異常等問題的首要依據。

（1）格式：明文文本格式，可直接用 cat/tail?查看。

（2）記錄內容：

服務器啟動時的配置加載信息（如 “加載 my.cnf 成功”“InnoDB 引擎初始化完成”）。
運行中的錯誤（如 “內存不足”“磁盤空間滿”“主從復制連接失敗”）。
服務器關閉時的狀態信息。

（3）配置

# 啟用錯誤日志（指定日志路徑）
log_error = /var/lib/mysql/mysql-error.log
# 日志級別（可選：DEBUG/INFO/NOTICE/WARNING/ERROR/CRITICAL/ALERT/EMERGENCY，默認 ERROR）
log_error_verbosity = 3  # 3=記錄 ERROR/WARNING/NOTICE，適合日常排查

（4）常用操作

# tail -f /var/lib/mysql/mysql-error.log? ? ? ?//實時查看錯誤日志
# grep "ERROR" /var/lib/mysql/mysql-error.log | tail -10? ? ? ?? ? //查看最近錯誤

3. 慢查詢日志（Slow Query Log）

????????慢查詢日志記錄執行時間超過long_query_time變量定義的時長的 SQL 語句，定位 “拖慢數據庫的低效查詢”。默認不啟用。

（1）格式：明文文本格式，記錄查詢時間、執行時長、SQL 語句等信息。

（2）關鍵配置

>SHOW GLOBAL VARIABLES LIKE '匹配模式';? ? ? ? //查看數據庫的配置信息
匹配模式?支持通配符?%（匹配任意字符序列）和?_（匹配單個字符）

? ? ? ? //如果去掉 GLOBAL，則查詢的是當前會話的變量

大量慢查詢會直接導致：

數據庫卡慢，正常查詢也變慢
應用響應延遲，用戶操作卡頓
服務器資源（CPU、內存、IO）被占滿
嚴重時可能引發數據庫崩潰或連接失敗，影響整個系統運行

常用操作：

# cat /var/lib/mysql/mysql-slow.log? ? ? //查看慢查詢日志：??
# mysqldumpslow -s t /var/lib/mysql/mysql-slow.log? ? //按執行時間排序,工具分析慢查詢

4. 查詢日志（General Query Log）：全操作記錄

5. 中繼日志（Relay Log）：主從復制專屬

（三）通用日志類型：事務日志與歸檔日志

除了數據庫專屬日志，以下兩類日志是所有 DBMS 的通用概念：

1. 事務日志（Transaction Log）

2. 歸檔日志（Archive Log）

三、日志管理的關鍵注意事項

磁盤空間控制：
- 日志（尤其是 binlog、查詢日志）會快速占用磁盤，需配置自動過期。
- 避免將日志與數據存儲在同一磁盤，防止日志占滿導致數據庫無法寫入。
安全性：
- 日志可能包含敏感數據（如用戶密碼、業務數據），需限制日志文件權限（如 MySQL 日志設為?600?權限，僅 root 可讀寫）。
- 歸檔日志建議加密存儲（如 AES 加密），防止泄露。
性能平衡：
- 慢查詢日志的閾值需合理設置（如高并發場景設為 500ms，避免記錄過多正常查詢）。
定期備份與測試：
- 歸檔日志需定期備份到異地，避免本地磁盤損壞導致日志丟失。
- 定期測試 “基于日志的恢復流程”（如 MySQL 用 binlog 恢復、PostgreSQL 用 WAL 恢復），確保日志可用。

二、MySQL 備份與恢復詳解

（一）備份基礎：核心概念與分類

在選擇備份方案前，需先明確備份的核心維度，避免盲目操作。

維度	確認項
需求明確	要保多久數據、故障后能接受丟多少數據（RPO）、多久能恢復業務（RTO）、需要恢復全庫還是單表 / 單條數據；
環境適配	數據庫是什么引擎、數據量多大、業務忙不忙、服務器空間夠不夠、有沒有從庫可用、是否支持增量備份和時間點恢復、備份工具兼容和方案是否適配
風險規避	備份時數據會不會被修改、備份時會不會影響業務、備份后文件會不會損壞、恢復時能不能用（比如定期測試）

1. 按備份數據范圍分類

備份類型	定義	優點	缺點	適用場景
全量備份	備份整個數據庫實例（所有數據庫、表、結構 + 數據）	恢復簡單（直接恢復即可）	備份文件大、耗時久、占用資源多	基礎備份、小型數據庫
增量備份	僅備份上一次備份（全量 / 增量）后變化的數據必須按順序恢復增量備份	備份文件小、速度快、資源占用低	恢復復雜（需先恢復全量 + 按順序恢復增量）	大型數據庫
差異備份	僅備份上一次全量備份后變化的數據	恢復較增量簡單（全量 + 最新差異）、文件比增量大但比全量小	備份文件隨時間增大（直到下一次全量）	中型數據庫

備份類型

定義

優點

缺點

適用場景

全量備份

備份整個數據庫實例（所有數據庫、表、結構 + 數據）

恢復簡單（直接恢復即可）

備份文件大、耗時久、占用資源多

基礎備份、小型數據庫

增量備份

僅備份上一次備份（全量 / 增量）后變化的數據

必須按順序恢復增量備份

備份文件小、速度快、資源占用低

恢復復雜（需先恢復全量 + 按順序恢復增量）

大型數據庫

差異備份

僅備份上一次全量備份后變化的數據

恢復較增量簡單（全量 + 最新差異）、文件比增量大但比全量小

備份文件隨時間增大（直到下一次全量）

中型數據庫

2. 按備份時數據庫狀態分類

冷備份（離線備份）：備份前需停止 MySQL 服務，確保數據無寫入。
優點：備份文件一致性極高；缺點：業務中斷，適用于非核心低峰期或測試環境。
熱備份（在線備份）：備份時數據庫可正常讀寫，無感知。
優點：零業務中斷；缺點：需特定工具（如mysqldump --single-transaction、Percona XtraBackup），僅支持?InnoDB 引擎（MyISAM 不支持事務，無法熱備份）。
溫備份（半在線備份）：備份時數據庫可讀取，但禁止寫入。
優點：業務只讀不中斷；缺點：影響寫操作，適用于讀多寫少場景。

3. 按備份文件格式分類

邏輯備份：將操作備份為 SQL 語句文件，可跨版本、跨平臺恢復。mysqldump
物理備份：直接拷貝數據文件。直接拷貝 MySQL 存儲引擎的物理文件（如 .ibd?.frm?文件），恢復速度快，但僅支持相同版本、相同架構的 MySQL。Percona XtraBackup

（二）核心備份工具與方法

MySQL 官方及第三方提供了多種備份工具，不同工具適配不同場景，以下是最常用的 3 類工具。

1. mysqldump

// MySQL 自帶的邏輯備份工具，通過導出 SQL 語句實現備份，支持全量、單庫、單表備份，適用于中小型數據庫（數據量 < 10GB）。

1.1 基礎語法

mysqldump 選項數據庫名表名?> 備份文件.sql? ? ? ? ?

需求	命令	說明
全量備份（所有庫）	mysqldump -uroot -p --all-databases >?	備份所有數據庫（含系統庫 mysql），需輸入密碼
單庫備份	mysqldump -uroot -p 庫名 >?	僅備份此數據庫
多庫備份	mysqldump -uroot -p --databases 庫名1 庫名2? >?	僅備份此數據庫
單表備份	mysqldump -uroot -p 庫名表名 >?	僅備份此庫中的此表
熱備份（InnoDB 引擎）	mysqldump -uroot -p --single-transaction 庫名	通過事務快照實現熱備份，不鎖表（僅 InnoDB 生效）
溫備份（鎖表）	mysqldump -uroot -p --lock-all-tables 庫名 >	加全局讀鎖，禁止寫入，確保數據一致性，直到備份結束
備份時包含存儲過程 / 函數	mysqldump -uroot -p --routines 庫名 >?	默認不備份存儲過程，需加 --routines
在備份文件中記錄備份時 MySQL 二進制日志（binlog）的文件名和位置	mysqldump -uroot -p?--master-date=1 >	=1 記錄的 binlog 位置信息以?非注釋的 SQL 語句形式寫入備份文件，恢復時會自動執行 =2 記錄的 binlog 位置信息以注釋形式寫入備份文件，恢復時不會執行

1.2 案例顯示

（0）?準備存放備份數據的目錄

（1）周一全量備份

# mysqldump -uroot -p --lock-all-tables --master-data=2 --all-databases > /db/bak/data_$(date +%F_%T).sql

（2）模擬數據庫變化，登錄 MySQL，手動插入測試數據（生成 binlog 事件）

（3）周二增量備份

①方法1：指定起始位置備份（精準）

# mysqlbinlog --start-position=157 /db/log/binlog.000021 > /db/bak/data_$(date +%F_%T).sql? ? ?//導出binlog.000021中從1325位置到當前的所有事件（即周二新增的數據）

②方法2：按 binlog 文件備份（適用于多文件場景）

若全量備份后生成了多個 binlog 文件，可直接備份新增的文件：

# cp /db/log/binlog.000021 /backup/? ? ? ??//?直接復制文件（后續恢復時用mysqlbinlog解析）

查看所有 binlog 文件，結合文件名的生成順序，大致判斷哪些文件包含需要的日志

假設全量備份在?20:05:30?結束，增量的起始位置就是 備份結束后第一個事件的起點。

// 這里可知是1325

（4）模擬數據變化

（5）周三增量備份

# grep "end_log_pos" /db/bak/increment_tue_*.sql | tail -1
//查看周二增量備份的最后位置（假設周二增量結束位置是2500）
# mysqlbinlog --start-position=2500 /db/log/binlog.000021 > /db/bak/increment_wed_2025-08-27_10:00:00.sql? ?
//從2500位置開始備份周三的新增數據（假設binlog仍為000021）

（6）模擬報錯

>DROP TABLE sul123.store;? ? ? //?模擬故障：誤刪除store表

（7）臨時關閉二進制功能（防止恢復過程生成新日志干擾）

（8）恢復周一的全局

（9）依次恢復增量

（10）根據二進制日志恢復未備份的數據

假設故障發生在周三增量備份后，需提取 “周三增量結束位置” 到 “故障發生前” 的 binlog 事件：

意思是備份到了8582

SET sql_log_bin = 1;? ? ? ?//?恢復binlog記錄（可選，根據業務需求）

恢復后若用戶無法使用，執行FLUSH PRIVILEGES;或重新授權。

跨版本恢復時避免直接恢復?mysql?庫：若從 5.7 恢復到 8.0，建議：

僅恢復業務庫（排除?mysql?庫）；
手動在新庫中重新創建用戶并授權（避免版本兼容問題）。

2. Percona XtraBackup（第三方物理熱備份工具）

Percona XtraBackup（簡稱 PXB）是 Percona 推出的免費物理備份工具，支持 InnoDB 引擎的熱備份，備份 / 恢復速度遠快于?mysqldump，適用于大型數據庫（數據量 > 10GB）。

（1）下載

（2）自動拷貝（會自動鎖表）

（3）完全備份

（4）增量備份

（5）模擬損壞

（6）恢復備份數據

# xtrabackup --prepare --apply-log-only --target-dir=/db/bak/data_2024-11-05_full/
# xtrabackup --prepare --apply-log-only --target-dir=/db/bak/data_2024-11-05_full/ --incremental-dir=<第一次增量目錄>
# xtrabackup --prepare ?--target-dir=/db/bak/data_2024-11-05_full/ --incremental-dir=<最后一次增量目錄>? ? ? ? ? ? ? ?? ?//準備最后一次增量備份時，不需要加--apply-log-only參數
# xtrabackup --no-defaults --copy-back --target-dir=/db/bak/data_2024-11-05_full/ --datadir=原始數據目錄? ? ? ? ? ? ? ? ? ? ?//恢復數據
# chown -R mysql.mysql /db/data/? ? ??//修改權限并重啟