雲計算

MySQL binlog

2020-05-26

【轉載請註明出處】：https://www.jianshu.com/p/5851356bdddf

1、binlog介紹

binlog是Mysql sever層維護的一種二進制日誌，與innodb引擎中的redo/undo log是完全不同的日誌；其主要是用來記錄對mysql數據更新或潛在發生更新的SQL語句，並以"事務"的形式保存在磁盤中。mysql的binlog是多文件存儲，定位一個LogEvent需要通過binlog filename + binlog position，進行定位。

作用主要有：

  • 複製：MySQL Replication在Master端開啟binlog，Master把它的二進制日誌傳遞給slaves並回放來達到master-slave數據一致的目的
  • 數據恢復：通過mysqlbinlog工具恢復數據
  • 增量備份

2、binlog的數據格式

MySQL Replication 複製可以是基於一條語句 (Statement Level) ，也可以是基於一條記錄 (Row Level)，可以在 MySQL 的配置參數中設定這個複製級別，不同複製級別的設置會影響到 Master 端的 bin-log 日誌格式。

row-based

在基於行的日誌中，master會將事件寫入二進制日誌文件以表明單個表的行如何受到影響。日誌中會記錄成每一行數據被修改的形式，然後在 slave 端再對相同的數據進行修改。

優點： 在 row 模式下，bin-log 中可以不記錄執行的 SQL 語句的上下文相關的信息，僅僅只需要記錄那一條記錄被修改了，修改成什麼樣了。所以 row 的日誌內容會非常清楚的記錄下每一行數據修改的細節，非常容易理解。而且不會出現某些特定情況下的存儲過程或 function ，以及 trigger 的調用和觸發無法被正確複製的問題。

缺點：所有的執行的語句當記錄到日誌中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日誌內容,比如一條update語句，修改多條記錄，則binlog中每一條修改都會有記錄，這樣造成binlog日誌量會很大，特別是當執行alter table之類的語句的時候，由於表結構修改，每條記錄都發生改變，那麼該表每一條記錄都會記錄到日誌中。

statement-based

每一條會修改數據的 SQL 都會記錄到 master 的 bin-log 中。slave 在複製的時候 SQL 進程會解析成和原來 master 端執行過的相同的 SQL 再次執行。

優點： 不記錄每一行數據的變化，減少了 bin-log 日誌量，節省 I/O 以及存儲資源，提高性能。因為只記錄在 master 上所執行的語句的細節，以及執行語句時候的上下文的信息。

缺點： 由於記錄的執行語句，所以，為了讓這些語句在 slave 端也能正確執行，那麼他還必須記錄每條語句在執行的時候的一些相關信息，也就是上下文信息，以保證所有語句在 slave 端杯執行的時候能夠得到和在 master 端執行時候相同的結果。在 statement 中，目前已經發現的就有不少情況會造成 MySQL 的複製出現問題，主要是修改數據的時候使用了某些特定的函數或者功能的時候會出現，比如：sleep() 函數在有些版本中就不能被正確複製，在存儲過程中使用了 last_insert_id() 函數，可能會使 slave 和 master 上得到不一致的 id 等等。由於 row 是基於每一行來記錄的變化，所以不會出現類似的問題。

mixed

從 5.1.8 版本開始，MySQL 提供了除 statement 和 row 之外的第三種複製模式：mixed，實際上就是前兩種模式的結合。在 mixed 模式下，MySQL 會根據執行的每一條具體的 SQL 語句來區分對待記錄的日誌形式，也就是在 statement 和 row 之間選擇一種。一般的語句修改使用statment格式保存binlog，如表結構變更，但對於statement無法完成主從複製的操作，如一些函數，則採用row格式保存binlog。

如果 binlog 採用了 Mixed 模式，那麼在以下幾種情況下會自動將 binlog 的模式由 statement 模式變為 row 模式：

  • 當 DML 語句更新一個 NDB（NDB Cluster） 表時；
  • 當函數中包含 UUID() 時；
  • 2 個及以上包含 AUTO_INCREMENT 字段的表被更新時；
  • 執行 INSERT DELAYED 語句時；
  • 用 UDF（Userdefined function） 時；
  • 視圖中必須要求運用 row 時，例如建立視圖時使用了 UUID() 函數；

row-based和statement-based特點總結

