MySQL主從復(fù)制

MySQL Replication

主從復(fù)制（也稱 AB 復(fù)制）允許將來自一個(gè)MySQL數(shù)據(jù)庫服務(wù)器（主服務(wù)器）的數(shù)據(jù)復(fù)制到一個(gè)或多個(gè)MySQL數(shù)據(jù)庫服務(wù)器（從服務(wù)器）。
==默認(rèn)情況下，復(fù)制是異步的 從站不需要永久連接以接收來自主站的更新。==
根據(jù)配置，您可以復(fù)制數(shù)據(jù)庫中的所有數(shù)據(jù)庫，所選數(shù)據(jù)庫甚至選定的表。
MySQL中復(fù)制的優(yōu)點(diǎn)包括：

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

Replication 的原理

image.png

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

  1. 主服務(wù)器上面的任何修改都會通過自己的 I/O tread(I/O 線程)保存在二進(jìn)制日志 Binary log 里面。
  2. 從服務(wù)器上面也啟動一個(gè) I/O thread，通過配置好的用戶名和密碼, 連接到主服務(wù)器上面請求讀取二進(jìn)制日志，然后把讀取到的二進(jìn)制日志寫到本地的一個(gè)Realy log（中繼日志）里面。
  3. 從服務(wù)器上面同時(shí)開啟一個(gè) SQL thread 定時(shí)檢查 Realy log(這個(gè)文件也是二進(jìn)制的)，如果發(fā)現(xiàn)有更新立即把更新的內(nèi)容在本機(jī)的數(shù)據(jù)庫上面執(zhí)行一遍。

每個(gè)從服務(wù)器都會收到主服務(wù)器二進(jìn)制日志的全部內(nèi)容的副本。
從服務(wù)器設(shè)備負(fù)責(zé)決定應(yīng)該執(zhí)行二進(jìn)制日志中的哪些語句。
除非另行指定，否則主從二進(jìn)制日志中的所有事件都在從站上執(zhí)行。
如果需要，您可以將從服務(wù)器配置為僅處理一些特定數(shù)據(jù)庫或表的事件。

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

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

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

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

一主多從

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


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

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

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

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

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

SUPER 權(quán)限的用戶可以使用SET sql_log_bin=0語句禁用其當(dāng)前環(huán)境下自己的語句的二進(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ù)器拒絕來自從服務(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ò)，則從站無法與主站通信，并且復(fù)制失敗。
  1. 應(yīng)該創(chuàng)建一個(gè)專門用于復(fù)制數(shù)據(jù)的用戶
    每個(gè)從站使用MySQL用戶名和密碼連接到主站，因此主站上必須有用戶帳戶，從站可以使用該帳戶進(jìn)行連接。

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

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

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

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

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql>
  1. 在從服務(wù)器上使用剛才的用戶進(jìn)行測試連接
shell> mysql -urepl -p'123' -hmysql-master1

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. 配置從服務(wù)器，并重啟
    在從服務(wù)器上編輯其配置文件 my.cnf 并添加如下內(nèi)容：
// my.cnf 文件
[mysqld]
server-id=2
  1. 導(dǎo)入數(shù)據(jù)到從服務(wù)器，并配置連接到主服務(wù)器的相關(guān)信息
    在從服務(wù)器上操作

方式一：

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

shell> mysql -uroot -p'密碼' < /root/dump.db

在從服務(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; #此POS需要根據(jù)自己的master POS進(jìn)行配置

mysql> show master status\G   用此命令查看master POS

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

cat  /root/dump.db
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
方式二：
/*導(dǎo)入數(shù)據(jù)*/
mysql> source   /root/dump.db

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

...略... 

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


...略...

mysql> CHANGE MASTER TO
MASTER_HOST='mysql-master1',
MASTER_USER='repl',
MASTER_PASSWORD='123',
  1. 啟動從服務(wù)器的復(fù)制線程以開始復(fù)制
mysql> start slave;
Query OK, 0 rows affected (0.09 sec)

檢查是否成功

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

mysql> show slave status\G
image.png

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

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

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

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

主服務(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ù)

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

通過使用命令行客戶端連接到主服務(wù)器來啟動主服務(wù)器上的會話，并通過執(zhí)行以下FLUSH TABLES WITH READ LOCK語句來刷新所有表和阻止寫語句：

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)
  1. 在從服務(wù)器的 mysql 中執(zhí)行如下語句
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)制日志文件的名稱，一個(gè)事件不能橫跨兩個(gè)文件
  • Pos 這是該事件在文件中的開始位置
  • Event_type 事件的類型，事件類型是給slave傳遞信息的基本方法，每個(gè)新的binlog都以Format_desc類型開始，以Rotate類型結(jié)束
  • Server_id 創(chuàng)建該事件的服務(wù)器id
  • End_log_pos 該事件的結(jié)束位置，也是下一個(gè)事件的開始位置，因此事件范圍為Pos~End_log_pos - 1
  • Info 事件信息的可讀文本，不同的事件有不同的信息

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

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

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

mysql> STOP SLAVE;

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

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

要再次開始執(zhí)行，請使用以下START SLAVE語句：

mysql> START SLAVE;

要啟動特定線程，請指定線程類型：

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

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

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

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

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

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

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

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

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

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

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

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

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

以下示例說明了三個(gè)線程如何顯示在輸出中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

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

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

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

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

了解

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

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

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

[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)建專屬用戶并授權(quán)
假如有數(shù)據(jù)導(dǎo)出數(shù)據(jù)

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

從庫配置
測試用戶有效性

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

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

# 可選項(xiàng), 把連接到 master 的信息存到數(shù)據(jù)庫中的表中
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;

配置多線程復(fù)制

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

image

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

配置多線程復(fù)制

image
  1. 先在從庫上查看默認(rèn)的多線程復(fù)制類型
mysql> show variables like "slave_parallel_type";
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| slave_parallel_type | DATABASE |
+---------------------+----------+
1 row in set (0.01 sec)

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

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

show processlist;


mysql> stop slave
  1. 配置并發(fā)線程的方式
mysql> set global slave_parallel_type = "logical_clock";
Query OK, 0 rows affected (0.00 sec)
  1. 配置并發(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)
  1. 啟動從服務(wù)器的賦值鏈路
mysql> start slave

關(guān)于復(fù)制的架構(gòu)

  1. 主主復(fù)制

image

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

  1. 主動—被動模式的Master-Master(Master-Master in Active-Passive Mode)

image

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

  1. 帶從服務(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)故障問題，而且還可以將讀密集型的請求放到slave上。

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

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

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

MySQL在加載并開啟Semi-sync插件后，每一個(gè)事務(wù)需等待備庫接收日志后才返回給客戶端。如果做的是小事務(wù)，兩臺主機(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 證書和私鑰 公鑰

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：證書頒發(fā)機(jī)構(gòu)（CA）證書文件的路徑名。（--ssl-capath類似但指定CA證書文件目錄的路徑名。）

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

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

從服務(wù)器配置

首先要保證從服務(wù)器的 sql 線程和 io 線程處于關(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ù)制用戶

全新創(chuàng)建

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

給原來的用戶添加 REQUIRE SSL

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

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

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

屬性 值
命令行格式 --max-binlog-size=#
系統(tǒng)變量 max_binlog_size
范圍 全局
動態(tài) 是
類型 整數(shù)
默認(rèn)值 1073741824
最低價(jià)值 4096
最大價(jià)值 1073741824

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

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

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

配置文件中設(shè)置

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

[mysqld]
expire_logs_days=10

在 MySQL 終端中手動刪除

--清除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);
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容