MySQL面試寶典-主從複製篇

一：請簡述MySQL主從複製原理。
二：請簡述MySQL非同步、半同步、全同步複製的區別。
三：主從複製過程中，二進位制日誌是主庫主動推送？還是從庫主動請求？
四：請簡述MySQL主從複製幾個相關引數。
五：請簡述傳統複製和基於GTID複製的差異。
六：請簡述MySQL GTID複製模式有哪些限制？
七：請簡述binlog有哪些種類，有哪些區別。
八：請簡述MySQL過濾複製。
九：MySQL主從複製過程中，從庫某一張表資料異常導致複製中斷，如何恢復。
十：請簡述MySQL主從複製主鍵衝突問題解決思路。
十一：請簡述reset master,reset slave作用。
十二：請簡述master.info和relay-log.info作用。

一：請簡述MySQL主從複製原理。

MySQL的複製功能用三個執行緒來實現：

主庫:Binlog Dump執行緒

從庫:I/O執行緒和SQL執行緒

1.使用者提交資料的修改，然後Master主庫把所有資料庫變更寫進Binary Log二進位制日誌。

主庫透過Binlog Dump執行緒把二進位制日誌內容推送給Slave從庫，從庫被動接收資料，不是主動獲取，除非是新建連線。

2.在從庫上執行START SLAVE語句時，已經使用CHANGE MASTER TO語句配置好複製資訊，從庫會建立一個I/O執行緒，該執行緒連線到主庫並請求主庫為其傳送所需的二進位制日誌。

從庫I/O執行緒與主庫Binlog Dump執行緒成功建立連線後，從庫I/O執行緒接收主庫Binlog Dump執行緒傳送的二進位制日誌，並將它們寫入從庫本地的Relay Log中繼日誌檔案。

3.從庫SQL執行緒讀取並解析中繼日誌中的內容，按照讀取的順序進行回放，二進位制日誌中存放的事務順序就是主庫中事務的提交順序，並將資料變更寫入本地資料庫檔案中，這樣就實現了資料在主從資料庫例項之間的同步。

二：請簡述MySQL非同步、半同步、全同步複製的區別。

mysql非同步複製

mysql非同步複製是指，mysql主庫將事務資訊寫入binlog檔案中的時候，此時主庫會透過binlog dump執行緒給從庫傳送這些新的binlog變化，然後並不等待從庫的響應繼續提交事務並寫入binlog，所以主庫並不保證這些事務變化的binlog資料會傳輸並應用到任何從庫。

MySQL預設的複製即是非同步的，主庫在執行完客戶端提交的事務後會立即將結果返給給客戶端，並不關心從庫是否已經接收並處理，這樣就會有一個問題，主如果crash掉了，此時主上已經提交的事務可能並沒有傳到從庫上，如果此時，強行將從提升為主，可能導致新主上的資料不完整。

主庫將事務 Binlog 事件寫入到 Binlog 檔案中，此時主庫只會通知一下 Dump 執行緒傳送這些新的 Binlog，然後主庫就會繼續處理提交操作，而此時不會保證這些 Binlog 傳到任何一個從庫節點上。

mysql全同步複製

mysql全同步複製是指，當主庫提交事務的binlog後，所有的從庫節點必須全部收到事務並且apply並且提交這些內容之後，即io_thread和sql_thread完成所有binlog變化的接受的應用執行，主庫的執行緒才可以繼續進行後續操作，但是缺點是，主庫完成一個事務的時間會被拉長，效能急劇降低。

mysql半同步複製

mysql半同步複製是介於非同步和全同步之間，主庫只需要等待至少一個從節點，收到並且flush binlog到relay log檔案即可，主庫不需要等待所有從庫給主庫反饋，這裡只是一個收到的反饋，而並不是從庫已經完成並提交的反饋，即從庫只應用完成io_thread內容即可無需等到sql_thread的執行完成。