statement 優點：

  • 歷史悠久，技術成熟；
  • 產生的 binlog 文件較小；
  • binlog 中包含了所有數據庫修改信息，可以據此來審核數據庫的安全等情況；
  • binlog 可以用於實時的還原，而不僅僅用於複製；
  • 主從版本可以不一樣，從服務器版本可以比主服務器版本高；

statement 缺點：

  • 不是所有的 UPDATE 語句都能被複制，尤其是包含不確定操作的時候；
  • 調用具有不確定因素的函數時複製也可能出現問題；

  • 運用以下函數的語句也不能被複制：

    1、LOAD_FILE()
2、UUID()
3、USER()
4、FOUND_ROWS()
5、SYSDATE() (除非啟動時啟用了 –sysdate-is-now 選項)
  • INSERT … SELECT 會產生比 RBR（row-based replication） 更多的行級鎖；
  • 複製須要執行全表掃描 (WHERE 語句中沒有運用到索引) 的 UPDATE 時，須要比 row 請求更多的行級鎖；
  • 對於有 AUTO_INCREMENT 字段的 InnoDB 表而言，INSERT 語句會阻塞其他 INSERT 語句；
  • 對於一些複雜的語句，在從服務器上的耗資源情況會更嚴重，而 row 模式下，只會對那個發生變化的記錄產生影響；
  • 存儲函數(不是存儲流程 )在被調用的同時也會執行一次 NOW() 函數，這個可以說是壞事也可能是好事；
  • 確定了的 UDF 也須要在從服務器上執行；
  • 數據表必須幾乎和主服務器保持一致才行，否則可能會導致複製出錯；
  • 執行復雜語句如果出錯的話，會消耗更多資源；

row 優點：

  • 任何情況都可以被複制，這對複製來說是最安全可靠的；
  • 和其他大多數數據庫系統的複製技能一樣；
  • 多數情況下，從服務器上的表如果有主鍵的話，複製就會快了很多；

  • 複製以下幾種語句時的行鎖更少：

    1、INSERT … SELECT
2、包含 AUTO_INCREMENT 字段的 INSERT
3、沒有附帶條件或者並沒有修改很多記錄的 UPDATE 或 DELETE 語句
  • 執行 INSERT，UPDATE，DELETE 語句時鎖更少；
  • 從服務器上採用多線程來執行復製成為可能；

row 缺點：

  • 生成的 binlog 日誌體積大了很多；
  • 複雜的回滾時 binlog 中會包含大量的數據；
  • 主服務器上執行 UPDATE 語句時，所有發生變化的記錄都會寫到 binlog 中，而 statement 只會寫一次，這會導致頻繁發生 binlog 的寫併發請求；
    -UDF 產生的大 BLOB 值會導致複製變慢；
  • 不能從 binlog 中看到都複製了寫什麼語句(加密過的)；
  • 當在非事務表上執行一段堆積的 SQL 語句時，最好採用 statement 模式，否則很容易導致主從服務器的數據不一致情況發生；

另外，針對系統庫 MySQL 裡面的表發生變化時的處理準則如下：

  • 如果是採用 INSERT，UPDATE，DELETE 直接操作表的情況，則日誌格式根據 binlog_format 的設定而記錄；
  • 如果是採用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那麼無論如何都要使用 statement 模式記錄；
  • 使用 statement 模式後，能處理很多原先出現的主鍵重複問題；
如何選擇binlog的模式

1、如果生產中使用MySQL的特殊功能相對少（存儲過程、觸發器、函數）。選擇默認的語句模式，Statement Level。
2、如果生產中使用MySQL的特殊功能較多的，可以選擇Mixed模式。
3、如果生產中使用MySQL的特殊功能較多，又希望數據最大化一致，此時最好Row level模式；但是要注意，該模式的binlog非常“沉重”。

3、binlog與redo/undo log區別

兩者是完全不同的日誌，主要有以下幾個區別：

  • 層次不同。redo/undo log是innodb層維護的，而binlog是mysql server層維護的，跟採用何種引擎沒有關係，記錄的是所有引擎的更新操作的日誌記錄。
  • 記錄內容不同。redo/undo日誌記錄的是每個頁的修改情況，屬於物理日誌+邏輯日誌結合的方式（redo log物理到頁，頁內採用邏輯日誌，undo log採用的是邏輯日誌），目的是保證數據的一致性。binlog記錄的都是事務操作內容，格式是二進制的。
  • 記錄時機不同。redo/undo日誌在事務執行過程中會不斷的寫入，而binlog是在事務最終commit前寫入的。當然，binlog什麼時候刷新到磁盤跟參數sync_binlog相關。

