MySQL 8 複製（六） ——拓撲與效能

一、複製拓撲

1. 一主一（多）從

2. 雙（多）主複製

4. 多源複製

5. Blackhole引擎與日誌伺服器

二、複製效能

1. 測試規劃

2. sync_binlog與innodb_flush_log_at_trx_commit

3. 組提交與多執行緒複製

4. 基於WriteSet的多執行緒複製

可以在任意個主從庫之間建立複雜的複製拓撲結構，如普通的一主一（多）從、雙（多）主複製、級聯複製，MySQL 5.7.2後新增的多源複製，特殊場景下使用的Blackhole引擎與日誌伺服器等等。複製中的MySQL伺服器須要遵循以下基本原則：

拓撲中的每個伺服器必須有一個唯一的server_id和server_uuid。

一個主庫可以有多個從庫（或者說一個從庫可以有多個兄弟從庫）。

如果開啟了log_slave_updates選項，一個從庫可以把其對應主庫上的資料變化傳播到它的從庫。

一、複製拓撲

1. 一主一（多）從

（1）一級主從

一主一從的基本配置是最簡單的拓撲結構，而一主多從的結構和基本配置差不多簡單，因為從庫之間根本沒有互動，它們僅僅是連線到同一個主庫。圖1顯示了這種結構。

MySQL 8 複製（六）——拓撲與效能

圖1

儘管這是非常簡單的拓撲結構，但它非常靈活，能滿足多種需求。為滿足資料一致性和主從切換需求，從庫原則上應為只讀，下面是從庫的一些用途：

為不同的角色使用不同的從庫，例如為滿足讀寫分離需求，在從庫新增不同於主庫的適合讀操作的索引（不要忘記在從庫執行任何寫操作前 set sql_log_bin=0）。

把一臺從庫只當做備用主庫，除了複製沒有其它資料傳輸。

將一臺從庫放到遠端資料中心，用作災難恢復。

延遲複製一個從庫，以備恢復使用者錯誤。

使用其中一個從庫，作為備份或測試使用。

這種結構流行原因是它避免了很多其它拓撲的複雜性。例如，可以方便地比較不同從庫重放的事件在主庫二進位制日誌中的位置，當然如果啟用GTID就更跟簡單了，支援自動定位。換句話說，如果在同一個邏輯點停止所有從庫的複製，它們正在讀取的是主庫上同一個日誌檔案的相同物理位置。這是個很好的特性，可以減輕管理員許多工作，例如把從庫提升為主庫。

這種特性只存在於兄弟從庫之間。在沒有直接的主從或者兄弟關係的伺服器上去比較日誌檔案的位置要複雜很多。例如樹形複製或分散式主庫，很難計算出複製事件的邏輯順序。

（2）級聯主從

如果正在將主庫複製到大量從庫中，不管是把資料分發到不同的地方，還是提供更高的讀效能，使用級聯複製都能更好地管理，如圖2所示。

MySQL 8 複製（六）——拓撲與效能

圖2

這種設計的好處是減輕了主庫的負擔，將讀負載分發到多個從庫。缺點是中間層出現的任何錯誤都會影響到多個伺服器。如果每個從庫和主庫直接相連就不會存在這樣的問題。同時中間層次越多，處理故障就會越複雜和困難。

2. 雙（多）主複製

（1）主動-主動模式下的雙主複製

雙主複製包含兩臺MySQL伺服器，每一個都被配置為對方的主庫和從庫，換句話說，它們是一對主庫。圖3顯示了該結構。

MySQL 8 複製（六）——拓撲與效能

圖3

主動-主動模式指的是兩臺伺服器對於應用均可讀寫，通常用於特殊目的。例如一個可能的應用場景是兩個處於不同地理位置的辦公室，並且都需要一份可寫的資料拷貝。這種配置最大的問題時如何解決衝突，兩個可寫的互主伺服器導致的問題非常多。這通常發生在兩臺伺服器同時修改一行記錄，或同時在兩臺伺服器上向一個包含auto_increment列的表裡插入資料。這些問題會經常發生，而且需要不易解決，因此並不推薦這種模式。下面演示兩個常見的問題。

在兩臺機器更新順序不同導致資料不一致或1032錯誤。

-- 主1

mysql> select * from t1;

+---+

| a |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

-- 主2

mysql> select * from t1;

+---+

| a |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

-- 主2延遲複製，模擬兩個主庫不同的執行順序

stop slave;

change master to master_delay = 10;

start slave;

-- 主1

set binlog_format='statement';

update t1 set a=a+1;

-- 主2在複製之前（10秒之內）執行

set binlog_format='statement';

update t1 set a=a*2;

-- 10秒之後查詢

-- 主1

mysql> select * from t1;

+------+

| a |

+------+

| 4 |

+------+

1 row in set (0.00 sec)

-- 主2

mysql> select * from t1;

+------+

| a |

+------+

| 3 |

+------+

1 row in set (0.00 sec)

複製沒有報告任何錯誤，但兩個庫的資料已經不一致。主1上先執行的a=a+1，緊接著由於複製執行a=a*2，最終結果是4。主2上由於延遲複製，先執行a=a*2，10秒後執行復制的a=a+1，最終結果是3。此實驗是在binlog_format='statement'下進行的，如果設定binlog_format='row'，則兩個庫（首先是主1，10秒後是主2）的都會報1032錯誤，show slave status中顯示的錯誤如下：

Last_SQL_Errno: 1032

Last_SQL_Error: Could not execute Update_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log binlog.000001, end_log_pos 2534

報1032的原因是應用複製時記錄已經發生改變，找不到更新時間點的資料行。

由auto_increment引起的1062錯誤。

-- 主1

use test;

create table t1(a int auto_increment primary key);

delimiter //

create procedure p1(a int)

begin

declare i int default 1;

while i<=a do

insert into t1(a) select null;

set i=i+1;

end while;

end;

//

delimiter ;

call p1(1000);

-- 主2，在主1執行過程期間同時在主2執行

call p1(1000);

show slave status中顯示如下錯誤：

Last_SQL_Errno: 1062

Last_SQL_Error: Could not execute Write_rows event on table test.t1; Duplicate entry '366' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binlog.000001, end_log_pos 101521

因為本機插入的資料與複製插入的資料產生衝突而報1062錯誤。通過在兩個伺服器設定不同的auto_increment_offset、auto_increment_increment，可以讓MySQL自動為insert語句選擇不互相沖突的值，稍微增加了點安全性。

-- 主1

set auto_increment_offset=1;

set auto_increment_increment=2;

call p1(1000);

-- 主2，在主1執行過程期間同時在主2執行

set auto_increment_offset=2;

set auto_increment_increment=2;

call p1(1000);

主1上插入單數，主2插入雙數，複製與本機資料不衝突。過程執行完後，兩個庫都插入了2000條資料，但預設配置 innodb_autoinc_lock_mode=2 會造成序列值不連續。

-- 主1

mysql> select count(*),min(a),max(a) from t1;

+----------+--------+--------+

| count(*) | min(a) | max(a) |

+----------+--------+--------+

| 2000 | 1 | 2414 |

+----------+--------+--------+

1 row in set (0.00 sec)

-- 主2

mysql> select count(*),min(a),max(a) from t1;

+----------+--------+--------+

| count(*) | min(a) | max(a) |

+----------+--------+--------+

| 2000 | 1 | 2414 |

+----------+--------+--------+

1 row in set (0.00 sec)

可以看到複製正常，兩個伺服器資料是一致。但這隻極端理想的場景：從空表開始插入資料，配置複製時沒有聯機訪問。如果在配置雙主複製時已經有資料，情況將複雜的多。同時允許向兩臺主庫寫入很危險，極易造成複製錯誤或資料不一致。資料不同步還僅僅是開始。當正常的複製發生錯誤停止了，但應用仍然在同時向兩臺伺服器寫入資料，這時候會發生什麼呢？不能簡單地把資料從一臺伺服器複製到另外一臺，因為這兩臺機器上需要複製的資料都可能發生了變化。解決這個問題將非常困難。總的來說，允許向兩個伺服器上同時寫入所帶來的麻煩遠遠大於其帶來的好處。只要作為從庫可寫，就存在主從資料不一致的風險。

