本文非常詳細地介紹MySQL複製相關的內容，包括基本概念、複製原理、如何配置不同類型的複製(傳統複製)等等。在此文章之後，還有幾篇文章分別介紹GTID複製、半同步複製、實現MySQL的動靜分離，以及MySQL 5.7.17引入的革命性功能：組複製(MGR)。

本文是MySQL Replication的基礎，但卻非常重要。對於MySQL複製，如何搭建它不是重點(因為簡單，網上資源非常多)，如何維護它才是重點(網上資源不集中)。以下幾個知識點是掌握MySQL複製所必備的：

1. 複製的原理
   
2. 將master上已存在的數據恢復到slave上作為基準數據
   
3. 獲取正確的binlog坐標
   
4. 深入理解show slave status中的一些狀態信息

本文對以上內容都做了非常詳細的說明。希望對各位初學、深入MySQL複製有所幫助。

mysql replication官方手冊：https://dev.mysql.com/doc/refman/5.7/en/replication.html。

1.複製的基本概念和原理

mysql複製是指從一個mysql伺服器(MASTER)將數據通過日誌的方式經過網路傳送到另一臺或多台mysql伺服器(SLAVE)，然後在slave上重放(replay或redo)傳送過來的日誌，以達到和master數據同步的目的。

它的工作原理很簡單。首先確保master資料庫上開啟了二進位日誌，這是複製的前提。

• 在slave準備開始複製時，首先要執行change master to語句設置連接到master伺服器的連接參數，在執行該語句的時候要提供一些信息，包括如何連接和要從哪複製binlog，這些信息在連接的時候會記錄到slave的datadir下的master.info文件中，以後再連接master的時候將不用再提供這新信息而是直接讀取該文件進行連接。
  
• 在slave上有兩種線程，分別是IO線程和SQL線程。
  • IO線程用於連接master，監控和接受master的binlog。當啟動IO線程成功連接master時，master會同時啟動一個dump線程，該線程將slave請求要複製的binlog給dump出來，之後IO線程負責監控並接收master上dump出來的二進位日誌，當master上binlog有變化的時候，IO線程就將其複製過來並寫入到自己的中繼日誌(relay log)文件中。
    
  • slave上的另一個線程SQL線程用於監控、讀取並重放relay log中的日誌，將數據寫入到自己的資料庫中。如下圖所示。

可以認為複製有三個步驟：

1. 數據修改寫入master資料庫的binlog中。
   
2. slave的IO線程複製這些變動的binlog到自己的relay log中。
   
3. slave的SQL線程讀取並重新應用relay log到自己的資料庫上，讓其和master資料庫保持一致。

從複製的機制上可以知道，在複製進行前，slave上必須具有master上部分完整內容作為複製基準數據。例如，master上有資料庫A，二進位日誌已經寫到了pos1位置，那麼在複製進行前，slave上必須要有資料庫A，且如果要從pos1位置開始複製的話，還必須有和master上pos1之前完全一致的數據。如果不滿足這樣的一致性條件，那麼在replay中繼日誌的時候將不知道如何進行應用而導致數據混亂。也就是說，複製是基於binlog的position進行的，複製之前必須保證position一致。(註：這是傳統的複製方式所要求的)

可以選擇對哪些資料庫甚至資料庫中的哪些表進行複製。預設情況下，MySQL的複製是非同步的。slave可以不用一直連著master，即使中間斷開了也能從斷開的position處繼續進行複製。

MySQL 5.6對比MySQL 5.5在複製上進行了很大的改進，主要包括支持GTID(Global Transaction ID,全局事務ID)複製和多SQL線程並行重放。GTID的複製方式和傳統的複製方式不一樣，通過全局事務ID，它不要求複製前slave有基準數據，也不要求binlog的position一致。

MySQL 5.7.17則提出了組複製(MySQL Group Replication,MGR)的概念。像資料庫這樣的產品，必須要儘可能完美地設計一致性問題，特別是在集群、分散式環境下。Galera就是一個MySQL集群產品，它支持多主模型(多個master)，但是當MySQL 5.7.17引入了MGR功能後，Galera的優勢不再明顯，甚至MGR可以取而代之。MGR為MySQL集群中多主複製的很多問題提供了很好的方案，可謂是一項革命性的功能。

複製和二進位日誌息息相關，所以學習本章必須先有二進位日誌的相關知識。

2.複製的好處

圍繞下麵的拓撲圖來分析：

主要有以下幾點好處：

1.提供了讀寫分離的能力。

replication讓所有的slave都和master保持數據一致，因此外界客戶端可以從各個slave中讀取數據，而寫數據則從master上操作。也就是實現了讀寫分離。

需要註意的是，為了保證數據一致性，寫操作必須在master上進行。

通常說到讀寫分離這個詞，立刻就能意識到它會分散壓力、提高性能。

2.為MySQL伺服器提供了良好的伸縮(scale-out)能力。

由於各個slave伺服器上只提供數據檢索而沒有寫操作，因此"隨意地"增加slave伺服器數量來提升整個MySQL群的性能，而不會對當前業務產生任何影響。

之所以"隨意地"要加上雙引號，是因為每個slave都要和master建立連接，傳輸數據。如果slave數量巨多，master的壓力就會增大，網路帶寬的壓力也會增大。

3.資料庫備份時，對業務影響降到最低。

由於MySQL伺服器群中所有數據都是一致的(至少幾乎是一致的)，所以在需要備份資料庫的時候可以任意停止某一臺slave的複製功能(甚至停止整個mysql服務)，然後從這台主機上進行備份，這樣幾乎不會影響整個業務(除非只有一臺slave，但既然只有一臺slave，說明業務壓力並不大，短期內將這個壓力分配給master也不會有什麼影響)。

4.能提升數據的安全性。

這是顯然的，任意一臺mysql伺服器斷開，都不會丟失數據。即使是master宕機，也只是丟失了那部分還沒有傳送的數據(非同步複製時才會丟失這部分數據)。

5.數據分析不再影響業務。

需要進行數據分析的時候，直接劃分一臺或多台slave出來專門用於數據分析。這樣OLTP和OLAP可以共存，且幾乎不會影響業務處理性能。

3.複製分類和它們的特性

MySQL支持兩種不同的複製方法：傳統的複製方式和GTID複製。MySQL 5.7.17之後還支持組複製(MGR)。

• (1).傳統的複製方法要求複製之前，slave上必須有基準數據，且binlog的position一致。
  