顯然，我們執行SELECT等不涉及數據更新的語句是不會記binlog的，而涉及到數據更新則會記錄。要注意的是，對支持事務的引擎如innodb而言，必須要提交了事務才會記錄binlog。

binlog刷新到磁盤的時機跟sync_binlog參數相關，如果設置為0，則表示MySQL不控制binlog的刷新，由文件系統去控制它緩存的刷新，而如果設置成不為0的值則表示每sync_binlog次事務，MySQL調用文件系統的刷新操作刷新binlog到磁盤中。設為1是最安全的，在系統故障時最多丟失一個事務的更新，但是會對性能有所影響，一般情況下會設置為100或者0，犧牲一定的一致性來獲取更好的性能。

4、binlog配置

開啟binlog

my.cnf配置中設置：

[mysqld]  
log-bin=mysql-bin    #binlog文件名前綴  
binlog-format=ROW    #ROW,Statement,MiXED三種格式 
expire_logs_days=7    #binlog過期清理時間
sync_binlog=1    #刷新到磁盤的時機

重啟mysql

service mysqld start

不重啟mysql重新加載my.cnf文件的情況下，修改binlog類型的方法：

#只對當前會話有效，mysql重啟無效
mysql> SET SESSION binlog_format = 'ROW'; 
#新會話有效，mysql重啟無效
mysql> SET GLOBAL binlog_format = 'ROW';

5、binlog的分析

binlog文件的目錄在my.cnf配置文件中datadir指定的位置，也可以通過sql語句查看所在位置及相關信息

#查看所在位置
mysql> show variables like '%datadir%';
#查看binlog的開啟狀態及文件名
mysql> show variables like '%log_bin%';
#查看binlog當前的格式
mysql> show variables like '%format%';
#查看binlog文件列表
mysql> show binary logs; 
#查看binlog的狀態
mysql> show master status;

默認情況下binlog日誌是二進制格式，無法直接查看。可使用兩種方式進行查看：

  • mysqlbinlog
    進入datadir查看binlog文件

      $ mysqlbinlog mysql-bin.000001
  ## 或者，遠程讀取 binlog 文件
  $ mysqlbinlog -R -hIP -uNAME -pPASSWORD mysql-bin.000001

  • 命令行解析

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

    如：

      mysql> show binlog events in 'mysql-bin.000001' from 0 limit 2,1 ;
statement格式log
# at 524
#190406 23:36:43 server id 1  end_log_pos 589 CRC32 0x790d8d0f  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 589
#190406 23:36:43 server id 1  end_log_pos 676 CRC32 0xc0498a17  Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1554565003/*!*/;
BEGIN
/*!*/;
# at 676
# at 708
#190406 23:36:43 server id 1  end_log_pos 708 CRC32 0xe7b12002  Intvar
SET INSERT_ID=75/*!*/;
#190406 23:36:43 server id 1  end_log_pos 863 CRC32 0x902ebba7  Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1554565003/*!*/;
insert into `test`.`t` ( `d`, `s`) values ( NOW(), '2019-03-15 09:53:47')
/*!*/;
# at 863
#190406 23:36:43 server id 1  end_log_pos 894 CRC32 0x30487fcc  Xid = 7
COMMIT/*!*/;
row格式log
# at 428
#190404 17:07:00 server id 1  end_log_pos 493 CRC32 0xcb947c46  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 493
#190404 17:07:00 server id 1  end_log_pos 573 CRC32 0x5050376a  Query   thread_id=33    exec_time=0     error_code=0
SET TIMESTAMP=1554368820/*!*/;
BEGIN
/*!*/;
# at 573
#190404 17:07:00 server id 1  end_log_pos 621 CRC32 0xe48f4fe2  Table_map: `test`.`t` mapped to number 108
# at 621
#190404 17:07:00 server id 1  end_log_pos 671 CRC32 0x8af05d9b  Write_rows: table id 108 flags: STMT_END_F

BINLOG '
NMmlXBMBAAAAMAAAAG0CAAAAAGwAAAAAAAEABHRlc3QAAXQAAwMSEgIAAATiT4/k
NMmlXB4BAAAAMgAAAJ8CAAAAAGwAAAAAAAEAAgAD//hJAAAAmaLJEcCZop6db5td8Io=
'/*!*/;
# at 671
#190404 17:07:00 server id 1  end_log_pos 702 CRC32 0x82022821  Xid = 8
COMMIT/*!*/;