（2）主動-被動模式下的雙主複製

這是前面描述的雙主結構的變體，主要區別在於其中的一臺伺服器是隻讀的被動伺服器。這種拓撲結構能夠避免之前討論的問題，也是構建容錯性和高可用性系統的強大方式。兩個伺服器從硬體到作業系統再到MySQL配置都應該完全相同。為便於故障轉移，只讀最好由客戶端應用保證，通過設定以下系統變數強制只讀僅作為可選項。

set global read_only=1;

set global super_read_only=1;

這種方式使得反覆切換主動和被動伺服器非常方便，因為伺服器的配置是對稱的。這使得故障轉移和故障恢復相對容易。它也允許使用者在不關閉伺服器的情況下執行維護、優化、升級作業系統（或者應用程式、硬體等）或其它任務。例如，執行alter table操作可能會鎖住整個表，阻塞對錶的讀寫，這可能會花費很長時間並導致服務中斷。

use test;

create table t1(a int auto_increment primary key);

insert into t1 select -1;

commit;

-- session 1

set autocommit=0;

insert into t1 select null;

-- session 2

alter table t1 add column (b int);

-- session 3

update t1 set a=-2 where a=-1;

-- session 4

show processlist;

+-----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------------+-----------------------------------+

+-----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------------+-----------------------------------+

+-----+-------------+-----------------+------+-------------+------+-----------------------------------------------------------------------------+-----------------------------------+

7 rows in set (0.00 sec)

可以看到，如果在執行alter table時，表上有未提交的事務，alter table 本身和其後的所有DML都會等待table metadata lock，而不論這些後續的DML操作的是哪些行，因為metadata lock是一個表級鎖。當session 1的事務提交或回滾，session 2才能得以執行。高併發場景下，線上DDL極有可能造成災難性的後果。一種暴力的解決方案是，先kill掉所有sleep的MySQL執行緒，緊接著執行alter table，這樣不會因為metadata lock而卡住後面的DML。在這個例子中，如果session 2可以先得到執行，即使操作需要很長時間，也不會對後面的DML造成等待。指令碼檔案的內容可能為：

#!/bin/bash

source ~/.bashrc

rm -rf /tmp/kill.sql

mysql -u root -p123456 -P3306 -h127.0.0.1 -e "select * into outfile '/tmp/kill.sql' from (select concat('kill ',id,';') from information_schema.processlist where command='sleep' union all select 'set sql_log_bin=0;' union all select 'alter table test.t1 add column (b int);') t;"

mysql -u root -p123456 -P3306 -h127.0.0.1 < /tmp/kill.sql

注意，將所有sleep的執行緒都殺掉這個操作會導致沒有提交的事務回滾，是有風險的，需要根據業務場景進行操作。主庫上大表的DDL操作可能引起從庫的複製延時變大。在不影響資料一致性的前提下（如drop、truncate等等），一種可能的解決方案是執行DDL前先set sql_log_bin=0，讓大的DDL操作不寫入binlog，從而不會複製到從庫，之後再在從庫手動執行一遍。

然而在主動-被動模式的雙主配置下，線上DDL變得更具可操作性。可以先停止主動伺服器上的複製執行緒，這樣就不會複製被動伺服器上執行的任何更新。然後在被動伺服器上執行alter table操作，交換角色，最後在先前的主動伺服器上啟動複製執行緒。這個伺服器將會讀取中繼日誌並執行相同的alter語句。這可能花費很長時間，但不要緊，因為該伺服器沒有為任何活躍查詢提供服務。假設A、B庫配置了雙主複製，A為主動庫提供服務，B為被動庫進行復制。如果需要在一個大表上增加欄位，可能的操作步驟如下：

A庫stop slave，此時A不會複製B的更新。

B庫執行 alter table，B此時仍然複製來自A的更新。

互動角色，B變為主動提供讀寫服務，A變為被動，這意味著應用連線需要切換到B。

A庫start slave，此時A將重放B上的alter table語句和其它更新。

整個過程不停庫，只需修改應用連線的資料庫即可。如果使用虛IP技術，則應用不需要做任何修改，原本可能導致服務中斷的DDL操作將對應用完全透明。下面的過程用於驗證第2步操作中，B上的alter table不會阻塞它對A的複製。

-- 1. A停止複製

stop slave;

-- 2. B上執行一個長時間的alter table操作

alter table t1 add column (b int);

-- 3. 在上一步執行過程中，A上操作同一個表

call p1(1000000);

-- 4. B確認複製狀態和執行緒狀態

show slave status\G

show processlist;

select max(a) from t1;

-- 5. 前面的步驟都執行完後，A開啟複製

start slave;

show slave status的Read_Master_Log_Pos和Exec_Master_Log_Pos不停改變，show processlist中的State沒有任何鎖，t1表的資料一直處於更新狀態，說明B對A的複製不會被其上的alter table阻塞。注意，如果在第2步執行前從庫被修改表上有未提交的事務（從主庫複製過來），依然會阻塞第2步執行。但情況要比在主庫上緩解很多，其一是因為從庫預設為單執行緒複製，沒有併發，事務應該很快被提交。其次是從庫可以設定成autocommit=on，這也會縮短alter table語句被阻塞的時間。調換以上步驟2和3的執行順序，可以驗證B對A的複製同樣也不會阻塞其上的alter table語句執行。上面的步驟並非無懈可擊，成立的前提是alter table與複製的語句相容，否則會導致複製錯誤。但通常來說都是先修改資料庫表結構，再升級應用程式，這樣看來此前提成立是自然而然的，問題並不大。下面的過程只是演示一種出錯的情況。

-- 1. A停止複製

stop slave;

-- 2. A上執行一個長時間的操作

call p1(1000000);

-- 3. 在上一步執行過程中，B上alter table同一個表

alter table t1 add column b int,drop column a;

-- 4. B確認複製狀態和執行緒狀態

show slave status\G

由於t1.a列被刪除，新增了一列b，而列a與列b的資料型別不相容，導致B庫上的複製報錯：

Last_Errno: 1677

Last_Error: Column 0 of table 'test.t1' cannot be converted from type 'bigint' to type 'int(11)'

執行下面的修復後複製繼續：

alter table t1 change b a bigint auto_increment primary key;

stop slave;

start slave;

讓我們看看主動伺服器上更新時會發生什麼事情。更新被記錄到二進位制日誌中，通過複製傳遞給被動伺服器的中繼日誌中。被動伺服器重放中繼日誌裡的查詢，如果開啟了log_slave_updates選項，它還會將複製事件記錄到自己的二進位制日誌中。由於複製事件的伺服器ID與主動伺服器相同，因此主動伺服器將忽略這些事件，通過這種方式避複製免死迴圈。設定主動-被動的雙主拓撲結構在某種意義上類似於建立一個熱備份，但可以使用這個“備份”來提高效能，例如，用它來執行讀操作、備份、輪換維護以及升級等。

（3）擁有從庫的雙主結構

另外一種相關的配置是為每個主庫增加一個從庫，如圖4所示。

MySQL 8 複製（六）——拓撲與效能

圖4

這種配置的優點是增加了冗餘，對於不同地理位置的複製拓撲，能夠消除站點單點失效的問題。也可以像平常一樣，將讀查詢分配到從庫上。如果在本地為了故障轉移使用雙主結構，這種配置同樣有用。當主庫失效時，有兩種不同的處理方式，一是用從庫代替主庫，二是把從庫指向另一個不同的主庫。以圖4為例，假設主庫1失效，採用第一種方式，需要將從庫1提升為新的主庫1，修改主庫2的複製配置，指向新的主庫1，並將新主庫指向主庫2，保持雙主配置。如果採用第二種方式，只需要將從庫1指向主庫2，但這樣拓撲已從雙主變為一主兩從。

（4）環形複製

如圖5所示，雙主結構實際上是環形結構的一種特例。環形結構可以有三個或更多的主庫。每個伺服器都是在它之前的伺服器的備庫，是在它之後的伺服器的主庫。這種結構也稱為環形複製（circular replication）。

