MySQL Replication

主從復(fù)制（也稱(chēng) AB 復(fù)制）允許將來(lái)自一個(gè)MySQL數(shù)據(jù)庫(kù)服務(wù)器（主服務(wù)器）的數(shù)據(jù)復(fù)制到一個(gè)或多個(gè)MySQL數(shù)據(jù)庫(kù)服務(wù)器（從服務(wù)器）。

==默認(rèn)情況下，復(fù)制是異步的 從站不需要永久連接以接收來(lái)自主站的更新。==

根據(jù)配置，您可以復(fù)制數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)庫(kù)，所選數(shù)據(jù)庫(kù)甚至選定的表。

MySQL中復(fù)制的優(yōu)點(diǎn)包括：

• 橫向擴(kuò)展解決方案 - 在多個(gè)從站之間分配負(fù)載以提高性能。在此環(huán)境中，所有寫(xiě)入和更新都必須在主服務(wù)器上進(jìn)行。但是，讀取可以在一個(gè)或多個(gè)從設(shè)備上進(jìn)行。該模型可以提高寫(xiě)入性能（因?yàn)橹髟O(shè)備專(zhuān)用于更新），同時(shí)顯著提高了越來(lái)越多的從設(shè)備的讀取速度。
• 數(shù)據(jù)安全性 - 因?yàn)閿?shù)據(jù)被復(fù)制到從站，并且從站可以暫停復(fù)制過(guò)程，所以可以在從站上運(yùn)行備份服務(wù)而不會(huì)破壞相應(yīng)的主數(shù)據(jù)。
• 分析 - 可以在主服務(wù)器上創(chuàng)建實(shí)時(shí)數(shù)據(jù)，而信息分析可以在從服務(wù)器上進(jìn)行，而不會(huì)影響主服務(wù)器的性能。
• 遠(yuǎn)程數(shù)據(jù)分發(fā) - 您可以使用復(fù)制為遠(yuǎn)程站點(diǎn)創(chuàng)建數(shù)據(jù)的本地副本，而無(wú)需永久訪(fǎng)問(wèn)主服務(wù)器。

Replication 的原理

image

==前提是作為主服務(wù)器角色的數(shù)據(jù)庫(kù)服務(wù)器必須開(kāi)啟二進(jìn)制日志==

1. 主服務(wù)器上面的任何修改都會(huì)通過(guò)自己的 I/O tread(I/O 線(xiàn)程)保存在二進(jìn)制日志 Binary log 里面。

2. 從服務(wù)器上面也啟動(dòng)一個(gè) I/O thread，通過(guò)配置好的用戶(hù)名和密碼, 連接到主服務(wù)器上面請(qǐng)求讀取二進(jìn)制日志，然后把讀取到的二進(jìn)制日志寫(xiě)到本地的一個(gè)Realy log（中繼日志）里面。

3. 從服務(wù)器上面同時(shí)開(kāi)啟一個(gè) SQL thread 定時(shí)檢查 Realy log(這個(gè)文件也是二進(jìn)制的)，如果發(fā)現(xiàn)有更新立即把更新的內(nèi)容在本機(jī)的數(shù)據(jù)庫(kù)上面執(zhí)行一遍。

每個(gè)從服務(wù)器都會(huì)收到主服務(wù)器二進(jìn)制日志的全部?jī)?nèi)容的副本。

從服務(wù)器設(shè)備負(fù)責(zé)決定應(yīng)該執(zhí)行二進(jìn)制日志中的哪些語(yǔ)句。

除非另行指定，否則主從二進(jìn)制日志中的所有事件都在從站上執(zhí)行。

如果需要，您可以將從服務(wù)器配置為僅處理一些特定數(shù)據(jù)庫(kù)或表的事件。

==重要: 您無(wú)法將主服務(wù)器配置為僅記錄特定事件。==

每個(gè)從站(從服務(wù)器)都會(huì)記錄二進(jìn)制日志坐標(biāo)：

• 文件名
• 文件中它已經(jīng)從主站讀取和處理的位置。

由于每個(gè)從服務(wù)器都分別記錄了自己當(dāng)前處理二進(jìn)制日志中的位置，因此可以斷開(kāi)從服務(wù)器的連接，重新連接然后恢復(fù)繼續(xù)處理。

一主多從

如果一主多從的話(huà)，這時(shí)主庫(kù)既要負(fù)責(zé)寫(xiě)又要負(fù)責(zé)為幾個(gè)從庫(kù)提供二進(jìn)制日志。此時(shí)可以稍做調(diào)整，將二進(jìn)制日志只給某一從，這一從再開(kāi)啟二進(jìn)制日志并將自己的二進(jìn)制日志再發(fā)給其它從?；蛘呤歉纱噙@個(gè)從不記錄只負(fù)責(zé)將二進(jìn)制日志轉(zhuǎn)發(fā)給其它從，這樣架構(gòu)起來(lái)性能可能要好得多，而且數(shù)據(jù)之間的延時(shí)應(yīng)該也稍微要好一些。工作原理圖如下：

