MySQL Replication crash saftety

type

status

date

slug

summary

設置建議

下圖是基於 MySQL 5.7.30~8.0.31 且 table engine = innodb：

若不考慮效能僅基於安全性有以下設置組合：

考慮到效能進行簡化，推薦以下組合：

Master

必須是雙 1 的配置：

一來保證 Master 不丟失任何 Transaction，二來如果 sync_binlog ≠ 1 時有可能發生 Slave 資料多餘 Master 的狀況。

當 sync_binlog ≠ 1 時，會在 FLUSH 階段後就將 binlog 發送給 slave 應用，這導致了當 Master crash 丟失了 binlog 中部分事件在 recovery 時將其 rollback，這就導致 Slave 和 Master 不一致的狀況。

當 sync_binlog = 1 時，會在 SYNC 階段 (binlog fsync disk) 後才會將 binlog 發送給 slave 應用，當 Master crash 不會丟失 binlog 中的 Event，因此不會發生問題。

Slave

通用設定 ( < MySQL 8.0.23)

這 2 個參數 ( master_info_repository 和 relay_log_info_repository ) 用於設置 replication 相關的 metadata 要儲存為 file 還是 innodb table。

這 2 個參數於 5.7 預設值為 file ，在 8.0~8.0.22 為 table ，從 8.0.23 開始棄用不需設置。

備註：其實 master_info_repository 設置為 table 為非必要，因為只有 relay_log_info 會和 SQL thread 會在同一個 transaction 一起 commit。

未開啟 MTS ( slave_parallel_workers = 0 )

傳統 POSITION

relay_log_recovery = 0

說明：當 relay_log_recovery = 0 時，在 start slave 時 IO_thread 會從 connection metadata 中紀錄的 master_log_name 和 master_log_pos 開始繼續向 master 拉取 binlog event，而 SQL_thread 也會從 applier metadata 中的紀錄繼續讀取 relay log 進行回放。因此 relay log 和 connection metadata - mysql.slave_master_info 的完整性都非常重要，必須保證每個 event 寫入 relay log 時都必須 fsync relay log、mysql.slave_master_info，因此設置了 sync_master_info = 1 及 sync_relay_log = 1 。

注意：因為 sync_relay_log = 1 表示每個 event 寫入 relay log 都要 fsync 到 disk 一次，因此效能受到很大的影響。

備註： relay_log_info_repository = TABLE 時，因為 SQL_thread 的 applier metadata 也就是 mysql.slave_relay_log_info 換和回放的每個 event 處於同一個 transarction，也就是說會一起 commit、rollback，因此 sync_relay_log_info 的設置變得一點也不重要。

relay_log_recovery = 1

說明：當 relay_log_recovery = 1 時，會在 start slave 時將 applier metadata 的 master_log_name 和 master_log_pos覆蓋 connection metadata 的 master_log_name 和 master_log_pos ，也就是說 IO_thread 會重新從 SQL_thread 最後回放的 event 對應的 master binlog 位置重新向 master 拉取 binlog event ，而 SQL_thread 也不會使用舊有的 relay log 而是直接從新的 relay log 開始回放，也就是說 relay log 和 connection metadata 一點都不重要，因此保持預設值即可。

GTID AUTO_POSITION

relay_log_recovery = 0

說明：當 relay_log_recovery = 0 時，因為 SQL_thread 會從 applier metadata 中的紀錄繼續讀取 relay log 進行回放，因此 relay log 的完整性同樣重要，需要設置 sync_relay_log = 1。和傳統 position 的不同，開啟 Gtid 後有以下兩個變量：

Retrieved_Gtid_Set：slave IO_thread 已接收的 Gtid。

Executed_Gtid_Set：slave 已經執行的 Gtid。

其中 Retrieved_Gtid_Set 會從 relay log 中掃描確認 (同樣需設置 sync_relay_log = 1 )，並和 Executed_Gtid_Set 做並集 (UNION) 發送給 master，master 和自己的 Executed_Gtid_Set 比較將有落差的 event 發送給 slave，因此 connection metadata 不重要 sync_master_info 可以保預設值即可。

relay_log_recovery = 1

說明：當 relay_log_recovery = 1 時，因為 SQL_thread 會被設定到新的 relay log (也就是不再使用舊有的 relay log)，而且 Retrieved_Gtid_Set 不會初始化，因此 relay log 的完整性不重要 sync_relay_log 可以維持預設值。