MySQL 8 複製（六）——拓撲與效能

圖5

環形結構沒有雙主結構的一些優點，例如對稱配置和簡單的故障轉移，並且完全依賴於環上的每一個可用節點，這大大增加了整個系統失效的機率。如果從環中移除一個節點，這個節點發起的事件就會陷入無限迴圈：它將永遠繞著伺服器迴圈。因為唯一可以根據伺服器ID將其過濾的伺服器是建立這個事件的伺服器。下面的步驟可以模擬這種場景，M1、M2、M3構成的三個主庫的環形複製，M1複製M3、M3複製M2、M2複製M1。

-- 1. M1停止sql_thread執行緒

stop slave sql_thread;

-- 2. M2停止sql_thread執行緒

stop slave sql_thread;

-- 3. M3做更新

insert into test.t1 values (1);

commit;

-- 4. M3停庫

mysqladmin -uroot -p123456 shutdown

-- 5. M1啟動sql_thread執行緒，此時M3的更新複製到M1

start slave sql_thread;

-- 6. M1複製M2，此時原環形複製中移除了M3，其中master_log_file和master_log_pos從M2的show master status的輸出得到。

stop slave;

change master to

master_host = '172.16.1.126',

master_port = 3306,

master_user = 'repl',

master_password = '123456',

master_auto_position = 0,

master_log_file='binlog.000002',

master_log_pos=664210;

start slave;

-- 7. M2啟動sql_thread執行緒，此時M2複製了來自M3的更新，並繼續傳遞給M1，複製陷入死迴圈。在M1、M2上查詢test.t1，可以看到記錄不停增長。

start slave sql_thread;

如果三個主庫都啟用GTID複製，以上過程不會陷入死迴圈，因為複製不再通過server_id過濾本地事件，而是通過server_uuid複製事務。總的來說，環形結構非常脆弱，應該儘量避免。可以通過為每個節點增加從庫的方式來減少環形複製的風險，如圖6所示。但這僅僅防範了伺服器失效的風險，斷電或者其它一些影響到網路連線的問題都可能破壞整個環。

MySQL 8 複製（六）——拓撲與效能

圖6

4. 多源複製

MySQL 5.7.6開始支援多源複製（Multi-Source Replication）。多源複製不同於多主複製，多主複製指的是在複製拓撲中存在多個主庫，而多源複製是指一個從庫可以同時從多個主庫進行復制。圖7所示為兩主一從的多源複製結構。

MySQL 8 複製（六）——拓撲與效能

圖7

多源複製可用於將來自多個伺服器的資料合併或備份到單個伺服器，如合併表分片。應用事務時，多源複製不會檢測或解決任何衝突，如果需要，這些任務將留給應用程式實現。在多源複製拓撲中，從庫為每個接收其事務的主庫建立複製通道。

（1）複製通道

複製通道是一個字串，表示從主庫到從庫的複製路徑。為提供與先前版本的相容性，MySQL伺服器在啟動時自動建立一個預設通道，其名稱為空字串（""）。這個通道始終存在，不能被使用者建立或銷燬。如果沒有建立其它通道（具有非空名稱），則複製語句僅作用於預設通道，以便舊版從庫的所有複製語句按預期執行。多源複製中，從庫開啟多個命名通道，每個通道都有自己的中繼日誌和複製執行緒。一旦複製通道的I/O執行緒接收到事務，它們就會被新增到通道對應的中繼日誌檔案中並傳遞給SQL執行緒。這使得每個通道能夠獨立執行。複製通道還與主機名和埠關聯。可以將多個通道分配給主機名和埠的相同組合。在MySQL 8.0中，新增到一個從庫的最大通道數為256。每個複製通道獨立配置，必須具有唯一非空名稱。

（2）配置

多源複製拓撲至少需要配置兩個主庫和一個從庫。可以將多源複製中的主庫配置為使用基於全域性事務識別符號（GTID）的複製或基於二進位制日誌位置的複製。配置多源複製的步驟如下。

1. 將從庫的master_info_repository、relay_log_info_repository系統變數設定為TABLE。

stop slave;

set global master_info_repository = 'table';

set global relay_log_info_repository = 'table';

這是MySQL 8.0的預設值。多源複製拓撲中的從庫需要使用表儲存主庫二進位制日誌和本身中繼日誌的資訊，多源複製與基於檔案（file）的儲存庫不相容。現在不推薦將這兩個引數設定為'file'。

2. 將主庫新增到從庫

change master to

master_host = '172.16.1.125',

master_port = 3306,

master_user = 'repl',

master_password = '123456',

master_auto_position = 1

for channel 'master-125';

change master to

master_host = '172.16.1.126',

master_port = 3306,

master_user = 'repl',

master_password = '123456',

master_auto_position = 1

for channel 'master-126';

這裡使用GTID複製，設定兩主一從的多源複製。CHANGE MASTER TO語句通過使用FOR CHANNEL子句將新主庫新增到複製通道。多源複製與自動定位相容。

3. 啟動從庫複製

-- 啟動所有執行緒所有通道的複製

start slave;

-- 啟動所有通道的io_thread執行緒

start slave io_thread;

-- 啟動所有通道的sql_thread執行緒

start slave sql_thread; 啟動所有通道的sql_thread執行緒

-- 啟用單個通道

start slave for channel 'master_125';

start slave io_thread for channel 'master_125';

start slave sql_thread for channel 'master_125';

停止複製命令也啟動複製類似，只是把Start換成stop。同樣重置也可以選擇重置所有和重置單一通道：

reset slave;

reset slave for channel 'master_125';

（3）監控

監控可以使用performance_schema.replication*表，這些表的第一列都是Channel_Name。注意SHOW VARIABLES語句不適用於多個複製通道。這些變數的資訊已遷移到複製效能表。在具有多個通道的拓撲中使用SHOW VARIABLES語句僅顯示預設通道的狀態。

-- 查詢特定通道的連線狀態

mysql> select * from replication_connection_status where channel_name='master-125'\G

*************************** 1. row ***************************

CHANNEL_NAME: master-125

GROUP_NAME:

SOURCE_UUID: 8eed0f5b-6f9b-11e9-94a9-005056a57a4e

THREAD_ID: 10421

SERVICE_STATE: ON

COUNT_RECEIVED_HEARTBEATS: 41

LAST_HEARTBEAT_TIMESTAMP: 2019-06-24 16:21:31.583443

RECEIVED_TRANSACTION_SET:

LAST_ERROR_NUMBER: 0

LAST_ERROR_MESSAGE:

LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00.000000

LAST_QUEUED_TRANSACTION:

LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000

LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000

LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000

LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000

QUEUEING_TRANSACTION:

QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000

QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00.000000

QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00.000000

1 row in set (0.00 sec)

使用SHOW SLAVE STATUS FOR CHANNEL監控特定通道的狀態，如果不加FOR CHANNEL子句，則返回所有複製通道的狀態，每個通道一行。（4）簡單測試

-- 主庫1

mysql> insert into test.t1 values(125);

Query OK, 1 row affected (0.01 sec)

-- 主庫2

mysql> insert into test.t1 values(126);

Query OK, 1 row affected (0.01 sec)

-- 從庫

mysql> select * from test.t1;

+------+

| a |

+------+

| 1 |

| 125 |

| 126 |

+------+

3 rows in set (0.00 sec)

-- 主庫1

mysql> truncate table test.t1;

Query OK, 0 rows affected (0.01 sec)

-- 從庫

mysql> select * from test.t1;

Empty set (0.00 sec)

兩個主庫新增的資料都複製到從庫，但只在一個主庫清空表，從庫表所有資料全部被清空。因此使用多源複製要避免多個主庫具有同名的資料庫。

-- 主庫1

mysql> create user 'u1'@'%' identified by '123456';

Query OK, 0 rows affected (0.01 sec)

-- 主庫2

mysql> create user 'u1'@'%' identified by '123456';

Query OK, 0 rows affected (0.01 sec)

-- 從庫

mysql> show slave status\G

通道master-125複製狀態正常，但master-126報錯：

