mysql日誌系統簡單使用

pedro7發表於2021-02-27
MySql

MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB 公司開發，屬於 Oracle 旗下產品。MySQL 是最流行的關係型資料庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關聯式資料庫管理系統) 應用軟體之一。

在任何一種資料庫中，都會有各種各樣的日誌，記錄著資料庫工作的方方面面，以幫助資料庫管理員追蹤資料庫曾 經發生過的各種事件。
在 MySQL 中，有 4 種不同的日誌，分別是

  • 錯誤日誌
  • 二進位制日誌 （BINLOG 日誌）
  • 查詢日誌
  • 慢查詢日誌

這些日誌記錄著資料庫在不同方面的蹤跡，學會使用mysql日誌對我們使用和維護資料庫有很大幫助。

一、錯誤日誌

錯誤日誌是 MySQL 中最重要的日誌之一，它記錄了當 mysqld 啟動和停止時，以及伺服器在執行過程中發生任何嚴重錯誤時的相關資訊。當資料庫出現任何故障導致無法正常使用時，可以首先檢視此日誌。
該日誌是預設開啟的 ， 預設存放目錄為 mysql 的資料目錄（var/lib/mysql）, 預設的日誌檔名為 hostname.err（hostname是主機名）。

1、檢視日誌位置

show variables like 'log_error%';

檢視日誌位置

2、檢視日誌內容

tail -f /var/lib/mysql/xxx.err

檢視日誌內容

二、二進位制日誌

二進位制日誌（BINLOG）記錄了所有的 DDL（資料定義語言）語句和 DML（資料操縱語言）語句，但是不包括資料查詢語句。此日誌對於災難時的資料恢復起著極其重要的作用，MySQL的主從複製， 就是通過該binlog實現的。

1、開啟二進位制日誌

二進位制日誌，預設情況下是沒有開啟的，需要到MySQL的配置檔案中開啟，並配置MySQL日誌的格式。

  • 配置檔案位置 : /etc/my.cnf
  • 日誌存放位置 : 配置時，給定了檔名但是沒有指定路徑，日誌預設寫入Mysql的資料目錄。
#配置開啟binlog日誌， 日誌的檔案字首為 mysqlbin -----> 生成的檔名如 : mysqlbin.000001,mysqlbin.000002 
log_bin=mysqlbin
 
#配置二進位制日誌的格式 
binlog_format=STATEMENT

2、二進位制日誌格式

（1）STATEMENT
該日誌格式在日誌檔案中記錄的都是SQL語句（statement），每一條對資料進行修改的SQL都會記錄在日誌檔案中，通過Mysql提供的mysqlbinlog工具，可以清晰的檢視到每條語句的文字。主從複製的時候，從庫（slave）會將日誌解析為原文字，並在從庫重新執行一次。

（2）ROW
該日誌格式在日誌檔案中記錄的是每一行的資料變更，而不是記錄SQL語句。比如，執行SQL語句 ：update tb_book set status='1' , 如果是STATEMENT 日誌格式，在日誌中會記錄一行SQL檔案； 如果是ROW，由於是對全表進行更新，也就是每一行記錄都會發生變更，ROW 格式的日誌中會記錄每一行的資料變更。

（3）MIXED
這是目前MySQL預設的日誌格式，即混合了STATEMENT 和 ROW兩種格式。預設情況下采用STATEMENT，但是在一些特殊情況下采用ROW來進行記錄。MIXED 格式能儘量利用兩種模式的優點，而避開他們的缺點。

3、日誌讀取

由於日誌以二進位制方式儲存，不能直接讀取，需要用mysqlbinlog工具來檢視，語法如下

mysqlbinlog log-file;

4、檢視STATEMENT格式日誌

（1）執行插入語句

insert into tb_book values(null,'Lucene','2088-05-01','0');

（2）在mysql資料檔案目錄找到二進位制檔案
找到二進位制檔案

  • mysqlbin.index是日誌索引檔案，記錄日誌的檔名。
  • mysql-bin.000001是日誌檔案

（3）檢視日誌檔案
檢視日誌檔案
在開啟的日誌檔案中找到insert語句資訊

5、檢視ROW格式日誌

