MySQL 8 複製（三）——延遲複製與部分複製

一、延遲複製

1. 延遲複製簡介

2. 延遲複製時間戳

3. 監控延遲複製

二、部分複製

1. 簡介

2. 評估資料庫級複製和二進位制日誌選項

3. 評估表級複製選項

4. 複製規則應用

5. 部分複製示例

三、主從切換

1. 計劃內切換

2. 計劃外切換

一、延遲複製

1. 延遲複製簡介

即使通常MySQL複製很快，但MySQL預設的複製存在延遲，並且使用者無法縮短延遲時間。另一方面，有時卻需要特意增加複製的延遲。設想這樣一種場景，使用者在主庫上誤刪除了一個表，並且該操作很快被複制到從庫。當使用者發現這個錯誤時，從庫早就完成了該事件重放。此時主庫、從庫都沒有那個被誤刪的表了，如何恢復？如果有備份，可以幸運地從備份恢復，丟失的資料量取決於備份的新舊和從備份時間點到表被刪除時間點之間該表上資料的變化量。如果沒有備份呢？這種情況下，延遲複製或許可以幫上忙，作為一種恢復資料的備選方案。如果在發現問題時，從庫還沒有來得及重放相應的中繼日誌，那麼就有機會在從庫獲得該表，繼而進行恢復。這裡忽略一些其它資料恢復方案，例如已經存在類似Oracle閃回技術（Flashback）在MySQL上的實現，實現方式為解析相應的二進位制日誌事件，生成反向的SQL語句。這些程式多為個人作品，並沒有被加入MySQL發行版本中，因此在易用性、適用性、可靠性等方面還不能與原生的功能相提並論。

MySQL支援延遲複製，以便從庫故意執行比主庫晚至少在指定時間間隔的事務。在MySQL 8.0中，延遲複製的方法取決於兩個時間戳：immediate_commit_timestamp和original_commit_timestamp。如果複製拓撲中的所有伺服器都執行MySQL 8.0.1或更高版本，則使用這些時間戳測量延遲複製。如果從庫未使用這些時間戳，則執行MySQL 5.7的延遲複製。

複製延遲預設為0秒。使用CHANGE MASTER TO MASTER_DELAY = N語句將延遲設定為N秒。從主庫接收的事務比主庫上的提交至少晚N秒才在從庫上執行。每個事務發生延遲（不是以前MySQL版本中的事件），實際延遲僅強制在gtid_log_event或anonymous_gtid_log_event事件上。二進位制日誌中的每個GTID事務始終都以Gtid_log_event開頭，匿名事務沒有分配GTID，MySQL確保日誌中的每個匿名事務都以Anonymous_gtid_log_event開頭。對於事務中的其它事件，不會對它們施加任何等待時間，而是立即執行。注意，START SLAVE和STOP SLAVE立即生效並忽略任何延遲，RESET SLAVE將延遲重置為0。例如，下面將實驗環境中一主兩從半同步複製中的一個從庫設定為延遲60秒複製：

mysql> change master to master_delay = 60;

ERROR 3085 (HY000): This operation cannot be performed with a running slave sql thread; run STOP SLAVE SQL_THREAD FOR CHANNEL '' first.

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_delay = 60;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

mysql>

聯機設定延遲複製時，需要先停止sql_thread執行緒。現在主庫執行一個事務，觀察從庫的變化：

-- 主

mysql> create table test.t3(a int);

Query OK, 0 rows affected (0.01 sec)

mysql>

-- 從

mysql> desc test.t3;

ERROR 1146 (42S02): Table 'test.t3' doesn't exist

mysql> desc test.t3;

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

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

| a | int(11) | YES | | NULL | |

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

1 row in set (0.00 sec)

mysql>

主庫上建立了一個表test.t3，DDL語句自成一個事務。60秒後，從庫上才出現該表。

從庫上performance_schema模式下的replication_applier_configuration.desired_delay表列顯示使用master_delay選項配置的延遲，replication_applier_status.remaining_delay表列顯示剩餘的延遲秒數。

-- 從

mysql> select desired_delay from performance_schema.replication_applier_configuration;

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

| desired_delay |

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

| 60 |

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

1 row in set (0.00 sec)

mysql> select remaining_delay from performance_schema.replication_applier_status;

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

| remaining_delay |

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

| NULL |

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

1 row in set (0.00 sec)

mysql>

-- 主

mysql> drop table test.t3;

Query OK, 0 rows affected (0.02 sec)

mysql>

-- 從

mysql> select remaining_delay from performance_schema.replication_applier_status;

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

| remaining_delay |

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

| 54 |

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

1 row in set (0.00 sec)

mysql> select remaining_delay from performance_schema.replication_applier_status;

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

| remaining_delay |

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

| 23 |

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

1 row in set (0.00 sec)

mysql> select remaining_delay from performance_schema.replication_applier_status;

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

| remaining_delay |

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

| 16 |

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

1 row in set (0.00 sec)

mysql> select remaining_delay from performance_schema.replication_applier_status;

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

| remaining_delay |

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

| NULL |

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

1 row in set (0.00 sec)

mysql>

延遲複製可用於多種目的：

防止使用者在主庫上出錯。延遲複製時，可以將延遲的從庫回滾到錯誤之前的時間。

測試滯後時系統的行為方式。例如，在應用程式中，延遲可能是由從庫裝置上的重負載引起的。但是，生成此負載級別可能很困難。延遲複製可以模擬滯後而無需模擬負載。它還可用於除錯與從庫滯後相關的條件。

檢查資料庫過去的快照，而不必重新載入備份。例如，通過配置延遲為一週的從庫，如果需要看一下最近幾天開發前的資料庫樣子，可以檢查延遲的從庫。

2. 延遲複製時間戳