Last_SQL_Errno: 1396

Last_SQL_Error: Error 'Operation CREATE USER failed for 'u1'@'%'' on query. Default database: 'test'. Query: 'CREATE USER 'u1'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$*B_B^@}R;15egC4\nYdRPGtaEXbF.jB36e2UpAZEoXEPck87oeMl4j8rO6iu5''

建使用者的時候報告1396錯誤，原因是mysql庫中已經有了這個使用者。恢復複製的過程如下：

1. 停止從庫通道master-126的複製

stop slave for channel 'master-126';

2. 在從庫上確認出錯的事務ID

show slave status for channel 'master-126'\G

...

Retrieved_Gtid_Set: 53442434-8bfa-11e9-bc15-005056a50f77:1008-1009

Executed_Gtid_Set: 53442434-8bfa-11e9-bc15-005056a50f77:1-1008,

6a739bf0-961d-11e9-8dd8-005056a5497f:1-1885,

8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-24240

...

可以看到，從庫從53442434-8bfa-11e9-bc15-005056a50f77接收到事務1009，但只執行到1008，所以確定報錯的事務為：53442434-8bfa-11e9-bc15-005056a50f77:1009。

3. 在從庫上注入一個空事務跳過錯誤

set gtid_next='53442434-8bfa-11e9-bc15-005056a50f77:1009';

begin;commit;

set gtid_next=automatic;

start slave for channel 'master-126';

對於mysql庫，建議使用REPLICATE_IGNORE_DB將其遮蔽掉：

stop slave;

change replication filter replicate_ignore_db = (mysql);

start slave;

在主庫上對mysql庫進行操作時，需要加use mysql，否則不會進行過濾。

5. Blackhole引擎與日誌伺服器

（1）Blackhole儲存引擎與複製

MySQL 8中show engines命令返回儲存引擎如下：

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

像MyISAM、InnoDB一樣，BlackHole是另一種MySQL引擎。該引擎的功能可謂名副其實，任何寫入到此引擎的資料均會被丟棄掉，不做實際儲存，和Linux中的 /dev/null 檔案所起的作用類似。建立一個blackhole的表時，MySQL伺服器在資料庫目錄建立一個.frm表定義檔案，沒有其他檔案關聯到這個表。雖然blackhole表不儲存任何資料，但它卻能夠接收並重放二進位制日誌，如果開啟了log_slave_updates，它也能把複製向下傳播，如同普通的級聯複製拓撲一樣。

當從庫足夠多時，會對主庫造成很大的負載。每個從庫會在主庫上建立一個執行緒執行binlog dump命令。該命令讀取二進位制檔案中的資料並將其傳送給從庫。每個從庫都會重複這樣的工作，它們不會共享binlog dump的資源。如果從庫很多，並且有大的事件時，例如binlog_format為statement時一次很大的load data infile操作，主庫的負載會顯著上升，甚至可能由於從庫同時請求同樣的事件而耗盡記憶體並崩潰。另一方面，如果從庫請求的資料不在檔案系統的快取中，可能會導致大量的磁碟檢索，這同樣會影響主庫的效能並增加鎖的競爭。

因此，如果需要多個從庫，一個好辦法是從主庫移除負載並利用blackhole進行分發，即所謂的分發主庫。分發主庫實際上也是一個從庫，它唯一的目的就是提取和提供主庫的二進位制日誌。多個從庫連線到分發主庫，這使原來的主庫擺脫了負擔，如圖8所示。

MySQL 8 複製（六）——拓撲與效能

圖8

很難說當主庫資料達到多少時需要一個分發主庫。按照通用準則，如果主庫接近滿負載，不應該為其建立10個以上的從庫。如果只有少量寫操作，或者只複製其中一部分表，則主庫可以提供更多的複製。如果需要，可以使用多個分發主庫向大量從庫進行復制，或者使用級聯的分發主庫。對於跨資料中心的複製，設定slave_compressed_protocol能節約一些主庫頻寬。該變數是全域性系統變數，預設值為off，可以動態設定。

還可以通過分發主庫實現其它目的，如對二進位制日誌事件執行過濾和重放規則。這比在每個從庫上重複進行日誌記錄、重放和過濾要高效得多。使用blackhole儲存引擎可以支援更多的從庫。雖然會在分發主庫執行查詢，但代價極小，因為blackhole表中沒有任何資料。

一個常見的問題是如何確保分發伺服器上的每個表都是blackhole儲存引擎。如果在主庫建立了一個表並指定了不同的儲存引擎呢？確實，不管何時在從庫上使用不同的儲存引擎總會導致同樣的問題。通常的解決方案是設定伺服器的預設儲存引擎：

default_storage_engine=blackhole

這隻會影響那些沒有指定儲存引擎的create table的語句。如果有一個無法控制的應用，這種拓撲結構可能會非常脆弱。可以設定disabled_storage_engines禁用多個儲存引擎。該系統變數為只讀，只能通過配置檔案修改，並重啟MySQL伺服器使之生效。下面演示如何聯機搭建一個blackhole的分發主庫。

伺服器角色分配：

172.16.1.125：主庫。假設為生產主庫，可以在以下整個過程中存在負載。

172.16.1.126：blackhole分發主庫。一個初始化的MySQL伺服器。

172.16.1.127：從庫。

MySQL伺服器配置：

172.16.1.125：

[mysqld]

server_id=1125

gtid_mode=ON

enforce-gtid-consistency=true

172.16.1.126：

[mysqld]

server_id=1126

gtid_mode=ON

enforce-gtid-consistency=true

default_storage_engine=blackhole

default_tmp_storage_engine=blackhole

disabled_storage_engines='innodb'

secure_file_priv='/tmp'

172.16.1.127:

server_id=1127

gtid_mode=ON

enforce-gtid-consistency=true

其它配置使用MySQL 8的預設值。啟用GTID複製，三臺MySQL伺服器均已建立複製賬號。

（1）初始化blackhole分發主庫

在126執行執行內容如下的指令碼檔案init_blackhole.sh

source ~/.bashrc

# 全量匯入主庫，無資料

mysqldump --single-transaction --all-databases --host=172.16.1.125 -d --user=wxy --password=123456 | mysql -uroot -p123456

# 修改所有表的儲存引擎為blackhole

rm -rf /tmp/black.sql

mysql -uroot -p123456 -e "

select concat('alter table ', table_schema, '.', table_name, ' engine=''blackhole''', ';')

from information_schema.tables

where table_schema not in ('information_schema','mysql','performance_schema','sys')

and table_type='BASE TABLE' into outfile '/tmp/black.sql';"

# 在執行的SQL檔案第一行加入sql_log_bin=0，否則下級從庫也會執行

sed -i '1i\set sql_log_bin=0;' /tmp/black.sql

mysql -uroot -p123456 < /tmp/black.sql

（2）初始化從庫

因為是聯機配置複製，使用xtrabackup初始化從庫。

# 將主庫備份到從庫，在125執行

xtrabackup -uroot -p123456 --socket=/tmp/mysql.sock --no-lock --backup --compress --stream=xbstream --parallel=4 --target-dir=./ | ssh mysql@172.16.1.127 "xbstream -x -C /usr/local/mysql/data/ --decompress"

# 從庫執行應用日誌，在127執行

xtrabackup --prepare --target-dir=/usr/local/mysql/data/

# 啟動從庫，在127執行

mysqld_safe --defaults-file=/etc/my.cnf &

（3）啟動複製

-- 在126執行

change master to

master_host = '172.16.1.125',

master_port = 3306,

master_user = 'repl',

master_password = '123456',

master_auto_position = 1;

start slave;

show slave status\G

-- 在127執行

change master to

master_host = '172.16.1.126',

master_port = 3306,

master_user = 'repl',

master_password = '123456',

master_auto_position = 1;

start slave;

show slave status\G

至此完成了分發主庫的複製拓撲結構的搭建。

（2）日誌伺服器

