技術文章 » MySQL系列之二進位制紀錄檔Binlog學習筆記

MySQL系列之二進位制紀錄檔Binlog學習筆記

2020-10-18 14:00:17

學習準備：

  • Mysql服務於端（5.7+版本）
  • Mysql使用者端軟體（workbench、sqlyog etc.）

1、什麼是Binlog？

在上一篇部落格的學習，我們知道了InnoDB儲存引擎的兩種事務紀錄檔，redo log是InnoDB特有的功能，而MySQL也是有自己的紀錄檔機制的，也即本文學習的binlog

binlog（二進位制紀錄檔）：binary log，簡稱是binlog，binlog記錄所有資料庫表結構變更以及表資料修改，而不會記錄SELECT和SHOW這類操作，資料儲存的是二進位制資料

binlog以事件的形式儲存，還包括sql執行所需的時間等等資訊，開啟Binlog紀錄檔有以下兩個最重要的使用場景：

  • 主從複製：binlog的特性可以被應用於主從複製，主庫（master）開啟binlog功能，從庫（salve）通過binlog的事件記錄，將資料同步到資料庫
  • 資料恢復：binlog可以用於在資料恢復，因為binlog記錄了sql的變更修改操作紀錄檔，如果數一不小心刪庫了，就可以通過binlog進行資料恢復

2、Binlog記錄模式

Binlog紀錄檔的檔名預設是「主機名_binlog-序列號」的形式，eg：adminstrator_binlog-000001，當然也可以在組態檔修改命名為mysqlbinlog：

[mysql]
log-bin=mysqlbinlog

binlog對log event進行記錄，檔案記錄模式有STATEMENT、ROW和MIXED三種，具體含義：

  • Row模式：row-based replication，簡稱RBR，紀錄檔會記錄每一行資料被修改的情況，如果用於主從複製，在slave資料庫會對相同的資料進行修改
  • STATEMENT模式：statement-based replication, 簡稱SBR，這種模式，沒錯修改變更的sql都會被記錄在紀錄檔裡
  • MIXED模式：mixed-based replication, 簡稱MB，這種模式，一般會使用STATEMENT模式儲存binlog，對於STATEMENT模式無法複製的操作使用ROW模式儲存binlog

在 MySQL 5.7.7 之前，預設的格式是 STATEMENT，在 MySQL 5.7.7 及更高版本中，預設值是 ROW。紀錄檔格式通過 binlog-format 指定，如 binlog-format=STATEMENT、binlog-format=ROW、binlog-format=MIXED。

3、Binlog檔案結構