MySQL 8.0提供了一種新方法，用於測量複製拓撲中的延遲，或稱複製滯後。該方法取決於與寫入二進位制日誌的每個事務（不是每個事件）的GTID相關聯的以下時間戳：

original_commit_timestamp：將事務寫入（提交）到主庫二進位制日誌之後的自1970年1月1日00:00:00 UTC以來的微秒數。

immediate_commit_timestamp：將事務寫入（提交）到從庫的二進位制日誌之後的自1970年1月1日00:00:00 UTC以來的微秒數。

mysqlbinlog的輸出以兩種格式顯示這些時間戳，從epoch開始的微秒和TIMESTAMP格式，後者基於使用者定義的時區以獲得更好的可讀性。例如：

#190516 15:12:18 server id 1125 end_log_pos 239 CRC32 0xc1ebcb7c Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1557990738835397 immediate_commit_timestamp=1557990738838735 transaction_length=192

# original_commit_timestamp=1557990738835397 (2019-05-16 15:12:18.835397 CST)

# immediate_commit_timestamp=1557990738838735 (2019-05-16 15:12:18.838735 CST)

/*!80001 SET @@session.original_commit_timestamp=1557990738835397*//*!*/;

/*!80014 SET @@session.original_server_version=80016*//*!*/;

/*!80014 SET @@session.immediate_server_version=80016*//*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 239

通常，original_commit_timestamp在應用事務的所有副本上始終相同。在主從複製中，主庫二進位制日誌中事務的original_commit_timestamp始終與其immediate_commit_timestamp相同。在從庫的中繼日誌中，事務的original_commit_timestamp和immediate_commit_timestamp與主庫的二進位制日誌中的相同，而在其自己的二進位制日誌中，事務的immediate_commit_timestamp對應於從庫提交事務的時間。

在組複製設定中，當原始主伺服器是組的成員時，將在事務準備好提交時生成original_commit_timestamp。再具體說，當事務在原始主伺服器上完成執行並且其寫入集準備好傳送給該組的所有成員以進行認證時，生成original_commit_timestamp。因此，相同的original_commit_timestamp被複制到所有伺服器應用事務，並且每個伺服器使用immediate_commit_timestamp在其自己的二進位制日誌中儲存本地提交時間。

組複製中獨有的檢視更改事件是一種特殊情況。包含該事件的事務由每個伺服器生成，但共享相同的GTID。因此，這種事務不是先在主伺服器中執行，然後複製到該組其它成員，而是該組的所有成員都執行並應用相同的事務。由於沒有原始主伺服器，因此這些事務的original_commit_timestamp設定為零。

3. 監控延遲複製

在MySQL 8之前的老版本中，監控複製的延遲（滯後）最常用的方法之一是依賴於show slave status輸出中的seconds_behind_master欄位。但是，當使用比傳統主從複製更復雜的複製拓撲，例如組複製時，此度量標準不再適用。MySQL 8中新增的immediate_commit_timestamp和original_commit_timestamp可提供有關複製延遲的更精細的資訊。監控支援這些時間戳的複製延遲的推薦方法是使用以下performance_schema模式中的表。

replication_connection_status：與主伺服器連線的當前狀態，提供有關連線執行緒排隊到中繼日誌中的最後和當前事務的資訊。

replication_applier_status_by_coordinator：協調器執行緒的當前狀態，僅在使用多執行緒複製時顯示該資訊，提供有關協調器執行緒緩衝到工作佇列的最後一個事務的資訊，以及當前正在緩衝的事務。

replication_applier_status_by_worker：應用從主伺服器接收事務的執行緒的當前狀態，提供有關應用程式執行緒或使用多執行緒複製時每個工作執行緒應用的事務資訊。

使用這些表，可以監控相應執行緒處理的最後一個事務以及該執行緒當前正在處理的事務的資訊，包括：

事務的GTID。

從庫中繼日誌中檢索的事務的original_commit_timestamp和immediate_commit_timestamp。

執行緒開始處理事務的時間。

對於上次處理的事務，執行緒完成處理它的時間。

除Performance Schema表之外，show slave status的輸出還有三個欄位與延遲複製有關：

SQL_Delay：非負整數，表示使用CHANGE MASTER TO MASTER_DELAY = N配置的複製延遲，以秒為單位。與performance_schema.replication_applier_configuration.desired_delay值相同。

SQL_Remaining_Delay：當Slave_SQL_Running_State等待主執行事件後的MASTER_DELAY秒時，該欄位包含一個整數，表示延遲剩餘的秒數。在它他時候，此欄位為NULL。與performance_schema.replication_applier_status.remaining_delay值相同。

Slave_SQL_Running_State：一個字串，指示SQL執行緒的狀態（類似於Slave_IO_State）。該值與SHOW PROCESSLIST顯示的SQL執行緒的State值相同。

當從庫的SQL執行緒在執行事件之前等待延遲時，SHOW PROCESSLIST將其狀態值顯示為：Waiting until MASTER_DELAY seconds after master executed event。

二、部分複製

到目前為止，我們討論的都是MySQL例項級的複製，複製拓撲中的所有伺服器都包含整個例項的全部資料集，主庫的任何資料變化都會原封不動地再從庫進行重放。本節說明另一種不同於此的複製——部分複製。

1. 簡介

如果主庫未將修改資料的SQL語句或變化的資料行寫入其二進位制日誌，則不會複製該事件。如果主庫記錄了二進位制日誌並將其中的事件傳送到從庫，從庫也可以自己確定是執行它還是忽略它。這就是實現MySQL部分複製的兩種方式。

主庫上，可以使用--binlog-do-db和--binlog-ignore-db選項來控制要記錄更改的資料庫，以控制二進位制日誌記錄。但是不應該使用這些選項來控制複製哪些資料庫和表，推薦的方法是在從庫上使用過濾來控制從庫上執行的事件。在從庫端，是否執行接收事件的決定是根據從庫上啟動的--replicate-* 選項做出的。在MySQL 5.7中，可以使用CHANGE REPLICATION FILTER語句動態設定由這些選項控制的過濾器，而不用重啟MySQL例項。無論是使用--replicate-* 選項在啟動時建立還是通過CHANGE REPLICATION FILTER執行從庫，管理此類過濾器的規則都是相同的。注意，複製過濾器不能用於為組複製，因為在某些伺服器上過濾事務會使組無法就一致狀態達成協議。

預設時沒有--replicate-* 選項，從庫執行接收的所有事件，這是最簡單的情況。否則，結果取決於給定的特定選項。首先檢查資料庫級選項（--replicate-do-db，--replicate-ignore-db），如果未使用任何資料庫級選項，則繼續檢查可能正在使用的任何表級選項，未匹配的選項不會被執行。對於僅影響資料庫的語句（即CREATE DATABASE，DROP DATABASE和ALTER DATABASE），資料庫級選項始終優先於任何--replicate-wild-do-table選項。換句話說，對於此類語句，當且僅當沒有適用的資料庫級選項時，才會檢查--replicate-wild-do-table選項。

為了更容易確定選項集合會產生什麼影響，建議避免混合使用“do”和“ignore”選項或萬用字元和非萬用字元選項。如果指定了任何--replicate-rewrite-db選項，則在測試--replicate- *過濾規則之前應用它們。所有複製過濾選項都遵循相同的區分大小寫規則，這些規則適用於MySQL伺服器中其它位置的資料庫和表的名稱，包括lower_case_table_names系統變數的效果。

2. 評估資料庫級複製和二進位制日誌選項

在評估複製選項時，從庫首先檢查是否存在適用的--replicate-do-db或--replicate-ignore-db選項。使用--binlog-do-db或--binlog-ignore-db時，過程類似，只是在主庫上檢查選項。檢查匹配的資料庫取決於正在處理的事件的二進位制日誌格式。如果使用ROW格式，則要更改資料的資料庫是要檢查的資料庫。如果使用STATEMENT格式記錄了語句，則預設資料庫（使用USE語句指定）是要檢查的資料庫。看下面的實驗。（1）在從庫設定過濾器為replicate_do_db=(db2)。

-- 從

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.01 sec)

mysql> change replication filter replicate_do_db=(db2);

Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.01 sec)

mysql>

（2）主庫設定預設資料庫為db1，然後刪除db2.t1。

-- 主

mysql> set binlog_format=statement;

Query OK, 0 rows affected (0.00 sec)

mysql> use db1;

Database changed

mysql> drop table db2.t1;

Query OK, 0 rows affected (0.01 sec)

mysql>

（3）檢查從庫的複製執行情況

-- 從

mysql> desc db2.t1;

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

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

| a | int(11) | YES | | NULL | |

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

1 row in set (0.01 sec)

mysql>

從庫上並沒有刪除db2.t1。原因是在STATEMENT格式，過濾器沒有匹配預設資料庫db1。（4）改變主的預設資料庫為db2，然後建立表db1.t1。

-- 主

mysql> use db2;

Database changed

mysql> create table db1.t1(a int);

Query OK, 0 rows affected (0.03 sec)

mysql>

（5）檢查從庫的複製執行情況

-- 從

mysql> desc db1.t1;

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

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

| a | int(11) | YES | | NULL | |

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

1 row in set (0.00 sec)

mysql>

因為過濾器匹配了預設資料庫，所以語句在從庫上執行。（6）將主庫的二進位制日誌格式改為ROW，再進行測試。

-- 主

mysql> use db1;

Database changed

mysql> set binlog_format=row;

Query OK, 0 rows affected (0.00 sec)

mysql> create table db2.t1(a int);

Query OK, 0 rows affected (0.02 sec)

mysql>

此時從庫已經存在db2.t1，並且replicate_do_db=(db2)，按照文件的說法，此時會執行復制，預想的結果是因為從庫上表已經存在而報錯，然而並沒有。

-- 主

mysql> drop table db2.t1;

Query OK, 0 rows affected (0.02 sec)

mysql> create table db2.t1(a varchar(5));

Query OK, 0 rows affected (0.01 sec)

mysql> insert into db2.t1 values('aaa');

Query OK, 1 row affected (0.01 sec)

mysql>

當主庫刪除表db2.t1，而從庫卻沒刪除。再主庫建立新表db2.t1，與從庫已存在的db2.t1結構不相容。向主庫的db2.t1插入記錄後，從庫的複製卻報錯了：

Last_SQL_Error: Column 0 of table 'db2.t1' cannot be converted from type 'varchar(20(bytes))' to type 'int(11)'

可以看到，當預設資料庫與replicate_do_db不同時，create table、drop table語句不會被複制到從庫，但DML語句會正常複製。注意，行格式只記錄DML語句，即使binlog_format = ROW，DDL語句也始終記錄為語句。因此，始終根據基於語句的複製規則篩選所有DDL語句。這意味著必須使用USE語句顯式選擇預設資料庫，以便應用DDL語句。資料庫級過濾選項的檢查流程如圖1所示。

MySQL 8 複製（三）——延遲複製與部分複製

圖1 資料庫級過濾選項的檢查流程

重要的是，此階段通過的語句尚未實際執行，在檢查了所有表級選項（如果有）之後，結果允許執行該語句，語句才會真正執行。二進位制日誌選項的檢查步驟簡單描述如下：

（1）是否有--binlog-do-db或--binlog-ignore-db選項？

是，繼續第（2）步；

否，記錄語句並退出。

（2）是否有預設資料庫（USE選擇了任何資料庫）？

是，繼續第（3）步；

否，忽略語句並退出。

（3）有一個預設資料庫。是否有--binlog-do-db選項？

是，它們中的任何一個都匹配資料庫嗎？

是，記錄該語句並退出；

否，忽略語句並退出。

否，繼續執行第4步。

（4）是否有任何--binlog-ignore-db選項與資料庫匹配？

是，忽略該語句並退出；

否，記錄語句並退出。

在確定CREATE DATABASE、ALTER DATABASE和DROP DATABASE語句是記錄還是忽略時，正在建立、更改或刪除的資料庫將替換預設資料庫。--binlog-do-db有時可能意味著“忽略其他資料庫”。例如，使用基於語句的日誌記錄時，僅使用--binlog-do-db = sales執行的伺服器不會寫入預設資料庫與sales不同的二進位制日誌語句。使用具有相同選項的基於行的日誌記錄時，伺服器僅記錄那些更改sales庫資料的更新。

3. 評估表級複製選項

僅當滿足以下兩個條件之一時，從庫才會檢查並評估表選項：

沒有資料庫選項。

有資料庫選項但與語句不匹配。

作為初始規則，如果主庫啟用了基於語句的複製並且語句出現在儲存函式內，則從庫執行語句並退出。對於基於語句的複製，複製事件表示語句，構成給定事件的所有更改都與單個SQL語句相關聯。對於基於行的複製，每個事件表示單個錶行中的更改，因此單個語句（如UPDATE mytable SET mycol = 1）可能會產生許多基於行的事件。從事件角度來看，檢查表選項的過程對於基於行和基於語句的複製都是相同的。

到達表級選項檢查時，如果沒有表選項，從庫簡單地執行所有事件。如果有任何--replicate-do-table或--replicate-wild-do-table選項，則事件必須匹配其中一個才能執行，否則它會被忽略。如果存在任何--replicate-ignore-table或--replicate-wild-ignore-table選項，則執行所有事件，但匹配任何這些選項的事件除外。圖2詳細地描述了表級選項評估過程，起點是資料庫級選項的評估結束，如上節圖1所示。

MySQL 8 複製（三）——延遲複製與部分複製

圖2 表級選項評估過程

如果單個SQL語句中同時含有--replicate-do-table或--replicate-wild-do-table選項包含的表，以及--replicate-ignore-table或--replicate-wild-ignore-table選項包含的另一個表，如果語句是使用binlog_format = ROW記錄的DML語句，更新的表和忽略的表都可以按預期複製，該更新的行更新，該忽略的行忽略。如果是基於語句的複製，無論是DDL還是DML語句，如果匹配了--replicate-do-table中的表，語句將被複制，包括--replicate-ignore-table中的表也會更新。這點與MySQL 8官方文件中的描述不符：

Statement-based replication stops if a single SQL statement operates on both a table that is included by a --replicate-do-table or --replicate-wild-do-table option, and another table that is ignored by a --replicate-ignore-table or --replicate-wild-ignore-table option. The slave must either execute or ignore the complete statement (which forms a replication event), and it cannot logically do this. This also applies to row-based replication for DDL statements, because DDL statements are always logged as statements, without regard to the logging format in effect. The only type of statement that can update both an included and an ignored table and still be replicated successfully is a DML statement that has been logged with binlog_format=ROW.

下面簡單驗證一下表級的過濾複製規則。（1）從庫中設定表級複製過濾。

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

mysql> change replication filter replicate_do_table = (db1.t1), replicate_ignore_table = (db1.t2);

Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.01 sec)

mysql>

（2）在主庫上執行更新，並在從庫檢查複製情況。

-- 主

mysql> create database db1;

Query OK, 1 row affected (0.01 sec)

mysql> create table db1.t1(a int);

Query OK, 0 rows affected (0.02 sec)

mysql> create table db1.t2(a int);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into db1.t1 values (1);

Query OK, 1 row affected (0.01 sec)

mysql> insert into db1.t2 values (1);

Query OK, 1 row affected (0.00 sec)

mysql>

-- 從

mysql> select * from db1.t1;

+------+

| a |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql>

可以看到，create database 以及和表匹配的DDL、DML語句正常複製。（3）在從庫執行同樣地語句使主從資料一致，以便繼續實驗。

-- 從

mysql> create table db1.t2(a int);

Query OK, 0 rows affected (0.02 sec)

mysql> insert into db1.t2 values (1);

Query OK, 1 row affected (0.01 sec)

mysql>

在MySQL主從複製中，為主從保證資料一致性，通常將從庫設定為只讀（read_only=on），這裡只是為了方便後續實驗才在從庫執行寫操作。（4）在主庫上執行正常的單表更新，並在從庫檢查複製情況。

-- 主

mysql> update db1.t1 set a=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> update db1.t2 set a=2;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql>

-- 從

mysql> select * from db1.t1;

+------+

| a |

+------+

| 2 |

+------+

1 row in set (0.00 sec)

mysql> select * from db1.t2;

+------+

| a |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql>

符合預期，db1.t1正常複製，db1.t2被忽略。（5）在一句更新語句中同時包含replicate_do_table與replicate_ignore_table中的表

-- 主

mysql> update db1.t1 t1, db1.t2 t2 set t1.a=3, t2.a=3;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

mysql>

-- 從

mysql> select * from db1.t1;

+------+

| a |

+------+

| 3 |

+------+

1 row in set (0.00 sec)

mysql> select * from db1.t2;

+------+

| a |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql>

在binlog_format=row時，兩個表的複製符合預期，db1.t1正常複製，db1.t2被忽略。將二進位制日誌格式換成statement再試。

-- 主

mysql> set binlog_format=statement;

Query OK, 0 rows affected (0.00 sec)

mysql> update db1.t1 t1, db1.t2 t2 set t1.a=4, t2.a=4;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

mysql>

-- 從

mysql> select * from db1.t1;

+------+

| a |

+------+

| 4 |

+------+

1 row in set (0.00 sec)

mysql> select * from db1.t2;

+------+

| a |

+------+

| 4 |

+------+

1 row in set (0.00 sec)

mysql>

這個語句還是複製成功了，包括replicate_ignore_table中的db1.t2，也正常更新。（6）在主庫同時刪除db1.t1和db1.t2表

-- 主

mysql> drop table db1.t1,db1.t2;

Query OK, 0 rows affected (0.04 sec)

mysql>

-- 從

mysql> select * from db1.t1;

ERROR 1146 (42S02): Table 'db1.t1' doesn't exist

mysql> select * from db1.t2;

ERROR 1146 (42S02): Table 'db1.t2' doesn't exist

mysql>

從上覆製成功，db1.t2也被刪除了。

4. 複製規則應用

本節提供一些有關複製過濾選項不同組合的說明和用法示例。下表給出了複製過濾規則型別的一些典型組合：

條件（選項型別）

結果

沒有--relicate-*選項

從庫執行從主庫接收的所有事件。

有--replicate-*-db選項，但沒有表選項

從伺服器使用資料庫選項接受或忽略事件。它執行這些選項允許的所有事件，因為沒有表限制。

有--replicate-*-table選項，但沒有資料庫選項

由於沒有資料庫條件，因此在資料庫檢查階段接受所有事件。從庫僅根據表選項執行或忽略事件。

資料庫和表選項的組合

從庫使用資料庫選項接受或忽略事件。然後，它根據表選項評估這些選項允許的所有事件。這有時會導致結果看似違反直覺，根據使用的是基於語句還是基於行的複製，結果可能會有所不同。

下面是一個更復雜的示例，我們檢查基於語句和基於行的設定的結果。假設主庫上有兩個表db1.t1和db2.t2，並且從庫在執行時只有以下選項：

replicate-ignore-db = db1

replicate-do-table = db2.t2

執行下面的步驟初始化複製。

（1）從庫去掉以前的過濾規則

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

mysql> change replication filter replicate_do_table = (), replicate_ignore_table = ();

Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.01 sec)

mysql>

（2）主庫建立物件

mysql> create database db1;

Query OK, 1 row affected (0.00 sec)

mysql> create database db2;

Query OK, 1 row affected (0.01 sec)

mysql> create table db1.t1(a int);

Query OK, 0 rows affected (0.02 sec)

mysql> create table db2.t2(a int);

Query OK, 0 rows affected (0.02 sec)

mysql>

（3）從庫增加過新的濾規則

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.01 sec)

mysql> change replication filter replicate_ignore_db = (db1), replicate_do_table = (db2.t2);

Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

mysql>

現在我們在主庫上執行以下語句：

mysql> set binlog_format=statement;

Query OK, 0 rows affected (0.00 sec)

mysql> use db1;

Database changed

mysql> insert into db2.t2 values (1);

Query OK, 1 row affected (0.00 sec)

mysql>

檢視從庫：

mysql> select * from db2.t2;

Empty set (0.00 sec)

mysql>

從庫的db2.t2表沒有資料。USE語句使db1成為預設資料庫，與--replicate-ignore-db選項匹配，因此忽略INSERT語句，不檢查表選項。

用row方式再執行一遍：

mysql> set binlog_format=row;

Query OK, 0 rows affected (0.00 sec)

mysql> use db1;

Database changed

mysql> insert into db2.t2 values (1);

Query OK, 1 row affected (0.00 sec)

mysql>

檢視從庫：

mysql> select * from db2.t2;

+------+

| a |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql>

這回db2.t2複製了一條資料。使用基於行的複製時，預設資料庫對從庫讀取資料庫選項的方式沒有影響。因此，USE語句對如何處理--replicate-ignore-db選項沒有影響。此選項指定的資料庫與INSERT語句更改資料的資料庫不匹配，因此從庫繼續檢查表選項。--replicate-do-table指定的表與要更新的表匹配，並插入行。

5. 部分複製示例

在某些情況下，可能只有一個主庫（伺服器），並且希望將不同的資料庫複製到不同的從庫（伺服器）。例如，可能希望將不同的銷售資料分發到不同的部門，以幫助在資料分析期間分散負載。如圖3所示，將主庫的db1複製到從庫1，db2複製到從庫2。

MySQL 8 複製（三）——延遲複製與部分複製

圖3 將主庫上不同的資料庫複製到不同的從庫

實現時可以先配置正常的一主兩從複製，然後通過在每個從庫上使用--replicate-wild-do-table配置選項來限制每個從庫執行的事件。注意，在使用基於語句的複製時，不應將--replicate-do-db用於此目的，因為基於語句的複製會導致此選項的影響因當前所選的資料庫而異。這也適用於混合格式複製，因為這可能使用基於語句的格式複製某些更新。

在從庫1執行：

stop slave sql_thread;

change replication filter replicate_wild_do_table=('db1.%');

start slave sql_thread;

在從庫2執行：

stop slave sql_thread;

change replication filter replicate_wild_do_table=('db2.%');

start slave sql_thread;

此配置中的每個從庫從主庫接收整個二進位制日誌，但僅執行二進位制日誌中--replicate-wild-do-table選項所包含的資料庫和表的那些事件。測試：

-- 主

create database db1;

create database db2;

create table db1.t1(a int);

create table db2.t2(a int);

insert into db1.t1 select 1;

insert into db2.t2 select 2;

-- 從1

mysql> select * from db1.t1;

+------+

| a |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

mysql> select * from db2.t2;

ERROR 1049 (42000): Unknown database 'db2'

mysql>

-- 從2

mysql> select * from db1.t1;

ERROR 1049 (42000): Unknown database 'db1'

mysql> select * from db2.t2;

+------+

| a |

+------+

| 2 |

+------+

1 row in set (0.00 sec)

mysql>

資料如預期複製，db1和db2的資料分別複製到從庫1和從庫2。下面看一下routine的複製情況。

-- 主

delimiter //

create procedure db1.p1 ()

begin

select 1;

end;

//

delimiter ;

-- 從1

mysql> call db1.p1();

+---+

| 1 |

+---+

| 1 |

+---+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

-- 從2

mysql> call db1.p1();

ERROR 1305 (42000): PROCEDURE db1.p1 does not exist

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.1.125

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000011

Read_Master_Log_Pos: 73101

Relay_Log_File: hdp4-relay-bin.000047

Relay_Log_Pos: 1591

Relay_Master_Log_File: binlog.000011

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table: db2.%

Replicate_Wild_Ignore_Table:

Last_Errno: 1049

Last_Error: Error 'Unknown database 'db1'' on query. Default database: ''. Query: 'CREATE DEFINER=`wxy`@`%` PROCEDURE `db1`.`p1`()

begin

select 1;

end'

Skip_Counter: 0

Exec_Master_Log_Pos: 72869

Relay_Log_Space: 2194

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1049

Last_SQL_Error: Error 'Unknown database 'db1'' on query. Default database: ''. Query: 'CREATE DEFINER=`wxy`@`%` PROCEDURE `db1`.`p1`()

begin

select 1;

end'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1125

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

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp: 190524 15:20:03

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 0

Network_Namespace:

1 row in set (0.00 sec)

mysql>

在主庫上的db1建立儲存過程，從庫1正常複製，但從庫2卻報錯了，它還是執行了複製，只是因為缺少db1資料庫而複製報錯。可見，replicate_wild_do_table只對表起作用，而對於routine無效，主庫上所有庫的routine都會在所有從庫執行復制。

如果在複製開始之前有必須同步到從庫的資料，則可以將所有資料同步到每個從庫，然後在從庫刪除不想保留的資料庫或表。或者使用mysqldump為每個資料庫建立單獨的轉儲檔案，並在每個從庫上載入相應的轉儲檔案，例如：

# 從庫1

mysqldump --single-transaction --databases db1 --master-data=1 --host=172.16.1.125 --user=wxy --password=123456 --apply-slave-statements | mysql -uroot -p123456

# 從庫2

mysqldump --single-transaction --databases db2 --master-data=1 --host=172.16.1.125 --user=wxy --password=123456 --apply-slave-statements | mysql -uroot -p123456

這兩種方式都可與聯機進行，並且對主庫的影響不大。整例項複製與mysqldump的聯機複製具體步驟參見“MySQL 8 複製（一）——非同步複製”。

三、主從切換

有時需要把從庫指向一個新的主庫。例如滾動升級伺服器，或者主庫出現問題時需要把一臺從庫轉換成主庫。可以使用CHANGE MASTER TO語句告訴從庫連線新的主庫。從庫不檢查主庫上的資料庫是否與從庫上現有的資料庫相容，它只是從新主庫二進位制日誌中的指定座標開始讀取和執行事件。這種主從角色轉換可以粗略地分為計劃內和計劃外兩種。

1. 計劃內切換

計劃內主從切換簡單說就是事先有準備的維護性操作，通常需要執行以下步驟：

（1）停止當前主庫的所有寫操作。如果可以，最好能關閉所有的客戶端連線。

（2）通過flush tables with read lock在主庫上停止所有活躍的寫入，這一步是可選的。也可以在主庫上設定read_only選項。從這一刻開始，應該禁止向即將被替換的主庫做任何寫入。因為一旦它不是主庫，寫入就意味著資料丟失。注意，即使設定read_only也不會阻止當前已存在的事務繼續提交。為了更好地保證這一點，可以“kill”所有開啟的事務，這將會真正地結束所有寫入。例如可以編寫以下內容的kill_mysql_session.sh指令碼：

#!/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 'set global read_only=on;' union all select concat('kill ',id,';') from information_schema.processlist where command='sleep' ) t; "

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

之後就可以執行kill_mysql_session.sh殺掉會話。較新版本的MySQL，需要在配置檔案中設定secure_file_priv引數，並重啟mysql後才能執行資料匯出操作。

（3）選擇一個備庫作為新的主庫，並確保它已經完全跟上主庫（執行完所有中繼日誌）。

（4）確保新主庫和舊主庫資料一致。可選。例如在兩個庫上執行“mysqldump -uroot --skip-dump-date | md5sum”，檢查校驗和是否相同。

（5）在新主庫上執行stop slave。

（6）在新主庫上執行reset slave all，使其斷開與老主庫的連線。

（7）執行show master status記錄新主庫的二進位制日誌座標。

（8）確保其它從庫已經追上就主庫。

（9）關閉舊主庫。

（10）如果需要，在新主庫上執行set global read_only=on使其可寫。

（11）在每臺從庫上執行change master to語句，使用前面第（7）步獲得的二進位制座標，來指向新主庫。

（12）將客戶端連線到新主庫。

2. 計劃外切換

當主庫崩潰時，需要提升一個從庫來替代它。如果只有一個從庫，那別無選擇，只能使用這臺備庫。但如果有超過一個的從庫，就需要做一些額外工作。對主從拓撲結構中的從庫進行提升的過程可以簡單描述如下：

（1）讓所有從庫執行完其從崩潰前的舊主庫獲得的中繼日誌。

（2）選擇並設定新主庫。

（3）查詢其它所有從庫最後執行的事件，在新主庫上對應的二進位制座標。

（4）其它所有從庫重置複製，按上一步獲得的二進位制座標連線到新主庫，啟動新複製。

這其中隱藏著很多細節，因此用一個具體的例子進行詳細說明。假設一個標準的MySQL 8的一主兩從複製結構，主庫標記為M（172.16.1.125），兩個從庫分別標記為為S1（172.16.1.126）、S2（172.16.1.127）。全部使用MySQL 8預設的複製相關配置：

log_bin=ON

binlog_format=ROW

log_slave_updates=ON

gtid_mode=OFF

兩個從庫的read_only=ON。

我們在這個複製場景下，模擬主庫伺服器不可用時，如何提升一個從庫稱為新主庫，並將其它從庫指向新主庫，目標是保證最少的事務丟失。以下是實驗步驟。

（1）在M上做一些資料更新

create database db1;

use db1;

create table t1(a int);

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

update t1 set a=3 where a=1;

delete from t1 where a=3;

insert into t1 select 1; -- 用於檢視同樣地SQL語句，event是否一樣

insert into t1 select 1;

commit;

（2）停止S2複製，模擬S2是落後的從庫

stop slave;

（3）重新整理S1的日誌

flush logs;

由於伺服器重啟、不同的配置、日誌輪轉或者flush logs命令等原因，會造成同一事件在不同的伺服器上有不同的偏移量，這步模擬此種情況。

（4）在M再做一些更新

insert into t1 values (10),(11),(12);

delete from t1 where a=1;

commit;

此時S2的複製已經停止，上面的資料更新對它不可知。S1正常複製，以此人為模擬兩個從庫的快慢之分。

（5）停止M，模擬主庫損壞

mysqladmin -uwxy -p shutdown

（6）啟動S2的複製

start slave;

（7）檢視當前兩個從庫的執行緒狀態、複製狀態和當前資料

show processlist;

show slave status\G

select * from db1.t1;

S1上的顯示如下：

mysql> show processlist;

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

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

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

4 rows in set (0.00 sec)

mysql> show slave status\G

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

Slave_IO_State: Reconnecting after a failed master event read

Master_Host: 172.16.1.125

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000015

Read_Master_Log_Pos: 2918

Relay_Log_File: hdp3-relay-bin.000003

Relay_Log_Pos: 837

Relay_Master_Log_File: binlog.000015

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 2918

Relay_Log_Space: 3409

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2003

Last_IO_Error: error reconnecting to master 'repl@172.16.1.125:3306' - retry-time: 60 retries: 1

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1125

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

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 190528 14:15:22

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 0

Network_Namespace:

1 row in set (0.00 sec)

mysql> select * from db1.t1;

+------+

| a |

+------+

| 2 |

| 10 |

| 11 |

| 12 |

+------+

4 rows in set (0.00 sec)

mysql>

S2上的顯示如下：

mysql> show processlist;

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

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

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

4 rows in set (0.00 sec)

mysql> show slave status\G

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

Slave_IO_State: Connecting to master

Master_Host: 172.16.1.125

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000015

Read_Master_Log_Pos: 2356

Relay_Log_File: hdp4-relay-bin.000002

Relay_Log_Pos: 2520

Relay_Master_Log_File: binlog.000015

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 2356

Relay_Log_Space: 2727

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2003

Last_IO_Error: error connecting to master 'repl@172.16.1.125:3306' - retry-time: 60 retries: 2

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1125

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

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 190528 14:16:40

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 0

Network_Namespace:

1 row in set (0.00 sec)

mysql> select * from db1.t1;

+------+

| a |

+------+

| 2 |

| 1 |

+------+

4 rows in set (0.00 sec)

mysql>

S1和S2的SQL執行緒狀態均為“Slave has read all relay log; waiting for more updates”，說明兩個從庫都已經完成了所有中繼日誌的重放。S1的Relay_Master_Log_File和Exec_Master_Log_Pos分別是binlog.000015和2918，S2的Relay_Master_Log_File和Exec_Master_Log_Pos分別是binlog.000015和2356，說明S1更接近於原來的主庫M，應當將S1提升為新主庫。從表db1.t1的資料也可以明顯看到S1的資料更新。

（8）提升S1為新主庫

stop slave;

reset slave all;

set global read_only=off;

同時去掉配置檔案中的read_only，以免重啟庫時忘記更改配置：

sed -i 's/^read_only/#&/' /etc/my.cnf

（9）找到S2上最後的重放事件，在S1上對應的二進位制座標

首先在S2上檢視最後的二進位制座標：

mysql> show master status;

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

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

| binlog.000006 | 9620 | | | |

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

1 row in set (0.00 sec)

mysql>

然後在當前binlog檔案binlog.000006裡查詢最後的重放事件，在S2上執行：

mysqlbinlog --base64-output=decode-rows --verbose /usr/local/mysql/data/binlog.000006

結果顯示如下：

...

# at 9347

#190528 14:14:24 server id 1125 end_log_pos 9433 CRC32 0xd6fcb00c Anonymous_GTID last_committed=36 sequence_number=37 rbr_only=yes original_committed_timestamp=1559024064330837 immediate_commit_timestamp=1559024064349703 transaction_length=273

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

# original_commit_timestamp=1559024064330837 (2019-05-28 14:14:24.330837 CST)

# immediate_commit_timestamp=1559024064349703 (2019-05-28 14:14:24.349703 CST)

/*!80001 SET @@session.original_commit_timestamp=1559024064330837*//*!*/;