使用MySQL複製的另一種用途是建立沒有資料的日誌伺服器。它唯一的目的就是更加容易重放或過濾二進位制日誌事件。假設有一組二進位制日誌或中繼日誌，可能從備份或者一臺崩潰的伺服器上獲取，希望能夠重放這些日誌中的事件。最容易想到的是通過mysqlbinlog命令列工具從其中提取出事件，但更加方便和高效得方法是配置一個沒有任何應用資料的MySQL例項並使其認為這些二進位制日誌是它擁有的。因為無須執行二進位制日誌，日誌伺服器也就不需要任何資料，它的目的僅僅是將複製事件提供給別的伺服器。

我們來看看該策略是如何工作的。假設日誌被命名為binlog.000001、binlog.000002等等，將這些日誌放到日誌伺服器的日誌資料夾中，假設為/usr/local/mysql/data。然後在啟動伺服器前編輯my.cnf檔案：

log_bin = /usr/local/mysql/data/binlog

log_bin_index = /usr/local/mysql/data/binlog.index

伺服器不會自動發現日誌檔案，因此還需要更新日誌的索引檔案。Linux上可用下面的命令完成。

/bin/ls -1 /usr/local/mysql/data/binlog.[0-9]* > /usr/local/mysql/data/binlog.index

確保執行MySQL的賬戶能夠讀寫日誌索引檔案。現在可以啟動日誌伺服器並通過show master logs命令來確保其找到日誌檔案。當主庫失效但二進位制日誌尚存，可以設定一個日誌伺服器，把從庫指向它，然後讓所有從庫趕上主庫的失效點。

相比於使用mysqlbinlog來實現恢復，日誌伺服器有優勢主要體現在：

速度快，因為無須將語句從日誌匯出來並傳給MySQL。

可以觀察到複製過程。

容易處理錯誤，如跳過執行失敗的語句。

便於過濾複製事件。

二、複製效能

我們可以將複製的時間分為兩部分：一是事件從主庫到從庫的傳輸時間，二是事件在從庫上的執行時間。事件在主庫上記錄二進位制日誌後到傳遞到從庫的時間理論上非常快，因為它只取決於網路速度。MySQL二進位制日誌的dump執行緒不是通過輪詢方式請求事件，而是由主庫來通知從庫新的事件，因為前者低效且緩慢。從主庫讀取一個二進位制日誌事件是一個阻塞型網路呼叫，當主庫記錄事件後，馬上就開始傳送。因此可以說，只要I/O執行緒被喚醒並且能夠通過網路傳輸資料，事件就會很快到達從庫。但如果網路很慢並且二進位制日誌事件很大，記錄二進位制日誌和在從庫上執行的延遲可能會非常明顯。如果查詢需要執行很長時間而網路很快，通常可以認為重放時間佔據了更多的複製時間開銷。

本節主要從日誌持久化、組提交與多執行緒複製，以及新增的WRITESET特性三個方面，討論對複製效能產生的影響。我們先簡要介紹每種特性的基礎知識，然後針對不同情況進行測試，最後由測試結果得出結論。所有測試均基於GTID的標準主從非同步複製。

1. 測試規劃

這裡使用的思路是：記錄主庫加壓前後的GTID，得到從庫需要執行的事務數。然後在從庫上執行復制，記錄執行時間，得到從庫的每秒執行事務數（TPS）作為衡量複製效能的指標。測試目的在於對比不同情況下複製的效能，而不是針對測量絕對值進行優化。主庫加壓使用tpcc-mysql基準測試工具。

（1）測試環境

測試環境如下，已經配置好GTID非同步複製。

主庫：172.16.1.125

從庫：172.16.1.126

MySQL版本：8.0.16

測試通用引數：

主庫：

server_id=1125

gtid_mode=ON

enforce-gtid-consistency=true

innodb_buffer_pool_size=4G

從庫：

server_id=1126

gtid_mode=ON

enforce-gtid-consistency=true

innodb_buffer_pool_size=4G

（2）tpcc-mysql測試前準備

TPC-C是專門針對聯機交易處理系統（OLTP系統）的規範，tpcc-mysql則是percona公司基於TPC-C衍生出來的產品，專用於MySQL基準測試，下載地址為https://github.com/Percona-Lab/tpcc-mysql。這裡使用tpcc-mysql只是為了給主庫加壓。使用tpcc-mysql開始測試前完成以下準備工作，所有步驟均在主庫上執行：

1. 安裝

cd tpcc-mysql-master/src

make

2. 建立測試庫

mysql -uroot -p123456 -e "create database tpcc_test;"

3. 建表和索引

cd tpcc-mysql-master

mysql -uroot -p123456 -Dtpcc_test < create_table.sql

mysql -uroot -p123456 -Dtpcc_test < add_fkey_idx.sql

4. 生成資料

tpcc_load -h127.0.0.1 -d tpcc_test -u root -p "123456" -w 10

-w引數指定建立的倉庫數。

5. 備份測試庫

mysqldump --databases tpcc_test -uroot -p123456 --set-gtid-purged=off > tpcc_test.sql

為在同等環境下進行比較，每次測試前都要重新生成測試庫中的表、索引和資料，因此這裡做一個測試庫的邏輯備份。一定要加--set-gtid-purged=off，因為將備份匯入主庫時，需要在從庫通過複製同時生成。下面是每次測試在從庫執行的自動化指令碼：

# 初始化tpcc資料

mysql -uwxy -p123456 -h172.16.1.125 < tpcc_test.sql

# 讀取主庫的二進位制座標

read master_file master_pos < <(mysql -uwxy -p123456 -h172.16.1.125 -e "show master status;" --skip-column-names | awk '{print $1,$2}')

# 從庫初始化tcpp資料結束後停止複製

mysql -uwxy -p123456 -e "select master_pos_wait('$master_file',$master_pos);stop slave;"

# 取得從庫開始GTID

read start_gtid < <(mysql -uwxy -p123456 -e "show variables like 'gtid_executed';" --skip-column-names | awk '{print $2}' | sed "s/\\\n//g")

# 主庫執行壓測，10個倉庫，32個併發執行緒，預熱1分鐘，壓測5分鐘

tpcc_start -h172.16.1.125 -d tpcc_test -u wxy -p "123456" -w 10 -c 32 -r 60 -l 300 > tpcc_test.log 2>&1

# 讀取主庫的二進位制座標

read master_file master_pos < <(mysql -uwxy -p123456 -h172.16.1.125 -e "show master status;" --skip-column-names | awk '{print $1,$2}')

# 從庫複製開始時間

start_time=`date '+%s'`

# 從庫執行復制

mysql -uwxy -p123456 -e "start slave;select master_pos_wait('$master_file',$master_pos);"

# 從庫複製結束時間

end_time=`date '+%s'`

# 複製執行時長

elapsed=$(($end_time - $start_time))

# 取得從庫結束GTID

read end_gtid < <(mysql -uwxy -p123456 -e "show variables like 'gtid_executed';" --skip-column-names | awk '{print $2}' | sed "s/\\\n//g")

# 取得從庫執行的事務數

read start end < <(mysql -uwxy -p123456 -e "select gtid_subtract('$end_gtid','$start_gtid');" --skip-column-names | awk -F: '{print $2}' | awk -F- '{print $1,$2}')

trx=$(($end - $start + 1))

# 計算從庫、主庫的TPS

Slave_TPS=`expr $trx / $elapsed`

Master_TPS=`expr $trx / 360`

# 列印輸出

echo "TRX: $trx" "Elapsed: $elapsed" "Slave TPS: $Slave_TPS" "Master TPS: $Master_TPS"

2. sync_binlog與innodb_flush_log_at_trx_commit

sync_binlog控制MySQL伺服器將二進位制日誌同步到磁碟的頻率，可取值0、1、N，MySQL 8的預設值為1。innodb_flush_log_at_trx_commit控制提交時是否將innodb日誌同步到磁碟，可取值0、1、2，MySQL 8的預設值為1。關於這兩個引數已經在“MySQL 8 複製（一）——非同步複製”中詳細討論，這裡不再贅述。簡單說，對於複製來講，sync_binlog為0可能造成從庫丟失事務，innodb_flush_log_at_trx_commit為0可能造成從庫比主庫事務多。而從效能角度看，雙1的效能最差，雙0的效能最好。權衡資料安全與效能，一般建議主庫都設定為雙1，根據場景從庫可以設定成其它組合來提升效能。