image

關(guān)于二進(jìn)制日志

mysqld將數(shù)字?jǐn)U展名附加到二進(jìn)制日志基本名稱(chēng)以生成二進(jìn)制日志文件名。每次服務(wù)器創(chuàng)建新日志文件時(shí)，該數(shù)字都會(huì)增加，從而創(chuàng)建一系列有序的文件。每次啟動(dòng)或刷新日志時(shí)，服務(wù)器都會(huì)在系列中創(chuàng)建一個(gè)新文件。服務(wù)器還會(huì)在當(dāng)前日志大小達(dá)到后自動(dòng)創(chuàng)建新的二進(jìn)制日志文件 max_binlog_size。二進(jìn)制日志文件可能會(huì)比max_binlog_size使用大型事務(wù)時(shí)更大， 因?yàn)槭聞?wù)是以一個(gè)部分寫(xiě)入文件，而不是在文件之間分割。

為了跟蹤已使用的二進(jìn)制日志文件， mysqld還創(chuàng)建了一個(gè)二進(jìn)制日志索引文件，其中包含所有使用的二進(jìn)制日志文件的名稱(chēng)。默認(rèn)情況下，它具有與二進(jìn)制日志文件相同的基本名稱(chēng)，并帶有擴(kuò)展名'.index'。在mysqld運(yùn)行時(shí)，您不應(yīng)手動(dòng)編輯此文件。

術(shù)語(yǔ)二進(jìn)制日志文件通常表示包含數(shù)據(jù)庫(kù)事件的單個(gè)編號(hào)文件。

術(shù)語(yǔ) 二進(jìn)制日志 表示含編號(hào)的二進(jìn)制日志文件集加上索引文件。

SUPER 權(quán)限的用戶(hù)可以使用SET sql_log_bin=0語(yǔ)句禁用其當(dāng)前環(huán)境下自己的語(yǔ)句的二進(jìn)制日志記錄

配置 Replication

配置步驟：

1. 在主服務(wù)器上，您必須啟用二進(jìn)制日志記錄并配置唯一的服務(wù)器ID。需要重啟服務(wù)器。

編輯主服務(wù)器的配置文件 my.cnf，添加如下內(nèi)容

[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1

創(chuàng)建日志目錄并賦予權(quán)限

shell> mkdir /var/log/mysql
shell> chown mysql.mysql /var/log/mysql

重啟服務(wù)

shell> systemctl restart mysqld

注意：

如果省略server-id（或?qū)⑵滹@式設(shè)置為默認(rèn)值0），則主服務(wù)器拒絕來(lái)自從服務(wù)器的任何連接。

為了在使用帶事務(wù)的InnoDB進(jìn)行復(fù)制設(shè)置時(shí)盡可能提高持久性和一致性，
您應(yīng)該在master my.cnf文件中使用以下配置項(xiàng)：
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

確保未在復(fù)制主服務(wù)器上啟用skip-networking選項(xiàng)。
如果已禁用網(wǎng)絡(luò)，則從站無(wú)法與主站通信，并且復(fù)制失敗。

2. 應(yīng)該創(chuàng)建一個(gè)專(zhuān)門(mén)用于復(fù)制數(shù)據(jù)的用戶(hù)

每個(gè)從站使用MySQL用戶(hù)名和密碼連接到主站，因此主站上必須有用戶(hù)帳戶(hù)，從站可以使用該帳戶(hù)進(jìn)行連接。

master 上的任何賬戶(hù)都可以用于復(fù)制的操作，前提是必須先被授權(quán)。

創(chuàng)建一個(gè)僅具有復(fù)制過(guò)程權(quán)限的單獨(dú)帳戶(hù)，以最大程度地降低對(duì)其他帳戶(hù)的危害。

例如，要使用新用戶(hù) repl 可以從任何主機(jī)上連接到 master 上進(jìn)行復(fù)制操作, 并且用戶(hù) repl 僅可以使用復(fù)制的權(quán)限。

在 master 上執(zhí)行如下操作

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> 

3. 在從服務(wù)器上使用剛才的用戶(hù)進(jìn)行測(cè)試連接

shell> mysql -urepl -p'123' -hmysql-master1

下面的操作根據(jù)如下情況繼續(xù)

主服務(wù)器中有數(shù)據(jù)

• 如果在啟動(dòng)復(fù)制之前有現(xiàn)有數(shù)據(jù)需要與從屬設(shè)備同步，請(qǐng)保持客戶(hù)端正常運(yùn)行，以便鎖定保持不變。這可以防止進(jìn)行任何進(jìn)一步的更改，以便復(fù)制到從站的數(shù)據(jù)與主站同步。