/*!80014 SET @@session.original_server_version=80016*//*!*/;

/*!80014 SET @@session.immediate_server_version=80016*//*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 9433

#190528 14:14:24 server id 1125 end_log_pos 9502 CRC32 0xf10333d5 Query thread_id=8 exec_time=0 error_code=0

SET TIMESTAMP=1559024064/*!*/;

BEGIN

/*!*/;

# at 9502

#190528 14:14:24 server id 1125 end_log_pos 9549 CRC32 0x0fb24522 Table_map: `db1`.`t1` mapped to number 104

# at 9549

#190528 14:14:24 server id 1125 end_log_pos 9589 CRC32 0x6af67d3e Write_rows: table id 104 flags: STMT_END_F

### INSERT INTO `db1`.`t1`

### SET

### @1=1

# at 9589

#190528 14:14:24 server id 1125 end_log_pos 9620 CRC32 0x763d087a Xid = 211

COMMIT/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[mysql@hdp4~]$

最後一個事件的在原主庫上的提交時間戳是original_committed_timestamp=1559024064330837，正如前面討論延遲複製時所述，這個時間戳是主庫的原始提交時間，單位精確到微妙，在所有從庫上相同。因此可以通過它將不同從庫上相同的事件聯絡起來。

最後查詢S1上的binlog中“original_committed_timestamp=1559024064330837”對應的檔名和偏移量，在S1上執行：