下表所示為從庫上sync_binlog、innodb_flush_log_at_trx_commit四種設定的測試結果：

測試中主庫執行了一共360秒（預熱+壓測），TPS為510。從表中可以明顯看到這兩個引數的不同組合對複製效能的影響。當從庫僅為單執行緒複製時，只有雙0的設定在執行時間和TPS上優於主庫，其它組合會造成複製延遲。

3. 組提交與多執行緒複製

MySQL 5.6支援多執行緒複製（multi-threaded slave，MTS），但太過侷限。它只實現了基於schema的多執行緒複製，使不同資料庫下的DML操作可以在從庫並行重放，這樣設計的複製效率並不高。如果使用者例項僅有一個庫，那麼就無法實現並行重放，甚至效能會比原來的單執行緒更差，而單庫多表是比多庫多表更為常見的一種情形。

MySQL 5.7的多執行緒複製基於組提交實現，不再有基於schema的多執行緒複製限制。

（1）組提交

從MySQL 5.6開始同時支援Innodb redo log和binlog組提交，並且預設開啟，大大提高了MySQL的事務處理效能。和很多RDBMS一樣，MySQL為了保證事務處理的一致性和永續性，使用了WAL（Write Ahead Log）機制，即對資料檔案進行修改前，必須將修改先記錄日誌。Redo log就是一種WAL的應用，每次事務提交時，不用同步重新整理磁碟資料檔案，只需要同步重新整理redo log就夠了。相比寫資料檔案時的隨機I/O，寫Redo log時的順序I/O能夠提高事務提交速度。Redo log的刷盤操作將會是最終影響MySQL TPS的瓶頸所在。為了緩解這一問題的影響，MySQL使用了redo log組提交，將多個redo log刷盤操作合併成一個。

為了保證redo log和binlog的資料一致性，MySQL使用了兩階段提交（prepare階段和commit階段），由binlog作為事務的協調者。而引入兩階段提交使得binlog又成為了效能瓶頸，於是MySQL 5.6增加了binlog的組提交，目的同樣是將binlog的多個刷盤操作合併成一個。結合redo log本身已經實現的組提交，將提交過程分成Flush stage、Sync stage、Commit stage三個階段完成組提交，最大化每次刷盤的收益，弱化磁碟瓶頸。每個階段都有各自的佇列，使每個會話的事務進行排隊，提高併發效能。

Flush階段：

首先獲取佇列中的事務組，將redo log中prepare階段的資料刷盤。

將binlog資料寫入檔案系統緩衝，並不能保證資料庫崩潰時binlog不丟失。

Flush階段佇列的作用是提供了redo log的組提交。

如果在這一步完成後資料庫崩潰，由於協調者binlog中不保證有該組事務的記錄，所以MySQL可能會在重啟後回滾該組事務。

Sync階段：

將binlog快取sync到磁碟，sync_binlog=1時該佇列中所有事務的binlog將永久寫入磁碟。

為了增加一組事務中的事務數量，提高刷盤收益，MySQL使用兩個引數控制獲取佇列事務組的時機：

binlog_group_commit_sync_delay=N：在等待N微秒後，開始事務刷盤。

binlog_group_commit_sync_no_delay_count=N：如果佇列中的事務數達到N個，就忽視binlog_group_commit_sync_delay的設定，直接開始刷盤。

Sync階段佇列的作用是支援binlog的組提交。

如果在這一步完成後資料庫崩潰，由於協調者binlog中已經有了事務記錄，MySQL會在重啟後通過Flush階段中Redo log刷盤的資料繼續進行事務的提交。

Commit階段：

首先獲取佇列中的事務組。

依次將redo log中已經prepare的事務在儲存引擎層提交，清除回滾資訊，向redo log中寫入COMMIT標記。

Commit階段不用刷盤，如上所述，Flush階段中的redo log刷盤已經足夠保證資料庫崩潰時的資料安全了。

Commit階段佇列的作用是承接Sync階段的事務，完成最後的引擎提交，使得Sync可以儘早的處理下一組事務，最大化組提交的效率。

Commit階段會受到引數binlog_order_commits的影響，當該引數為OFF時，不保證binlog和事務提交的順序一致，因為此時允許多個執行緒發出事務提交指令。也正是基於同樣的原因，可以防止逐個事務提交成為吞吐量瓶頸，效能會有少許提升。多數情況下，儲存引擎的提交指令與binlog不同序無關緊要，因為多個單獨事務中執行的操作，無論提交順序如何都應該產生一致的結果。但也不是絕對的，例如會影響XtraBackup工具的備份。XtraBackup會從innodb page中獲取最後提交事務的binlog位置資訊，binlog_order_commits=0時事務提交順序和binlog順序可能不一致，這樣此位置前可能存在部分prepare狀態的事務，這些事務在備份恢復後會因回滾而丟失。

binlog_order_commits的預設值為ON，此時儲存引擎的事務提交指令將在單個執行緒上序列化，以致事務始終以與寫入二進位制日誌相同的順序提交。

這裡有一篇MySQL組提交的圖解說明：[圖解MySQL]MySQL組提交(group commit)。

（2）多執行緒複製

MySQL 5.6開始出現基於schema的多執行緒複製，簡單說就是主庫上不同資料庫上的DML可以在從庫上並行重放。因為大多數生產環境依然習慣於單庫多表的架構，這種情況下MTS依然還是單執行緒的效果。MySQL 5.7實現了基於組提交的多執行緒複製，其思想簡單易懂：主庫上同一個組提交的事務可以在從庫並行重放，因為這些事務之間沒有任何衝突，由儲存引擎的ACID所保證。為了與5.6版本相容，5.7引入了新的變數slave_parallel_type，可以配置為下面兩個值之一：

DATABASE：預設值，基於schema的多執行緒複製方式。

LOGICAL_CLOCK：基於組提交的多執行緒複製方式。

那麼從庫如何知道事務是否在一組中呢？MySQL 5.7的設計方式是將組提交資訊存放在二進位制日誌的GTID_EVENT中。

[mysql@hdp2/usr/local/mysql/data]$mysqlbinlog binlog.000064 | grep last_committed | awk '{print $11, $12}' | head -10

last_committed=0 sequence_number=1

last_committed=0 sequence_number=2

last_committed=0 sequence_number=3

last_committed=0 sequence_number=4

last_committed=0 sequence_number=5

last_committed=0 sequence_number=6

last_committed=0 sequence_number=7

last_committed=0 sequence_number=8

last_committed=0 sequence_number=9

last_committed=0 sequence_number=10

[mysql@hdp2/usr/local/mysql/data]$

last_committed表示事務提交的時候，上次事務提交的編號。事務在perpare階段獲取相同的last_committed而且相互不影響，最終會作為一組進行提交。如果事務具有相同的last_committed，表示這些事務都在一組內，可以進行並行重放。例如上述last_committed為0的10個事務在從庫是可以進行並行重放的。這個機制是Commit-Parent-Based Scheme的實現方式。

sequence_number是事務計數器。記錄在GTID_EVENT中的sequence_number和last_committed使用的是相對當前二進位制日誌檔案的值。即每個二進位制日誌檔案中事務的last_commited起始值為0，sequence_number為1。由於二進位制日誌檔案切換時，需要等待上一個檔案的事務執行完，所以這裡記錄相對值並不會導致衝突事務並行執行。

由於在MySQL中寫入是基於鎖的併發控制，所以所有在主庫同時處於prepare階段且未提交的事務就不會存在鎖衝突，從庫就可以並行執行。Commit-Parent-Based Scheme使用的就是這個原理，簡單描述如下：

主庫上有一個全域性計數器（global counter）。每一次儲存引擎提交之前，計數器值就會增加。

主庫上，事務進入prepare階段之前，全域性計數器的當前值會被儲存在事務中，這個值稱為此事務的commit-parent。

主庫上，commit-parent會在事務的開頭被儲存在binlog中。

從庫上，如果兩個事務有同一個commit-parent，它們就可以並行被執行。