主庫在執行完客戶端提交的事務後不是立刻返回給客戶端，而是等待至少一個從庫接收到並寫到relay log中才返回給客戶端。相對於非同步複製，半同步複製提高了資料的安全性，同時它也造成了一定程度的延遲，這個延遲最少是一個TCP/IP往返的時間。所以，半同步複製最好在低延時的網路中使用。

主庫可以使用系統變數rpl_semi_sync_master_wait_for_slave_count(預設值為1)來設定需要收到多少個從庫發回的ACK訊息，才能執行儲存引擎層的事務提交。

三：主從複製過程中，二進位制日誌是主庫主動推送？還是從庫主動請求？

既有從庫主動請求，也有主庫主動推送的情況。

對於複製執行緒在主從之間新建立連線或重新建立連線的情況，不是主庫主動推送二進位制日誌，因為這是主庫並不知道需要傳送哪些二進位制日誌給新建立連線的從庫，而是從庫主動向主庫請求所需的二進位制日誌，

從庫向主庫註冊連線時，攜帶了從庫自身所需二進位制日誌的位置資訊。主從之間的複製連線始終是從庫先發起請求的，就算主庫主動斷開從庫的連線，重新建立連線時也是從庫重試的，而不是主庫。

如果複製執行緒已經在主從之間建立連線，而且從庫已經完全接收建立連線時請求的二進位制日誌內容，後續的增量二進位制日誌是由主庫主動推送給從庫的，而不是從庫主動向主庫請求的，

因為這是主庫隨時都可以寫入新的內容，從庫難以即時感知，也沒必要即時感知。

四：請簡述MySQL主從複製幾個相關引數。

(1)log-slave-updates

log-slave-updates這個引數用來配置從伺服器的更新是否寫入二進位制日誌，這個選項預設是不開啟的。

(2)master-connect-retry

master-connect-retry這個引數是用來設定在和主伺服器連線丟失的時候，重試的時間間隔，預設是60秒

(3)relay_log_recovery = 1

當slave從庫當機後，假如relay-log損壞了，導致一部分中繼日誌沒有處理，則自動放棄所有未執行的relay-log，並且重新從master上獲取日誌，這樣就保證了relay-log的完整性。

預設情況下該功能是關閉的，將relay_log_recovery的值設定為 1時，可在slave從庫上開啟該功能，建議開啟。

(4)slave-skip-errors

在複製過程中，由於各種的原因，從伺服器可能會遇到執行BINLOG中的SQL出錯的情況，在預設情況下，伺服器會停止複製程式，不再進行同步，等到使用者自行來處理。

Slave-skip-errors的作用就是用來定義複製過程中從伺服器可以自動跳過的錯誤號，當複製過程中遇到定義的錯誤號，就可以自動跳過，直接執行後面的SQL語句。

--slave-skip-errors=[err1,err2,…….|ALL]

但必須注意的是，啟動這個引數，如果處理不當，很可能造成主從資料庫的資料不同步，在應用中需要根據實際情況，如果對資料完整性要求不是很嚴格，那麼這個選項確實可以減輕維護的成本。

往下跳一個指標,1可以更換其他數字

set global sql_slave_skip_counter=1;

(5)sync_binlog

MySQL提供一個sync_binlog引數來控制資料庫的binlog刷到磁碟上去。

預設，sync_binlog=0，表示MySQL不控制binlog的重新整理，由檔案系統自己控制它的快取的重新整理。這時候的效能是最好的，但是風險也是最大的。因為一旦系統Crash，在binlog_cache中的所有binlog資訊都會被丟失。

如果sync_binlog>0，表示每sync_binlog次事務提交，MySQL呼叫檔案系統的重新整理操作將快取刷下去。最安全的就是sync_binlog=1了，表示每次事務提交，MySQL都會把binlog刷下去，是最安全但是效能損耗最大的設定。

這樣的話，在資料庫所在的主機作業系統損壞或者突然掉電的情況下，系統才有可能丟失1個事務的資料。但是binlog雖然是順序IO，但是設定sync_binlog=1，多個事務同時提交，同樣很大的影響MySQL和IO效能。雖然可以透過group commit的補丁緩解，但是重新整理的頻率過高對IO的影響也非常大。

