深入了解MySQL日誌——開啟binlog日誌

一、為什麼需要開啟binlog日誌

MySQL的binlog是一種二進位日誌，主要用於記錄MySQL伺服器上的操作，包括對資料庫進行的更改操作、授權操作等。當出現數據錯誤或者丟失數據時，我們可以通過查看binlog日誌來了解確切的操作情況，以此快速定位問題並進行修復。同時，binlog日誌還可以用於數據複製與同步，備份與還原等操作。

二、如何開啟binlog日誌

MySQL開啟binlog日誌的操作十分簡單，只需要在配置文件中添加對應的參數即可。

#打開MySQL配置文件
vim /etc/my.cnf

#進入文件後，在[mysqld]標籤下添加如下參數：
log-bin=mysql-bin
expire_logs_days=7

其中，”log-bin”表示開啟binlog日誌，”expire_logs_days”表示binlog日誌的保留時間，在該時間到達後會自動刪除。

三、binlog日誌參數說明

除了上述的兩個參數，我們還可以在配置文件中添加其他參數來對binlog日誌進行更為詳細的設置。

1. max_binlog_size

該參數用於設置binlog日誌文件的最大大小，一旦文件達到該大小，MySQL會自動創建一個新的binlog日誌文件繼續記錄。默認值為1073741824（1GB）。

2. binlog_format

該參數用於設置binlog日誌的格式，有三種：statement、row和mixed。

statement模式記錄的是每個執行的SQL語句，適用於大部分情況；row模式記錄的是每個操作的具體行數，適用於更為複雜的操作場景；mixed模式會自動根據SQL語句的操作類型（如簡單的select語句會使用statement模式，而複雜的更新語句會使用row模式）來選擇何種模式記錄。

3. binlog-do-db和binlog-ignore-db

這兩個參數用於設置哪些資料庫需要被記錄到binlog日誌中或者被忽略，多個資料庫之間需要使用”,”隔開。

#binlog-do-db
binlog-do-db=db1,db2

#binlog-ignore-db
binlog-ignore-db=mysql,sys

四、常用的binlog日誌操作

1. 查看當前的binlog日誌狀態

#通過登錄MySQL伺服器來查詢狀態
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 107
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

在結果中，”File”對應的是當前使用的binlog文件名，”Position”表示該文件中當前的偏移位置。

2. 查看當前所有的binlog日誌

該操作可以通過命令行工具來查看，也可以在MySQL客戶端中執行。

#通過命令行工具查看
[root@localhost data]# ls -l /var/lib/mysql/mysql-bin.*
-rw-r----- 1 mysql mysql 314936 Mar  6 14:34 /var/lib/mysql/mysql-bin.000010
-rw-r----- 1 mysql mysql 1073743650 Mar  7 14:32 /var/lib/mysql/mysql-bin.000011
-rw-r----- 1 mysql mysql 1073741988 Mar  8 14:30 /var/lib/mysql/mysql-bin.000012

#通過MySQL客戶端查看
mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     36978 |
| mysql-bin.000002 |     75440 |
| mysql-bin.000003 |  12020411 |
+------------------+-----------+

3. 查看指定時間段內的binlog記錄

該操作需要使用MySQL的binlog解析工具”mysqlbinlog”，可以通過該工具來查看指定時間段內的binlog記錄。

#查看從指定時間開始的binlog
[root@localhost data]# mysqlbinlog --start-datetime="2022-03-06T00:00:00" /var/lib/mysql/mysql-bin.000012

#查看從指定時間到指定時間之間的binlog
[root@localhost data]# mysqlbinlog --start-datetime="2022-03-06T00:00:00" --stop-datetime="2022-03-06T12:00:00" /var/lib/mysql/mysql-bin.000012

4. 清空過期的binlog日誌

當binlog日誌達到保留時間後，我們需要手動清理過期的binlog日誌。

#檢查當前的binlog狀態，找出需要刪除的binlog文件。
mysql> show master status;
+------------------+-----------+
| File             | Position  |
+------------------+-----------+
| mysql-bin.000012 | 108766634 |
+------------------+-----------+
1 row in set (0.00 sec)

#通過PURGE BINARY LOGS命令來刪除過期的binlog日誌，所有在當前文件之前的binlog文件都會被清除。
mysql> PURGE BINARY LOGS TO 'mysql-bin.000011';

五、總結

binlog日誌是MySQL非常重要的一部分，通過了解它的特性以及如何開啟、配置和操作binlog日誌，我們可以更好地保護數據的完整性和安全性，保證數據的一致性，並為數據的複製、同步和備份提供了方便和保障。