• (2).GTID複製方法不要求基準數據和binlog的position一致性。GTID複製時，master上只要一提交，就會立即應用到slave上。這極大地簡化了複製的複雜性，且更好地保證master上和各slave上的數據一致性。

從數據同步方式的角度考慮，MySQL支持4種不同的同步方式：同步(synchronous)、半同步(semisynchronous)、非同步(asynchronous)、延遲(delayed)。所以對於複製來說，就分為同步複製、半同步複製、非同步複製和延遲複製。

3.1 同步複製

客戶端發送DDL/DML語句給master，master執行完畢後還需要等待所有的slave都寫完了relay log才認為此次DDL/DML成功，然後才會返回成功信息給客戶端。同步複製的問題是master必須等待，所以延遲較大，在MySQL中不使用這種複製方式。

例如上圖中描述的，只有3個slave全都寫完relay log並返回ACK給master後，master才會判斷此次DDL/DML成功。

3.2 半同步複製

客戶端發送DDL/DML語句給master，master執行完畢後還要等待一個slave寫完relay log並返回確認信息給master，master才認為此次DDL/DML語句是成功的，然後才會發送成功信息給客戶端。半同步複製只需等待一個slave的回應，且等待的超時時間可以設置，超時後會自動降級為非同步複製，所以在區域網內(網路延遲很小)使用半同步複製是可行的。

例如上圖中，只有第一個slave返回成功，master就判斷此次DDL/DML成功，其他的slave無論複製進行到哪一個階段都無關緊要。

3.3 非同步複製

客戶端發送DDL/DML語句給master，master執行完畢立即返回成功信息給客戶端，而不管slave是否已經開始複製。這樣的複製方式導致的問題是，當master寫完了binlog，而slave還沒有開始複製或者複製還沒完成時，slave上和master上的數據暫時不一致，且此時master突然宕機，slave將會丟失一部分數據。如果此時把slave提升為新的master，那麼整個資料庫就永久丟失這部分數據。

3.4 延遲複製

顧名思義，延遲複製就是故意讓slave延遲一段時間再從master上進行複製。

4.配置一主一從

此處先配置預設的非同步複製模式。由於複製和binlog息息相關，如果對binlog還不熟悉，請先瞭解binlog，見：詳細分析二進位日誌。

mysql支持一主一從和一主多從。但是每個slave必須只能是一個master的從，否則從多個master接受二進位日誌後重放將會導致數據混亂的問題。

以下是一主一從的結構圖：

在開始傳統的複製(非GTID複製)前，需要完成以下幾個關鍵點，這幾個關鍵點指導後續複製的所有步驟。

1. 為master和slave設定不同的server-id，這是主從複製結構中非常關鍵的標識號。到了MySQL 5.7，似乎不設置server id就無法開啟binlog。設置server id需要重啟MySQL實例。
   
2. 開啟master的binlog。剛安裝並初始化的MySQL預設未開啟binlog，建議手動設置binlog且為其設定文件名，否則預設以主機名為基名時修改主機名後會找不到日誌文件。
   
3. 最好設置master上的變數sync_binlog=1(MySQL 5.7.7之後預設為1，之前的版本預設為0)，這樣每寫一次二進位日誌都將其刷新到磁碟，讓slave伺服器可以儘快地複製。防止萬一master的二進位日誌還在緩存中就宕機時，slave無法複製這部分丟失的數據。
   
4. 最好設置master上的redo log的刷盤變數innodb_flush_log_at_trx_commit=1(預設值為1)，這樣每次提交事務都會立即將事務刷盤保證持久性和一致性。
   
5. 在slave上開啟中繼日誌relay log。這個是預設開啟的，同樣建議手動設置其文件名。
   
6. 建議在master上專門創建一個用於複製的用戶，它只需要有複製許可權replication slave用來讀取binlog。
   
7. 確保slave上的數據和master上的數據在"複製的起始position之前"是完全一致的。如果master和slave上數據不一致，複製會失敗。
   
8. 記下master開始複製前binlog的position，因為在slave連接master時需要指定從master的哪個position開始複製。
   
9. 考慮是否將slave設置為只讀，也就是開啟read_only選項。這種情況下，除了具有super許可權(mysql 5.7.16還提供了super_read_only禁止super的寫操作)和SQL線程能寫資料庫，其他用戶都不能進行寫操作。這種禁寫對於slave來說，絕大多數場景都非常適合。

4.1 一主一從

一主一從是最簡單的主從複製結構。本節實驗環境如下：

1. 配置master和slave的配置文件。

   [mysqld]          # master
   datadir=/data
   socket=/data/mysql.sock
   log-bin=master-bin
   sync-binlog=1
   server-id=100

   [mysqld]       # slave
   datadir=/data
   socket=/data/mysql.sock
   relay-log=slave-bin
   server-id=111

2. 重啟master和slave上的MySQL實例。

   service mysqld restart

3. 在master上創建複製專用的用戶。

   create user 'repl'@'192.168.100.%' identified by 'P@ssword1!';
   grant REPLICATION SLAVE on *.* to 'repl'@'192.168.100.%';

4. 將slave恢復到master上指定的坐標。
   這是備份恢復的內容，此處用一個小節來簡述操作過程。詳細內容見MySQL備份和恢復(一)、(二)、(三)。

4.2 將slave恢復到master指定的坐標

對於複製而言，有幾種情況：

• (1).待複製的master沒有新增數據，例如新安裝的mysql實例。這種情況下，可以跳過恢復這個過程。
  
• (2).待複製的master上已有數據。這時需要將這些已有數據也應用到slave上，並獲取master上binlog當前的坐標。只有slave和master的數據能匹配上，slave重放relay log時才不會出錯。

第一種情況此處不贅述。第二種情況有幾種方法，例如使用mysqldump、冷備份、xtrabackup等工具，這其中又需要考慮是MyISAM表還是InnoDB表。

在實驗開始之前，首先在master上新增一些測試數據，以innodb和myisam的數值輔助表為例。

DROP DATABASE IF EXISTS backuptest;
CREATE DATABASE backuptest;
USE backuptest;

# 創建myisam類型的數值輔助表和插入數據的存儲過程
CREATE TABLE num_isam (n INT NOT NULL PRIMARY KEY) ENGINE = MYISAM ;