（1）在配置檔案中進行配置

#配置開啟binlog日誌， 日誌的檔案字首為 mysqlbin -----> 生成的檔名如 : mysqlbin.000001,mysqlbin.000002 
log_bin=mysqlbin
 
#配置二進位制日誌的格式 
binlog_format=ROW

注：每次修改配置檔案都記得重啟mysql服務

service mysql restart

（2）插入資料
插入資料

  • 如果日誌格式是ROW，直接檢視資料是看不懂的，可以在mysqlbinlog後面加上引數-vv
  • 使用--no-defaults引數可以避免字符集帶來的影響
    檢視資料更新資訊

6、日誌刪除

對於比較繁忙的系統，由於每天生成日誌量大 ，這些日誌如果長時間不清楚，將會佔用大量的磁碟空間。下面講解幾種刪除日誌的常見方法
（1）方法一
通過 Reset Master 指令刪除全部 binlog 日誌，刪除之後，日誌編號，將從 xxxx.000001重新開始 。
刪除之前 ，先查詢下日誌檔案 ：
查詢日誌檔案
執行刪除日誌指令

Reset Master

再查詢一下
查詢刪除結果
最後剩下的000001是一個空的配置檔案

（2）方法二

purge master logs to 'mysqlbin.******

該命令將刪除******編號之前的所有日誌
（3）方法三

purge master logs vefore 'yyyy-mm-dd hh24:mi:ss'

該命令將刪除yyyy-mm-dd hh24:mi:ss之前產生的所有日誌

（4）方法四
設定引數 expire_logs_days=#，此引數的含義是設定日誌的過期天數，過了指定的天數後日志將被自動刪除，這樣有利於減少DBA管理日誌的工作量
設定日誌過期天數

三、查詢日誌

查詢日誌中記錄了客戶端的所有操作語句，而二進位制日誌不包含查詢資料的SQL語句。
預設情況下， 查詢日誌是未開啟的。如果需要開啟查詢日誌，可以設定以下配置

#該選項用來開啟查詢日誌 ， 可選值 ： 0 或者 1 ； 0 代表關閉， 1 代表開啟 
general_log=1
 
#設定日誌的檔名 ， 如果沒有指定， 預設的檔名為 host_name.log 
general_log_file=file_name

1、開啟查詢日誌

開啟查詢日誌

2、執行一組操作

執行一組操作

3、檢視查詢日誌

檢視查詢日誌

四、慢查詢日誌

慢查詢日誌記錄了所有執行時間超過引數 long_query_time 設定值並且掃描記錄數不小於min_examined_row_limit 的所有的SQL語句的日誌。long_query_time 預設為 10 秒，最小為 0， 精度可以到微秒。

1、檔案位置和格式

慢查詢日誌預設是關閉的 。可以通過兩個引數來控制慢查詢日誌

# 該引數用來控制慢查詢日誌是否開啟， 可取值： 1 和 0 ， 1 代表開啟， 0 代表關閉 
slow_query_log=1 
 
# 該引數用來指定慢查詢日誌的檔名 
slow_query_log_file=slow_query.log
 
# 該選項用來配置查詢的時間限制， 超過這個時間將認為值慢查詢， 將需要進行日誌記錄， 預設10s 
long_query_time=10

設定檔案位置和格式

一般設定成2s左右就可以了，在我的實驗中，為了完成慢查詢，我設定成了0.5s

在mysql客戶端下可以查詢到當前的long_query_time
查詢當前long_query_time

2、執行查詢操作

（1）查詢之前先看一眼慢查詢日誌
慢查詢日誌
（2）執行一個比較快的查詢動作
比較快的查詢動作
（3）執行一個慢的查詢動作
執行一個慢的查詢動作
如果慢查詢日誌內容很多， 直接檢視檔案，比較麻煩， 這個時候可以藉助於mysql自帶的 mysqldumpslow 工具， 來對慢查詢日誌進行分類彙總。
mysqldumpslow

由於這裡只有一條慢查詢記錄，沒有體現出mysqldumpslow的作用，但如果慢查詢記錄多，就可以分類彙總


以上便是關於mysql日誌系統的簡單使用總結

相關文章