binlog機制中用來表示儲存修改操作的資料結構是Log event。不同的修改操作對應的不同的log event。比較常用的log event有：Query event、Row event、Xid event等，可以獲取一份log來看看：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201012 11:26:13 server id 1  end_log_pos 124 CRC32 0x253ff34e 	Start: binlog v 4, server v 8.0.13 created 201012 11:26:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
1cyDXw8BAAAAeAAAAHwAAAABAAQAOC4wLjEzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADVzINfEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgFO8z8l
'/*!*/;
# at 124
#201012 11:26:14 server id 1  end_log_pos 155 CRC32 0xf80d0acf 	Previous-GTIDs
# [empty]
# at 155
#201012 14:04:52 server id 1  end_log_pos 228 CRC32 0xe767e3f3 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no	original_committed_timestamp=1602482692499600	immediate_commit_timestamp=1602482692499600	transaction_length=183
# original_commit_timestamp=1602482692499600 (2020-10-12 14:04:52.499600 ?¨ª¨¤¡ä?¡Â??¡ã?¦Ìo¡À¨º¡Á?¨º¡À??)
# immediate_commit_timestamp=1602482692499600 (2020-10-12 14:04:52.499600 ?¨ª¨¤¡ä?¡Â??¡ã?¦Ìo¡À¨º¡Á?¨º¡À??)
/*!80001 SET @@session.original_commit_timestamp=1602482692499600*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 228
#201012 14:04:52 server id 1  end_log_pos 338 CRC32 0x8f981f33 	Query	thread_id=9	exec_time=0	error_code=0	Xid = 16
SET TIMESTAMP=1602482692/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create database `test`
/*!*/;
# at 338
#201012 14:31:46 server id 1  end_log_pos 411 CRC32 0xc6dbb42e 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1602484306791600	immediate_commit_timestamp=1602484306791600	transaction_length=181
# original_commit_timestamp=1602484306791600 (2020-10-12 14:31:46.791600 ?¨ª¨¤¡ä?¡Â??¡ã?¦Ìo¡À¨º¡Á?¨º¡À??)
# immediate_commit_timestamp=1602484306791600 (2020-10-12 14:31:46.791600 ?¨ª¨¤¡ä?¡Â??¡ã?¦Ìo¡À¨º¡Á?¨º¡À??)
/*!80001 SET @@session.original_commit_timestamp=1602484306791600*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

畫表格表示其主要資訊：

資訊描述
position位於檔案中的位置，表示事件記錄從檔案第?個位元組開始
timestamp事件開始的時間戳
server_id伺服器的server id
end_log_pos表示下一個事件開始的位置
thread_id執行該事件的執行緒id
exec_time事件執行的花費時間
error_code錯誤碼，0意味著沒有發生錯誤
type事件型別Query

4、Binlog寫入機制

binlog 什麼時候重新整理到磁碟？binlog刷資料到磁碟跟引數 sync_binlog 相關

  • 如果設定為0：則表示MySQL不控制binlog的重新整理，由檔案系統去控制紀錄檔的重新整理；
  • 如果設定為不為0的值：則表示每 sync_binlog 次事務，MySQL呼叫檔案系統重新整理binlog到磁碟中

然後binlog具體是怎麼刷紀錄檔的？畫圖表示：
在這裡插入圖片描述

  • 根據記錄模式和操作觸發log事件從而生成log event
  • 將事務執行過程的log event寫入快取區，快取區是一種binlog_cache_mngr資料結構，該結構中有兩個緩衝區，一個是stmt_cache，用於存放不支援事務的資訊；另一個是trx_cache，用於存放支援事務的資訊。
  • 事務在提交階段會將產生的log event寫入到磁碟binlog檔案中

5、Binlog命令操作

  • 啟用Binlog
    通過命令set global log_bin=1;啟動，也可以在my.ini組態檔加上設定：
[mysql]
#log-bin=ON
binlog-format=ROW
log-bin=mysqlbinlog

不能通過命令設定，只能修改my.ini組態檔
在這裡插入圖片描述

  • 確定binlog是否啟動
    在這裡插入圖片描述

  • 檢視binlog相關引數
    在這裡插入圖片描述

  • 設定binlog_format模式

set global binlog_format='ROW';　
  • binlog events命令
//等價於show master logs;
show binary logs; 
//檢視最新一個binlog紀錄檔檔名稱和Position
show master status;
//檢視 binlog 內容
show binlog events;
//檢視 指定binlog 內容
show binlog events in 'mysqlbinlog.000001';
  • mysqlbinlog 命令
// 檢視指定的binlog紀錄檔檔案
mysqlbinlog "檔名"
// 檢視指定binlog檔案並儲存到test.sql檔案
mysqlbinlog "檔名" > "test.sql"

要到對應目錄，不然就需要指定路徑：
在這裡插入圖片描述
檢視test.sql：
在這裡插入圖片描述

  • 使用 binlog 恢復資料
// 按指定時間恢復
mysqlbinlog --start-datetime="2020-10-25 18:00:00" --stop-
datetime="2020-10-26 00:00:00" mysqlbinlog.000001 | mysql -uroot -p123

// 按事件位置號恢復
mysqlbinlog --start-position=592 --stop-position=708 mysqlbinlog.000001 | mysql -u root -p
  • 清除binlog檔案

指定紀錄檔檔案

purge binary logs to 'mysqlbinlog.000001';

按時間清紀錄檔

purge binary logs before '2020-10-10 00:00:00';

清除所有紀錄檔

reset master; //清除所有檔案

ps：可以通過設定expire_logs_days引數來啟動自動清理功能。預設值為0表示沒啟用。設定為1表示超出1天binlog檔案會自動刪除掉。


mysql> show binary logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mysqlbinlog.000001 |       519 |
+--------------------+-----------+
1 row in set (0.00 sec)

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysqlbinlog.000001
         Position: 519
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

mysql> show binlog events \G;
... 
mysql> show binlog events in 'mysqlbinlog.000001'\G;
*************************** 1. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 124
       Info: Server ver: 8.0.13, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 124
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 155
       Info:
*************************** 3. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 155
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 228
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 4. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 228
 Event_type: Query
  Server_id: 1
End_log_pos: 338
       Info: create database `test` /* xid=16 */
*************************** 5. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 338
 Event_type: Anonymous_Gtid
  Server_id: 1
End_log_pos: 411
       Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
*************************** 6. row ***************************
   Log_name: mysqlbinlog.000001
        Pos: 411
 Event_type: Query
  Server_id: 1
End_log_pos: 519
       Info: drop database `test` /* xid=40 */
6 rows in set (0.00 sec)

6、Redo Log和Binlog區別

  • Redo log是InnoDB特有的功能，屬於事務紀錄檔；Binlog是MySQL的功能
  • Redo log不是二進位制檔案，而Binlog是一種二進位制的紀錄檔檔案
  • Redo log是物理紀錄檔，記錄的是資料頁更新的狀態內容，Binlog是邏輯紀錄檔，記錄的是更新過程
  • Redo Log作為伺服器異常宕機後事務資料自動恢復使用，Binlog可以作為主從複製和資料恢復使用
  • Redo Log紀錄檔是迴圈寫，紀錄檔空間大小是固定，Binlog是追加寫入，不會覆蓋寫

附錄：參考資料

smileNicky·主頁內容推薦員 CSDN認證部落格專家 分散式 Java Spring
java程式設計師，軟體工程專業，專注於sql調優，SpringBoot，spring框架學習，個人郵箱[email protected]，公眾號nickymp