DROP PROCEDURE IF EXISTS proc_num1;
DELIMITER $$
CREATE PROCEDURE proc_num1 (num INT) 
BEGIN
    DECLARE rn INT DEFAULT 1 ;
    TRUNCATE TABLE backuptest.num_isam ;
    INSERT INTO backuptest.num_isam VALUES(1) ;
    dd: WHILE rn * 2 < num DO 
        BEGIN
            INSERT INTO backuptest.num_isam 
            SELECT rn + n FROM backuptest.num_isam;
            SET rn = rn * 2 ;
        END ;
    END WHILE dd;
    INSERT INTO backuptest.num_isam 
    SELECT n + rn 
    FROM backuptest.num_isam 
    WHERE n + rn <= num;
END ;
$$
DELIMITER ;

# 創建innodb類型的數值輔助表和插入數據的存儲過程
CREATE TABLE num_innodb (n INT NOT NULL PRIMARY KEY) ENGINE = INNODB ;

DROP PROCEDURE IF EXISTS proc_num2;
DELIMITER $$
CREATE PROCEDURE proc_num2 (num INT) 
BEGIN
    DECLARE rn INT DEFAULT 1 ;
    TRUNCATE TABLE backuptest.num_innodb ;
    INSERT INTO backuptest.num_innodb VALUES(1) ;
    dd: WHILE rn * 2 < num DO 
        BEGIN
            INSERT INTO backuptest.num_innodb 
            SELECT rn + n FROM backuptest.num_innodb;
            SET rn = rn * 2 ;
        END ;
    END WHILE dd;
    INSERT INTO backuptest.num_innodb 
    SELECT n + rn 
    FROM backuptest.num_innodb 
    WHERE n + rn <= num ;
END ;
$$
DELIMITER ;

# 分別向兩個數值輔助表中插入100W條數據
CALL proc_num1 (1000000) ;
CALL proc_num2 (1000000) ;

所謂數值輔助表是只有一列的表，且這個欄位的值全是數值，從1開始增長。例如上面的是從1到100W的數值輔助表。

mysql> select * from backuptest.num_isam limit 10;
+----+
| n  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+

4.2.1 獲取master binlog的坐標

如果master是全新的資料庫實例，或者在此之前沒有開啟過binlog，那麼它的坐標位置是position=4。之所以是4而非0，是因為binlog的前4個記錄單元是每個binlog文件的頭部信息。

如果master已有數據，或者說master以前就開啟了binlog並寫過資料庫，那麼需要手動獲取position。為了安全以及沒有後續寫操作，必須先鎖表。

mysql> flush tables with read lock;

註意，這次的鎖表會導致寫阻塞以及innodb的commit操作。

然後查看binlog的坐標。

mysql> show master status;   # 為了排版，簡化了輸出結果
+-------------------+----------+--------------+--------+--------+
| File              | Position | Binlog_Do_DB | ...... | ...... |
+-------------------+----------+--------------+--------+--------+
| master-bin.000001 |      623 |              |        |        |
+-------------------+----------+--------------+--------+--------+

記住master-bin.000001和623。

4.2.2 備份master數據到slave上

下麵給出3種備份方式以及對應slave的恢復方法。建議備份所有庫到slave上，如果要篩選一部分資料庫或表進行複製，應該在slave上篩選(篩選方式見後文篩選要複製的庫和表)，而不應該在master的備份過程中指定。

• 方式一：冷備份直接cp。這種情況只適用於沒有新寫入操作。嚴謹一點，只適合拷貝完成前master不能有寫入操作。

1. 如果要複製所有庫，那麼直接拷貝整個datadir。
   
2. 如果要複製的是某個或某幾個庫，直接拷貝相關目錄即可。但註意，這種冷備份的方式只適合MyISAM表和開啟了innodb_file_per_table=ON的InnoDB表。如果沒有開啟該變數，innodb表使用公共表空間，無法直接冷備份。
   
3. 如果要冷備份innodb表，最安全的方法是先關閉master上的mysql，而不是通過表鎖。

所以，如果沒有涉及到innodb表，那麼在鎖表之後，可以直接冷拷貝。最後釋放鎖。

mysql> flush tables with read lock;
mysql> show master status;   # 為了排版，簡化了輸出結果
+-------------------+----------+--------------+--------+--------+
| File              | Position | Binlog_Do_DB | ...... | ...... |
+-------------------+----------+--------------+--------+--------+
| master-bin.000001 |      623 |              |        |        |
+-------------------+----------+--------------+--------+--------+

shell> rsync -avz /data 192.168.100.150:/
mysql> unlock tables;

此處實驗，假設要備份的是整個實例，因為涉及到了innodb表，所以建議關閉MySQL。因為是冷備份，所以slave上也應該關閉MySQL。

# master和slave上都執行
shell> mysqladmin -uroot -p shutdown 

然後將整個datadir拷貝到slave上(當然，有些文件是不用拷貝的，比如master上的binlog、mysql庫等)。

# 將master的datadir(/data)拷貝到slave的datadir(/data)
shell> rsync -avz /data 192.168.100.150:/

需要註意，在冷備份的時候，需要將備份到目標主機上行的DATADIR/auto.conf刪除，這個文件中記錄的是mysql server的UUID，而master和slave的UUID必須不能一致。

然後重啟master和slave。因為重啟了master，所以binlog已經滾動了，不過這次不用再查看binlog坐標，因為重啟造成的binlog日誌移動不會影響slave。

• 方式二：使用mysqldump進行備份恢復。

這種方式簡單的多，而且對於innodb表很適用，但是slave上恢復時速度慢，因為恢復時數據全是通過insert插入的。因為mysqldump可以進行定時點恢復甚至記住binlog的坐標，所以無需再手動獲取binlog的坐標。

shell> mysqldump -uroot -p --all-databases --master-data=2 >dump.db

註意，--master-data選項將再dump.db中加入change master to相關的語句，值為2時，change master to語句是註釋掉的，值為1或者沒有提供值時，這些語句是直接激活的。同時，--master-data會鎖定所有表(如果同時使用了--single-transaction，則不是鎖所有表，詳細內容請參見mysqldump)。

因此，可以直接從dump.db中獲取到binlog的坐標。記住這個坐標。

[root@xuexi ~]# grep -i -m 1 'change master to' dump.db 
-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=154;

然後將dump.db拷貝到slave上，使用mysql執行dump.db腳本即可。也可以直接在master上遠程連接到slave上執行。例如：

shell> mysql -uroot -p -h 192.168.100.150 -e 'source dump.db'