1. 在主服務(wù)器中導(dǎo)出先有的數(shù)據(jù)

如果主數(shù)據(jù)庫(kù)包含現(xiàn)有數(shù)據(jù)，則必須將此數(shù)據(jù)復(fù)制到每個(gè)從站。有多種方法可以實(shí)現(xiàn)：

要選擇轉(zhuǎn)儲(chǔ)數(shù)據(jù)庫(kù)的適當(dāng)方法，請(qǐng)?jiān)谝韵逻x項(xiàng)之間進(jìn)行選擇：

• 使用mysqldump工具創(chuàng)建要復(fù)制的所有數(shù)據(jù)庫(kù)的轉(zhuǎn)儲(chǔ)。這是推薦的方法，尤其是在使用時(shí) InnoDB。

• 如果數(shù)據(jù)庫(kù)存儲(chǔ)在二進(jìn)制可移植文件中，則可以將原始數(shù)據(jù)文件復(fù)制到從屬數(shù)據(jù)庫(kù)。這比使用mysqldump并在每個(gè)slave上導(dǎo)入文件更有效，因?yàn)樗鼤?huì)INSERT在重放語(yǔ)句時(shí)省略更新索引的開(kāi)銷(xiāo) 。InnoDB 不建議這樣做。

shell> mysqldump  -u用戶(hù)名  -p密碼  --all-databases  --master-data=1 > dbdump.db

這里的用戶(hù)是本機(jī)服務(wù)器的用戶(hù)

如果不使用 --master-data，則需要手動(dòng)鎖定單獨(dú)會(huì)話(huà)中的所有表。

2. 從主服務(wù)器中使用 scp 或 rsync 等工具，把備份出來(lái)的數(shù)據(jù)傳輸?shù)綇姆?wù)器中。

在主服務(wù)中執(zhí)行如下命令

scp  dbdump.db root@mysql-slave1:/root/

這里的 mysql-slave1 需要能在主服務(wù)器中 ping 通。

3. 配置從服務(wù)器，并重啟
   在從服務(wù)器上編輯其配置文件 my.cnf 并添加如下內(nèi)容：

// my.cnf 文件
[mysqld]
server-id=2

4. 導(dǎo)入數(shù)據(jù)到從服務(wù)器，并配置連接到主服務(wù)器的相關(guān)信息

在從服務(wù)器上操作

方式一：

導(dǎo)入數(shù)據(jù)

shell> mysql < /root/fulldb.dump

在從服務(wù)器上配置連接到主服務(wù)器的相關(guān)信息

進(jìn)入從服務(wù)執(zhí)行如下操作：

mysql> CHANGE MASTER TO
MASTER_HOST='mysql-master1',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;

注意上面的 mysql-bin.000001 是你 mysqldump 后的備份文件中出現(xiàn)的文件名。
而 154 也是需要參照 mysqldump 文件中的內(nèi)容。
比如：

cat  /root/fulldb.dump
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;

方式二：

/*導(dǎo)入數(shù)據(jù)*/
mysql> source   /root/fulldb.dump

前提是mysqldump 時(shí)，使用 --master-data=1, 這樣備份完成的文件中就會(huì)出現(xiàn)如下內(nèi)容：

...略... 

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;

...略...

