mysql紀錄檔檔案General_log和Binlog開啟及詳解

2022-07-11 18:04:01

背景：

週末歸納下mysql的紀錄檔檔案，其中general_log在mysql入侵中已經用到過，binlog即將會用到。注：mysql版本為5.7.20

General_log 詳解

1.介紹

開啟 general log 將所有到達MySQL Server的SQL語句記錄下來。

一般不會開啟開功能，因為log的量會非常龐大。但個別情況下可能會臨時的開一會兒general log以供排障使用。
相關引數一共有3：general_log、log_output、general_log_file

show variables like 'general_log';  -- 檢視紀錄檔是否開啟
set global general_log=on; -- 開啟紀錄檔功能

show variables like 'general_log_file';  -- 看看紀錄檔檔案儲存位置
set global general_log_file='tmp/general.lg'; -- 設定紀錄檔檔案儲存位置

show variables like 'log_output';  -- 看看紀錄檔輸出型別  table或file
set global log_output='table'; -- 設定輸出型別為 table
set global log_output='file';   -- 設定輸出型別為file

log_output='FILE’ 表示將紀錄檔存入檔案,預設值是FILE　

***log_output=‘TABLE’***表示將紀錄檔存入資料庫,這樣紀錄檔資訊就會被寫入到***mysql.slow_log***表中.

mysql資料庫支援同時兩種紀錄檔儲存方式,設定的時候以逗號隔開即可,如:log_output=‘FILE,TABLE‘.紀錄檔記錄到系統專用紀錄檔表中,要比記錄到檔案耗費更多的系統資源,因此對於需要啟用慢查紀錄檔,又需要比夠獲得更高的系統效能,那麼建議優先記錄到檔案.

2.開啟資料庫general_log步驟

先執行sql指令：show variables like ‘%log%’;

可以看到預設general_log是OFF的，我們直接開啟：***set global general_log = ON;***（永久修改需要在my.cnf的【mysqld】中新增：general_log = 1）

OK，現在mysql就會在***general_log_file***顯示的路徑檔案裡記錄general紀錄檔了！（從現在開始記錄）我預設的路徑是 /usr/local/mysql/data/VM_0_17_redhat.log

Binlog 詳解

1.介紹

MySQL的二進位制紀錄檔可以說是MySQL最重要的紀錄檔了，它記錄了所有的DDL和DML(除了資料查詢語句)語句（記錄mysql內部增刪改等對mysql資料庫有更新的內容的記錄（對資料庫的改動），對資料庫的查詢select或show等不會被binlog紀錄檔記錄），以事件形式記錄，還包含語句所執行的消耗的時間，MySQL的二進位制紀錄檔是事務安全型的。

一般來說開啟二進位制紀錄檔大概會有1%的效能損耗。

兩個最重要的使用場景:

其一：MySQL Replication在Master端開啟binlog，Mster把它的二進位制紀錄檔傳遞給slaves來達到master-slave資料一致的目的。

其二：自然就是資料恢復了，通過使用mysqlbinlog工具來使恢復資料。

二進位制紀錄檔包括兩類檔案：

二進位制紀錄檔索引檔案（檔名字尾為.index）用於記錄所有的二進位制檔案；

二進位制紀錄檔檔案（檔名字尾為.00000*）記錄資料庫所有的DDL和DML(除了資料查詢語句)語句事件。

2.開啟binlog紀錄檔

檢視binlog開啟狀態：

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

vim編輯開啟mysql組態檔my.cnf：

vim /etc/my.cnf
在【mysqld】中新增：
log-bin=/home/data/mysql-log/mysql-bin
server-id=12345

網上很多教學都只是新增log-bin一行就行了，此處我們為什麼要加 server-id？

因為我們用的是5.7及以上版本的話，不加server-id重啟mysql服務會報錯，5.7以下版本就不用加了。

所以必須新增server-id這個引數！隨機指定一個不能和其他叢集中機器重名的字串，如果只有一臺機器，那就可以隨便指定了。

注意！修改組態檔後重啟報錯最好定位到mysql的errlog，檢視具體錯誤，我出現過一個錯誤就是***用root自定義建立bin-log所在的目錄，沒給mysql使用者許可權***。

還有一種設定方式（指定三個引數）：

log_bin=ON  
log_bin_basename=/var/lib/mysql/mysql-bin  
log_bin_index=/var/lib/mysql/mysql-bin.index

第一個引數是開啟binlog紀錄檔

第二個引數是binlog紀錄檔的基本檔名，後面會追加標識來表示每一個檔案

第三個引數指定的是binlog檔案的索引檔案，這個檔案管理了所有的binlog檔案的目錄

重啟後檢視：

3.常用binlog紀錄檔操作命令

1.檢視所有binlog紀錄檔列表

mysql> show master logs;

2.檢視master狀態，即最後(最新)一個binlog紀錄檔的編號名稱，及其最後一個操作事件pos結束點(Position)值

mysql> show master status;

3.重新整理log紀錄檔，自此刻開始產生一個新編號的binlog紀錄檔檔案