對於高併發事務的系統來說，“sync_binlog”設定為0和設定為1的系統寫入效能差距可能高達5倍甚至更多。

五：請簡述傳統複製和基於GTID複製的差異。

MySQL 5.6之前只支援傳統複製，即 基於二進位制日誌檔案和位置的複製，在5.6及之後的版本中，出現了基於GTID的複製。

傳統複製，也可以稱為基於二進位制日誌檔案和位置的複製，在從庫中配置複製時，要求指定從庫中獲取的二進位制日誌檔案(binlog file)和位置(binlog position),

以便從庫中的複製執行緒啟動時，能夠以指定的二進位制日誌和位置為起點，持續讀取主庫中的二進位制日誌，並在從庫中應用，從而達到資料庫同步的目的。

基於GTID的複製，是新的事務複製方法，利用GTID可以自動在主庫尋找需要複製的二進位制日誌記錄，因此不需要關心日誌檔案或位置，極大地簡化了許多常見的複製任務。

GTID（ Global Transaction Identifier）全域性事務標識，由主庫上生成的與事務繫結的唯一標識，這個標識不僅在主庫上是唯一的，在MySQL叢集內也是唯一的。

GTID是 MySQL 5.6 版本引入的一個有關於主從複製的重大改進，相對於之前版本基於Binlog檔案+Position的主從複製，基於GTID的主從複製，資料一致性更高，主從資料複製更健壯，主從切換、故障切換不易出錯，很少需要人為介入處理。

GTID之前的主從複製是基於檔案+偏移的方式，建立主從複製，必須先知道主庫的binlog檔案和偏移位置（ MASTER_LOG_FILE 和 MASTER_LOG_POS）。

而使用基於GTID的主從複製，設定 MASTER_AUTO_POSITION =1，從庫傳送自身已經接收到的gtid給主庫，主庫將從庫缺失的gtid及其對應的binlog檔案傳送給從庫，也就是主庫只傳送從庫沒有接收到的事務。

所有的資訊由MySQL叢集自動獲取完成，不需要人為干預，大大簡化了複製搭建過程。

MySQL 5.7.x 及之後的版本新增了一張InnoDB儲存引擎的mysql.gtid_executed表來持久化GTID資訊。

MySQL GTID特點

1.事務提交產生GTID，GTID與事務及事務提交所在的節點繫結，GTID與事務一起寫入Binlog，但是從庫應用Binlog並不會生成新的GTID。

2.叢集中的任何一個節點，根據其GTID值就可以知道哪些事務已經執行，哪些事務沒有執行，如果發現某個GTID已執行，重複執行該GTID，將會被忽略，即同一個GTID只能被應用一次。

3.當一個連線執行一個特定GTID的事務，但是還沒有提交，此時有另外一個連線也要執行相同GTID的事務，那麼第二個連線的執行將會被阻塞，直到第一個事務提交或者回滾。如果第一個事務成功提交，第二個事務將會被忽略。如果第一個事務回滾，第二個事務正常執行。

如何開啟GTID

gtid_mode=ON

enforce_gtid_consistency=ON

GTID長啥樣

GTID = server_uuid:transaction_id

示例：3E11FA47-71CA-11E1-9E33-C80AA9429562:1

server_uuid標識了該事務執行的源節點，儲存在資料目錄中的auto.cnf檔案中，transaction_id 是在該主庫上生成的事務序列號，從1開始，示例中 3E11FA47-71CA-11E1-9E33-C80AA9429562 是這個節點的server_uuid，1為這個節點上提交的第1個事務的事務號，如果提交了10個事務，GTID會是這樣： 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-10

GTID儲存在什麼地方？

GTID與事務繫結在一起，隨著事務的提交，GTID隨事務資訊一起寫入Binlog，透過主從複製，傳遞到從庫。對於已經執行了的事務，其GTID通常會記錄在MySQL的系統變數@@GLOBAL.gtid_executed 以及系統表mysql.gtid_executed中，系統變數@@GLOBAL.gtid_executed 在記憶體中，屬於非持久化儲存，而系統表mysql.gtid_executed屬於持久化儲存