while read LINE

do

filename=/usr/local/mysql/data/${LINE:2}

echo $filename

mysqlbinlog --base64-output=decode-rows --verbose $filename | grep -A30 -n "original_committed_timestamp=1559024064330837"

done < /usr/local/mysql/data/binlog.index

結果顯示如下：

[mysql@hdp3~]$while read LINE

> do

> filename=/usr/local/mysql/data/${LINE:2}

> echo $filename

> mysqlbinlog --base64-output=decode-rows --verbose $filename | grep -A30 -n "original_committed_timestamp=1559024064330837"

> done < /usr/local/mysql/data/binlog.index

/usr/local/mysql/data/binlog.000011

/usr/local/mysql/data/binlog.000012

/usr/local/mysql/data/binlog.000013

/usr/local/mysql/data/binlog.000014

/usr/local/mysql/data/binlog.000015

/usr/local/mysql/data/binlog.000016

/usr/local/mysql/data/binlog.000017

/usr/local/mysql/data/binlog.000018

/usr/local/mysql/data/binlog.000019

/usr/local/mysql/data/binlog.000020

/usr/local/mysql/data/binlog.000021

/usr/local/mysql/data/binlog.000022

/usr/local/mysql/data/binlog.000023

194:#190528 14:14:24 server id 1125 end_log_pos 2193 CRC32 0x75e3db63 Anonymous_GTID last_committed=8 sequence_number=9 rbr_only=yes original_committed_timestamp=1559024064330837 immediate_commit_timestamp=1559024064361609 transaction_length=273

