MySQL 備庫可以設定 sync_binlog 非 1 嗎？【轉】

paul_hch發表於2024-08-07
MySql

眾所周知，防止斷電丟失 Binlog、故障恢復過程丟失資料，MySQL 主庫必須設定 sync_binlog=1。那麼作為備庫可以例外嗎？

我們的第一反應當然是不行，既然主庫會丟資料，備庫自然一樣。但其實不然，備庫丟了資料是可以重新從主庫上覆制的，只要這個複製的位置和備庫本身資料的位置一致就 OK 了，它們能一致嗎？本文將對這個問題進行討論。

背景知識

為了更好的說明這個問題，下面贅述一下相關的知識點：

  1. InnoDB 的二階段提交中，Prepare 階段寫 Redo Log，Commit 階段寫 Binlog，故障恢復時保證：
  2. 所有已提交事務的 Binlog 一定存在。
  3. 所有未提交事務一定不記錄 Binlog。
  4. 備庫設定 relay_log_info_repository = table 時，slave_relay_log_info（即備庫回放位置）的更新與 Relay Log 回放的 SQL 在同一個事務中提交。
  5. GTID 持久化在 Binlog 中，備庫在某些條件下啟動複製時會從 Executed_Gtid_Set 開始到主庫複製資料。

根據以上 3 點，備庫如果設定 sync_binlog 不為 1，在做故障恢復時的就會發生以下情況。

  • 事務狀態：TRX_COMMITTED_IN_MEMORY、TRX_NOT_STARTED。如果 Binlog 未落盤，事務會重做，資料將比 Binlog 多，slave_relay_log_info 表記錄的複製位置也將領先 Executed_Gtid_Set。
  • 事務狀態：TRX_PREPARED。 由於Binlog 未刷盤，Recovery 時會回滾事務，資料與 Binlog 是一致的，slave_relay_log_info 表記錄的複製位置等於 Executed_Gtid_Set。

如果備庫斷電恢復後，啟動複製時用的位置由 slave_relay_log_info 決定，則備庫資料還是能正常複製資料，並且能與主庫保持一致，只是 GTID 會出現跳號。

反之如果由 Executed_Gtid_Set 決定，則備庫複製會因為重複回放事務而報錯，需要進行修復。下面設計一個實驗來進行驗證。

實驗過程

1. 設定備庫引數並製造“故障”

備庫引數設定如下，主庫用工具併發寫入資料（這裡用的 mysqlslap），然後備庫強制關機（reboot -f）。

sync_binlog = 1000
innodb_flush_log_at_trx_commit = 1
relay_log_info_repository = table  ##slave_relay_log_info 表為 innodb 表
relay_log_recovery = on
gtid_mode = on

2. 重啟備庫

備庫伺服器開機後重啟 MySQL，檢視的資訊如下。

show master status 輸出的 Executed_Gtid_Set 如下：
fb9b7d78-6eb5-11ec-985a-0242ac101704:1-167216 

mysql> select * from slave_relay_log_info\G
*************************** 1. row ***************************
  Number_of_lines: 7
   Relay_log_name: ./localhost-relay-bin.000004
    Relay_log_pos: 4
  Master_log_name: mysql-bin.000001
   Master_log_pos: 48159613
        Sql_delay: 0
Number_of_workers: 0
               Id: 1
     Channel_name:
1 row in set (0.00 sec)

根據輸出內容可知，從庫的資料確實回放到了 mysql-bin.000001:48159613，對應的 GTID 為：
fb9b7d78-6eb5-11ec-985a-0242ac101704:167222，
只是從庫的 Binlog 有丟失，GTID 為：
fb9b7d78-6eb5-11ec-985a-0242ac101704:1-167216。

...
SET @@SESSION.GTID_NEXT= 'fb9b7d78-6eb5-11ec-985a-0242ac101704:167222'/*!*/;
...
### INSERT INTO `mysqlslap`.`t`
### SET
###   @1=167216 /* INT meta=0 nullable=0 is_null=0 */
# at 48159586
#220407 14:10:34 server id 123456  end_log_pos 48159613         Xid = 169239
COMMIT/*!*/;
# at 48159613
...

從庫已經有 167222 事務對應的資料。

mysql> select * from t where id=167216;
+--------+
| id     |
+--------+
| 167216 |
+--------+
1 row in set (0.00 sec)

3. 備庫啟動複製

Error Log 顯示的起始位置和 slave_relay_log_info 內容一樣，從主庫的 mysql-bin.000001:48159613 開始，對應 GTID 為 167222+1。

Slave I/O thread: Start asynchronous replication to master 'repl@10.186.61.32:3308' in log 'mysql-bin.000001' at position 48159613

但接下來 SQL 執行緒報錯位置卻是 mysql-bin.000001:48158146，比開始位置還靠前，這個位置對應的 GTID 為 167217（即167216+1）：

2022-04-07T06:33:18.611181-00:00 4 [ERROR] Slave SQL for channel '': Could not execute Write_rows event on table mysqlslap.t; Duplicate entry '167212' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 48158146, Error_code: 1062

而且解析從庫 Relay Log（因為設定了 relay_log_recovery = on，啟動複製時會丟棄舊的未 Relay Log 重新到主庫取 Binlog），第一個事務也是 SET @@SESSION.GTID_NEXT= '
fb9b7d78-6eb5-11ec-985a-0242ac101704:167217'/*!*/;，而不是 167223。這說明了啟動複製的位置並不是 slave_relay_log_info 記錄的位置，而是從庫的 GTID。

4. 重複以上測試

在啟動從庫複製前執行 change master to master_auto_position=0; 這回不報錯，是從 167223 這個 GTID 開始複製資料，從庫 GTID 會出現跳號。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                           |
+------------------+----------+--------------+------------------+-------------------------------------------------------------+
| mysql-bin.000006 |  9976340 |              |                  | fb9b7d78-6eb5-11ec-985a-0242ac101704:1-167216:167223-200670 |
+------------------+----------+--------------+------------------+-------------------------------------------------------------+
1 row in set (0.01 sec)

結論

從庫 sync_binlog 設定不為 1，發生斷電會丟失 Binlog，因為 GTID 持久化在 Binlog 中，因此也會丟失 GTID。但是資料和 slave_relay_log_info 表中儲存的 SQL 執行緒回放位置一致。

此時：

  • 如果 master_auto_position=0，則從庫重啟複製時可以從正確的位置開始複製資料，從而與主庫資料一致。不過從庫會產生 GTID 跳號。
  • 如果 master_auto_position=1，則從庫重啟複製時會從 GTID 處開始複製資料，由於 GTID 有丟失，所以會重複回放事務，產生報錯。

轉自

MySQL 備庫可以設定 sync_binlog 非 1 嗎？-今日頭條
https://www.toutiao.com/article/7397351436743246351/?log_from=3e7c681c8f92f_1723000169039

相關文章