基於GTID的複製搭建：

CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3306,MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;

基於Binlog+Position的複製搭建：

CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=524;

六：請簡述MySQL GTID複製模式有哪些限制？

1.更新操作涉及非事務引擎

在同一個事務中，不能同時操作支援事務(InnoDB)和不支援事務(MyISAM)的引擎。

這是由於同時操作這兩類引擎時可能導致將多個GTID被分配給同一個事務。

主從資料庫Server中相同的表使用不同的儲存引擎時(其中，一個Server使用事務表，另一個Server使用非事務表)，如果在非事務表上定義了觸發器，可能導致事務與GTID之間一一對應關係被破壞。

2.CREATE TABLE .. SELECT 語句

使用GRID複製時，CREATE TABLE ...語句不允許同時使用SELECT語句。

當binlog_format設定statement時，CREATE TABLE ... SELECT語句是作為一個整體且只分配一個GTID的事務形式被記錄在二進位制日誌中。

如果主庫使用statement格式二進位制日誌，而從庫使用row格式的二進位制日誌，則從庫將無法正確處理事務。

3.臨時表

使用GTID時(這裡指的是系統變數enforce_gtid_consistency設定為ON時)，事務、儲存過程、儲存函式和觸發器內不支援CREATE TEMPORARY TABLE和DROP TEMPORARY TABLE語句，

不過可以在這些物件之外執行CREATE TEMPORARY TABLE和DROP TEMPORARY TABLE語句，當需要使用autocommit = 1自動提交。

4.防止執行不受支援的語句

要防止執行會導致GTID複製失敗的語句，則需要在啟動GTID時，在整個複製拓撲的所有例項中啟用系統變數enforce_gtid_consistency。

當啟用此係統變數之後，上述可能會導致複製出現問題的語句將直接報錯，不予執行。

5.關於跳過事務

使用GTID時不支援使用系統變數sql_slave_skip_counter來跳過事務。

6.忽略Server例項

使用GTID時，不推薦在CHANGE MASTER TO語句中使用IGNORE_SERVER_IDS選項來忽略某個Server例項的二進位制日誌變更，因為在GTID複製模式下，已經應用的事務會自動被忽略。

在啟動GTID複製之前，請檢查並清除該選項的設定。

7.GTID複製模式和mysql_upgrade

當Server啟用了GTID複製模式時(gtid_mode = ON),如果需要對Server使用mysql_update進行升級，則不能啟用二進位制日誌記錄(--write-binlog選項)。

七：請簡述binlog有哪些種類，有哪些區別。

mysql複製主要有三種方式：

基於SQL語句的複製(statement-based replication, SBR)

基於行的複製(row-based replication, RBR)

混合模式複製(mixed-based replication, MBR)。

對應的，binlog的格式也有三種：STATEMENT，ROW，MIXED。

STATEMENT模式（SBR）

每一條修改資料的sql語句會記錄到binlog中。

優點：

並不需要記錄每一行的資料變化，減少了binlog日誌量，節約IO，提高效能。

缺點：

在某些情況下會導致master-slave中的資料不一致(如sleep()函式， last_insert_id()，以及user-defined functions(udf)等會出現問題)

ROW模式（RBR）

不記錄每條sql語句的上下文資訊，僅需記錄哪條資料被修改了，修改成什麼樣了。

優點：

不會出現某些特定情況下的儲存過程、或function、或trigger的呼叫和觸發無法被正確複製的問題。

缺點：

會產生大量的日誌，尤其是alter table的時候會讓日誌暴漲。

MIXED模式（MBR）

以上兩種模式的混合使用，一般的複製使用STATEMENT模式儲存binlog，對於STATEMENT模式無法複製的操作使用ROW模式儲存binlog，MySQL會根據執行的SQL語句選擇日誌儲存方式。