195-/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

196-# original_commit_timestamp=1559024064330837 (2019-05-28 14:14:24.330837 CST)

197-# immediate_commit_timestamp=1559024064361609 (2019-05-28 14:14:24.361609 CST)

198-/*!80001 SET @@session.original_commit_timestamp=1559024064330837*//*!*/;

199-/*!80014 SET @@session.original_server_version=80016*//*!*/;

200-/*!80014 SET @@session.immediate_server_version=80016*//*!*/;

201-SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

202-# at 2193

203-#190528 14:14:24 server id 1125 end_log_pos 2262 CRC32 0xb047eaf4 Query thread_id=8 exec_time=0 error_code=0

204-SET TIMESTAMP=1559024064/*!*/;

205-BEGIN

206-/*!*/;

207-# at 2262

208-#190528 14:14:24 server id 1125 end_log_pos 2309 CRC32 0xd9f43e3d Table_map: `db1`.`t1` mapped to number 91

209-# at 2309

210-#190528 14:14:24 server id 1125 end_log_pos 2349 CRC32 0x69e276a4 Write_rows: table id 91 flags: STMT_END_F

211-### INSERT INTO `db1`.`t1`

212-### SET

213-### @1=1

214-# at 2349

215-#190528 14:14:24 server id 1125 end_log_pos 2380 CRC32 0xee93c6db Xid = 44