• 方式三：使用xtrabackup進行備份恢復。

這是三種方式中最佳的方式，安全性高、速度快。因為xtrabackup備份的時候會記錄master的binlog的坐標，因此也無需手動獲取binlog坐標。

xtrabackup詳細的備份方法見：xtrabackup

註意：master和slave上都安裝percona-xtrabackup。

以全備份為例：

innobackupex -u root -p /backup

備份完成後，在/backup下生成一個以時間為名稱的目錄。其內文件如下：

[root@xuexi ~]# ll /backup/2018-05-29_04-12-15
total 77872
-rw-r----- 1 root root      489 May 29 04:12 backup-my.cnf
drwxr-x--- 2 root root     4096 May 29 04:12 backuptest
-rw-r----- 1 root root     1560 May 29 04:12 ib_buffer_pool
-rw-r----- 1 root root 79691776 May 29 04:12 ibdata1
drwxr-x--- 2 root root     4096 May 29 04:12 mysql
drwxr-x--- 2 root root     4096 May 29 04:12 performance_schema
drwxr-x--- 2 root root    12288 May 29 04:12 sys
-rw-r----- 1 root root       22 May 29 04:12 xtrabackup_binlog_info
-rw-r----- 1 root root      115 May 29 04:12 xtrabackup_checkpoints
-rw-r----- 1 root root      461 May 29 04:12 xtrabackup_info
-rw-r----- 1 root root     2560 May 29 04:12 xtrabackup_logfile

其中xtrabackup_binlog_info中記錄了binlog的坐標。記住這個坐標。

[root@xuexi ~]# cat /backup/2018-05-29_04-12-15/xtrabackup_binlog_info 
master-bin.000002       154

然後將備份的數據執行"準備"階段。這個階段不要求連接mysql，因此不用給連接選項。

innobackupex --apply-log /backup/2018-05-29_04-12-15

最後，將/backup目錄拷貝到slave上進行恢復。恢復的階段就是向MySQL的datadir拷貝。但註意，xtrabackup恢復階段要求datadir必須為空目錄。否則報錯：

[root@xuexi ~]# innobackupex --copy-back /backup/2018-05-29_04-12-15/
180529 23:54:27 innobackupex: Starting the copy-back operation

IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

innobackupex version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
Original data directory /data is not empty!

所以，停止slave的mysql並清空datadir。