在MySQL 5.7.7之前，預設的二進位制日誌採用statement格式。

在MySQL 5.7.7及更高的版本中，預設的二進位制變更為row格式。

八：請簡述MySQL過濾複製。

過濾複製可以從兩方面下手: 

1.配置主庫的dump執行緒, 讓其只傳送需要同步的db二進位制。

2.配置從庫的sql執行緒, 讓其只回放我們需要同步的db二進位制檔案。

主庫:

show master status;

Binlog_Do_DB           # 該引數用來指定需要同步的db

Binlog_Ignore_DB      # 該引數用來指定不需要同步的db

從庫：

show slave status\G

Replicate_Do_DB:          # 指定的回放db

Replicate_Ignore_DB:     # 指定不回放的db

Replicate_Do_Table:        # 指定回放的表

Replicate_Ignore_Table:   # 指定不回放的表

Replicate_Wild_Do_Table:       # 模糊指定回放的表

Replicate_Wild_Ignore_Table:  # 模糊指定不回放的表

設定過濾指定資料庫

mysql> STOP SLAVE SQL_THREAD;

Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);

設定過濾指定表

mysql> CHANGE REPLICATION FILTER

    -> REPLICATE_WILD_DO_TABLE = ('db1.t1%'),

    -> REPLICATE_WILD_IGNORE_TABLE =  ('db1.t2%');

Query OK, 0 rows affected (0.00 sec)

取消取消過濾

mysql> STOP SLAVE SQL_THREAD;

Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = ();

Query OK, 0 rows affected (0.00 sec)

mysql> start SLAVE SQL_THREAD;

Query OK, 0 rows affected (0.00 sec)

九：MySQL主從複製過程中，從庫某一張表資料異常導致複製中斷，如何恢復。

1.在主庫上備份表test1

mysqldump -uroot -p'xxx'  --single-transaction cjcdbtest test1 --master-data=2 |gzip >$(date +%F).test1.sql.gz

2.恢復此表到slave庫上

mysql cjcdbtest < 2022-04-05.test1.sql 

獲取出單獨備份表的快照gtid值：

gzip -d 2022-04-05.test1.sql.gz

grep -A6 'GLOBAL.GTID_PURGED' 2022-04-05.test1.sql 

由於GTID_EXECUTED不是空值，導致匯入表test1到slave庫失敗,具體報錯如下:

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

解決方法：

方法一：reset master

登陸slave庫：

mysql> reset master;

這個操作可以將當前庫的GTID_EXECUTED值置空

方法二：--set-gtid-purged=off

在dump匯出時，新增--set-gtid-purged=off引數，避免將gtid資訊匯出

mysqldump -uroot -p --set-gtid-purged=off ...

3.線上開啟複製過濾

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('cjcdbtest.test1');

Query OK, 0 rows affected (0.00 sec)

[root@mysql02 ~]# mysql -e "show slave status\G"|egrep 'cjcdbtest.test1'

  Replicate_Wild_Ignore_Table: cjcdbtest.test1

4.啟動複製，回放到8a9fb9a3-f579-11ea-830d-90b11c12779c:42262時停止複製（此時從庫上所有表的資料都在同一狀態，是一致的）

mysql> START SLAVE UNTIL SQL_AFTER_GTIDS ='8a9fb9a3-f579-11ea-830d-90b11c12779c:42262';

Query OK, 0 rows affected, 1 warning (0.03 sec)

5.線上關閉複製過濾:

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ();

6.開啟slave複製SQL執行緒：

 start slave sql_thread;

 

主從複製恢復

mysql -e "show slave status\G"|egrep 'IO_Running|SQL_Running'

十：請簡述MySQL主從複製主鍵衝突問題解決思路。

Last_SQL_Error: Error 'Duplicate entry '1001-164761-0' for key 'PRIMARY'' on query. Default database: 'bug'. Query: 'insert into misdata (uid,mid,pid,state,mtime) values (164761,1001,0,-1,1262623560)'

檢查主鍵衝突原因：

show global variables like 'binlog_format';

