圖文結合帶你搞懂MySQL日誌之Binary log（二進位日誌）

来源:https://www.cnblogs.com/greatsql/archive/2023/06/05/17457381.html
-Advertisement-
Play Games

往期回顧 - [圖文結合帶你搞定MySQL日誌之Undo log(回滾日誌)](http://mp.weixin.qq.com/s?__biz=MzkzMTIzMDgwMg==&mid=2247496981&idx=1&sn=ec496da6e52e19ee505483a15fb54f6b&chks ...


往期回顧

6ingvhy40

此篇為圖文結合搞懂MySQL日誌的最後一篇，MySQL中共有八大日誌，其中數據定義語句日誌不是給用戶查看的，在此篇尾部簡單介紹，接下來圖文結合系列還會繼續推出新文章！

二進位日誌(Binary log)

binlog可以說是MySQL中比較重要的日誌了，在日常開發及運維過程中，經常會遇到。

binlog即binary log，二進位日誌文件，也叫作變更日誌（update log）。它記錄了資料庫所有執行的DDL和DML等資料庫更新事件的語句，但是不包含沒有修改任何數據的語句（如數據查詢語句select、show等）。

它以事件形式記錄並保存在二進位文件中。通過這些信息，我們可以再現數據更新操作的全過程。

如果想要記錄所有語句（例如，為了識別有問題的查詢)，需要使用通用查詢日誌。

Binary log主要應用場景：

  • 一是用於數據恢復，如果MySQL資料庫意外停止，可以通過二進位日誌文件來查看用戶執行了哪些操作，對資料庫伺服器文件做了哪些修改，然後根據二進位日誌文件中的記錄來恢複數據庫伺服器。
  • 二是用於數據複製，由於日誌的延續性和時效性，master把它的二進位日誌傳遞給slaves來達到master-slave數據一致的目的。

可以說MySQL資料庫的數據備份、主備、單主、多主、MGR都離不開Binary log,需要依靠Binary log來同步數據，保證數據一致性。

圖片

查看預設情況

查看記錄二進位日誌是否開啟：在MySQL8中預設情況下，二進位文件是開啟的。

mysql>  show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |  //開關
| log_bin_basename                | /var/lib/mysql/binlog       | // 存放路徑
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | ON                          |//  函數創建 
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |//變更sql記錄下來
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)
  • log_bin_basename:是binlog日誌的基本文件名，後面會追加標識來表示每一個文件
  • log_bin_index:是binlog文件的素引文件，這個文件管理了所有的binlog文件的目錄
  • log_bin_trust_function_creators:限制存儲過程，前面我們已經講過了，這是因為二進位日誌的一個重要功能是用於主從複製，而存儲函數有可能導致主從的數據不一致。所以當開啟二進位日誌後，需要限制存儲函數的創建、修改、調用
  • log_bin_use_v1_row_events此只讀系統變數已棄用。ON表示使用版本1二進位日誌行，OFF表示使用版本2二進位日誌行（MySQL5.6的預設值為2)。

日誌參數設置

方式 1 ：永久性方式

修改MySQL的my.cnf或my.ini文件可以設置二進位日誌的相關參數：

[mysqld]
#啟用二進位日誌
log-bin=atguigu-bin
binlog_expire_logs_seconds= 600
max_binlog_size=100M

提示:

  1. log-bin=mysql-bin

打開日誌(主機需要打開)，這個mysql-bin也可以自定義，這裡也可以加上路徑，如:/home/www/mysql_bin_log/mysql-bin

  1. binlog_expire_logs_seconds

此參數控制二進位日誌文件保留的時長單位是秒，預設2592000 30天 --14400 4小時;86400 1天; 259200 3天;

  1. max_binlog_size

控制單個二進位日誌大小，當前日誌文件大小超過此變數時，執行切換動作。此參數的最大和預設值是1GB，該設置並不能嚴格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性，可能不做切換日誌的動作只能將該事務的所有SQL都記錄進當前日誌，直到事務結束。一般情況下可採取預設值。

