MySQL中binlog日誌檔的詳細介紹
這篇文章帶給大家的內容是關於MySQL中binlog日誌檔的詳細介紹，有一定的參考價值，有需要的朋友可以參考一下，希望對你有幫助。
MySQL 的 binlog日誌文件，記錄了資料庫表的全部修改操作。本文簡單整理 MySQL binlog 相關知識，以及如何使用 binlog 復原或閃回資料庫資料。
STATEMENT 格式的 binlog
要開啟 binlog，需要在啟動 MySQL 時傳入 --log-bin 參數。或者也可以在 MySQL 設定檔 /etc/my.cnf，設定 log_bin 開啟 binlog。 MySQL 5.7 開始，開啟 binlog 後，--server-id 參數也必須指定，否則 MySQL 伺服器會啟動失敗。
binlog_format 支援 STATEMENT, ROW, MIXED 三種格式，MySQL 5.5 和 5.6 預設為 STATEMENT，MySQL 5.7.7 開始預設為 ROW。若 SQL 使用 UUID(), RAND(), VERSION() 等函數，或使用預存程序、自訂函數，基於 STATEMENT 的主從復時，是不安全的（很多人可能會認為 NOW(), CURRENT_TIMESTAMP 這些函數也是不安全的，事實上是安全的） [doc1, doc2 ]。基於 ROW 的主從複製，是最安全的複製方式。
現在先來看看STATEMENT 格式的binlog，/etc/my.cnf 檔案修改的內容如下：
server_id = 1 log_bin = mysql-bin binlog_format = STATEMENT binlog_row_image=FULL
重啟MySQL 後，在資料目錄datadir 下，例如/var/lib /mysql/，將會產生對應的binlog 文件，mysql-bin.index 和mysql-bin.000001。 .index 後綴的檔案保存全部 binlog 檔名。 mysql-bin.000001 檔案記錄 binlog 內容。每次 MySQL 啟動或 flush 日誌，都將依序號建立一個新的日誌檔案。另外，當日誌檔案大小超過 max_binlog_size 時，也會建立新的日誌檔案。
現在來試試 binlog 功能。假設在testdb 庫在有hello 表，並對其中某行做修改操作：
mysql> select * from hello; +----+-------+ | id | name | +----+-------+ | 1 | Andy | | 2 | Bill | | 3 | Candy | +----+-------+ 4 rows in set (0.00 sec) mysql> update hello set name = 'Will' where id = 3; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
binlog 為二進位文件，需要使用mysqlbinlog（doc, man）命令查看：
$ sudo mysqlbinlog /var/lib/mysql/mysql-bin.000001 # 直接在 mysql 服务器上读取 binlog 文件 $ mysqlbinlog -R -h192.168.2.107 -uroot -p123456 mysql-bin.000001 # 或者，远程读取 binlog 文件
執行update後對應新增的binlog 檔案內容：
# at 154 #180617 22:47:49 server id 1 end_log_pos 219 CRC32 0x4bd9d69b Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #180617 22:47:49 server id 1 end_log_pos 302 CRC32 0x476fafc9 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1529246869/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1075838976/*!*/; 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/*!*/; BEGIN /*!*/; # at 302 #180617 22:47:49 server id 1 end_log_pos 423 CRC32 0x7f2c2c7a Query thread_id=2 exec_time=0 error_code=0 use `testdb`/*!*/; SET TIMESTAMP=1529246869/*!*/; update hello set name = 'Will' where id = 3 /*!*/; # at 423 #180617 22:47:49 server id 1 end_log_pos 454 CRC32 0x68da744a Xid = 12 COMMIT/*!*/;
ROW 格式的binlog
修改/etc/my.cnf 的binlog_format 為ROW，再重啟MySQL。格式修改後，會產生一個新的 binlog 檔案 mysql-bin.000002。
mysql> show create table hello; +-------+-------------------------------------------------------------------------+ | Table | Create Table +-------+-------------------------------------------------------------------------+ | hello | CREATE TABLE `hello` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 | +-------+-------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from hello where id; +----+------+ | id | name | +----+------+ | 1 | Andy | | 2 | Lily | | 3 | Will | +----+------+ 1 row in set (0.00 sec) mysql> update hello set name = 'David' where id = 3; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
查看 ROW 格式的 binlog，需要使用 sudo mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000002 指令。執行update 後對應新增的binlog 內容：
# at 154 #180617 22:54:13 server id 1 end_log_pos 219 CRC32 0x2ce70d4d Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #180617 22:54:13 server id 1 end_log_pos 293 CRC32 0x8183fddf Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1529247253/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1075838976/*!*/; 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/*!*/; BEGIN /*!*/; # at 293 #180617 22:54:13 server id 1 end_log_pos 346 CRC32 0x0fc7e1a4 Table_map: `testdb`.`hello` mapped to number 110 # at 346 #180617 22:54:13 server id 1 end_log_pos 411 CRC32 0xb58e729d Update_rows: table id 110 flags: STMT_END_F ### UPDATE `testdb`.`hello` ### WHERE ### @1=3 ### @2='Will' ### SET ### @1=3 ### @2='David' # at 411 #180617 22:54:13 server id 1 end_log_pos 442 CRC32 0xef964db8 Xid = 13 COMMIT/*!*/;
若執行下列SQL：
mysql> insert hello (name) values ('Frank');
Query OK, 1 row affected (0.02 sec)對應產生的binlog 內容：
# at 442 #180617 22:55:47 server id 1 end_log_pos 507 CRC32 0x79de08a7 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 507 #180617 22:55:47 server id 1 end_log_pos 581 CRC32 0x56f9eb6a Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1529247347/*!*/; BEGIN /*!*/; # at 581 #180617 22:55:47 server id 1 end_log_pos 634 CRC32 0xedb73620 Table_map: `testdb`.`hello` mapped to number 110 # at 634 #180617 22:55:47 server id 1 end_log_pos 684 CRC32 0x525a6a70 Write_rows: table id 110 flags: STMT_END_F ### INSERT INTO `testdb`.`hello` ### SET ### @1=4 ### @2='Frank' # at 684 #180617 22:55:47 server id 1 end_log_pos 715 CRC32 0x09a0d4de Xid = 14 COMMIT/*!*/;
若執行如下SQL：
mysql> delete from hello where id = 2; Query OK, 1 row affected (0.02 sec)
對應產生的binlog 內容：
# at 715 #180617 22:56:44 server id 1 end_log_pos 780 CRC32 0x9f52450e Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 780 #180617 22:56:44 server id 1 end_log_pos 854 CRC32 0x0959bc8d Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1529247404/*!*/; BEGIN /*!*/; # at 854 #180617 22:56:44 server id 1 end_log_pos 907 CRC32 0x2945260f Table_map: `testdb`.`hello` mapped to number 110 # at 907 #180617 22:56:44 server id 1 end_log_pos 956 CRC32 0xc70df255 Delete_rows: table id 110 flags: STMT_END_F ### DELETE FROM `testdb`.`hello` ### WHERE ### @1=2 ### @2='Bill' # at 956 #180617 22:56:44 server id 1 end_log_pos 987 CRC32 0x0c98f18e Xid = 15 COMMIT/*!*/;
使用binlog 增量恢復
MySQL 邏輯備份通常會結合全量備份和增量備份，並使用mysqldump 定期全量備份資料庫，然後利用binlog 保存增量資料。恢復資料時，就是用 mysqldump 備份的資料還原到備份的時間點。資料庫在備份時間點到目前時間的增量修改，則透過 mysqlbinlog 將 binlog 中的增量資料還原到資料庫。現在假設已經使用mysqldump 將資料庫還原到：
mysql> select * from hello; +----+------+ | id | name | +----+------+ | 1 | Andy | | 2 | Lily | | 3 | Will | +----+------+ 3 rows in set (0.00 sec)
之後執行的SQL：
update hello set name = 'David' where id = 3;
insert hello (name) values ('Frank');
delete from hello where id = 2;不管是使用STATEMENT 或ROW，mysqlbinlog 指令都可以將binlog 增量還原到資料庫 [doc ] 。
觀察binlog 可以看到，從最開始的update hello set name = 'David' where id = 3; 到最終的delete from hello where id = 2;，時間上從"2018-06-17 22:54:13" 到"2018-06-17 22:56:44"，所以基於時間點恢復，命令如下：
$ sudo mysqlbinlog --start-datetime="2018-06-17 22:54:13" --stop-datetime="2018-06-17 22:56:44" mysql-bin.000002 | mysql -uroot -p123456
binlog 的事件位置號碼是從" 154" 到"956"，但需要注意的是用--start-position 和--stop-position 指定位置點範圍，邏輯上對應的是start <= position < stop，所以基於時間點恢復，命令如下：
$ sudo mysqlbinlog --start-position=154 --stop-position=957 mysql-bin.000002 | mysql -uroot -p123456
兩種方式任意執行，都能將資料恢復到：
mysql> select * from hello; +----+-------+ | id | name | +----+-------+ | 1 | Andy | | 3 | David | | 4 | Frank | +----+-------+ 3 rows in set (0.00 sec)<p><strong>#使用binlog2sql 閃回</strong></p> <p>binlog2sql，作者為曹單鋒，大眾點評DBA。 binlog2sql，從 MySQL binlog 解析出你想要的 SQL。根據不同選項，你可以得到原始 SQL、回滾 SQL、移除主鍵的 INSERT SQL 等。 binlog2sql，底層實作依賴 python-mysql-replication，由函式庫完成 MySQL 複製協定和 binlog 格式的解析。 </p> <pre class="brush:php;toolbar:false">$ python binlog2sql/binlog2sql.py -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=957 --start-file='mysql-bin.000002' UPDATE `testdb`.`hello` SET `id`=3, `name`='David' WHERE `id`=3 AND `name`='Will' LIMIT 1; #start 4 end 411 time 2018-06-17 22:54:13 INSERT INTO `testdb`.`hello`(`id`, `name`) VALUES (4, 'Frank'); #start 442 end 684 time 2018-06-17 22:55:47 DELETE FROM `testdb`.`hello` WHERE `id`=2 AND `name`='Bill' LIMIT 1; #start 715 end 956 time 2018-06-17 22:56:44
產生回溯 sql：
$ python binlog2sql/binlog2sql.py --flashback -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=956 --start-file='mysql-bin.000002' INSERT INTO `testdb`.`hello`(`id`, `name`) VALUES (2, 'Bill'); #start 715 end 956 time 2018-06-17 22:56:44 DELETE FROM `testdb`.`hello` WHERE `id`=4 AND `name`='Frank' LIMIT 1; #start 442 end 684 time 2018-06-17 22:55:47 UPDATE `testdb`.`hello` SET `id`=3, `name`='Will' WHERE `id`=3 AND `name`='David' LIMIT 1; #start 154 end 411 time 2018-06-17 22:54:13
閃回的現實原理很簡單，先透過 MySQL 複製協定的 com-binlog-dump 指令 dump 出 binlog，然後再依照 binlog 的格式規格解析 binlog，將 binlog 轉換成 SQL，再將這些 SQL 轉換反向邏輯的 SQL，最後再倒序執行。
Java 解析 binlog
上文中的 binlog2sql 其实底层依赖 python-mysql-replication 库，这是 Python 库。如果想使用 Java 解析 binlog 可以使用 mysql-binlog-connector-java（github）库。目前开源的 CDC 工具，如 Zendesk maxwell、Redhat debezium、LinkedIn Databus 等都底层依赖 mysql-binlog-connector-java 或者其前身 open-replicator。使用 mysql-binlog-connector-java 的示例代码如下：
BinaryLogClient client = new BinaryLogClient("192.168.2.107", 3306, "root", "123456");
client.setBinlogFilename("mysql-bin.000001");
client.setBinlogPosition(4);
client.setBlocking(false);
client.registerEventListener(event -> {
System.out.println(event);
});
client.connect();输出（省略部分内容）：
...
Event{header=EventHeaderV4{timestamp=1529247253000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=346, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247253000, eventType=EXT_UPDATE_ROWS, serverId=1, headerLength=19, dataLength=46, nextPosition=411, flags=0}, data=UpdateRowsEventData{tableId=110, includedColumnsBeforeUpdate={0, 1}, includedColumns={0, 1}, rows=[
{before=[3, Will], after=[3, David]}
]}}
...
Event{header=EventHeaderV4{timestamp=1529247347000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=634, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247347000, eventType=EXT_WRITE_ROWS, serverId=1, headerLength=19, dataLength=31, nextPosition=684, flags=0}, data=WriteRowsEventData{tableId=110, includedColumns={0, 1}, rows=[
[4, Frank]
]}}
...
Event{header=EventHeaderV4{timestamp=1529247404000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=907, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247404000, eventType=EXT_DELETE_ROWS, serverId=1, headerLength=19, dataLength=30, nextPosition=956, flags=0}, data=DeleteRowsEventData{tableId=110, includedColumns={0, 1}, rows=[
[2, Bill]
]}}以上是MySQL中binlog日誌檔的詳細介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
AI Hentai Generator
免費產生 AI 無盡。
熱門文章
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
熱門話題
MySQL：初學者的數據管理易用性
Apr 09, 2025 am 12:07 AM
MySQL適合初學者使用，因為它安裝簡單、功能強大且易於管理數據。 1.安裝和配置簡單，適用於多種操作系統。 2.支持基本操作如創建數據庫和表、插入、查詢、更新和刪除數據。 3.提供高級功能如JOIN操作和子查詢。 4.可以通過索引、查詢優化和分錶分區來提升性能。 5.支持備份、恢復和安全措施，確保數據的安全和一致性。
忘記數據庫密碼，能在Navicat中找回嗎？
Apr 08, 2025 pm 09:51 PM
Navicat本身不存儲數據庫密碼，只能找回加密後的密碼。解決辦法：1. 檢查密碼管理器；2. 檢查Navicat的“記住密碼”功能；3. 重置數據庫密碼；4. 聯繫數據庫管理員。
navicat premium怎麼創建
Apr 09, 2025 am 07:09 AM
使用 Navicat Premium 創建數據庫：連接到數據庫服務器並輸入連接參數。右鍵單擊服務器並選擇“創建數據庫”。輸入新數據庫的名稱和指定字符集和排序規則。連接到新數據庫並在“對象瀏覽器”中創建表。右鍵單擊表並選擇“插入數據”來插入數據。
mysql：簡單的概念，用於輕鬆學習
Apr 10, 2025 am 09:29 AM
MySQL是一個開源的關係型數據庫管理系統。 1）創建數據庫和表：使用CREATEDATABASE和CREATETABLE命令。 2）基本操作：INSERT、UPDATE、DELETE和SELECT。 3）高級操作：JOIN、子查詢和事務處理。 4）調試技巧：檢查語法、數據類型和權限。 5）優化建議：使用索引、避免SELECT*和使用事務。
MySQL和SQL：開發人員的基本技能
Apr 10, 2025 am 09:30 AM
MySQL和SQL是開發者必備技能。 1.MySQL是開源的關係型數據庫管理系統，SQL是用於管理和操作數據庫的標準語言。 2.MySQL通過高效的數據存儲和檢索功能支持多種存儲引擎，SQL通過簡單語句完成複雜數據操作。 3.使用示例包括基本查詢和高級查詢，如按條件過濾和排序。 4.常見錯誤包括語法錯誤和性能問題，可通過檢查SQL語句和使用EXPLAIN命令優化。 5.性能優化技巧包括使用索引、避免全表掃描、優化JOIN操作和提升代碼可讀性。
navicat怎麼新建連接mysql
Apr 09, 2025 am 07:21 AM
可在 Navicat 中通過以下步驟新建 MySQL 連接：打開應用程序並選擇“新建連接”（Ctrl N）。選擇“MySQL”作為連接類型。輸入主機名/IP 地址、端口、用戶名和密碼。 （可選）配置高級選項。保存連接並輸入連接名稱。
Navicat for MariaDB如何查看數據庫密碼？
Apr 08, 2025 pm 09:18 PM
Navicat for MariaDB 無法直接查看數據庫密碼，因為密碼以加密形式存儲。為確保數據庫安全，有三個方法可重置密碼：通過 Navicat 重置密碼，設置複雜密碼。查看配置文件（不推薦，風險高）。使用系統命令行工具（不推薦，需要對命令行工具精通）。
phpmyadmin怎麼打開
Apr 10, 2025 pm 10:51 PM
可以通過以下步驟打開 phpMyAdmin：1. 登錄網站控制面板；2. 找到並點擊 phpMyAdmin 圖標；3. 輸入 MySQL 憑據；4. 點擊 "登錄"。