兩種格式的日誌都包括如下信息：

  • position: 位於文件中的位置（# at 573），說明該事件記錄從文件哪個字節開始
  • timestamp: 事件發生的時間戳（#190404 17:07:00）
  • exec_time: 事件執行的花費時間
  • error_code: 錯誤碼
  • server id: 服務器標識
  • thread_id: 代理線程id
  • type: 事件類型(Query)
  • SET TIMESTAMP=1554368820/*!*/;: 開始事物的時間
  • end_log_pos: 為事件的終點(end_log_pos 671)
  • Xid: 事件指示提交的XA事務

6、binlog基本操作總結

查看
#查看所在位置
mysql> show variables like '%datadir%';
#查看binlog的開啟狀態及文件名
mysql> show variables like '%log_bin%';
#查看binlog當前的格式
mysql> show variables like '%format%';
#查看binlog文件列表
mysql> show binary logs; 
#查看binlog的狀態，即最後一個binlog日誌的編號名稱，及其最後一個操作事件pos結束點
mysql> show master status;

使用mysqlbinlog工具查看binlog文件

$ mysqlbinlog mysql-bin.000001
## 或者，遠程讀取 binlog 文件
$ mysqlbinlog -R -hIP -uNAME -pPASSWORD mysql-bin.000001
恢復數據
#基於時間點恢復：
$ mysqlbinlog --start-datetime="2019-04-04 23:20:35" --stop-datetime="2019-04-04 23:50:18" mysql-bin.000001 | mysql -uroot -p123456 

# 基於時間點恢復：
$ mysqlbinlog --start-position= 428 --stop-position=671 mysql-bin.000001 | mysql -uroot -p123456

也可以使用工具binlog2sql
)，從MySQL binlog解析出你要的SQL。根據不同選項，你可以得到原始SQL、回滾SQL、去除主鍵的INSERT SQL等。

清理
#刷新log日誌，自此刻開始產生一個新編號的binlog日誌文件
#每當mysqld服務重啟時，會自動執行此命令，刷新binlog日誌；在mysqldump備份數據時加 -F 選項也會刷新binlog日誌；
mysql> flush logs;
#重置(清空)所有binlog日誌
mysql> reset master;
#刪除指定日期以前的日誌索引中binlog日誌文件
mysql> purge master logs before '2019-03-15 09:35:00';
#刪除指定日誌文件的日誌索引中binlog日誌文件
mysql> purge master logs to 'binlog.000001';

7、 MySQL基於binlog的複製過程

複製是mysql最重要的功能之一，mysql集群的高可用、負載均衡和讀寫分離都是基於複製來實現的；從5.6開始複製有兩種實現方式，基於binlog和基於GTID（全局事務標示符），基於binlog的一主一從複製的基本過程如下：

  1. Master將數據改變記錄到二進制日誌(binary log)中
  2. Slave上面的IO進程連接上Master，並請求從指定日誌文件的指定位置（或者從最開始的日誌）之後的日誌內容
  3. Master接收到來自Slave的IO進程的請求後，負責複製的IO進程會根據請求信息讀取日誌指定位置之後的日誌信息，返回給Slave的IO進程。返回信息中除了日誌所包含的信息之外，還包括本次返回的信息已經到Master端的bin-log文件的名稱以及bin-log的位置
  4. Slave的IO進程接收到信息後，將接收到的日誌內容依次添加到Slave端的relay-log文件的最末端，並將讀取到的Master端的 bin-log的文件名和位置記錄到master-info文件中，以便在下一次讀取的時候能夠清楚的告訴Master從某個bin-log的哪個位置開始往後的日誌內容
  5. Slave的Sql進程檢測到relay-log中新增加了內容後，會馬上解析relay-log的內容成為在Master端真實執行時候的那些可執行的內容，並在自身執行

【轉載請註明出處】：https://www.jianshu.com/p/5851356bdddf

Related Posts

揭秘！机器人和你对话时在想什么？ | 开发者必读（045期）

2019-08-20

阿里云CDN上线 WAF，一站式提供分发+安全能力

2019-08-20

阿里云合作伙伴峰会SaaS加速器专场 | 阿里邮箱产品生态

2019-08-20

Leave a Reply

Your email address will not be published. Required fields are marked *