```mysql
mysql> CHANGE MASTER TO
MASTER_HOST='mysql-master1',
MASTER_USER='repl',
MASTER_PASSWORD='123',

5. 啟動(dòng)從服務(wù)器的復(fù)制線(xiàn)程以開(kāi)始復(fù)制

mysql> start slave;
Query OK, 0 rows affected (0.09 sec)

檢查是否成功

在從服務(wù)上執(zhí)行如下操作

mysql> show slave status\G

輸出結(jié)果中應(yīng)該看到 I/O 線(xiàn)程和 SQL 線(xiàn)程都是 YES, 就表示成功。

執(zhí)行此過(guò)程后，在主服務(wù)上操作的修改數(shù)據(jù)的操作都會(huì)在從服務(wù)器中執(zhí)行一遍，這樣就保證了數(shù)據(jù)的一致性。

image

image

將新的服務(wù)器加入，變?yōu)閺姆?wù)器

和上面的步驟一樣，但是新加入的服務(wù)器的server-id 的值不能和現(xiàn)有都服務(wù)器 server-id 的值一樣。

假如在新加入從服務(wù)器之前，主服務(wù)器執(zhí)行了刪除庫(kù)的操作。
并且，刪除的庫(kù)剛好是在第一次 mysqldump 備份時(shí)的數(shù)據(jù)中。
就會(huì)出現(xiàn)問(wèn)題，在從服務(wù)器上提示報(bào)錯(cuò)沒(méi)有這個(gè)數(shù)據(jù)庫(kù)；

主服務(wù)器中無(wú)數(shù)據(jù)

主服務(wù)器中設(shè)置

1. my.cnf配置文件

[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1

創(chuàng)建日志目錄并賦予權(quán)限

shell> mkdir /var/log/mysql
shell> chown mysql.mysql /var/log/mysql

重啟服務(wù)

從服務(wù)器設(shè)置

1. my.cnf配置文件

[mysqld]
server-id=3

重啟服務(wù)

2. 查看主服務(wù)器的二進(jìn)制日志的名稱(chēng)

通過(guò)使用命令行客戶(hù)端連接到主服務(wù)器來(lái)啟動(dòng)主服務(wù)器上的會(huì)話(huà)，并通過(guò)執(zhí)行以下FLUSH TABLES WITH READ LOCK語(yǔ)句來(lái)刷新所有表和阻止寫(xiě)語(yǔ)句：

mysql> mysql> FLUSH TABLES WITH READ LOCK;

mysql> show master status \G
****************** 1\. row ****************
             File: mysql-bin.000001
         Position: 0
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

2. 在從服務(wù)器的 mysql 中執(zhí)行如下語(yǔ)句

mysql> CHANGE MASTER TO
MASTER_HOST='mysql-master1',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=0;

mysql> start slave;

查看

在master上執(zhí)行show binlog events命令，可以看到第一個(gè)binlog文件的內(nèi)容。

mysql> show binlog events\G
*************************** 1\. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 107
       Info: Server ver: 5.5.28-0ubuntu0.12.10.2-log, Binlog ver: 4
*************************** 2\. row ***************************
   Log_name: mysql-bin.000001
        Pos: 107
 Event_type: Query
  Server_id: 1
End_log_pos: 181
       Info: create user rep
*************************** 3\. row ***************************
   Log_name: mysql-bin.000001
        Pos: 181
 Event_type: Query
  Server_id: 1
End_log_pos: 316
       Info: grant replication slave on *.* to rep identified by '123456'
3 rows in set (0.00 sec)

• Log_name 是二進(jìn)制日志文件的名稱(chēng)，一個(gè)事件不能橫跨兩個(gè)文件
• Pos 這是該事件在文件中的開(kāi)始位置
• Event_type 事件的類(lèi)型，事件類(lèi)型是給slave傳遞信息的基本方法，每個(gè)新的binlog都以Format_desc類(lèi)型開(kāi)始，以Rotate類(lèi)型結(jié)束
• Server_id 創(chuàng)建該事件的服務(wù)器id
• End_log_pos 該事件的結(jié)束位置，也是下一個(gè)事件的開(kāi)始位置，因此事件范圍為Pos~End_log_pos - 1
• Info 事件信息的可讀文本，不同的事件有不同的信息

在從站上暫停復(fù)制

您可以使用STOP SLAVE和 START SLAVE語(yǔ)句停止并啟動(dòng)從站上的復(fù)制 。

要停止從主服務(wù)器處理二進(jìn)制日志，請(qǐng)使用 STOP SLAVE：

mysql> STOP SLAVE;

當(dāng)復(fù)制停止時(shí)，從I / O線(xiàn)程停止從主二進(jìn)制日志讀取事件并將它們寫(xiě)入中繼日志，并且SQL線(xiàn)程停止從中繼日志讀取事件并執(zhí)行它們。您可以通過(guò)指定線(xiàn)程類(lèi)型單獨(dú)暫停I / O或SQL線(xiàn)程：

mysql> STOP SLAVE IO_THREAD;
mysql> STOP SLAVE SQL_THREAD;

要再次開(kāi)始執(zhí)行，請(qǐng)使用以下START SLAVE語(yǔ)句：

mysql> START SLAVE;

要啟動(dòng)特定線(xiàn)程，請(qǐng)指定線(xiàn)程類(lèi)型：

mysql> START SLAVE IO_THREAD;
mysql> START SLAVE SQL_THREAD;

復(fù)制原理實(shí)現(xiàn)細(xì)節(jié)

MySQL復(fù)制功能使用三個(gè)線(xiàn)程實(shí)現(xiàn)，一個(gè)在主服務(wù)器上，兩個(gè)在從服務(wù)器上：

• Binlog轉(zhuǎn)儲(chǔ)線(xiàn)程 主設(shè)備創(chuàng)建一個(gè)線(xiàn)程，以便在從設(shè)備連接時(shí)將二進(jìn)制日志內(nèi)容發(fā)送到從設(shè)備?？梢?a target="_blank">SHOW PROCESSLIST在主服務(wù)器的輸出中將此線(xiàn)程標(biāo)識(shí)為Binlog Dump線(xiàn)程。

  二進(jìn)制日志轉(zhuǎn)儲(chǔ)線(xiàn)程獲取主機(jī)二進(jìn)制日志上的鎖，用于讀取要發(fā)送到從機(jī)的每個(gè)事件。一旦讀取了事件，即使在事件發(fā)送到從站之前，鎖也會(huì)被釋放。

• 從屬 I/O線(xiàn)程 在從屬服務(wù)器上發(fā)出 START SLAVE 語(yǔ)句時(shí)，從屬服務(wù)器會(huì)創(chuàng)建一個(gè) I/O 線(xiàn)程，該線(xiàn)程連接到主服務(wù)器并要求主服務(wù)器發(fā)送其在二進(jìn)制日志中的更新記錄。

  從屬 I/O線(xiàn)程讀取主Binlog Dump線(xiàn)程發(fā)送的更新 （請(qǐng)參閱上一項(xiàng)）并將它們復(fù)制到包含從屬中繼日志的本地文件。

  此線(xiàn)程的狀態(tài)顯示為 Slave_IO_running輸出 SHOW SLAVE STATUS或 Slave_running輸出中的狀態(tài)SHOW STATUS。

• 從屬SQL線(xiàn)程 從屬設(shè)備創(chuàng)建一個(gè)SQL線(xiàn)程來(lái)讀取由從屬 I/O 線(xiàn)程寫(xiě)入的中繼日志，并執(zhí)行其中包含的事件。

• 當(dāng)從屬服務(wù)器從放的事件，追干上主服務(wù)器的事件后，從屬服務(wù)器的 I/O 線(xiàn)程將會(huì)處于休眠狀態(tài)，直到主服務(wù)器的事件有更新時(shí)，被主服務(wù)器發(fā)送的信號(hào)喚醒。

在前面的描述中，每個(gè)主/從連接有三個(gè)線(xiàn)程。具有多個(gè)從站的主站為每個(gè)當(dāng)前連接的從站創(chuàng)建一個(gè)二進(jìn)制日志轉(zhuǎn)儲(chǔ)線(xiàn)程，每個(gè)從站都有自己的I / O和SQL線(xiàn)程。

從站使用兩個(gè)線(xiàn)程將讀取更新與主站分開(kāi)并將它們執(zhí)行到獨(dú)立任務(wù)中。因此，如果語(yǔ)句執(zhí)行緩慢，則不會(huì)減慢讀取語(yǔ)句的任務(wù)。例如，如果從服務(wù)器尚未運(yùn)行一段時(shí)間，則當(dāng)從服務(wù)器啟動(dòng)時(shí)，其I / O線(xiàn)程可以快速?gòu)闹鞣?wù)器獲取所有二進(jìn)制日志內(nèi)容，即使SQL線(xiàn)程遠(yuǎn)遠(yuǎn)落后。如果從服務(wù)器在SQL線(xiàn)程執(zhí)行了所有獲取的語(yǔ)句之前停止，則I / O線(xiàn)程至少已獲取所有內(nèi)容，以便語(yǔ)句的安全副本本地存儲(chǔ)在從屬的中繼日志中，準(zhǔn)備在下次執(zhí)行時(shí)執(zhí)行奴隸開(kāi)始。

該SHOW PROCESSLIST語(yǔ)句提供的信息可以告訴您主服務(wù)器和從服務(wù)器上有關(guān)復(fù)制的信息。有關(guān)主狀態(tài)的信息，請(qǐng)參見(jiàn)第8.14.4節(jié)“復(fù)制主線(xiàn)程狀態(tài)”。有關(guān)從站狀態(tài)，請(qǐng)參見(jiàn)第8.14.5節(jié)“復(fù)制從站I / O線(xiàn)程狀態(tài)”和 第8.14.6節(jié)“復(fù)制從站SQL線(xiàn)程狀態(tài)”。

以下示例說(shuō)明了三個(gè)線(xiàn)程如何顯示在輸出中SHOW PROCESSLIST。

在主服務(wù)器上，輸出SHOW PROCESSLIST如下所示：

mysql> SHOW PROCESSLIST\G
*************************** 1\. row ***************************
     Id: 2
   User: root
   Host: localhost:32931
     db: NULL
Command: Binlog Dump
   Time: 94
  State: Has sent all binlog to slave; waiting for binlog to
         be updated
   Info: NULL

這里，線(xiàn)程2是Binlog Dump為連接的從屬服務(wù)的復(fù)制線(xiàn)程。該 State信息表明所有未完成的更新已發(fā)送到從站，并且主站正在等待更多更新發(fā)生。如果Binlog Dump在主服務(wù)器上看不到任何 線(xiàn)程，則表示復(fù)制未運(yùn)行; 也就是說(shuō)，目前沒(méi)有連接任何從站。

在從屬服務(wù)器上，輸出SHOW PROCESSLIST如下所示：

mysql> SHOW PROCESSLIST\G
*************************** 1\. row ***************************
     Id: 10
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 11
  State: Waiting for master to send event
   Info: NULL
*************************** 2\. row ***************************
     Id: 11
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 11
  State: Has read all relay log; waiting for the slave I/O
         thread to update it
   Info: NULL

該State信息指示線(xiàn)程10是與主服務(wù)器通信的I / O線(xiàn)程，并且線(xiàn)程11是處理存儲(chǔ)在中繼日志中的更新的SQL線(xiàn)程。在 SHOW PROCESSLIST運(yùn)行時(shí)，兩個(gè)線(xiàn)程都處于空閑狀態(tài)，等待進(jìn)一步更新。

Time列中 的值可以顯示從站與主站進(jìn)行比較的時(shí)間。請(qǐng)參見(jiàn) 第A.13節(jié)“MySQL 5.7 FAQ：復(fù)制”。如果主站側(cè)有足夠的時(shí)間在Binlog Dump線(xiàn)程上沒(méi)有活動(dòng)，則主站確定從站不再連接。對(duì)于任何其他客戶(hù)端連接，這樣做的超時(shí)取決于的值 net_write_timeout和 net_retry_count; 有關(guān)這些的更多信息，請(qǐng)參見(jiàn)第5.1.7節(jié)“服務(wù)器系統(tǒng)變量”。

該SHOW SLAVE STATUS語(yǔ)句提供有關(guān)從屬服務(wù)器上的復(fù)制處理的其他信息。請(qǐng)參見(jiàn) 第16.1.7.1節(jié)“檢查復(fù)制狀態(tài)”。

基于事務(wù)的 Replication

就是利用 GTID 來(lái)實(shí)現(xiàn)的復(fù)制

GTID（全局事務(wù)標(biāo)示符）最初由google實(shí)現(xiàn)，在MySQL 5.6中引入.GTID在事務(wù)提交時(shí)生成，由UUID和事務(wù)ID組成.uuid會(huì)在第一次啟動(dòng)MySQL時(shí)生成，保存在數(shù)據(jù)目錄下的auto .CNF文件里，事務(wù)ID則從1開(kāi)始自增使用GTID的好處主要有兩點(diǎn)：

1. 不再需要指定傳統(tǒng)復(fù)制中的master_log_files和master_log_pos，使主從復(fù)制更簡(jiǎn)單可靠
2. 可以實(shí)現(xiàn)基于庫(kù)的多線(xiàn)程復(fù)制，減小主從復(fù)制的延遲

實(shí)驗(yàn)環(huán)境要求： 5.7.6 以上版本

主庫(kù)配置

[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1
gtid_mode=ON
enforce_gtid_consistency=1   # 強(qiáng)制執(zhí)行GTID一致性。

重啟服務(wù)

其他和之前的一樣

• 創(chuàng)建專(zhuān)屬用戶(hù)并授權(quán)
• 假如有數(shù)據(jù)導(dǎo)出數(shù)據(jù)

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> 

從庫(kù)配置

測(cè)試用戶(hù)有效性

shell> mysql -urepl -p'123' -hmysql-master1

[mysqld]
server-id=2
gtid_mode=ON
enforce_gtid_consistency=1

# 可選項(xiàng), 把連接到 master 的信息存到數(shù)據(jù)庫(kù)中的表中
master-info-repository=TABLE
relay-log-info-repository=TABLE

重啟服務(wù)

假如有數(shù)據(jù)，先導(dǎo)入數(shù)據(jù)

mysql> source dump.db

Mysql 終端執(zhí)行連接信息

mysql> CHANGE MASTER TO
MASTER_HOST='172.16.153.10',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_AUTO_POSITION=1;

> start slave;

開(kāi)啟 GTID 后的導(dǎo)出導(dǎo)入數(shù)據(jù)的注意點(diǎn)

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events

意思是： 當(dāng)前數(shù)據(jù)庫(kù)實(shí)例中開(kāi)啟了 GTID 功能, 在開(kāi)啟有 GTID 功能的數(shù)據(jù)庫(kù)實(shí)例中, 導(dǎo)出其中任何一個(gè)庫(kù), 如果沒(méi)有顯示地指定--set-gtid-purged參數(shù), 都會(huì)提示這一行信息. 意思是默認(rèn)情況下, 導(dǎo)出的庫(kù)中含有 GTID 信息, 如果不想導(dǎo)出包含有 GTID 信息的數(shù)據(jù)庫(kù), 需要顯示地添加--set-gtid-purged=OFF參數(shù).

mysqldump -uroot  -p  --set-gtid-purged=OFF   --all-databases > alldb.db

導(dǎo)入數(shù)據(jù)是就可以相往常一樣導(dǎo)入了。

配置多線(xiàn)程復(fù)制

多線(xiàn)程復(fù)制在 5.6 中被引入，并且在 5.7 中得到了進(jìn)一步的完善。

5.7 中是基于邏輯時(shí)鐘的方式進(jìn)行的多線(xiàn)程復(fù)制。

配置過(guò)程：

1. 先在從庫(kù)上查看默認(rèn)的多線(xiàn)程復(fù)制類(lèi)型

mysql> show variables like "slave_parallel_type";
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| slave_parallel_type | DATABASE |
+---------------------+----------+
1 row in set (0.01 sec)

mysql>

2. 接著在從庫(kù)上停止目前正在運(yùn)行復(fù)制鏈路

停止之前可以查看目前的線(xiàn)程數(shù)

show processlist;

mysql> stop slave

3. 配置并發(fā)線(xiàn)程的方式

mysql> set global slave_parallel_type = "logical_clock";
Query OK, 0 rows affected (0.00 sec)

4. 配置并發(fā)數(shù)量

mysql> set global slave_parallel_workers = 4;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "slave_parallel_workers";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 4     |
+------------------------+-------+
1 row in set (0.00 sec)

5. 啟動(dòng)從服務(wù)器的復(fù)制鏈路

mysql> start slave

關(guān)于復(fù)制的架構(gòu)（擴(kuò)展）

1. 主主復(fù)制

image

上圖中，Master-Master復(fù)制的兩臺(tái)服務(wù)器，既是master，又是另一臺(tái)服務(wù)器的slave。這樣，任何一方所做的變更，都會(huì)通過(guò)復(fù)制應(yīng)用到另外一方的數(shù)據(jù)庫(kù)中。在這種復(fù)制架構(gòu)中，各自上運(yùn)行的不是同一db，比如左邊的是db1,右邊的是db2，db1的從在右邊反之db2的從在左邊，兩者互為主從，再輔助一些監(jiān)控的服務(wù)還可以實(shí)現(xiàn)一定程度上的高可以用。

2. 主動(dòng)—被動(dòng)模式的Master-Master(Master-Master in Active-Passive Mode)

image

上圖中，這是由master-master結(jié)構(gòu)變化而來(lái)的，它避免了M-M的缺點(diǎn)，實(shí)際上，這是一種具有容錯(cuò)和高可用性的系統(tǒng)。它的不同點(diǎn)在于其中只有一個(gè)節(jié)點(diǎn)在提供讀寫(xiě)服務(wù)，另外一個(gè)節(jié)點(diǎn)時(shí)刻準(zhǔn)備著，當(dāng)主節(jié)點(diǎn)一旦故障馬上接替服務(wù)。比如通過(guò)corosync+pacemaker+drbd+MySQL就可以提供這樣一組高可用服務(wù)，主備模式下再跟著slave服務(wù)器，也可以實(shí)現(xiàn)讀寫(xiě)分離。

3. 帶從服務(wù)器的Master-Master結(jié)構(gòu)(Master-Master with Slaves)

image

這種結(jié)構(gòu)的優(yōu)點(diǎn)就是提供了冗余。在地理上分布的復(fù)制結(jié)構(gòu)，它不存在單一節(jié)點(diǎn)故障問(wèn)題，而且還可以將讀密集型的請(qǐng)求放到slave上。

半同步機(jī)制（擴(kuò)展）

MySQL-5.5 及以上支持半同步復(fù)制
早前的MySQL復(fù)制只能是基于異步來(lái)實(shí)現(xiàn)，從MySQL-5.5開(kāi)始，支持半自動(dòng)復(fù)制。在以前的異步（asynchronous）復(fù)制中，主庫(kù)在執(zhí)行完一些事務(wù)后，是不會(huì)管備庫(kù)的進(jìn)度的。如果備庫(kù)處于落后，而更不幸的是主庫(kù)此時(shí)又出現(xiàn)Crash（例如宕機(jī)），這時(shí)備庫(kù)中的數(shù)據(jù)就是不完整的。簡(jiǎn)而言之，在主庫(kù)發(fā)生故障的時(shí)候，我們無(wú)法使用備庫(kù)來(lái)繼續(xù)提供數(shù)據(jù)一致的服務(wù)了。Semisynchronous Replication(半同步復(fù)制)則一定程度上保證提交的事務(wù)已經(jīng)傳給了至少一個(gè)備庫(kù)。Semi synchronous中，僅僅保證事務(wù)的已經(jīng)傳遞到備庫(kù)上，但是并不確保已經(jīng)在備庫(kù)上執(zhí)行完成了。

此外，還有一種情況會(huì)導(dǎo)致主備數(shù)據(jù)不一致。在某個(gè)session中，主庫(kù)上提交一個(gè)事務(wù)后，會(huì)等待事務(wù)傳遞給至少一個(gè)備庫(kù)，如果在這個(gè)等待過(guò)程中主庫(kù)Crash，那么也可能備庫(kù)和主庫(kù)不一致，這是很致命的。如果主備網(wǎng)絡(luò)故障或者備庫(kù)掛了，主庫(kù)在事務(wù)提交后等待10秒（rpl_semi_sync_master_timeout的默認(rèn)值）后，就會(huì)繼續(xù)。這時(shí)，主庫(kù)就會(huì)變回原來(lái)的異步狀態(tài)。

MySQL在加載并開(kāi)啟Semi-sync插件后，每一個(gè)事務(wù)需等待備庫(kù)接收日志后才返回給客戶(hù)端。如果做的是小事務(wù)，兩臺(tái)主機(jī)的延遲又較小，則Semi-sync可以實(shí)現(xiàn)在性能很小損失的情況下的零數(shù)據(jù)丟失。

image

關(guān)于主從復(fù)制的更多參數(shù)

官網(wǎng)： https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html

加密復(fù)制

官網(wǎng)：https://dev.mysql.com/doc/refman/5.7/en/replication-solutions-encrypted-connections.html

主服務(wù)器

創(chuàng)建 CA 證書(shū)和私鑰 公鑰

shell> mysql_ssl_rsa_setup

My.cnf 文件配置項(xiàng)

以下的情況是用 yum 安裝 mysql 的情況

[mysqld]
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

選項(xiàng)如下：

• --ssl-ca：證書(shū)頒發(fā)機(jī)構(gòu)（CA）證書(shū)文件的路徑名。（--ssl-capath類(lèi)似但指定CA證書(shū)文件目錄的路徑名。）

  • --ssl-cert：服務(wù)器公鑰證書(shū)文件的路徑名。可以將其發(fā)送到客戶(hù)端，并根據(jù)其擁有的CA證書(shū)進(jìn)行身份驗(yàn)證。

  • --ssl-key：服務(wù)器私鑰文件的路徑名。

從服務(wù)器配置

首先要保證從服務(wù)器的 sql 線(xiàn)程和 io 線(xiàn)程處于關(guān)閉狀態(tài)

mysql> stop slave;
mysql> stop slave sql_thread;

mysql> CHANGE MASTER TO
    -> MASTER_HOST='master_hostname',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='password',
    -> MASTER_SSL=1,
    -> MASTER_SSL_CA = 'ca_file_name',
    -> MASTER_SSL_CAPATH = 'ca_directory_name',
    -> MASTER_SSL_CERT = 'cert_file_name',
    -> MASTER_SSL_KEY = 'key_file_name';
mysql> START SLAVE;

關(guān)于復(fù)制用戶(hù)

全新創(chuàng)建

mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password'
    -> REQUIRE SSL;
mysql> GRANT REPLICATION SLAVE ON *.*
    -> TO 'repl'@'%.example.com';

給原來(lái)的用戶(hù)添加 REQUIRE SSL

mysql> ALTER USER 'repl'@'%.example.com' REQUIRE SSL;

二進(jìn)制的日志的自動(dòng)刪除

Mysql 終端中設(shè)置

不用重啟服務(wù)

下面的命令是只保留 10 天內(nèi)的日志，就是10天前的全部刪除

mysql> set global expire_logs_days = 10;

當(dāng)二進(jìn)制日志的大小達(dá)到max_binlog_size系統(tǒng)變量的值時(shí)，將刷新二進(jìn)制日志 。

max_binlog_size

如果對(duì)二進(jìn)制日志的寫(xiě)入導(dǎo)致當(dāng)前日志文件大小超過(guò)此變量的值，則服務(wù)器將輪轉(zhuǎn)二進(jìn)制日志（關(guān)閉當(dāng)前文件并打開(kāi)下一個(gè)文件）。最小值為4096字節(jié)。最大值和默認(rèn)值為1GB。

事務(wù)在一個(gè)塊中寫(xiě)入二進(jìn)制日志，因此它永遠(yuǎn)不會(huì)在幾個(gè)二進(jìn)制日志之間拆分。因此，如果您有大事務(wù)，您可能會(huì)看到大于的二進(jìn)制日志文件max_binlog_size。

如果max_relay_log_size為0，則該值也 max_binlog_size適用于中繼日志。

配置文件中設(shè)置

此方法需要重啟服務(wù)

[mysqld]
expire_logs_days=10

在 MySQL 終端中手動(dòng)刪除

--清除MySQL-bin.010日志
mysql> PURGE MASTER LOGS TO 'MySQL-bin.010';

--清除2008-06-22 13:00:00前binlog日志
mysql> PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';   

--清除3天前binlog日志BEFORE，變量的date自變量可以為'YYYY-MM-DD hh:mm:ss'格式。
mysql> PURGE MASTER LOGS BEFORE DATE_SUB( NOW(), INTERVAL 3 DAY);