MySQL是怎么保證主備一致的？

MySQL 主備的基本原理

1. 基本的主備切換流程
   

• 狀態 1：客戶端的讀寫都直接訪問節點 A，而節點 B 是 A 的備庫
• 狀態 2：切換時，讀寫訪問的都是節點 B，而節點 A 是 B 的備庫
• 注意：建議備庫只設置制度（readonly）模式
  • 雖然是只讀，但是因為 readonly 設置對超級 (super) 權限用戶是無效的，而用于同步更新的線程，就擁有超級權限

2. 節點 A 到節點 B 的內部流程
   

• 主庫接收到客戶端的更新請求后，執行內部事務的更新邏輯，同時寫 binlog
• 備庫 B 跟主庫 A 之間維持了一個長連接
• 完整流程
  • 備庫 B 上通過 change master 命令，設置主庫 A 的 IP、端口、用戶名、密碼，以及要從哪個位置開始請求 binlog，這個位置包含文件名和日志偏移量
  • 在備庫 B 上執行 start slave 命令，這時候備庫會啟動兩個線程，就是圖中的 io_thread 和 sql_thread。其中 io_thread 負責與主庫建立連接
  • 主庫 A 校驗完用戶名、密碼后，開始按照備庫 B 傳過來的位置，從本地讀取 binlog，發給 B
  • 備庫 B 拿到 binlog 后，寫到本地文件，稱為中轉日志（relay log）
  • sql_thread 讀取中轉日志，解析出日志里的命令，并執行

binlog 的三種格式對比

1. 目前有三種格式：statement、row、mixed
2. 要注意修改 binlog 格式為 statement，可以用過 show variables like ‘%binlog_format%’; 查看

• sql

# 使用后重啟 mysql
set global binlog_format='STATEMENT'

• docker
  • 首先配置一下 my.cnf

    [mysqld]
    server_id=1000
    binlog-ignore-db=mysql  
    log-bin=mall-mysql-bin  
    binlog_cache_size=1M  
    binlog_format=statement
    expire_logs_days=7  
    slave_skip_errors=1062
    

  • 運行 docker

    docker run -p 3305:3306 --name mysql-master --restart=always --privileged=true \
    -v /root/mysql-master/log:/var/log/mysql \
    -v /root/mysql-master/data:/var/lib/mysql \
    -v /root/mysql-master/conf:/etc/mysql \
    -v /etc/localtime:/etc/localtime:ro \
    -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.30
    

3. 首先創建一個數據表

CREATE TABLE `t` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `t_modified`(`t_modified`)) ENGINE=InnoDB;insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');

4. 執行一次刪除語句，查看 delete 語句在 binlog 是怎么記錄的

• 要注意執行前，如果是通過 mysql 客戶端 啟動的，要執行 mysql -c -root ***，否則下面的注釋行不會記錄在 binlog

delete from t /*comment*/  where a>=4 and t_modified<='2018-11-10' limit 1;

5. 當 binlog_format=statement 時，binlog 里面記錄的就是 SQL 語句的原文

• 首先要通過 show variables like ‘log_%’; 查看 log_bin 參數是否為 ON，否則是看不到日志
• 通過 show binary logs; 去查看有哪些 binlog 日志，一般最大的 File_size 會記錄剛剛執行的 sql

show binlog events in 'mall-mysql-bin.000004';

• 輸出結果解釋
  • 第一行：可先忽略
  • 第二、四行： BEGIN 與 COMMIT 對應，中間是事務
  • 第三行：真實執行語句

6. 其實，剛剛執行的 delete 語句，注意：在 statement 格式下，是 unsafe 的，因為會出現主從不一致的情況
   

• 不一致的例子
  • 如果 delete 語句使用的是索引 a，那么會根據索引 a 找到第一個滿足條件的行，也就是說刪除的是 a=4 這一行
  • 但如果使用的是索引 t_modified，那么刪除的就是 t_modified='2018-11-09’ 也就是 a=5 這一行

7. 修改 binlog_format='row'，再看看 binlog 實際內容

• 這里的 binlog 里沒有了 SQL 語句的原文，而是換成兩個 event
  • Table_map event： 要操作的表是 test 庫的表 t
  • Delete_rows event：用于定義刪除的行為
    

8. 實際上面的信息還是沒看到i昂西信息，需要借助 mysqlbinlog 工具