statement 格式不安全

修改為 mixed/row 格式

針對雙主架構，合理配置auto_increment_offset和auto_increment_increment

A：my.cnf上加入引數 

auto_increment_offset = 1 

auto_increment_increment = 2 

這樣A的auto_increment欄位產生的數值是：1, 3, 5, 7, …等奇數ID了 

B：my.cnf上加入引數 

auto_increment_offset = 2 

auto_increment_increment = 2 

這樣B的auto_increment欄位產生的數值是：2, 4, 6, 8, …等偶數ID了 

考慮是否臨時跳過

在叢庫上，執行以下命令，跳過它：

先停止slave：stop slave;

set global sql_slave_skip_counter = 1 

再啟動slave：start slave;

說明：執行一次後檢視是否正常，不正常再繼續執行該命令

或者在從庫的my.cnf中加上這條：

slave-skip-errors = 1062

十一：請簡述reset master,reset slave作用。

RESET MASTER 

刪除所有index file 中記錄的所有binlog 檔案，將日誌索引檔案清空，建立一個新的日誌檔案，這個命令通常僅僅用於第一次用於搭建主從關係的時的主庫，

注意

  reset master 不同於purge binary log的兩處地方

1 reset master 將刪除日誌索引檔案中記錄的所有binlog檔案，建立一個新的日誌檔案 起始值從000001 開始，然而purge binary log 命令並不會修改記錄binlog的順序的數值。

2 reset master 不能用於有任何slave 正在執行的主從關係的主庫。因為在slave 執行時刻 reset master 命令不被支援，reset master 將master 的binlog從000001 開始記錄,slave 記錄的master log 則是reset master 時主庫的最新的binlog,從庫會報錯無法找的指定的binlog檔案。

RESET SLAVE 

reset slave 將使slave 忘記主從複製關係的位置資訊。該語句將被用於乾淨的啟動, 它刪除master.info檔案和relay-log.info 檔案以及所有的relay log 檔案並重新啟用一個新的relaylog檔案。

使用reset slave之前必須使用stop slave 命令將複製程式停止。

注 所有的relay log將被刪除不管他們是否被SQL thread程式完全應用(這種情況發生於備庫延遲以及在備庫執行了stop slave 命令),儲存複製連結資訊的master.info檔案將被立即清除,如果SQL thread 正在複製臨時表的過程中，執行了stop slave ，並且執行了reset slave，這些被複制的臨時表將被刪除。

RESET SLAVE  ALL

在 5.6 版本中 reset slave 並不會清理儲存於記憶體中的複製資訊比如  master host, master port, master user, or master password,也就是說如果沒有使用change master 命令做重新定向，執行start slave 還是會指向舊的master 上面。

當從庫執行reset slave之後,將mysqld shutdown 複製引數將被重置。

在5.6.3 版本以及以後 使用使用 RESET SLAVE ALL 來完全的清理複製連線引數資訊。(Bug #11809016)

RESET SLAVE ALL does not clear the IGNORE_SERVER_IDS list set by CHANGE MASTER TO. This issue is fixed in MySQL 5.7. (Bug #18816897)

In MySQL 5.6.7 and later, RESET SLAVE causes an implicit commit of an ongoing transaction. See Section 13.3.3, “Statements That Cause an Implicit Commit

十二：請簡述master.info和relay-log.info作用。

在MySQL 5.6.2之前，slave記錄的master資訊以及slave應用binlog的資訊存放在檔案中，即master.info與relay-log.info。

在5.6.2版本之後，允許記錄到table中，引數設定如下：

master-info-repository  = TABLE    ---FILE表示以檔案方式

relay-log-info-repository = TABLE  ---FILE表示以檔案方式

對應的表分別為mysql.slave_master_info與mysql.slave_relay_log_info，且這兩個表均為innodb引擎表。

由I/O執行緒更新master.info檔案。檔案中的行和SHOW SLAVE STATUS顯示的列的對應關係為：

由SQL執行緒更新relay-log.info檔案。

###chenjuchao 20220405###