216-COMMIT/*!*/;

217-# at 2380

218-#190528 14:14:48 server id 1126 end_log_pos 2424 CRC32 0xe9f46f61 Rotate to binlog.000024 pos: 4

219-SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

220-DELIMITER ;

221-# End of log file

222-/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

223-/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

/usr/local/mysql/data/binlog.000024

[mysql@hdp3~]$

可以看到，S2上最後一個事務在S1上對應的座標為binlog.000023、2380，至此已經確定了S2連線新主庫的change master to的座標。

（10）將S2連線到新主庫

stop slave;

reset slave all;

change master to

master_host='172.16.1.126',

master_port=3306,

master_user='repl',

master_password='123456',

master_log_file='binlog.000023',

master_log_pos=2380;

（11）在S2啟動複製，檢視複製狀態，驗證資料

start slave;

show slave status\G

select * from db1.t1;

複製狀態顯示如下，可以看到S2從新主庫的複製一切正常：

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.1.126

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000024

Read_Master_Log_Pos: 721

Relay_Log_File: hdp4-relay-bin.000004

Relay_Log_Pos: 929

Relay_Master_Log_File: binlog.000024

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 721

Relay_Log_Space: 1180

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1126

Master_UUID: a70e460e-7309-11e9-93e8-005056a50f77

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 0

Network_Namespace:

1 row in set (0.00 sec)

查詢db1.t1表的資料顯示如下，事務已經執行到M崩潰的時間點：

mysql> select * from db1.t1;

+------+

| a |

+------+

| 2 |

| 10 |

| 11 |

| 12 |

+------+

4 rows in set (0.00 sec)

至此，新主庫提升和其它從庫的重新指向已經完成，複製拓撲中所有庫的資料變化都已經追趕到最近，新的主從複製已經完成，客戶端應用可以連線到新主庫。

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

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

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

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-2675269/，如需轉載，請註明出處，否則將追究法律責任。

MySQL 8 複製（三）——延遲複製與部分複製

相關文章