mysql> flush logs;

注：每當mysqld服務重啟時，會自動執行此命令，重新整理binlog紀錄檔；在mysqldump備份資料時加 -F 選項也會重新整理binlog紀錄檔；

4.重置(清空)所有binlog紀錄檔

mysql> reset master;

5.檢視binlog紀錄檔內容（以表格形式）

mysql>  show binlog events in 'mysql-bin.000002';

4.mysqlbinlog命令使用

mysqlbinlog功能是將mysql的binlog紀錄檔轉換成Mysql語句，預設情況下binlog紀錄檔是二進位制檔案，無法直接檢視。我們直接在mysql目錄的bin目錄下啟動該命令。（在MySQL5.5以下版本使用mysqlbinlog命令時如果報錯，就加上 “–no-defaults”選項）

mysqlbinlog命令部分引數：

-d	//指定庫的binlog
-r	//相當於重定向到指定檔案
--start-position--stop-position	//按照指定位置精確解析binlog紀錄檔（精確），如不接--stop-positiion則一直到binlog紀錄檔結尾
--start-datetime--stop-datetime	//按照指定時間解析binlog紀錄檔（模糊，不準確），如不接--stop-datetime則一直到binlog紀錄檔結尾

備註：myslqlbinlog分庫匯出binlog，如使用-d引數，更新資料時必須使用use database。

例：解析yj-test資料庫的binlog紀錄檔並寫入my.sql檔案

./mysqlbinlog -d yj-test /home/data/mysql-log/mysql-bin.000003 -r my.sql

//使用位置精確解析binlog紀錄檔
./mysqlbinlog mysql-bin.000003 --start-position=100  --stop-position=200 -r my.sql

可以直接檢視所有binlog資訊：

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

注意：

檢視binlog內容可能存在兩個問題:

一是報錯，比如不支援’default-character-set=utf8’，這種是binlog工具和mysql設定出現衝突，可以在binlog命令後新增--no--defaults解決；

二是解析出來的binlog內容中sql語句出現亂碼，如：

BINLOG '
Q738XBMBAAAAOwAAAFATAAAAAFsAAAAAAAEABHRlc3QAAnQxAAQPDw8KBigAKAAoAA8CA/z/AACU
U3Q=
Q738XB4BAAAALgAAAH4TAAAAAFsAAAAAAAEAAgAE/wABMgIxMQFSycYPZKuVcw==
'

這種可以新增 --base64-output=DECODE-ROWS -v 引數解決！

5.binlog的三種工作模式

檢視binlog紀錄檔格式：

show variables like "binlog_format";

注：我的預設為 ROW 模式，和網上說的預設不一樣（Statement）

（1）Row level

　　ROW是基於行級別的,他會記錄每一行記錄的變化,就是將每一行的修改都記錄到binlog裡面,記錄的非常詳細，但sql語句並沒有在binlog裡。

　　紀錄檔中會記錄每一行資料被修改的情況，然後在slave端對相同的資料進行修改。在replication裡面也不會因為儲存過程觸發器等造成Master-Slave資料不一致的問題,但是有個致命的缺點紀錄檔量比較大.由於要記錄每一行的資料變化,當執行update語句後面不加where條件的時候或alter table的時候,產生的紀錄檔量是相當的大。

（2）Statement level（預設）

　　每一條被修改資料的sql都會記錄到master的bin-log中，slave在複製的時候sql程序會解析成和原來master端執行過的相同的sql再次執行

　　優點：解決了 Row level下的缺點，不需要記錄每一行的資料變化，減少bin-log紀錄檔量，節約磁碟IO，提高新能

　　缺點：statement level下對一些特殊功能的複製效果不是很好，比如：函數、儲存過程的複製。由於row level是基於每一行的變化來記錄的，所以不會出現類似問題

（3）Mixed（混合模式）

　　結合了Row level和Statement level的優點。

　　在預設情況下是statement,但是在某些情況下會切換到row狀態，如當一個DML更新一個ndb引擎表，或者是與時間使用者相關的函數等。在主從的情況下，在主機上如果是STATEMENT模式，那麼binlog就是直接寫now()，然而如果這樣的話，那麼從機進行操作的時間，也執行now()，但明顯這兩個時間不會是一樣的，所以對於這種情況就必須把STATEMENT模式更改為ROW模式，因為ROW模式會直接寫值而不是寫語句（該案例是錯誤的，即使是STATEMENT模式也可以使用now()函數，具體原因以後再分析）。同樣ROW模式還可以減少從機的相關計算，如在主機中存在統計寫入等操作時，從機就可以免掉該計算把值直接寫入從機。

一般企業binlog模式的選擇：

網際網路公司使用MySQL的功能較少（不用儲存過程、觸發器、函數），選擇預設的Statement level；

用到MySQL的特殊功能（儲存過程、觸發器、函數）則選擇Mixed模式；

用到MySQL的特殊功能（儲存過程、觸發器、函數），又希望資料最大化一直則選擇Row模式；

先記錄這麼多吧，後面使用時再進一步記錄。