service mysqld stop
rm -rf /data/*

恢復時使用的模式是"--copy-back"，選項後指定要恢復的源備份目錄。恢復時因為不需要連接資料庫，所以不用指定連接選項。

[root@xuexi ~]# innobackupex --copy-back /backup/2018-05-29_04-12-15/
180529 23:55:53 completed OK!

恢復完成後，MySQL的datadir的文件的所有者和屬組是innobackupex的調用者，所以需要改回mysql.mysql。

shell> chown -R mysql.mysql /data

啟動slave，並查看恢復是否成功。

shell> service mysqld start
shell> mysql -uroot -p -e 'select * from backuptest.num_isam limit 10;'
+----+
| n  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+

4.3 slave開啟複製

經過前面的一番折騰，總算是把該準備的數據都準備到slave上，也獲取到master上binlog的坐標(154)。現在還欠東風：連接master。

連接master時，需要使用change master to提供連接到master的連接選項，包括user、port、password、binlog、position等。

mysql> change master to 
        master_host='192.168.100.20',
        master_port=3306,
        master_user='repl',
        master_password='P@ssword1!',
        master_log_file='master-bin.000002',
        master_log_pos=154;

完整的change master to語法如下：

CHANGE MASTER TO option [, option] ...
option:
  | MASTER_HOST = 'host_name'
  | MASTER_USER = 'user_name'
  | MASTER_PASSWORD = 'password'
  | MASTER_PORT = port_num
  | MASTER_LOG_FILE = 'master_log_name'
  | MASTER_LOG_POS = master_log_pos
  | MASTER_AUTO_POSITION = {0|1}
  | RELAY_LOG_FILE = 'relay_log_name'
  | RELAY_LOG_POS = relay_log_pos
  | MASTER_SSL = {0|1}
  | MASTER_SSL_CA = 'ca_file_name'
  | MASTER_SSL_CAPATH = 'ca_directory_name'
  | MASTER_SSL_CERT = 'cert_file_name'
  | MASTER_SSL_CRL = 'crl_file_name'
  | MASTER_SSL_CRLPATH = 'crl_directory_name'
  | MASTER_SSL_KEY = 'key_file_name'
  | MASTER_SSL_CIPHER = 'cipher_list'
  | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}

然後，啟動IO線程和SQL線程。可以一次性啟動兩個，也可以分開啟動。

# 一次性啟動、關閉
start slave;
stop slave;

# 單獨啟動
start slave io_thread;
start slave sql_thread;

至此，複製就已經可以開始工作了。當master寫入數據，slave就會從master處進行複製。

例如，在master上新建一個表，然後去slave上查看是否有該表。因為是DDL語句，它會寫二進位日誌，所以它也會複製到slave上。

4.4 查看slave的信息

change master to後，在slave的datadir下就會生成master.info文件和relay-log.info文件，這兩個文件隨著複製的進行，其內數據會隨之更新。

4.4.1 master.info

master.info文件記錄的是IO線程相關的信息，也就是連接master以及讀取master binlog的信息。通過這個文件，下次連接master時就不需要再提供連接選項。

以下是master.info的內容，每一行的意義見官方手冊

[root@xuexi ~]# cat /data/master.info 
25                        # 本文件的行數
master-bin.000002         # IO線程正從哪個master binlog讀取日誌
154                       # IO線程讀取到master binlog的位置
192.168.100.20            # master_host
repl                      # master_user
P@ssword1!                # master_password
3306                      # master_port
60                        # master_retry，slave重連master的超時時間(單位秒)
0





0
30.000

0

86400


0

4.4.2 relay-log.info

relay-log.info文件中記錄的是SQL線程相關的信息。以下是relog-log.info文件的內容，每一行的意義見官方手冊

[root@xuexi ~]# cat /data/relay-log.info 
7                   # 本文件的行數
./slave-bin.000001  # 當前SQL線程正在讀取的relay-log文件
4                   # SQL線程已執行到的relay log位置
master-bin.000002   # SQL線程最近執行的操作對應的是哪個master binlog
154                 # SQL線程最近執行的操作對應的是master binlog的哪個位置
0                   # slave上必須落後於master多長時間
0                   # 正在運行的SQL線程數
1                   # 一種用於內部信息交流的ID，目前值總是1

4.4.3 show slave status

在slave上執行show slave status可以查看slave的狀態信息。信息非常多，每個欄位的詳細意義可參見官方手冊

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:        # slave上IO線程的狀態，來源於show processlist
                  Master_Host: 192.168.100.20
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: No          # IO線程的狀態，此處為未運行且未連接狀態
            Slave_SQL_Running: No          # SQL線程的狀態，此處為未運行狀態
              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: 154
              Relay_Log_Space: 154
              Until_Condition: None     # start slave語句中指定的until條件，
                                        # 例如，讀取到哪個binlog位置就停止
               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    # SQL線程執行過的位置比IO線程慢多少
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: 0      # master的server id
                  Master_UUID: 
             Master_Info_File: /data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:             # slave SQL線程的狀態
           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: 
1 row in set (0.01 sec)

因為太長，後面再列出show slave status時，將裁剪一些意義不大的行。

再次回到上面show slave status的信息。除了那些描述IO線程、SQL線程狀態的行，還有幾個log_file和pos相關的行，如下所列。

      Master_Log_File: master-bin.000002
  Read_Master_Log_Pos: 154
       Relay_Log_File: slave-bin.000001
        Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000002
  Exec_Master_Log_Pos: 154

理解這幾行的意義至關重要，前面因為排版限制，描述看上去有些重覆。所以這裡完整地描述它們：

• Master_Log_File：IO線程正在讀取的master binlog；
  
• Read_Master_Log_Pos：IO線程已經讀取到master binlog的哪個位置；
  
• Relay_Log_File：SQL線程正在讀取和執行的relay log；
  
• Relay_Log_Pos：SQL線程已經讀取和執行到relay log的哪個位置；
  
• Relay_Master_Log_File：SQL線程最近執行的操作對應的是哪個master binlog；
  
• Exec_Master_Log_Pos：SQL線程最近執行的操作對應的是master binlog的哪個位置。

所以，(Relay_Master_Log_File, Exec_Master_log_Pos)構成一個坐標，這個坐標表示slave上已經將master上的哪些數據重放到自己的實例中，它可以用於下一次change master to時指定的binlog坐標。

與這個坐標相對應的是slave上SQL線程的relay log坐標(Relay_Log_File, Relay_Log_Pos)。這兩個坐標位置不同，但它們對應的數據是一致的。

最後還有一個延遲參數Seconds_Behind_Master需要說明一下，它的本質意義是SQL線程比IO線程慢多少。如果master和slave之間的網路狀況優良，那麼slave的IO線程讀速度和master寫binlog的速度基本一致，所以這個參數也用來描述"SQL線程比master慢多少"，也就是說slave比master少多少數據，只不過衡量的單位是秒。但需要註意，這個參數的描述並不標準，只有在網速很好的時候做個大概估計，很多種情況下它的值都是0，即使SQL線程比IO線程慢了很多也是如此。

4.4.4 slave信息彙總

上面的master.info、relay-log.info和show slave status的狀態都是剛連接上master還未啟動IO thread、SQL thread時的狀態。下麵將顯示已經進行一段正在執行複製的slave狀態。

首先查看啟動io thread和sql thread後的狀態。使用show processlist查看即可。

mysql> start slave;

mysql> show processlist;   # slave上的信息，為了排版，簡化了輸出
+----+-------------+---------+--------------------------------------------------------+
| Id | User        | Command | State                                                  |
+----+-------------+---------+--------------------------------------------------------+
|  4 | root        | Sleep   |                                                        |
|  7 | root        | Query   | starting                                               |
|  8 | system user | Connect | Waiting for master to send event                       |
|  9 | system user | Connect | Slave has read all relay log; waiting for more updates |
+----+-------------+---------+--------------------------------------------------------+

可以看到：

• Id=8的線程負責連接master並讀取binlog，它是IO 線程，它的狀態指示"等待master發送更多的事件"；
  
• Id=9的線程負責讀取relay log，它是SQL線程，它的狀態指示"已經讀取了所有的relay log"。

再看看此時master上的信息。

mysql> show processlist;        # master上的信息，為了排版，經過了修改
+----+------+-----------------------+-------------+--------------------------------------+
| Id | User | Host                  | Command     | State                                |
+----+------+-----------------------+-------------+--------------------------------------+
|  4 | root | localhost             | Query       | starting                             |
|----|------|-----------------------|-------------|--------------------------------------|
| 16 | repl | 192.168.100.150:39556 | Binlog Dump | Master has sent all binlog to slave; |
|    |      |                       |             | waiting for more updates             |
+----+------+-----------------------+-------------+--------------------------------------+

master上有一個Id=16的binlog dump線程，該線程的用戶是repl。它的狀態指示"已經將所有的binlog發送給slave了"。

現在，在master上執行一個長事件，以便查看slave上的狀態信息。

仍然使用前面插入數值輔助表的存儲過程，這次分別向兩張表中插入一億條數據(儘管去抽煙、喝茶，夠等幾分鐘的。如果機器性能不好，請大幅減少插入的行數)。

call proc_num1(100000000);
call proc_num2(100000000);

然後去slave上查看信息，如下。因為太長，已經裁剪了一部分沒什麼用的行。

mysql> show slave status\G
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.20
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 512685413
               Relay_Log_File: slave-bin.000003
                Relay_Log_Pos: 336989434
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 336989219
      Slave_SQL_Running_State: Reading event from the relay log

從中獲取到的信息有：

1. IO線程的狀態
   
2. SQL線程的狀態
   
3. IO線程讀取到master binlog的哪個位置：512685413
   
4. SQL線程已經執行到relay log的哪個位置：336989434
   
5. SQL線程執行的位置對應於master binlog的哪個位置：336989219

可以看出，IO線程比SQL線程超前了很多很多，所以SQL線程比IO線程的延遲較大。

4.5 MySQL複製如何實現斷開重連

很多人以為change master to語句是用來連接master的，實際上這種說法是錯的。連接master是IO線程的事情，change master to只是為IO線程連接master時提供連接參數。

如果slave從來沒連接過master，那麼必須使用change master to語句來生成IO線程所需要的信息，這些信息記錄在master.info中。這個文件是change master to成功之後立即生成的，以後啟動IO線程時，IO線程都會自動讀取這個文件來連接master，不需要先執行change master to語句。

例如，可以隨時stop slave來停止複製線程，然後再隨時start slave，只要master.info存在，且沒有人為修改過它，IO線程就一定不會出錯。這是因為master.info會隨著IO線程的執行而更新，無論讀取到master binlog的哪個位置，都會記錄下這個位置，如此一來，IO線程下次啟動的時候就知道從哪裡開始監控master binlog。

前面還提到一個文件：relay-log.info文件。這個文件中記錄的是SQL線程的相關信息，包括讀取、執行到relay log的哪個位置，剛重放的數據對應master binlog的哪個位置。隨著複製的進行，這個文件的信息會即時改變。所以，通過relay-log.info，下次SQL線程啟動的時候就能知道從relay log的哪個地方繼續讀取、執行。

如果不小心把relay log文件刪除了，SQL線程可能會丟失了一部分相比IO線程延遲的數據。這時候，只需將relay-log.info中第4、5行記錄的"SQL線程剛重放的數據對應master binlog的坐標"手動修改到master.info中即可，這樣IO線程下次連接master就會從master binlog的這個地方開始監控。當然，也可以將這個坐標作為change master to的坐標來修改master.info。

此外，當mysql實例啟動時，預設會自動start slave，也就是MySQL一啟動就自動開啟複製線程。如果想要禁止這種行為，在配置文件中加上：

[mysqld]
skip-slave-start

4.6 一些變數

預設情況下，slave連接到master後會在slave的datadir下生成master.info和relay-log.info文件，但是這是可以通過設置變數來改變的。

• master-info-repository={TABLE|FILE}：master的信息是記錄到文件master.info中還是記錄到表mysql.slave_master_info中。預設為file。
  
• relay-log-info-repository={TABLE|FILE}：slave的信息是記錄到文件relay-log.info中還是記錄到表mysql.slave_relay_log_info中。預設為file。

IO線程每次從master複製日誌要寫入到relay log中，但是它是先放在記憶體的，等到一定時機後才會將其刷到磁碟上的relay log文件中。刷到磁碟的時機可以由變數控制。

另外，IO線程每次從master複製日誌後都會更新master.info的信息，也是先更新記憶體中信息，在特定的時候才會刷到磁碟的master.info文件；同理SQL線程更新realy-log.info也是一樣的。它們是可以通過變數來設置更新時機的。

• sync-relay-log=N：設置為大於0的數表示每從master複製N個事件就刷一次盤。設置為0表示依賴於操作系統的sync機制。
  
• sync-master-info=N：依賴於master-info-repository的設置，如果為file，則設置為大於0的值時表示每更新多少次master.info將其寫入到磁碟的master.info中，設置為0則表示由操作系統來決定何時調用fdatasync()函數刷到磁碟。如果設置為table，則設置為大於0的值表示每更新多少次master.info就更新mysql.slave_master_info表一次，如果設置為0則表示永不更新該表。
  
• sync-relay-log-info=N：同上。

5.一主多從

一主多從有兩種情況，結構圖如下。

以下是一主多從的結構圖(和一主一從的配置方法完全一致)：

以下是一主多從，但某slave是另一群MySQL實例的master：

配置一主多從時，需要考慮一件事：slave上是否要開啟binlog? 如果不開啟slave的binlog，性能肯定要稍微好一點。但是開啟了binlog後，可以通過slave來備份數據，也可以在master宕機時直接將slave切換為新的master。此外，如果是上面第二種主從結構，這台slave必須開啟binlog。可以將某台或某幾台slave開啟binlog，併在mysql動靜分離的路由演算法上稍微減少一點到這些slave上的訪問權重。

上面第一種一主多從的結構沒什麼可解釋的，它和一主一從的配置方式完全一樣，但是可以考慮另一種情況：向現有主從結構中添加新的slave。所以，稍後先介紹這種添加slave，再介紹第二種一主多從的結構。

5.1 向現有主從結構中添加slave

官方手冊：https://dev.mysql.com/doc/refman/5.7/en/replication-howto-additionalslaves.html

例如在前文一主一從的實驗環境下添加一臺新的slave。

因為新的slave在開始複製前，要有master上的基準數據，還要有master binlog的坐標。按照前文一主一從的配置方式，當然很容易獲取這些信息，但這樣會將master鎖住一段時間(因為要備份基準數據)。

深入思考一下，其實slave上也有數據，還有relay log以及一些倉庫文件標記著數據複製到哪個地方。所以，完全可以從slave上獲取基準數據和坐標，也建議這樣做。

仍然有三種方法從slave上獲取基準數據：冷備份、mysqldump和xtrabackup。方法見前文將slave恢復到master指定的坐標。

其實臨時關閉一個slave對業務影響很小，所以我個人建議，新添加slave時採用冷備份slave的方式，不僅備份恢復的速度最快，配置成slave也最方便，這一點和前面配置"一主一從"不一樣。但冷備份slave的時候需要註意幾點：

1. 可以考慮將slave1完全shutdown再將整個datadir拷貝到新的slave2上。
   
2. 建議新的slave2配置文件中的"relay-log"的值和slave1的值完全一致，否則應該手動從slave2的relay-log.info中獲取IO線程連接master時的坐標，併在slave2上使用change master to語句設置連接參數。
   方法很簡單，所以不做演示了。

5.2 配置一主多從(從中有從)

此處實現的一主多從是下麵這種結構：

這種情況有些不同，master只負責傳送日誌給slave1、slave2和slave3，slave 2_1和slave 2_2的日誌由slave2負責傳送，所以slave2上也必須要開啟binlog選項。此外，還必須開啟一個選項--log-slave-updates讓slave2能夠在重放relay log時也寫自己的binlog，否則slave2的binlog僅接受人為的寫操作。

問：slave能否進行寫操作？重放relay log的操作是否會記錄到slave的binlog中？

1. 在slave上沒有開啟read-only選項(只讀變數)時，任何有寫許可權的用戶都可以進行寫操作，這些操作都會記錄到binlog中。註意，read-only選項對具有super許可權的用戶以及SQL線程執行的重放寫操作無效。預設這個選項是關閉的。

   mysql> show variables like "read_only"; 
   +---------------+-------+
   | Variable_name | Value |
   +---------------+-------+
   | read_only     | OFF   |
   +---------------+-------+

2. 在slave上沒有開啟log-slave-updates和binlog選項時，重放relay log不會記錄binlog。

所以如果slave2要作為某些slave的master，那麼在slave2上必須要開啟log-slave-updates和binlog選項。為了安全和數據一致性，在slave2上還應該啟用read-only選項。

環境如下：

以下是master、slave1和slave2上配置文件內容。

# master上的配置
[mysqld]
datadir=/data
socket=/data/mysql.sock
server_id=100
sync-binlog=1
log_bin=master-bin
log-error=/data/err.log
pid-file=/data/mysqld.pid

# slave1上的配置
[mysqld]
datadir=/data
socket=/data/mysql.sock
server_id=111
relay-log=slave-bin
log-error=/data/err.log
pid-file=/data/mysqld.pid

log-slave-updates          # 新增配置
log-bin=master-slave-bin   # 新增配置
read-only=ON               # 新增配置


# slave2上的配置
[mysqld]
datadir=/data
socket=/data/mysql.sock
server_id=123
relay-log=slave-bin
log-error=/data/err.log
pid-file=/data/mysqld.pid
read-only=ON

因為slave2目前是全新的實例，所以先將slave1的基準數據備份到slave2。由於slave1自身就是slave，臨時關閉一個slave對業務影響很小，所以直接採用冷備份slave的方式。

# 在slave2上執行
shell> mysqladmin -uroot -p shutdown

# 在slave1上執行：
shell> mysqladmin -uroot -p shutdown
shell> rsync -az --delete /data 192.168.100.19:/
shell> service mysqld start

冷備份時，以下幾點千萬註意：

1. 因為slave2是slave1的從，所以在啟動MySQL前必須將備份到slave2上的和複製有關的文件都刪除。包括：
   • (1).master.info。除非配置文件種指定了skip-start-slave-start，否則slave2將再次連接到master並作為master的slave。
     
   • (2).relay-log.info。因為slave1啟動後會繼續執行relay log中的內容(如果有未執行的)，這時slave1會將這部分寫入binlog並傳送到slave2。
     
   • (3).刪除relay log文件。其實不是必須刪除，但建議刪除。
     
   • (4).刪除relay log index文件。
     
   • (5).刪除DATADIR/auto.conf。這個文件必須刪除，因為這裡面保留了mysql server的UUID，而master和slave的UUID必須不能一致。在啟動mysql的時候，如果沒有這個文件會自動生成自己的UUID並保存到auto.conf中。
     
2. 檢查slave1上從master複製過來的專門用於複製的用戶repl是否允許slave2連接。如果不允許，應該去master上修改這個用戶。
   
3. 因為slave1是剛開啟的binlog，所以slave2連接slave1時的binlog position應該指定為4。即使slave1不是剛開啟的binlog，它在重啟後也會滾動binlog。

所以，在slave2上繼續操作：

shell> ls /data
auto.cnf    ib_buffer_pool  ib_logfile1  performance_schema  slave-bin.000005
backuptest  ibdata1         master.info  relay-log.info      slave-bin.index
err.log     ib_logfile0     mysql        slave-bin.000004    sys            

shell> rm -f /data/{master.info,relay-log.info,auto.conf,slave-bin*}
shell> service mysqld start

最後連上slave2，啟動複製線程。

shell> mysql -uroot -p
mysql> change master to
        master_host='192.168.100.150',
        master_port=3306,
        master_user='repl',
        master_password='P@ssword1!',
        master_log_file='master-slave-bin.000001',
        master_log_pos=4;
mysql> start slave;
mysql> show slave status\G

6.MySQL複製中一些常用操作

6.1 篩選要複製的庫和表

預設情況下，slave會複製master上所有庫。可以指定以下變數顯式指定要複製的庫、表和要忽略的庫、表，也可以將其寫入配置文件。

            Replicate_Do_DB: 要複製的資料庫
        Replicate_Ignore_DB: 不複製的資料庫
         Replicate_Do_Table: 要複製的表
     Replicate_Ignore_Table: 不複製的表
    Replicate_Wild_Do_Table: 通配符方式指定要複製的表
Replicate_Wild_Ignore_Table: 通配符方式指定不複製的表

如果要指定列表，則多次使用這些變數進行設置。

需要註意的是，儘管顯式指定了要複製和忽略的庫或者表，但是master還是會將所有的binlog傳給slave並寫入到slave的relay log中，真正負責篩選的slave上的SQL線程。

另外，如果slave上開啟了binlog，SQL線程讀取relay log後會將所有的事件都寫入到自己的binlog中，只不過對於那些被忽略的事件只記錄相關的事務號等信息，不記錄事務的具體內容。所以，如果之前設置了被忽略的庫或表，後來取消忽略後，它們在取消忽略以前的變化是不會再重放的，特別是基於gtid的複製會嚴格比較binlog中的gtid。

總之使用篩選的時候應該多多考慮是否真的要篩選，是否是永久篩選。

6.2 reset slave和reset master

reset slave會刪除master.info/relay-log.info和relay log，然後新生成一個relay log。但是change master to設置的連接參數還在記憶體中保留著，所以此時可以直接start slave，並根據記憶體中的change master to連接參數複製日誌。

reset slave all除了刪除reset slave刪除的東西，還刪除記憶體中的change master to設置的連接信息。

reset master會刪除master上所有的二進位日誌，並新建一個日誌。在正常運行的主從複製環境中，執行reset master很可能導致異常狀況。所以建議使用purge來刪除某個時間點之前的日誌(應該保證只刪除那些已經複製完成的日誌)。

6.3 show slave hosts

如果想查看master有幾個slave的信息，可以使用show slave hosts。以下為某個master上的結果：

mysql> show slave hosts; 
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|       111 |      | 3306 |        11 | ff7bb057-2466-11e7-8591-000c29479b32 |
|      1111 |      | 3306 |        11 | 9b119463-24d2-11e7-884e-000c29867ec2 |
+-----------+------+------+-----------+--------------------------------------+

可以看到，該show中會顯示server-id、slave的主機地址和埠號、它們的master_id以及這些slave獨一無二的uuid號。

其中show結果中的host顯示結果是由slave上的變數report_host控制的，埠是由report_port控制的。

例如，在slave2上修改其配置文件，添加report-host項後重啟MySQL服務。

[mysqld]
report_host=192.168.100.19

在slave1(前文的實驗環境，slave1是slave2的master)上查看，host已經顯示為新配置的項。

mysql> show slave hosts;
+-----------+----------------+------+-----------+--------------------------------------+
| Server_id | Host           | Port | Master_id | Slave_UUID                           |
+-----------+----------------+------+-----------+--------------------------------------+
|       111 | 192.168.100.19 | 3306 |        11 | ff7bb057-2466-11e7-8591-000c29479b32 |
|      1111 |                | 3306 |        11 | 9b119463-24d2-11e7-884e-000c29867ec2 |
+-----------+----------------+------+-----------+--------------------------------------+

6.4 多線程複製

在老版本中，只有一個SQL線程讀取relay log並重放。重放的速度肯定比IO線程寫relay log的速度慢非常多，導致SQL線程非常繁忙，且實現到從庫上延遲較大。

在MySQL 5.6中引入了多線程複製，這個多線程指的是多個SQL線程，IO線程還是只有一個。當IO線程將master binlog寫入relay log中後，一個稱為"多線程協調器(multithreaded slave coordinator)"會對多個SQL線程進行調度，讓它們按照一定的規則去執行relay log中的事件。

需要謹記於心的是，如果對多線程複製沒有瞭解的很透徹，千萬不要在生產環境中使用多線程複製。它的確帶來了一些複製性能的提升，但隨之而來的是很多的"疑難雜症"，這些"疑難雜症"並非是bug，只是需要多多瞭解之後才知道為何會出現這些問題以及如何解決這些問題。稍後會簡單介紹一種多線程複製問題：gaps。

通過全局變數slave-parallel-workers控制SQL線程個數，設置為非0正整數N，表示多加N個SQL線程，加上原有的共N+1個SQL線程。預設為0，表示不加任何SQL線程，即關閉多線程功能。

mysql> show variables like "%parallel%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 0     |
+------------------------+-------+

顯然，多線程只有在slave上開啟才有效，因為只有slave上才有SQL線程。另外，設置了該全局變數，需要重啟SQL線程才生效，否則記憶體中還是只有一個SQL線程。

例如，初始時slave上的processlist如下：

設置slave_parallel_workers=2。

mysql> set @@global.slave_parallel_workers=2;
mysql> stop slave sql_thread;
msyql> start slave sql_thread;
mysql> show full processlist;

可見多出了兩個線程，其狀態信息是"Waiting for an event from Coordinator"。

雖然是多個SQL線程，但是複製時每個庫只能使用一個線程(預設情況下，可以通過--slave-parallel-type修改並行策略)，因為如果一個庫可以使用多個線程，多個線程並行重放relay log，可能導致數據錯亂。所以應該設置線程數等於或小於要複製的庫的數量，設置多了無效且浪費資源。

6.4.1 多線程複製帶來的不一致問題

雖然多線程複製帶來了一定的複製性能提升，但它也帶來了很多問題，最嚴重的是一致性問題。完整的內容見官方手冊。此處介紹其中一個最重要的問題。

關於多線程複製，最常見也是開啟多線程複製前最需要深入瞭解的問題是：由於多個SQL線程同時執行relay log中的事務，這使得slave上提交事務的順序很可能和master binlog中記錄的順序不一致(除非指定變數slave_preserve_commit_order=1)。(註意：這裡說的是事務而不是事件。因為MyISAM的binlog順序無所謂，只要執行完了就正確，而且多線程協調器能夠協調好這些任務。所以只需考慮innodb基於事務的binlog)

舉個簡單的例子，master上事務A先於事務B提交，到了slave上因為多SQL線程的原因，可能事務B提交了事務A卻還沒提交。

是否還記得show slave status中的Exec_master_log_pos代表的意義？它表示SQL線程最近執行的事件對應的是master binlog中的哪個位置。問題由此而來。通過show slave status，我們看到已經執行事件對應的坐標，它前面可能還有事務沒有執行。而在relay log中，事務B記錄的位置是在事務A之後的(和master一樣)，於是事務A和事務B之間可能就存在一個孔洞(gap)，這個孔洞是事務A剩餘要執行的操作。

正常情況下，多線程協調器記錄了一切和多線程複製相關的內容，它能識別這種孔洞(通過打低水位標記low-watermark)，也能正確填充孔洞。即使是在存在孔洞的情況下執行stop slave也不會有任何問題，因為在停止SQL線程之前，它會等待先把孔洞填充完。但危險因素太多，比如突然宕機、突然殺掉mysqld進程等等，這些都會導致孔洞持續下去，甚至可能因為操作不當而永久丟失這部分孔洞。

那麼如何避免這種問題，出現這種問題如何解決？

1.如何避免gap。

前面說了，多個SQL線程是通過協調器來調度的。預設情況下，可能會出現gap的情況，這是因為變數slave_preserve_commit_order的預設值為0。該變數指示協調器是否讓每個SQL線程執行的事務按master binlog中的順序提交。因此，將其設置為1，然後重啟SQL線程即可保證SQL線程按序提交，也就不可能會有gap的出現。

當事務B準備先於事務A提交的時候，它將一直等待。此時slave的狀態將顯示：

Waiting for preceding transaction to commit   # MySQL 5.7.8之後顯示該狀態
Waiting for its turn to commit       # MySQL 5.7.8之前顯示該狀態

儘管不會出現gap，但show slave status的Exec_master_log_pos仍可能顯示在事務A的坐標之後。

由於開啟slave_preserve_commit_order涉及到不少操作，它還要求開啟slave的binlog--log-bin(因此需要重啟mysqld)，且開啟重放relay log也記錄binlog的行為--log-slave-updates，此外，還必須設置多線程的並行策略--slave-parallel-type=LOGICAL_CLOCK。

shell> mysqladmin -uroot -p shutdown

shell> cat /etc/my.cnf
log_bin=slave-bin
log-slave-updates
slave_parallel_workers=1
slave_parallel_type=LOGICAL_CLOCK

shell>service mysqld start

2.如何處理已經存在的gap。

方法之一，是從master上重新備份恢復到slave上，這種方法是處理不當的最後解決辦法。

正常的處理方法是，使用START SLAVE [SQL_THREAD] UNTIL SQL_AFTER_MTS_GAPS;，它表示SQL線程只有先填充gaps後才能啟動。實際上，它涉及了兩個操作：

• (1).填充gaps
  
• (2).自動停止SQL線程(所以之後需要手動啟動SQL線程)

一般來說，在填充完gaps之後，應該先reset slave移除已經執行完的relay log，然後再去啟動sql_thread。