設置帶文件夾的bin-log日誌存放目錄

如果想改變日誌文件的目錄和名稱，可以對my.cnf或my.ini中的log_bin參數修改如下：

[mysqld]
log-bin="/var/lib/mysql/binlog/atguigu-bin"

註意：新建的文件夾需要使用mysql用戶，使用下麵的命令即可。

chown -R -v mysql:mysql binlog

提示 資料庫文件最好不要與日誌文件放在同一個磁碟上!這樣，當資料庫文件所在的磁碟發生故障時，可以使用日誌文件恢複數據。

方式 2 ：臨時性方式

如果不希望通過修改配置文件並重啟的方式設置二進位日誌的話，還可以使用如下指令，需要註意的是在mysql 8 中只有會話級別的設置，沒有了global級別的設置。

# global 級別
mysql> set global sql_log_bin= 0 ;
ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION variable and can`t be used
with SET GLOBAL

# session級別
mysql> SET sql_log_bin = 0 ;
Query OK, 0 rows affected (0.01 秒)

查看日誌

當MySQL創建二進位日誌文件時，先創建一個以“filename”為名稱、以“.index”為尾碼的文件，再創建一個以“filename”為名稱、以“.000001”為尾碼的文件。

MySQL服務重新啟動一次，以“.000001”為尾碼的文件就會增加一個，並且尾碼名按 1 遞增。即日誌文件的數與MySQL服務啟動的次數相同；如果日誌長度超過了max_binlog_size的上限（預設是1GB），就會創建一個新的日誌文件。

查看當前的二進位日誌文件列表及大小。指令如下：

mysql> SHOW BINARY LOGS;
+--------------------+-----------+-----------+
| Log_name           | File_size | Encrypted |
+--------------------+-----------+-----------+
| atguigu-bin.000001 | 156       | No        |
+--------------------+-----------+-----------+
1 rows in set (0.00 sec)

所有對資料庫的修改都會記錄在binglog中。但binlog是二進位文件，無法直接查看，藉助mysqlbinlog命令工具了。指令如下:在查看執行，先執行一條sQL語句，如下

update student set name='張三_back' where id=1;
[root@localhost ~]$ cd /var/lib/mysql
[root@localhost ~]$ mysqlbinlog  "/var/lib/mysql/lqhdb-binlog.000001"

執行結果可以看到，這是一個簡單的日誌文件，日誌中記錄了用戶的一些操作，這裡並沒有出現具體的SQL語句，這是因為binlog關鍵字後面的內容是經過編碼後的二進位日誌。

這裡一個update語句包含如下事件

  • Query事件負責開始一個事務(BEGIN)
  • Table_map事件負責映射需要的表
  • Update_rows事件負責寫入數據
  • Xid事件負責結束事務

下麵命令將行事件以偽SQL的形式表現出來

mysqlbinlog -v "/var/lib/mysql/binlog/test.000002"

前面的命令同時顯示binlog格式的語句，使用如下命令不顯示它

mysqlbinlog -v --base64-output=DECODE-ROWS "/var/lib/mysql/binlog/test.000002"

關於mysqlbinlog工具的使用技巧還有很多，例如只解析對某個庫的操作或者某個時間段內的操作等。簡單分享幾個常用的語句，更多操作可以參考官方文檔。

# 可查看參數幫助
mysqlbinlog --no-defaults --help

# 查看最後 100 行
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |tail - 100

# 根據position查找
mysqlbinlog --no-defaults --base64-output=decode-rows -vv atguigu-bin.000002 |grep -A 
20 '4939002'

上面這種辦法讀取出binlog日誌的全文內容比較多，不容易分辨查看到pos點信息，下麵介紹一種更為方便的查詢命令：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
  • IN 'log_name'：指定要查詢的binlog文件名（不指定就是第一個binlog文件）
  • FROM pos：指定從哪個pos起始點開始查起（不指定就是從整個文件首個pos點開始算）
  • LIMIT [offset]：偏移量(不指定就是 0 )
  • row_count :查詢總條數（不指定就是所有行）

上面這條語句可以將指定的binlog日誌文件，分成有效事件行的方式返回，並可使用limit指定pos點的起始偏移，查詢條數。其它舉例:

#a、查詢第一個最早的binlog日誌:
show binlog events\G ;

#b、指定查詢mysql-bin.088802這個文件
show binlog events in 'atguigu-bin. 008002'\G;

#c、指定查詢mysql-bin. 080802這個文件，從pos點:391開始查起:
show binlog events in 'atguigu-bin.008802' from 391\G;

#d、指定查詢mysql-bin.000802這個文件，從pos點:391開始查起，查詢5條（即5條語句)
show binlog events in 'atguigu-bin.000882' from 391 limit 5\G

#e、指定查詢 mysql-bin.880002這個文件，從pos點:391開始查起，偏移2行〈即中間跳過2個）查詢5條（即5條語句)。
show binlog events in 'atguigu-bin.088882' from 391 limit 2,5\G;

binlog格式查看

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   | //行格式
+---------------+-------+
1 rows in set (0.00 sec)

除此之外，binlog還有 2 種格式，分別是Statemen和Mixed

  • Statement 每一條會修改數據的sql都會記錄在binlog中。 優點：不需要記錄每一行的變化，減少了binlog日誌量，節約了IO，提高性能。
  • Row 5.1.5版本的MySQL才開始支持row level 的複製，它不記錄sql語句上下文相關信息，僅保存哪條記錄被修改。 優點：row level 的日誌內容會非常清楚的記錄下每一行數據修改的細節。而且不會出現某些特定情況下的存儲過程，或function，以及trigger的調用和觸發無法被正確複製的問題。
  • Mixed 從5.1.8版本開始，MySQL提供了Mixed格式，實際上就是Statement與Row的結合。

使用日誌恢複數據

mysqlbinlog恢複數據的語法如下：

mysqlbinlog [option] filename|mysql –uuser -ppass;

這個命令可以這樣理解：使用mysqlbinlog命令來讀取filename中的內容，然後使用mysql命令將這些內容恢復到資料庫中。

  • filename：是日誌文件名。

  • option：可選項，比較重要的兩對option參數是–start-date、–stop-date 和 --start-position、–stop-position。

    • –start-date 和 - -stop-date：可以指定恢複數據庫的起始時間點和結束時間點。
    • –start-position和–stop-position：可以指定恢複數據的開始位置和結束位置。

註意：使用mysqlbinlog命令進行恢復操作時，必須是編號小的先恢復，例如atguigu-bin.000001必須在atguigu-bin.000002之前恢復。

flush logs; #可以生成新的binLog 文件，不然這個文件邊恢復邊變大是不行的。

show binary logs; # 顯示有哪些binLog 文件

恢複數據

mysqlbinlog [option] filename|mysql –uuser -ppass;

mysqlbinlog --no-defaults  --start-position=236  --stop-position=1071 --database=my_db1 /var/lib/mysql/lqhdb-bin.000002 | /usr/bin/mysql -root -p123456 -v my_db1

刪除二進位日誌

MySQL的二進位文件可以配置自動刪除，同時MySQL也提供了安全的手動刪除二進位文件的方法。PURGE MASTER LOGS只刪除指定部分的二進位日誌文件，RESET MASTER刪除所有的二進位日誌文件。具體如下：

1.PURGE MASTER LOGS：刪除指定日誌文件

PURGE MASTER LOGS語法如下：

PURGE {MASTER | BINARY} LOGS TO ‘指定日誌文件名’

PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

舉例 :使用PURGE MASTER LOGS語句刪除創建時間比binlog.000005早的所有日誌

(1)多次重新啟動MysSQL服務，便於生成多個日誌文件。然後用SHOW語句顯示二進位日誌文件列表

SHOW BINARY LOGS;

(2）執行PURGE MASTER LOGS語句刪除創建時間比binlog.000005早的所有日誌

PURGE MASTER LOGS T0 "binlog. 000005";

(3）顯示二進位日誌文件列表

SHGW BINARY LOGS;

舉例:使用PURGE MASTER LOGS語句刪除2023年3月17日前創建的所有日誌文件。具體步驟如下:

(1) 顯示二進位日誌文件列表

SHOW BINARY LOGS;

(2）執行mysqlbinlog命令查看二進位日誌文件binlog.000005的內容

mysqlbinlog --no-defaults "/var/lib/mysql/binlog/atguigu-bin.000005"

(3）使用PURGE MASTER LOGS語句刪除2023年3月17日前創建的所有日誌文件

PURGE MASTER LOGS before "20220317";

(4）顯示二進位日誌文件列表

SHOW BINARY LOGS;

2022年01月05號之前的二進位日誌文件都已經被刪除，最後一個沒有刪除，是因為當前在用，還未記錄最後的時間，所以未被刪除。

2.RESET MASTER:刪除所有二進位日誌文件

reset master;

其它場景

二進位日誌可以通過資料庫的全量備份和二進位日誌中保存的增量信息，完成資料庫的無損失恢復。但是，如果遇到數據量大、資料庫和數據表很多（比如分庫分表的應用）的場景，用二進位日誌進行數據恢復，是很有挑戰性的，因為起止位置不容易管理。

在這種情況下，一個有效的解決辦法是配置主從資料庫伺服器，甚至是一主多從的架構，把二進位日誌文件的內容通過中繼日誌，同步到從資料庫伺服器中，這樣就可以有效避免資料庫故障導致的數據異常等問題。

深入理解二進位日誌

寫入機制

binlog的寫入時機也非常簡單，事務執行過程中，先把日誌寫到binlog cache，事務提交的時候，再把binlog cache寫到binlog文件中。因為一個事務的binlog不能被拆開，無論這個事務多大，也要確保一次性寫入，所以系統會給每個線程分配一個塊記憶體作為binlog cache。

我們可以通過binlog_cache_size參數控制單個線程binlog cache大，如果存儲內容超過了這個參數，就要暫存到磁碟(Swap)。binlog日誌刷盤流程如下:

圖片

上圖的write，是指把日誌寫入到文件系統的page cache，並沒有把數據持久化到磁碟，所以速度比較快。

上圖的fsync，才是將數據持久化到磁碟的操作

write和fsync的時機，可以由參數sync_binlog控制，預設是 0 。

為 0 的時候，表示每次提交事務都只write，由系統自行判斷什麼時候執行fsync。雖然性能得到提升，但是機器宕機，page cache裡面的binglog 會丟失。如下圖：圖片

為了安全起見，可以設置為 1 ，表示每次提交事務都會執行fsync，就如同 redo log 刷盤流程 一樣。最後還有一種折中方式，可以設置為N(N>1)，表示每次提交事務都write，但累積N個事務後才fsync。

圖片

在出現IO瓶頸的場景里，將sync_binlog設置成一個比較大的值，可以提升性能。同樣的，如果機器宕機，會丟失最近N個事務的binlog日誌。

binlog與redolog對比

  • redo log 它是物理日誌，記錄內容是“在某個數據頁上做了什麼修改”，屬於 InnoDB 存儲引擎層產生的。

  • 而 binlog 是邏輯日誌，記錄內容是語句的原始邏輯，類似於“給 ID=2 這一行的 c 欄位加 1”，屬於MySQL Server 層

  • 雖然它們都屬於持久化的保證，但是則重點不同。

    • redo log讓InnoDB存儲引擎擁有了崩潰恢復能力。
    • binlog保證了MySQL集群架構的數據一致性。

Enjoy GreatSQL

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

-Advertisement-
Play Games
更多相關文章
  • 手把手實踐丨基於STM32+NBIOT+華為雲IOT設計智能井蓋
    摘要：本文介紹基於STM32微控制器、BC26 NBIOT模組和華為雲IOT平臺，實現了一款智能井蓋系統。 本文分享自華為雲社區《基於STM32+NBIOT+華為雲IOT設計的智能井蓋》，作者：DS小龍哥 。 一、概述 智能井蓋是一種通過物聯網技術實現對井蓋狀態監測和管理的設備。當前介紹基於STM3 ...
  • 如何對VMware虛擬機磁碟擴容？
    ## 前置知識 動態捲管理（LVM，Logical Volume Manger）實現將多個硬碟和硬碟分區做成一個邏輯捲，並將邏輯捲統一管理。創建LVM順序為：物理捲PV->捲組VG->邏輯捲LV。 物理捲（PV，Physical Volume）：物理硬碟或分區； 捲組（VG，Volume Group ...
  • ubuntu20.4安裝配置ros系統（noetic）
    不同ubuntu版本對應的ros版本名稱 ubuntu版本 ros1版本 ros2版本 16.04 kinetic ardent 18.04 melodic dashing 20.04 noetic foxy 1、打開軟體與更新，切換ubuntu軟體源（國內阿裡雲） 2、打開終端，添加ros軟體源（ ...
  • 資料庫系統概論—關係理論、資料庫設計
    # 資料庫系統概論— 設計與應用開發篇(1) ## 一.關係數據理論 主要是關係中**屬性和屬性之間的依賴關係** ### 1相關基本概念 - **第一範式**：表中無表(屬性不可再分) - 數據依賴：是在一個關係內部屬性間的約束，分為函數和多值依賴。 eg:學號決定姓名 ### 2.規範化 ### ...
  • 9千多少兒百科全書百科知識ACCESS\EXCEL資料庫
    這是一個與兒童相關的百科知識資料庫，資料庫中包含了小孩子比較有興趣的動物植物、軍事知識、科學知識、天文地理等分類信息，是一個兒童教育必須掌握的知識。載圖下麵有更加詳細的分類統計信息： 詳細的分類情況如下： 動物植物包含：動物常識（256條）、海洋生物（69條）、人體奧秘（155條）、生物工程（114 ...
  • 近千個看圖猜電影瘋狂猜電影ACCESS\EXCEL資料庫
    之前發過一個《看圖猜電視劇電影含圖ACCESS資料庫》，今天又獲得了一個更完美的數據，不論在記錄數上還是在數據內容上都之前那麼更漂亮。更多看圖猜的數據可以訪問“看圖猜詞”分類； 不但包含電影名稱，還包含電影年份、主演、導演等 圖片的尺寸一般是：576X352，平均大小為26KB左右 截圖下方有顯示“ ...
  • MySQL Router高可用搭建
    1. 安裝簡介 2. 高可用搭建 3. 高可用及負載均衡測試 4. 問題處理 # 一、安裝簡介 ## 1.1 安裝目的 MySQL官方提供了InnoDB Cluster，該集群由MySQL MGR和MySQL Router組成。MySQL MGR在資料庫層面實現自主高可用性，而MySQL Route ...
  • 20項女生心理測試性格測試ACCESS資料庫
    今天這個資料庫是女生專項心理測試資料庫，資料庫包含4個表：Subject（測試項目表）、Question（項目下測試題目）、Answer（題目下選項及得分）、Explain（項目累計得分解析表），具體看以下截圖： 包含：你是一個樂觀的女生嗎？你是一個自信的女生嗎？你是一個可愛的女生嗎？你是一個意志堅 ...
一周排行
    -Advertisement-
    Play Games
  • C#自增運算符詳解：++i與i++的區別及應用場景
    概述：在C#中，++i和i++都是自增運算符，其中++i先增加值再返回，而i++先返回值再增加。應用場景根據需求選擇，首碼適合先增後用，尾碼適合先用後增。詳細示例提供清晰的代碼演示這兩者的操作時機和實際應用。 在C#中，++i 和 i++ 都是自增運算符，但它們在操作上有細微的差異，主要體現在操作的 ...
  • Taurus.MVC 性能壓力測試（ap 壓測 和 linux 下wrk 壓測）：.NET 版本
    上次發佈了：Taurus.MVC 性能壓力測試（ap 壓測 和 linux 下wrk 壓測）：.NET Core 版本，今天計劃準備壓測一下 .NET 版本，來測試並記錄一下 Taurus.MVC 框架在 .NET 版本的性能，以便後續持續優化改進。 為了方便對比，本文章的電腦環境和測試思路，儘量和... ...
  • 一文讀懂.NET WebAPI中FromRoute、FromQuery、FromBody的關鍵角色與用法
    .NET WebAPI作為一種構建RESTful服務的強大工具，為開發者提供了便捷的方式來定義、處理HTTP請求並返迴響應。在設計API介面時，正確地接收和解析客戶端發送的數據至關重要。.NET WebAPI提供了一系列特性，如[FromRoute]、[FromQuery]和[FromBody]，用 ...
  • WPF/C#實現圖像濾鏡優化方案：打造炫目視覺體驗！
    原因：我之所以想做這個項目，是因為在之前查找關於C#/WPF相關資料時，我發現講解圖像濾鏡的資源非常稀缺。此外，我註意到許多現有的開源庫主要基於CPU進行圖像渲染。這種方式在處理大量圖像時，會導致CPU的渲染負擔過重。因此，我將在下文中介紹如何通過GPU渲染來有效實現圖像的各種濾鏡效果。 生成的效果 ...
  • Bogus 實戰：使用 Bogus 和 EFCore 生成模擬數據和種子數據【完整教程】
    引言 上一章我們介紹了在xUnit單元測試中用xUnit.DependencyInject來使用依賴註入，上一章我們的Sample.Repository倉儲層有一個批量註入的介面沒有做單元測試，今天用這個示例來演示一下如何用Bogus創建模擬數據 ，和 EFCore 的種子數據生成 Bogus 的優 ...
  • WPF隨筆收錄-實時繪製心率曲線
    一、前言 在自己的項目中，涉及到實時心率曲線的繪製，項目上的曲線繪製，一般很難找到能直接用的第三方庫，而且有些還是定製化的功能，所以還是自己繪製比較方便。很多人一聽到自己畫就害怕，感覺很難，今天就分享一個完整的實時心率數據繪製心率曲線圖的例子；之前的博客也分享給DrawingVisual繪製曲線的方 ...
  • wpf程式修改啟動入口註意事項
    如果你在自定義的 Main 方法中直接使用 App 類並啟動應用程式，但發現 App.xaml 中定義的資源沒有被正確載入，那麼問題可能在於如何正確配置 App.xaml 與你的 App 類的交互。 確保 App.xaml 文件中的 x:Class 屬性正確指向你的 App 類。這樣，當你創建 Ap ...
  • 記一次 .NET某爐膛鍋爐檢測系統 崩潰分析
    一：背景 1. 講故事 上個月有個朋友在微信上找到我，說他們的軟體在客戶那邊隔幾天就要崩潰一次，一直都沒有找到原因，讓我幫忙看下怎麼回事，確實工控類的軟體環境複雜難搞，朋友手上有一個崩潰的dump，剛好丟給我來分析一下。 二：WinDbg分析 1. 程式為什麼會崩潰 windbg 有一個厲害之處在於 ...
  • 一個.NET內置依賴註入的小型強化版
    前言 .NET生態中有許多依賴註入容器。在大多數情況下，微軟提供的內置容器在易用性和性能方面都非常優秀。外加ASP.NET Core預設使用內置容器，使用很方便。 但是筆者在使用中一直有一個頭疼的問題：服務工廠無法提供請求的服務類型相關的信息。這在一般情況下並沒有影響，但是內置容器支持註冊開放泛型服 ...
  • WPF隨筆收錄-DataGrid固定右側列
    一、前言 在項目開發過程中，DataGrid是經常使用到的一個數據展示控制項，而通常表格的最後一列是作為操作列存在，比如會有編輯、刪除等功能按鈕。但WPF的原始DataGrid中，預設只支持固定左側列，這跟大家習慣性操作列放最後不符，今天就來介紹一種簡單的方式實現固定右側列。（這裡的實現方式參考的大佬 ...
所有分類