此commit-parent就是在binlog中看到的last_committed。如果commit-parent相同，即last_committed相同，則被視為同一組，可以並行重放。

Commit-Parent-Based Scheme的問題在於會降低複製的並行程度，如圖9所示（引自WL#7165）。

MySQL 8 複製（六）——拓撲與效能

圖9

每一個水平線代表一個事務,時間從左到右。P表示事務在進入prepare階段之前讀到的commit-parent值的那個時間點，可以簡單視為加鎖時間點。C表示事務增加了全域性計數器值的那個時間點，可以簡單視為釋放鎖的時間點。P對應的commit-parent是取自所有已經執行完的事務的最大的C對應的sequence_number，舉例來說：Trx4的P對應的commit-parent是Trx1的C對應的sequence_number。因為這個時候Trx1已經執行完，但是Trx2還未執行完。Trx5的P對應的commit-parent是Trx2的C對應的sequence_number。Trx6的P對應的commit-parent是Trx2的C對應的sequence_number。

Trx5和Trx6具有相同的commit-parent，在進行重放的時候，Trx5和Trx6可以並行執行。Trx4和Trx5不能並行執行，Trx6和Trx7也不能並行執行，因為它們的commit-parent不同。但注意到，在同一時段，Trx4和Trx5、Trx6和Trx7分別持有它們各自的鎖，事務互不衝突，所以在從庫上並行執行是不會有問題的。針對這種情況，為了進一步增加並行度，MySQL對並行複製的機制做了改進，提出了一種新的並行複製的方式：Lock-Based Scheme，使同時持有各自鎖的事務可以在從庫並行執行。

Lock-Based Scheme定義了一個稱為lock interval的概念，表示一個事務持有鎖的時間間隔。假設有兩個事務Trx1、Trx2，Trx1先於Trx2。那麼，當且僅當Trx1、Trx2的lock interval有重疊，則可以並行執行。換言之，若Trx1結束自己的lock interval早於Trx2開始自己的lock interval，則不能並行執行。如圖10所示，L表示lock interval的開始點，C表示lock interval的結束。

MySQL 8 複製（六）——拓撲與效能

圖10

對於C（lock interval的結束點），MySQL會給每個事務分配一個邏輯時間戳（logical timestamp），命名為transaction.sequence_number。此外，MySQL會獲取全域性變數global.max_committed_transaction，表示所有已經結束lock interval的事務的最大的sequence_number。對於L（lock interval的開始點），MySQL會把global.max_committed_transaction分配給一個變數，並取名叫transaction.last_committed。transaction.sequence_number和transaction.last_committed這兩個時間戳都會存放在binlog中，就是前面看到的last_committed和sequence_number。

根據以上分析得出，只要事務和當前執行事務的Lock Interval都存在重疊，就可以在從庫並行執行。圖9中，Trx3、Trx4、Trx5、Trx6四個事務可以並行執行，因為Trx3的sequence_number大於Trx4、Trx5、Trx6的last_committed，即它們的Lock Interval存在重疊。當Trx3、Trx4、Trx5執行完成之後，Trx6和Trx7可以併發執行，因為Trx6的sequence_number大於Trx7的last_committed，即兩者的lock interval存在重疊。Trx5和Trx7不能併發執行，因為Trx5的sequence_number小於Trx7的last_committed，即兩者的lock interval不存在重疊。

可以通過以下命令粗略檢視併發度：

[mysql@hdp2/usr/local/mysql/data]$mysqlbinlog binlog.000064 | grep -o 'last_committed.*' | sed 's/=/ /g' | awk '{print $4-$2-1}' | sort -g | uniq -c

1693 0

4795 1

8174 2

11378 3

13879 4

15407 5

15979 6

15300 7

13762 8

11471 9

9061 10

6625 11

4533 12

3006 13

1778 14

1021 15

521 16

243 17

135 18

61 19

31 20

23 21

18 22

7 23

5 24

7 25

3 26

3 27

6 28

1 29

1 30

2 31

1 32

3 33

3 34

1 37

1 39

1 40

1 42

1 44

1 46

1 49

1 50

1 56

1 120

第一列為事務數量，第二列表示這些事務能與它們之前的多少個事務並行執行。例如有1693個事務不能與之前的事務併發，必須等到所有前面的事務完成之後才能開始，但並不表示不能和後面的事務並行執行。當前事務無法判斷能否和後面的事務並行執行，只能與前面事務的sequence_number比較，得出自己是否可以併發執行。

僅僅設定為LOGICAL_CLOCK還會存在問題，因為此時在從庫上應用事務是無序的，和relay log中記錄的事務順序可能不一樣。在這種情況下，從庫的GTID會產生間隙，事務可能在某個時刻主從是不一致的，但是最終會一致，滿足最終一致性。相同記錄的修改，會按照順序執行，這由事務隔離級保證。不同記錄的修改，可以產生並行，並無資料一致性風險。這大概也是slave_preserve_commit_order引數預設為0的原因之一。

如果要保證事務是按照relay log中記錄的順序來重放，需要設定引數slave_preserve_commit_order=1，這要求從庫開啟log_bin和log_slave_updates，並且slave_parallel_type設定為LOGICAL_CLOCK。

啟用slave_preserve_commit_order後，正在執行的worker執行緒將等待，直到所有先前的事務提交後再提交。當複製執行緒正在等待其它worker執行緒提交其事務時，它會將其狀態報告為等待提交前一個事務。使用此模式，多執行緒複製的重放順序與主庫的提交順序保持一致。

slave_parallel_workers引數控制並行複製worker執行緒的數量。若將slave_parallel_workers設定為0，則退化為單執行緒複製。如果slave_parallel_workers=N（N>0），則單執行緒複製中的SQL執行緒將轉為1個coordinator執行緒和N個worker執行緒，coordinator執行緒負責選擇worker執行緒執行事務的二進位制日誌。例如將slave_parallel_workers設定為1，則SQL執行緒轉化為1個coordinator執行緒和1個worker執行緒，也是單執行緒複製。然而，與slave_parallel_workers=0相比，多了一次coordinator執行緒的轉發，因此slave_parallel_workers=1的效能反而比0還要差。MySQL 8中slave_parallel_workers引數可以動態設定，但需要重啟複製才能生效。

LOGICAL_CLOCK多執行緒複製為了準確性和實現的需要，其lock interval實際獲得的區間比理論值窄，會導致原本一些可以併發行行的事務在從庫上沒有並行執行。當使用級聯複製時，LOGICAL_CLOCK可能會使離主庫越遠的從庫並行度越小。

（3）多執行緒複製測試

從庫增加以下配置引數：

sync_binlog = 1

innodb_flush_log_at_trx_commit = 1

slave_preserve_commit_order = 1

slave_parallel_type = LOGICAL_CLOCK

下表所示為從庫上slave_parallel_workers分別設定為2、4、8、16的測試結果：

測試中主庫執行了一共360秒（預熱+壓測），TPS為509。從表中可以看到，在實驗負載場景下，多執行緒複製效能明顯高於單執行緒複製。slave_parallel_workers=8時效能最好，當worker數量增加到16時，效能反而比8時差。太多執行緒會增加執行緒間同步的開銷，因此slave_parallel_workers值並非越大越好，需要根據實際負載進行測試來確定其最佳值，通常建議建議4-8個worker執行緒。

4. 基於WriteSet的多執行緒複製

基於組提交LOGICAL_CLOCK多執行緒複製機制在每組提交事務足夠多，即業務量足夠大時表現較好。但很多實際業務中，雖然事務沒有Lock Interval重疊，但這些事務操作的往往是不同的資料行，也不會有鎖衝突，是可以並行執行的，但LOGICAL_CLOCK的實現無法使這部分事務得到並行重放。為了解決這個問題，MySQL在5.7.22版本推出了基於WriteSet的並行複製。簡單來說，WriteSet並行複製的思想是：不同事務的記錄不重疊，則都可在從庫上並行重放。可以看到並行的力度從組提交細化為記錄級。

（1）WriteSet物件

