淺析MySQL二進位日誌

来源:http://www.cnblogs.com/ivictor/archive/2016/08/17/5771910.html
-Advertisement-
Play Games

查看MySQL二進位文件中的內容有兩種方式 1. mysqlbinlog 2. SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] 下麵來測試一下，在mysql中執行如下操作 對應二進位日誌中的內容如下 ...


查看MySQL二進位文件中的內容有兩種方式

1.  mysqlbinlog

2.  SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

 

下麵來測試一下，在mysql中執行如下操作

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test.t1 values(1,'a');
Query OK, 1 row affected (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into test.t1 values(2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000021 |      546 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

 

對應二進位日誌中的內容如下

通過mysqlbinlog查看

# mysqlbinlog mysql-bin.000021

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;

# at 4
#160817  4:53:02 server id 1  end_log_pos 120 CRC32 0xf9bbe803     Start: binlog v 4, server v 5.6.31-log created 160817  4:53:02
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
Ln2zVw8BAAAAdAAAAHgAAAABAAQANS42LjMxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQPo
u/k=
'/*!*/;

# at 120
#160817  4:53:06 server id 1  end_log_pos 195 CRC32 0x0182ee55     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1471380786/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
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 195
#160817  4:53:06 server id 1  end_log_pos 298 CRC32 0xf9049380     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1471380786/*!*/;
insert into test.t1 values(1,'a')
/*!*/;

# at 298
#160817  4:53:06 server id 1  end_log_pos 329 CRC32 0xdb58b5b4     Xid = 25
COMMIT/*!*/;

# at 329
#160817  4:53:15 server id 1  end_log_pos 408 CRC32 0xcc370a55     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1471380795/*!*/;
BEGIN
/*!*/;

# at 408
#160817  4:53:15 server id 1  end_log_pos 515 CRC32 0x4fa06a6e     Query    thread_id=3    exec_time=0    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1471380795/*!*/;
insert into test.t1 values(2,'b')
/*!*/;

# at 515
#160817  4:53:15 server id 1  end_log_pos 546 CRC32 0x5f51e8bd     Xid = 33
COMMIT/*!*/;

DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

解析如下：

1. at xxx，不僅僅是事件開始的位置，同樣是二進位日誌的物理大小

    譬如上述日誌中，結束位置是end_log_pos 546，則二進位日誌的大小也是546.

# ll mysql-bin.000021
-rw-rw---- 1 mysql mysql 546 Aug 17 04:53 mysql-bin.000021

 

2. at 4，對應的事件類型是FORMAT_DESCRIPTION_EVENT，是所有binlog文件中的第一個事件，在一個binlog中僅出現一次，MySQL會根據FORMAT_DESCRIPTION_EVENT事件的定義來解析binlog中的其它事件。該事件類型定義了binlog版本，MySQL Server的版本，binlog的創建時間等。

 

3. at 120，是第一個事務開始的偏移量，對應的事件類型是QUERY_EVENT，實際上也只執行了一個BEGIN操作。

 

4. 下麵來截取一個事件來看看

# at 195
#160817  4:53:06 server id 1  end_log_pos 298 CRC32 0xf9049380     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1471380786/*!*/;
insert into test.t1 values(1,'a')
/*!*/;

   該事件對應的事件類型是QUERY_EVENT

   QUERY_EVENT類型的事件通常在以下幾種情況下使用。

   1> 事務開始時的BEGIN操作

   2> 對於STATEMENT格式的DML操作

   3> 對於ROW格式的DDL操作。

   該事件會指明server_id，slave_proxy_id（會話的線程id），execution time（查詢從開始執行到記錄到binlog所花的時間，單位為秒），error-code（錯誤碼），status-vars（status-vars是以鍵值對的形式保存起來的一系列由SET命令設置的上下文信息，譬如當前的時間戳），schema（當前選擇的資料庫），query（原生的DML語句，譬如insert into test.t1 values(1,'a')）

 

5. 同樣是insert操作，一個沒有切換schema，直接執行insert into test.t1 values(1,'a')，一個是先use test，再執行insert操作，反映在binlog中的內容也不一樣，實際上，這會影響基於庫的部分複製的判斷邏輯。

 

6. 在執行基於binlog的部分恢復時，截止的時間點應該是commit操作的end_log_pos，而不是commit操作之前的的at xxx。

    譬如，針對上面的commit操作

# at 515
#160817  4:53:15 server id 1  end_log_pos 546 CRC32 0x5f51e8bd     Xid = 33
COMMIT/*!*/;

    如果要執行第二個insert語句，則--stop-position=546，而不是515。

 

7. 在用mysqlbinlog查看binlog後都會帶上ROLLBACK操作，這個在執行基於binlog的部分恢復時，會有用處。

    # mysqlbinlog --stop-position=515 mysql-bin.000021

....
# at 408
#160817  4:53:15 server id 1  end_log_pos 515 CRC32 0x4fa06a6e     Query    thread_id=3    exec_time=0    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1471380795/*!*/;
insert into test.t1 values(2,'b')
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

   我只應用到binlog偏移量為515的位置，這個時候只有insert操作，而沒有針對該操作的commit，所以mysqlbinlog會顯式增加一個rollback操作，直接回滾事務。

 

通過SHOW BINLOG EVENTS查看

通過這種方式查看還是蠻直觀的

mysql> show binlog events in 'mysql-bin.000021';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                          |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
| mysql-bin.000021 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4         |
| mysql-bin.000021 | 120 | Query       |         1 |         195 | BEGIN                                         |
| mysql-bin.000021 | 195 | Query       |         1 |         298 | insert into test.t1 values(1,'a')             |
| mysql-bin.000021 | 298 | Xid         |         1 |         329 | COMMIT /* xid=25 */                           |
| mysql-bin.000021 | 329 | Query       |         1 |         408 | BEGIN                                         |
| mysql-bin.000021 | 408 | Query       |         1 |         515 | use `test`; insert into test.t1 values(2,'b') |
| mysql-bin.000021 | 515 | Xid         |         1 |         546 | COMMIT /* xid=33 */                           |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------+
7 rows in set (0.00 sec)

 

事務的操作是何時寫入到binlog中的？

MySQL使用binlog_cache_mngr結構來緩存一個事務的所有操作，如果用戶執行commit操作，則將binlog_cache_mngr中的內容寫入到binlog中；如果用戶執行rollback操作，則直接丟棄binlog_cache_mngr中的內容。否則的話，如果事務中的操作立刻寫入到binlog中，那麼在回滾時就相當麻煩。

當時有一點需要註意的是，對於非事務的存儲引擎，所有的修改會立刻寫入到binlog中。

譬如下麵的測試中，t_myisam是myisam表，t1是innodb表，在兩張表中分別插入一條記錄，再執行回滾。

mysql> set autocommit=0;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t_myisam values(1,'a');
Query OK, 1 row affected (0.07 sec)

mysql> insert into t1 values(4,'d');
Query OK, 1 row affected (0.06 sec)

mysql> rollback;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+

但通過查看binlog日誌的內容，即便該事務回滾了，針對t_myisam表的操作還是寫入到binlog中了

mysql> show binlog events in 'mysql-bin.000017';
+------------------+-----+-------------+-----------+-------------+------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                           |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------+
| mysql-bin.000017 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.31-log, Binlog ver: 4          |
| mysql-bin.000017 | 120 | Query       |         1 |         199 | BEGIN                                          |
| mysql-bin.000017 | 199 | Query       |         1 |         307 | use `test`; insert into t_myisam values(1,'a') |
| mysql-bin.000017 | 307 | Query       |         1 |         387 | COMMIT                                         |
+------------------+-----+-------------+-----------+-------------+------------------------------------------------+
4 rows in set (0.00 sec)

 

binlog的相關參數

max_binlog_size

指定binlog文件的大小，如果當前binlog文件的大小達到了參數指定的閥值，則會創建一個新的binlog文件。

註意：binlog文件的大小可能會超過max_binlog_size的值，因為一個事務所產生的所有事件都必須要記錄在同一個binlog文件中，所以即使binlog文件的大小超過max_binlog_size的值，也會等到當前事務的所有操作全部寫入到binlog文件中才能切換。

 

sql_log_bin

會話變數，設置sql_log_bin=0表示禁用當前會話的binlog功能。

 

sync_binlog

MySQL 5.7.7之前，預設為0，即binlog文件在每次寫入內容後並不會立即持久化到磁碟中，具體的持久化操作交給操作系統去處理。如果操作系統崩潰，可能導致對binlog的修改丟失。

為了避免這種情況，可將sync_binlog設置為1，這樣在每次事務提交時，該事務的操作寫入到binlog後，都會調用fsync操作將binlog的修改同步到磁碟中。但這樣會降低MySQL的性能，所以可將sync_binlog設置為N，代表N個事務後才執行一次fsync操作。

實際上，在引入binlog group commit後，上述持久化的單位並不是事務了，而是一組事務。

官檔解釋如下：

Controls the number of binary log commit groups to collect before synchronizing the binary log to disk. When sync_binlog=0, the binary log is never synchronized to disk, and when sync_binlog is set to a value greater than 0 this number of binary log commit groups is periodically synchronized to disk. When sync_binlog=1, all transactions are synchronized to the binary log before they are committed.

 

參考

1. MariaDB原理與實現

2. MySQL 5.7 Reference Manual

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • iOS開發new與alloc/init的區別
    [className new]基本等同於[[className alloc] init]； 區別隻在於alloc分配記憶體的時候使用了zone. 這個zone是個什麼東東呢？ 它是給對象分配記憶體的時候，把關聯的對象分配到一個相鄰的記憶體區域內，以便於調用時消耗很少的代價，提升了程式處理速度； 如果使用n ...
  • IOS開發基礎知識--碎片46
    1:帶中文的URL處理 2:取WebView高度 3:UIView的部分圓角問題 4：強制App直接退出 5：修改占位符顏色和大小 6：取消系統的返回手勢 7：改WebView字體/顏色 UIWebView設置字體大小，顏色，字體： UIWebView無法通過自身的屬性設置字體的一些屬性，只能通過h ...
  • 純 HTML5 APP與原生APP的差距在哪？
    純 HTML5 APP與原生APP的差距在哪？ 寫過一些純H5的APP，雖然開發起來的確很快很舒服，但和原生比起來純H5APP還是有很多問題，主要聚集在以下幾個方面： 1、動畫 動畫有很多種，比如側邊欄菜單的滑入滑出、元素的響應動畫、頁面切換之間的過場等等，在H5之下的眾多實現方法都沒有辦法達到純原 ...
  • 轉Oracle、MySql、SQLServer 數據分頁查詢
    最近簡單的對oracle,mysql,sqlserver2005的數據分頁查詢作了研究，把各自的查詢的語句貼出來供大家學習..... （一）、 mysql的分頁查詢 mysql的分頁查詢是最簡單的，藉助關鍵字limit即可實現查詢，查詢語句通式： 如下麵的截圖，每頁顯示的記錄數為20： 查詢（1-2 ...
  • MySQL外鍵使用及說明(簡單易懂)
    MySQL外鍵使用及說明 一、外鍵約束 MySQL通過外鍵約束來保證表與表之間的數據的完整性和準確性。 外鍵的使用條件： 1.兩個表必須是InnoDB表，MyISAM表暫時不支持外鍵（據說以後的版本有可能支持，但至少目前不支持）； 2.外鍵列必須建立了索引，MySQL 4.1.2以後的版本在建立外鍵 ...
  • NoSql之MongoDB--Ubuntu下安裝
    MongoDB只提供了64位LTS(長期支持)Ubuntu發行版的packages。例如,12.04 LTS,14.04 LTS,16.04 LTS等等。 1.導入被包管理系統使用的公鑰 Ubuntu軟體包管理工具(例如：dpkg和apt)通過要求經銷商使用GPG密鑰簽署包以確保方案的一致性和真實性 ...
  • Storm 運行例子
    1.建立Java工程 使用idea，添加lib庫，拷貝storm中lib到工程中 2.拷貝wordcount代碼 下載src包，解壓找到 apache-storm-0.9.4-src\apache-storm-0.9.4\examples\storm-starter\src\jvm\storm\st ...
  • MySQL實現兩張表數據的同步
    有兩張表A和B，要求往A裡面插入一條記錄的同時要向B裡面也插入一條記錄，向B裡面插入一條記錄的同時也向A插入一條記錄。兩張表的結構不同，需要將其中幾個欄位對應起來。可以用下麵的觸發器實現。 表A的觸發器： 表B的觸發器： ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類