和傳統 position 的不同，開啟 Gtid 後有以下兩個變量：

Retrieved_Gtid_Set：slave IO_thread 已接收的 Gtid。
Executed_Gtid_Set：slave 已經執行的 Gtid。

其中 Retrieved_Gtid_Set 不會初始化，因此和 Executed_Gtid_Set 做並集 (UNION) 的結果就還是 Executed_Gtid_Set 發送給 master，master 和自己的 Executed_Gtid_Set 比較將有落差的 event發送給 slave，因此 connection metadata 不重要 sync_master_info 可以保預設值即可。

開啟 MTS ( slave_parallel_workers > 0 僅考慮 slave_parallel_type = LOGICAL_CLOCK )

傳統 POSITION

relay_log_recovery = 0

說明：和未開啟 MTS 傳統 POSITION 相同，slave_preserve_commit_order 的開啟與否不影響 replication crash safe，只影響因果一致性。

relay_log_recovery = 1

slave_preserve_commit_order = OFF

說明：當開啟 MTS 且 slave_preserve_commit_order = OFF 時，由於會發生 GAP 因此在 slave 開啟時會從舊有的 relay log 中恢復 GAP，因此和未開啟 MTS 時不同，這邊必須保證 relay_log 完整性，也就是 sync_relay_log = 1 ，但也因此失去效能。

補充 - GAP：當在 Master 依序有 A → B → C 3個 Transaction 且不衝突，那在 Slave 設定 slave_preserve_commit_order = OFF 時有可能發生 B、C 已經 commit ，但是 A 可能某些原因還沒 commit，這時候就產生了 GAP。

slave_preserve_commit_order = ON

說明：當開啟 MTS 且 slave_preserve_commit_order = ON 時，不會產生 GAP 因此 relay log 不需保證完整性，因此 sync_relay_log 可以保持預設值增加效能。

注意：在 MySQL 8.0.28 之前有此 BUG： MySQL Bugs: #103636: Slave hangs with slave_preserve_commit_order On ，當開啟 slave_preserve_commit_order = ON 在高負載長時間運行時會用盡 commit order sequence tickets 導致 applier 掛起 (hang) 並且無期限的持續等待 commit order queue。

GTID AUTO_POSITION

relay_log_recovery = 0

設定和未開啟 MTS 時相同

Retrieved_Gtid_Set：slave IO_thread 已接收的 Gtid。

Executed_Gtid_Set：slave 已經執行的 Gtid。

唯一特別的是 slave_preserve_commit_order 不論 ON 還是 OFF 都不影響，因為 Gtid AUTO_POSITION 下會自行填補 GAP。

relay_log_recovery = 1

說明：slave 發送 Executed_Gtid_Set 給 master，master 和自己的 Executed_Gtid_Set 比較將有落差的 event 發送給 slave，同時這也會彌補 slave MTS 下的 GAP。

例外情況 (請務必確認此段敘述)

雖然最推薦使用 relay_log_recovery 來減少對於 relay log 的依賴性 (也就是允許 relay log 損壞)，但因為每次重啟 MySQL (不論是否 crash) 都會丟棄原有的 relay log，所以可能有以下問題：

如果 slave 未執行的 relay log 數量龐大，則當重啟 slave 時會影響 master 的網路相關 loading。

假設 master 不可用或 purge 掉 binlog，儘管 relay log 未損壞，但 relay_log_recovery 的關係 slave 因為無法獲取 binlog 無法同步。

社區建議官方只在 relay log 損壞時，才執行 relay_log_recovery

MySQL Bugs: #74321: Execute relay-log-recovery only when needed.

MySQL Bugs: #74323: Avoid overloading the master NIC on relay-log-recovery of a lagging slave.

其他方案：可以使用 binlog server

booking 解決方案參考：https://medium.com/booking-com-infrastructure/better-crash-safe-replication-for-mysql-a336a69b317f

在 MySQL 8.0.17 之前， GTID AUTO_POSITION 下如果 slave 有開啟 log_slave_update 則必須同時設定 sync_binlog = 1 & innodb_flush_log_at_trx_commit = 1，具體說明請參考 BUG#92109

在 MySQL 8.0.28 之前 slave_preserve_commit_order = ON 有 BUG，可能會用完 commit order sequence tickets 導致 applier 掛起 (hang) 並且無期限的持續等待 commit order queue。