MySQL中用WriteSet物件來記錄每行記錄，從原始碼來看WriteSet就是每條記錄hash後的值（必須開啟ROW格式的二進位制日誌），具體演算法如下：

上述公式中的index_name只記錄唯一索引，主鍵也是唯一索引。如果有多個唯一索引，則每條記錄會產生對應多個WriteSet值。另外，value這裡會分別計算原始值和帶有字符集排序規則（Collation）值的兩種WriteSet。所以一條記錄可能有多個WriteSet物件。

新產生的WriteSet物件會插入到WriteSet雜湊表，雜湊表的大小由引數binlog_transaction_dependency_history_size設定，預設25000。記憶體中保留的雜湊行數達到此值後，將清除歷史記錄。

（2）實現原理

基於WriteSet的複製優化了主庫組提交的實現，主要體現主庫端last_committed的定義變了。原來一組事務是指擁有同一個parent_commit的事務，在二進位制日誌中記錄為同一個last_committed。基於WriteSet的方式中，last_committed的含義是保證衝突事務（更新相同記錄的事務）不能擁有同樣的last_committed值，事務執行的並行度進一步提高。

當事務每次提交時，會計算修改的每個行記錄的WriteSet值，然後查詢雜湊表中是否已經存在有同樣的WriteSet，若無，WriteSet插入到雜湊表，寫入二進位制日誌的last_committed值不變。上一個事務跟當前事務的last_committed相等，意味著它們可以最為一組提交。若有，更新雜湊表對應的WriteSet值為sequence_number，並且寫入到二進位制日誌的last_committed值也更新為sequnce_number。上一個事務跟當前事務的last_committed必然不同，表示事務衝突，必須等待之前的事務提交後才能執行。

從庫端的邏輯跟以前一樣沒有變化，last_committed相同的事務可以並行執行。

要使用WriteSet方式組提交，需要設定binlog_transaction_dependency_tracking引數為WRITESET。binlog_transaction_dependency_tracking引數指定主庫確定哪些事務可以作為一組提交的方法，有三個可選值：

COMMIT_ORDER：依賴事務提交的邏輯時間戳，是預設值。如果事務更新的表上沒有主鍵和唯一索引，也使用該值。這是MySQL 5.7所使用使用的方式。

WRITESET：更新不同記錄的事務（不衝突）都可以並行化。

WRITESET_SESSION：與WRITESET的區別是WRITESET_SESSION需要保證同一個會話內的事務的先後順序。消除了從庫中某一時刻可能看到主庫從未出現過的資料庫狀態的問題。

從下面這個簡單的實驗可以直觀看到COMMIT_ORDER與WRITESET的區別。

drop table if exists t1;

create table t1 (a int primary key);

insert into t1 values (1), (2);

flush logs;

set global binlog_transaction_dependency_tracking = WRITESET;

update t1 set a=10 where a=1;

update t1 set a=20 where a=2;

set global binlog_transaction_dependency_tracking = COMMIT_ORDER;

update t1 set a=1 where a=10;

update t1 set a=2 where a=20;

檢視二進位制日誌：

[mysql@hdp2/usr/local/mysql/data]$mysqlbinlog binlog.000002 --base64-output=decode-rows -v | grep -e 'last_committed' -A4 -e 'UPDATE' | grep -v "# original\|# immediate\|/*!" | awk '{if ($1!="###") {print $11, $12} else {print $0}}'

last_committed=0 sequence_number=1

### UPDATE `test`.`t1`

### WHERE

### @1=1

### SET

### @1=10

last_committed=0 sequence_number=2

### UPDATE `test`.`t1`

### WHERE

### @1=2

### SET

### @1=20

last_committed=2 sequence_number=3

### UPDATE `test`.`t1`

### WHERE

### @1=10

### SET

### @1=1

last_committed=3 sequence_number=4

### UPDATE `test`.`t1`

### WHERE

### @1=20

### SET

### @1=2

[mysql@hdp2/usr/local/mysql/data]$

第一、二個事務的last_committed都是0。雖然這兩個事務的lock_interval沒有重疊，但它們修改的是不同的資料行，不存在事務衝突，因此它們的last_committed相同，可以作為一組並行提交。

當設定global binlog_transaction_dependency_tracking為COMMIT_ORDER時，第三、四個事務的last_committed分別為2和3。這兩個事務的lock_interval沒有重疊，即使更新的行不衝突，它們的last_committed也不相同，不能作為同一組並行提交。

與WriteSet相關的另一個引數是transaction_write_set_extraction。該引數定義計算WriteSet使用的雜湊演算法。如果用於多執行緒複製，必須將此變數設定為XXHASH64，這也是預設值。如果設定為OFF，則binlog_transaction_dependency_tracking只能設定為COMMIT_ORDER。如果binlog_transaction_dependency_tracking的當前值為WRITESET或WRITESET_SESSION，則無法更改transaction_write_set_extraction的值。

（3）WriteSet多執行緒複製測試

主庫增加以下配置引數：

binlog_transaction_dependency_tracking = WRITESET

transaction_write_set_extraction = XXHASH64

從庫增加以下配置引數：

sync_binlog = 1

innodb_flush_log_at_trx_commit = 1

slave_preserve_commit_order = 1

slave_parallel_type = LOGICAL_CLOCK

下表所示為從庫上slave_parallel_workers分別設定為2、4、8、16、32的測試結果：

測試中主庫執行了一共360秒（預熱+壓測），TPS平均為572，同等場景下的比COMMIT_ORDER高出12%。當16個複製執行緒時從庫TPS達到峰值619，比COMMIT_ORDER下效能最好的8複製執行緒高出13%。

MySQL的複製延遲是一直被詬病的問題之一，從以上三組測試得出了目前解決延遲最普遍的三種方法：

如果負載和資料一致性要求都不是太高，可以採用單執行緒複製 + 安全引數雙0。這種模式同樣擁有不錯的表現，一般壓力均可應付。

如果主庫的併發量很高，那麼基於order-commit的模式的多執行緒複製可以有很好的表現。

基於WriteSet的模式是目前併發度最高的多執行緒複製，基本可以滿足大部分場景。如果併發量非常高，或是要求從庫與主庫的延遲降至最低，可以採取這種方式。

————————————————

版權宣告：本文為CSDN博主「wzy0623」的原創文章，遵循 CC 4.0 BY-SA 版權協議，轉載請附上原文出處連結及本宣告。

原文連結：https://blog.csdn.net/wzy0623/article/details/94614149

About Me

........................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub、部落格園、CSDN和個人微 信公眾號（
xiaomaimiaolhr）上有同步更新

● 本文itpub地址：
http://blog.itpub.net/26736162 

● 本文部落格園地址：
http://www.cnblogs.com/lhrbest 

● 本文CSDN地址：
https://blog.csdn.net/lihuarongaini 

● 本文pdf版、個人簡介及小麥苗雲盤地址：
http://blog.itpub.net/26736162/viewspace-1624453/ 

● 資料庫筆試面試題庫及解答：
http://blog.itpub.net/26736162/viewspace-2134706/ 

● DBA寶典今日頭條號地址：
http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 

........................................................................................................................

● QQ群號：
230161599
、618766405

● 微 信群：可加我微 信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友
（
646634621
），註明新增緣由

● 於 2020-02-01 06:00 ~ 2020-02-31 24:00 在西安完成

● 最新修改時間：2020-02-01 06:00 ~ 2020-02-31 24:00

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

........................................................................................................................

● 
小麥苗的微店：
https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 
小麥苗出版的資料庫類叢書：
http://blog.itpub.net/26736162/viewspace-2142121/

● 
小麥苗OCP、OCM、高可用網路班：
http://blog.itpub.net/26736162/viewspace-2148098/

● 
小麥苗騰訊課堂主頁：
https://lhr.ke.qq.com/

........................................................................................................................
使用

微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（
xiaomaimiaolhr）及QQ群（DBA寶典）、新增小麥苗微 信，
學習最實用的資料庫技術。 


........................................................................................................................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2675324/，如需轉載，請註明出處，否則將追究法律責任。

MySQL 8 複製（六）——拓撲與效能

MySQL 8 複製（六） ——拓撲與效能

相關文章