• 如果沒有的話，執行 yum install mysql，會有相關的工具下載下來

# 其中 2191 是上面從對應的位置開始的
# 我這邊沒執行成功，因為可能 8 的版本，5 的 mysqlbinlog 沒辦法解析
mysqlbinlog -vv binlog.000058 --start-position=2191

• 其中的信息如下：
  • server id 1：表示這個事務是在 server_id=1 的這個庫上執行的
  • CRC32：每個 event 都有 CRC32 的值，主要是 binlog_checksum 設置為 CRC32
  • Table_map event：實際 map 到數字應該是 93。如果有操作多個表，每個表會有對應的數字
  • @1=4、 @2=4…：實際就是對應刪除的行每一列的值
  • binlog_row_image 默認配置為 FULL，所以 Delete_event 包含了刪除行的所有字段的值，如果把 binlog_row_image 設置為 MINIMAL，則只會記錄必要的信息，在這個例子里，就是只會記錄 id=4 這個信息
  • 最后的 Xid event，用于表示事務被正確地提交了

9. 總結

• 當 binlog_format 使用 row 格式的時候，binlog 里面記錄了真實刪除行的主鍵 id，這樣 binlog 傳到備庫去的時候，就肯定會刪除 id=4 的行，不會有主備刪除不同行的問題

為什么會有 mixed 格式的 binlog

1. statement 格式的 binlog 可能會導致主備不一致，所以要使用 row 格式
2. row 格式的缺點是，很占空間。

• 用一個 delete 語句刪掉 10 萬行數據，用 statement 的話就是一個 SQL 語句被記錄到 binlog 中，占用幾十個字節的空間
• 用 row 格式的 binlog，就要把這 10 萬條記錄都寫到 binlog 中

3. MySQL 就取了個折中方案，也就是有了 mixed 格式的 binlog。MySQL 自己會判斷這條 SQL 語句是否可能引起主備不一致，如果有可能，就用 row 格式，否則就用 statement 格式
4. 越來越多的場景要求把 MySQL 的 binlog 格式設置成 row，最直接好處：恢復數據

• delete 語句：
  • row 格式的 binlog 保留了被刪掉的行的整行信息。可以將 delete 語句轉換成 insert 數據插入回去恢復
• insert 語句
  • row 格式下，insert 語句的 binlog 里會記錄所有的字段信息。可以將 insert 轉成 delete 語句，刪除誤插入的一行數據
• update 語句：
  • binlog 里面會記錄修改前整行的數據和修改后的整行數據。只需要把 event 的前后兩行信息對調一下，就可以去數據庫里面執行恢復更新操作

5. mixed 格式的 binlog 現在已經用得不多了
6. 關于時間戳的問題

• 首先把 binlog 格式設置為 mixed，然后執行下面語句

insert into t values(10,10, now());

• MySQL 會選擇使用 statement 格式。如果 binlog 過了 1 分鐘才傳給備庫的話，主備的數據不會造成不一致，原因為
• 當使用 mysqlbinlog 工具查看的時候，它會多一條 SET TIMESTAMP 命令
  

7. 總結

• binlog 來恢復數據的標準做法是，用 mysqlbinlog 工具解析出來，然后把解析結果整個發給 MySQL 執行。類似下面的命令
  • 命令意思：將 master.000001 文件里面從第 2738 字節到第 2973 字節中間這段內容解析出來，放到 MySQL 去執行

mysqlbinlog master.000001  --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

循環復制問題（雙 M 結構）

1. 業務邏輯

• 節點 A 上更新了一條語句，然后再把生成的 binlog 發給節點 B，節點 B 執行完這條更新語句后也會生成 binlog
  • 建議：log_slave_updates 設置為 on，表示備庫執行 relay log 后生成 binlog。可以讓更新事件在備庫上也記錄一份

2. 上面業務邏輯可能會出現循環復制問題，解決的方式

• 規定兩個庫的 server id 必須不同，如果相同，則它們之間不能設定為主備關系
• 一個備庫接到 binlog 并在重放的過程中，生成與原 binlog 的 server id 相同的新的 binlog
• 每個庫在收到從自己的主庫發過來的日志后，先判斷 server id，如果跟自己的相同，表示這個日志是自己生成的，就直接丟棄這個日志