MySQL Bugs: #103636: Slave hangs with slave_preserve_commit_order On

傳統 POSITION 在開啟 MTS 且 slave_parallel_type = DATABASE 時，則必須設置 sync_relay_log = 1，因為沒有類似 slave_preserve_commit_order 的參數保證沒有 GAP 產生。

在 MySQL 8.0.19 之前 slave_preserve_commit_order = ON 必須要同時設置 log_slave_update，則無疑會進一步降低 replication 效率，因此更加不建議使用。

MySQL :: WL#7846: MTS: slave-preserve-commit-order when log-slave-updates/binlog is disabled

MySQL :: Preserving commit order on replicas with binary log disabled

參數解說

master_info_repository

該設定用來決定 connection metadata (也就是 IO thread 相關資訊) 如何保存。

FILE：保存到 master.info 檔案中，這表示沒有事務性。
TABLE：保存到 InnoDB TABLE，這表示有事務性。

當有多個 replication channel 時，此參數必須為 TABLE。

此參數在 5.7 預設為 FILE，在 8.0 預設為 TABLE，並在 8.0.23 棄用。

sync_master_info、sync_source_info

該參數用來指定每隔多少 Event 之後更新 connection metadata (也就是 IO thread metadata)。

當 master_info_repository = TABLE 時，表示每隔 N 個 event 之後更新 mysql.slave_master_info。
當 master_info_repository = FILE 時，表示每隔 N 個 event 之後寫入master.info 檔案並調用 fsync() 持久化到 disk。
當此參數設為 0 時，表示永遠不會更新 connection metadata。

此參數預設值為 10000。

sync_relay_log

此參數用來控制 relay log fsync() 到 disk 的策略。

當 sync_relay_log = 0，表示由 OS 控制決定何時將 relay log fsync() 到 disk 持久化。
當 sync_relay_log = N > 0，表示每當寫入 N 個 event 到 relay log 之後會將 relay log fsync() 到 disk 持久化。

此參數預設值為 10000。

relay_log_info_repository

該設定用來決定 applier metadata (也就是 SQL thread 相關資訊) 如何保存。

FILE：保存到 relay-log.info 檔案中，這表示沒有事務性。
TABLE：保存到 InnoDB TABLE，並且會和 SQL thread 應用的 Event 在同一個 Transaction 中，也就是說會一起 commit 或 rollback。

當未開啟 MTS 時，每次 Transaction commit 時都會更新 mysql.slave_relay_log_info。
當開啟 MTS 時，每次 Transaction commit 時都會更新 mysql.slave_worker_info，另外的 mysql.slave_relay_log_info 由 MTS checkpoint 負責。

當有多個 replication channel 時，此參數必須為 TABLE。

此參數在 5.7 預設為 FILE，在 8.0 預設為 TABLE，並在 8.0.23 棄用。

sync_relay_log_info

此參數根據 relay_log_info_repository 設置的不同有不同的效果：

當 relay_log_info_repository = TABLE 時，此參數的效果被忽略，因為 mysql.slave_relay_log_info、mysql.slave_worker_info 會跟隨 transaction 一起 commit
當 relay_log_info_repository = FILE 時：

當 sync_relay_log_info = 0，表示由 OS 控制決定何時將 relay-log.info fsync() 到 disk 持久化。
當 sync_relay_log_info = N > 0，表示每 N 個 transaction 將 relay-log.info fsync() 到 disk 持久化。

此參數預設值為 10000。

relay_log_recovery

當開啟 relay_log_recovery 時，slave 在啟動時會有以下行為：

創建一個新的 relay log
將 SQL thread 要執行的位置初始化到新的 relay log

也就是將記錄在 mysql.slave_relay_log_info 中的 relay_log_name 和 relay_log_pos 換成新的 relay log 和位置。

將 IO thread 要開始讀取 Master 的 binlog 位置切換到 SQL thread 最後應用的位置

也就是將記錄在 mysql.slave_relay_log_info 中的 master_log_name 和 master_log_pos 覆蓋 mysql.slave_master_info 的 master_log_name 和 master_log_pos 的值。

mysql.slave_relay_log_info 中的 master_log_name 和 master_log_pos：表示 SQL thread 最後一個已經應用的 Event 在 Master binlog 對應的位置。
mysql.slave_master_info 的 master_log_name 和 master_log_pos：表示 IO thread 最後一個從 Master 讀取的 Event 對應在 Master binlog 對應的位置。