MySQL Server 層四個紀錄檔詳解

一、MySQL Server層紀錄檔簡介

一個mysql client發起一個連線請求，處理請求的過程如下圖所示：

MySQL紀錄檔是在MySQL server上生成的，不管更改哪個儲存引擎，這些紀錄檔都是需要有的，包括：

• 錯誤紀錄檔：記錄mysqld服務執行過程中出現的cordump、error、exception等
• 查詢紀錄檔：記錄使用者端所有的SQL。由於上線專案的SQL太多了，開啟查詢紀錄檔IO太多導致MySQL效率低下，我們一般都不會開啟，只有偵錯時才開啟
• 二進位制紀錄檔：記錄資料的更改（insert、update、delete、alter …），非常重要，可用於資料恢復，主從複製。主從複製技術依賴於log_bin，主庫所有的更改操作都記錄在log_bin裡，從庫從binlog讀取主庫所有的操作，自己再執行一遍。
• 慢查詢紀錄檔：記錄了一些執行時間超過指定值的SQL語句，可供開發人員分析耗時SQL，從而針對性優化

檢視紀錄檔相關變數

二、組態檔引數

my.cnf

通過set方法只能影響當前session，如果想要設定永久有效，需要在組態檔上進行設定，然後重啟MySQL服務，就可以永久生效

linux下重啟mysqld服務的命令：sudo service mysqld restart

我們檢視一下組態檔/etc/mysql/my.cnf

• 給出log-error的路徑就是開啟了log-error，如果不自定義log-error的路徑，預設在data_dir
• 在開啟log-bin=mysql-bin的同時還要加上server-id=1(表示當前MySQL Server的身份），否則sudo service mysqld restart無法重啟服務
• 設定過期的時間expire_log_days，因為總有一天磁碟會被這個紀錄檔佔滿，導致伺服器不可執行，超過設定時間後紀錄檔檔案會被刪除

三、錯誤紀錄檔

錯誤紀錄檔是 MySQL 中最重要的紀錄檔之一，它記錄了當 mysqld 啟動和停止，以及伺服器在執行過程中發生任何嚴重錯誤（cordump，error，exception…）時的相關資訊。當資料庫出現故障導致無法正常使用時，可以首先檢視此紀錄檔

mysqld 使用的錯誤紀錄檔名為 host_name.err(host_name 為主機名) ，並預設在引數data_dir(資料目錄)指定的目錄中寫入紀錄檔檔案

四、查詢紀錄檔

查詢紀錄檔記錄了client傳送的所有SQL語句

由於上線專案sql特別多，開啟查詢紀錄檔IO太多導致MySQL效率低，我們一般都不會開啟，只有在偵錯時才開啟，比如通過檢視sql發現熱點資料從而可以進行快取

show global variables like '%genera%';

五、二進位制紀錄檔

不是明文，不能直接檢視，需要通過mysqlbinlog工具（mysql原生自帶）解析binlog紀錄檔檔案

二進位制紀錄檔(BINLOG)記錄了所有的 DDL(資料定義語言)語句和 DML(資料操縱語言) 語句，但是不包括資料查詢語句（不記錄select操作，記錄的是資料庫的更改操作）

語句以“事件”的形式儲存，它描述了資料的更改過程。二進位制紀錄檔對於災難時的資料恢復起著極其重要的作用。

兩個重要的應用場景：主從複製、資料恢復

主從複製：主庫所有的更新操作（update、delete、insert、alter …）都記錄在binlog中，從庫讀主庫的binlog，把binlog的所有操作在從庫上在進行一遍

檢視當前的binlog：

show binary logs;  -- show master logs;

binlog預設在MySQL的data_dir下

1. 演示binlog記錄更改

我們先重新整理一下，生成一個新的binlog

切換資料庫

更改一下資料

再次檢視binlog

我們發現紀錄檔的filesize和剛才的不一樣了，肯定記錄我們剛才的資料更改操作

如果我們直接cat紀錄檔檢視，會發現不是明文，無法直接檢視

我們需要通過mysqlbinlog進行檢視，如下：

mysqlbinlog --no-defaults --database=school --base64-output=decode-rows -v --start-datetime='2022-03-01 00:00:00' --stop-datetime='2022-03-31 00:00:00' mysql-bin.000001 | more

database：指定檢視某個庫的更改base64-output：binlog解碼方式start-datetime & stop-datetime：指定檢視某個時間段內的更改，不寫則檢視所有的更改mysql-bin.000001：檢視的二進位制紀錄檔檔案

我們檢視一下binlog

@1、@2、@3、@4：表示資料庫表的4個欄位server id：表示我們在my.cnf中設定的id，用於標識當前MySQL的身份at 565、at679：指的是當前事件在binlog記錄的位置，資料恢復的時候使用

2. 演示binlog資料恢復

現在建立資料庫mytest，並建立表，新增資料

假如現在有人把庫刪除了：

這時mytest庫的所有表和資料都沒有了，然而這些操作都會記錄在二進位制紀錄檔binlog裡面

理論上來說，可以從binlog把丟失的資料恢復出來。由於恢復過程也是對資料的修改，所以恢復過程產生的紀錄檔也要記錄在binlog中，這就需要我們指定binlog恢復區間

我們現在知道，我們建庫、建表、插入資料的操作都記錄在mysql-bin.00003檔案中

我們現在重新整理一下，生成一個新的binlog，這就可以讓我們接下來資料恢復的操作被記錄在mysql-bin.00004檔案中，而不會在追加到mysql-bin.00003

我們先檢視mysql-bin.00003，找需要恢復的區間

從mysql-bin.000003中拿出區間內所有的操作，通過管道放到MySQL shell上執行

檢視一下當前的庫

再檢視一下表和資料

到這裡，資料已經全部恢復了

我們不僅可以通過binlog記錄的位置，得到需要恢復的區間，也可以通過binlog記錄的時間得到需要恢復的區間

引數為：start-datetime、stop-datetime

由於binlog有一個過期時間，過期的紀錄檔資料都會進行備份，沒有過期的資料可以直接通過binlog恢復，如果需要恢復過期的資料，通過以下命令即可：

mysql> source ~/data.sql

$cat ~/data.sql | mysql -u  root -p

六、慢查詢紀錄檔

MySQL可以設定慢查詢紀錄檔，當SQL執行的時間超過我們設定的時間，那麼這些SQL就會被記錄在慢查詢紀錄檔當中

我們通過檢視紀錄檔，用explain分析這些SQL的執行計劃，來判定為什麼效率低下，是沒有使用到索引？還是索引本身建立的有問題？或者是索引使用到了，但是由於表的資料量太大，花費的時間就是很長，那麼此時我們可以把表分成n個小表，比如訂單表按年份分成多個小表等

慢查詢紀錄檔相關的引數如下所示：

慢查詢紀錄檔記錄了包含所有執行時間超過引數 long_query_time（單位：秒）所設定值的 SQL語句的紀錄檔，在MySQL上用命令可以檢視，如下：

這個值是可以修改的：

現在修改成執行時間超過1秒的SQL都會被記錄在慢查詢紀錄檔當中！可以設定為0.01秒，表示10毫秒

慢查詢紀錄檔，預設名稱是host_name-slow.log，存放在MySQL的data_dir下，內容格式顯示大致如下：

show profiles命令可有檢視sql詳細的執行時間，全域性變數的名字是：profiling

首先需要：set profiling=on

到此這篇關於MySQL Server 層四個紀錄檔的文章就介紹到這了,更多相關MySQL Server層紀錄檔內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！