大家好，我是anyux。本文介紹MySQL錯誤日誌和二進制日誌。
MySQL日誌有5種，對於運維人員，最常用到的日誌有3種，分別是錯誤日誌、二進制日誌和慢日誌
錯誤日誌(log_error)
錯誤日誌通常用於記錄數據庫服務端啟動、重啟、主從複製時，記錄錯誤，將日誌詳情保留在文件中，方便DBA、運維開發人員閱讀。
一般地閱讀錯誤日誌，都會通過grep命令查找到[ERROR]這種字符串，這通常能夠快速定位到數據庫服務錯誤的根源，而不至於手足無措
數據庫錯誤日誌，可以以下sql命令來查找
<code> select @@log_error;/<code>
上圖中的stderr就表示錯誤日誌，默認情況下，數據庫錯誤日誌功能是關閉的，錯誤日誌會被寫入到標準錯誤輸出(stderr)。如果要開啟，在配置文件中添加log-error=/data/mysql_error.log,後重啟服務
<code> [mysqld]
log-error=/data/mysql_error.log/<code>
如上圖，就是配置成功。配置時注意權限問題，一定要讓文件的權限和服務的權限相同
二進制日誌(bin_log)
開啟二進制日誌
記錄的是二進制日誌，是sql層的日誌
這個二進制日誌，直接打開是亂碼，它本就不是給人看的。
它的作用有兩個，一是用於主從複製，二是在數據恢復
二進制日誌默認是關閉的，開啟它需要配置多個參數,下面這兩個參數必須要配置，最為重要
<code> [mysqld]
給服務指定一個id號，取值區間0-65535，用於在主從複製時，標識不同的服務
server_id=6 log_bin可以用於指定二進制文件存放目錄
注意，定要讓文件目錄的權限和服務的權限相同
log-bin是二進制日誌文件名的前綴，隨著日誌的滾動，會產生較多的二進制日誌文件如，log-bin.00001,log-bin.00002等
log_bin=/data/mysql/log-bin
/<code>
二進制日誌其他參數
<code> 5.7版本以後，默認值為row
binlog_format=row/<code>
重啟服務，查看二進制日誌
如上圖，log-bin.000001是二進制日誌文件，log-bin是我們定義的前綴，你可以隨意指定，後面的000001是數據庫自動生成的，可以手動地以命令形式產生新的二進制日誌文件。log-bin.index可以認為是一個索引文件，它會記錄哪些文件是二進制日誌文件。log-bin.000001是二進制文件，打開也是亂碼，log-bin.index是索引文件，它是按行記錄每一個二進制日誌文件的位置。可以通過cat命令查看所有的二進制日誌文件位置
查看二進制日誌
二進制日誌記錄的是數據庫中所有的變更類的操作日誌，包括DDL(數據定義語句)、DCL(數據控制語句)、DML(數據操作語句)
對於DDL和DCL，二進制日誌會以語句的形式，原樣記錄
對於DML，它記錄的是已提交的事務。
對於DML語句，二進制日誌文件記錄它的方式有3種，分別是row(RBR行模式)、statement(SBR語句模式)、mixed(RBR混合模式)，通過binlog_format=row參數控制。
注意啦，binlog_format只會對DML語句起作用，對於DDL和DCL它是無效的。
二進制日誌記錄單元
event事件
二進制日誌的最小單元
對於DDL、DCL語句，每一個語句都是一個事件
對於DML語句
<code> begin; 事件1
update table set column_1='values' where id=2; 事件2
update table set column_2='others' where id=3; 事件3
commit; 事件4/<code>
上面代碼是一個事務，包含了4行語句，每一個語句都被稱為一個事件。對於上面的4行語句，如果執行事務失敗，需要回滾或寫入磁盤。就需要將此事務完整的語句拿到，進行回滾操作或重新寫入磁盤
event事件的開始和結束號碼
方便運維管理人員從二進日誌中截取需要的日誌事件
<code> show binary logs;/<code>
通過命令flush logs可以新建二進制日誌文件
<code> flush logs;/<code>
查看正在使用的二進制日誌
二進制日誌文件默認使用最好一個，無法指定二進制文件，這是數據庫自定義的
<code> show master status;/<code>
查看二進制日誌事件
創建數據庫
<code> create database binlog charset utf8mb4;
use binlog;
create table temp (id int);/<code>
查看二進制日誌文件中否記錄DDL語句
第一步先確認哪一個二進制日誌文件是當前使用的
<code> show master status/<code>
第二步查看日誌內容
<code> show binlog events in 'log-bin.000005';
也可以使用limit 查看日誌文件
show binlog events in 'log-bin.000005' limit 2,3;/<code>
上圖中的Event_type列中包含兩個Query,對應的info字段中包含create語句。這表明二進制日誌文件如實記錄了原樣的sql語句
第三步提交事務，查看日誌詳情
<code> insert into temp values(1);
commit;/<code>
如果要截取二進制日誌文件中完整的事務，只需要截取從BEGIN開始時的Pos，到COMMIT結束的End_log_pos，也就是上圖的，569到763。提示截取事務時，一定要從BEGIN開始截取到COMMIT，因為必須要保證事務的完整性
查看二進制日誌文件內容
通過file文件查看對應的二進制日誌文件
<code> file log-bin.000005;/<code>
上圖說明log-bin.000005是MySQL複製集日誌，可用於主從複製。可以使用mysqlbinlog 工具打開
<code> mysqlbinlog log-bin.000005 | less/<code>
以上命令可以按上下箭頭查看日誌信息，比較方便
上圖中的內容可以理解為二進制日誌的固定格式，重要的內容在下面
區分一個完整的方法，就是從一個at開始，找下一個at開始的上一行，就個範圍就是完整的events事件
現在找一個條較長的事件，對於SET開頭的行，忽略即可，
#200308 1:15:04 server id 6 end_log_pos 335
上面這句是條註釋，它的意思是在2020年3月8日，1點15分4秒 服務器id6，結束位置在335
create database binlog charset utf8mb4
上面這條sql語句就是創建數據庫的原語句
查找事務日誌,使用grep命令快速定位
<code> #排除以SET和/開頭的行
mysqlbinlog log-bin.000005 | egrep -v "^SET|^/" | less/<code>
從show binlog events in 'log-bin.000005';語句中找到BEGIN和COMMIT對應的開始和結束位置，如上圖所示
以井號開頭的不做解釋，可以看到自692位置開始到732位置結束，就是以RBR模式記錄的二進制日誌值
對於BINLOG後的內容看不懂，實際是被base64編碼過了，需要解碼才能看到
<code> mysqlbinlog --base64-output=decode-rows -vvv log-bin.000005 | egrep -v "^SET|^/" | less/<code>
看到上圖中有類似偽sql代碼，INSERT INTO binlog.temp表示向binlog庫的temp表插入數據，SET @1=1，@1表示第一列，=1表示賦值操作，完整翻譯就是向binlog庫的temp表的每一列插入數據1。
按範圍查看二進行日誌
此處選擇的是創建數據庫的起止節點
<code> mysqlbinlog --start-position=154 --stop-position=335 --base64-output=decode-rows -vvv log-bin.000005 | egrep -v "^SET|^/" | less/<code>
如果二進制日誌很大，不方便查看，可以通過指定起始節點來操作
如果查看到二進制日誌沒有問題，想用來恢復日誌，可以通過重定向到sql文件，再恢復數據
<code> mysqlbinlog --start-position=154 --stop-position=335 --base64-output=decode-rows -vvv log-bin.000005 | egrep -v "^SET|^/" | less > /tmp/a.sql/<code>
查看特定數據庫的信息
<code> mysqlbinlog -d binlog --base64-output=decode-rows -vvv log-bin.000005 | egrep -v "^SET|^/" | less/<code>
通過binlog恢復數據
模擬數據
<code> create database log_db charset utf8mb4;
use log_db;
create table log_01(id int);
insert into log_01 values(1);
commit;/<code>
模擬故障
<code> drop database log_db;/<code>
基於binlog恢復日誌
首先，找到當前記錄的二進制日誌文件，
<code> show master status;/<code>
查看事件信息
提示：如果二進制日誌過多，可以使用參數模式查看。使用重定向或grep過濾快速位置
<code> show binlog events in 'log-bin.000006';/<code>
找到創建庫語句的Pos數值，找到刪除數據庫的PoS數值
打開新的窗口，重定向到sql文件
進入二進制日誌文件目錄
<code> cd /data/mysql
mysqlbinlog --start-position=219 --stop-position=766 log-bin.000006 > /tmp/log_01.sql/<code>
設置臨時會話狀態
注意，恢復數據，暫停數據庫記錄二進制日誌。原因是，恢復數據的過程不需要再次被記錄
<code> 下面語句的表示：在當前會話中，臨時關閉二進制日誌，不影響業務的正常運行
set sql_log_bin=0;/<code>
恢復數據
<code> source /tmp/log_01.sql;/<code>
設置臨時會話狀態
<code> set sql_log_bin=1;/<code>
查看數據
<code> use log_db;
select * from log_01;/<code>
提示：以上恢復數據過程是一個簡單的示例，對於複雜的環境下，如基於2年來的二進制日誌進行數據恢復，是可以進行。但是恢復速度會很慢，需要查看的節點也會較多。通常的解決方案是，數據為備份+二進制日誌進行數據恢復。
歡迎在評論區一起討論，質疑。文章都是手打原創，每天最淺顯的介紹運維、數據庫相關的技術，喜歡我的文章就關注一波吧，可以看到最新更新和之前的文章哦。
閱讀更多 anyux1 的文章
