Linux的企業-Mysql主從同步，Gtid，半同步，相關參數（1）

一.Mysql主從同步

MySQL 支援單向、異步複制,複制過程中一個伺服器充當主伺服器,而一個或多個其它伺服器充

當從伺服器。主伺服器将更新寫入二進制日志檔案,并維護檔案的一個索引以跟蹤日志循環。這

些日志可以記錄發送到從伺服器的更新。當一個從伺服器連接配接主伺服器時,它通知主伺服器從服

務器在日志中讀取的最後一次成功更新的位置。從伺服器接收從那時起發生的任何更新,然後封

鎖并等待主伺服器通知新的更新。

請注意當你進行複制時,所有對複制中的表的更新必須在主伺服器上進行。否則,你必須要小心,

以避免使用者對主伺服器上的表進行的更新與對從伺服器上的表所進行的更新之間的沖突。

單向複制有利于健壯性、速度和系統管理:

1. 主伺服器/從伺服器設定增加了健壯性。主伺服器出現問題時,你可以切換到從伺服器作為備份

2. 通過在主伺服器和從伺服器之間切分處理客戶查詢的負荷,可以得到更好的客戶響應時間。

SELECT 查詢可以發送到從伺服器以降低主伺服器的查詢處理負荷。但修改

資料的語句仍然應發送到主伺服器,以便主伺服器和從伺服器保持同步。如果非更新查詢為主,該

負載均衡政策很有效,但一般是更新查詢。

3. 使用複制的另一個好處是可以使用一個從伺服器執行備份,而不會幹擾主伺服器。在備份過程

中主伺服器可以繼續處理更新。

MySQL 提供了資料庫的同步功能,這對我們實作資料庫的冗災、備份、恢複、負載均衡等都是

有極大幫助的。

<a href="https://s5.51cto.com/wyfs02/M01/07/D0/wKiom1nQ2Guh6xWZAAQCxQbWqrU520.png" target="_blank"></a>

二.配置環境

server2 主 172.25.29.2

server3 從 172.25.29.3

1.配置server2

<a href="https://s2.51cto.com/wyfs02/M00/A6/80/wKioL1nQrP2Tt1oDAAAb5QNZOIk717.png" target="_blank"></a>

<a href="https://s2.51cto.com/wyfs02/M01/07/CE/wKiom1nQrULjmtcmAABHalQTwYY899.png" target="_blank"></a>

<a href="https://s2.51cto.com/wyfs02/M01/A6/80/wKioL1nQrP6QEu-dAABR5pwoBMQ295.png" target="_blank"></a>

log-bin=mysql-bin  啟動二進制日志系統

binlog-do-db=test #二進制需要同步的資料庫名,如果需要同步多個庫,例如要再同步 westos

庫,再添加一行“binlog-do-db=westos”,以此類推

server-id=1

#必須為 1 到 232–1 之間的一個正整數值

binlog-ignore-db=mysql #禁止同步 mysql 資料庫

<a href="https://s2.51cto.com/wyfs02/M00/07/CE/wKiom1nQrUOBY_rrAABgu-LQgDk292.png" target="_blank"></a>

進入到mysql中建立授權使用者，檢視master資訊

<a href="https://s2.51cto.com/wyfs02/M02/07/CE/wKiom1nQrUOD33g_AABU5t0p9mY570.png" target="_blank"></a>

<a href="https://s4.51cto.com/wyfs02/M00/07/CE/wKiom1nQrUWhnHvHAABGfXgvCCg882.png" target="_blank"></a>

2.配置server3

配置檔案隻需寫上id号即可

<a href="https://s4.51cto.com/wyfs02/M02/A6/80/wKioL1nQrP-Thj1YAAA8DywF05o166.png" target="_blank"></a>

重新開機服務，将server2設定為主，注意log_file檔案和log_pso檔案位置，在server2上看

<a href="https://s4.51cto.com/wyfs02/M01/07/CE/wKiom1nQrUSzSs3yAABSz8uIFJY738.png" target="_blank"></a>

<a href="https://s4.51cto.com/wyfs02/M02/A6/80/wKioL1nQrP_jmKnKAABbif81KP8533.png" target="_blank"></a>

啟動從資料庫主機，檢視狀态

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如果都是 yes,表示從庫的 I/O,Slave_SQL 線程都正确開啟.表明資料庫正在同步

<a href="https://s4.51cto.com/wyfs02/M00/A6/80/wKioL1nQrQDwb5ldAACSz7c0eAc357.png" target="_blank"></a>

檢視同步的資料，顯示正常

<a href="https://s4.51cto.com/wyfs02/M02/07/CE/wKiom1nQrUWCRPlsAABS3qhg9Mo433.png" target="_blank"></a>

三.Gtid的設定

全局事務辨別：global transaction identifiers。GTID是一個事務一一對應，并且全局唯一ID。一個GTID在一個伺服器上隻執行一次，避免重複執行導緻資料混亂或者主從不一緻。

GTID用來代替傳統複制方法，不再使用MASTER_LOG_FILE+MASTER_LOG_POS開啟複制。而是使用MASTER_AUTO_POSTION=1的方式開始複制。MySQL-5.6.5開始支援的，MySQL-5.6.10後開始完善。在傳統的slave端，binlog是不用開啟的，但是在GTID中slave端的binlog是必須開啟的，目的是記錄執行過的GTID（強制）。

優勢：

更簡單的實作failover，不用以前那樣在需要找log_file和log_pos。更簡單的搭建主從複制。比傳統的複制更加安全。GTID是連續的沒有空洞的，保證資料的一緻性，零丢失。

工作原理：

（1）當一個事務在主庫端執行并送出時，産生GTID，一同記錄到binlog日志中。

（2）binlog傳輸到slave,并存儲到slave的relaylog後，讀取這個GTID的這個值設定gtid_next變量，即告訴Slave，下一個要執行的GTID值。

（3）sql線程從relay log中擷取GTID，然後對比slave端的binlog是否有該GTID。

（4）如果有記錄，說明該GTID的事務已經執行，slave會忽略。

（5）如果沒有記錄，slave就會執行該GTID事務，并記錄該GTID到自身的binlog，

   在讀取執行事務前會先檢查其他session持有該GTID，確定不被重複執行。

（6）在解析過程中會判斷是否有主鍵，如果沒有就用二級索引，如果沒有就用全部掃描。

1.安裝高版本5.7.19的mysql

   低版本不支援gtid

   删除之前的低版本的mysql檔案

   安裝完成後先配置好主從配置

   配置好server2,server3

<a href="https://s5.51cto.com/wyfs02/M02/07/CF/wKiom1nQs8ujatMJAABew9T4XdY341.png" target="_blank"></a>

<a href="https://s5.51cto.com/wyfs02/M01/A6/80/wKioL1nQs4biRBeuAABfv4CqUFE157.png" target="_blank"></a>

<a href="https://s4.51cto.com/wyfs02/M01/A6/80/wKioL1nQtFbw7Oq3AAEU9pOhnXc838.png" target="_blank"></a>

<a href="https://s4.51cto.com/wyfs02/M01/07/CF/wKiom1nQtJuiLg03AABM6EpjY0E213.png" target="_blank"></a>

<a href="https://s1.51cto.com/wyfs02/M02/07/CF/wKiom1nQtJziUrLPAAAkKJrm4QI955.png" target="_blank"></a>

<a href="https://s1.51cto.com/wyfs02/M00/A6/80/wKioL1nQtFfiX-e0AAA0lcTJLkI939.png" target="_blank"></a>

<a href="https://s1.51cto.com/wyfs02/M02/07/CF/wKiom1nQtJyzpN4TAAArrbObbic832.png" target="_blank"></a>

<a href="https://s1.51cto.com/wyfs02/M02/A6/80/wKioL1nQtFig0IPXAAD5xzzB110534.png" target="_blank"></a>

<a href="https://s1.51cto.com/wyfs02/M00/07/CF/wKiom1nQtJ3RF0ArAACUUSaE08s204.png" target="_blank"></a>

完成主從複制的配置

2.配置Gtid

配置server1 vim /etc/my.cnf

<a href="https://s2.51cto.com/wyfs02/M00/A6/81/wKioL1nQzrLhQIQ3AAAt8_BKNC8830.png" target="_blank"></a>

<a href="https://s2.51cto.com/wyfs02/M02/07/D0/wKiom1nQzvfRgNoZAAAVYt8hoJA934.png" target="_blank"></a>

<a href="https://s3.51cto.com/wyfs02/M01/A6/81/wKioL1nQzrPADXSOAAA0rSMtm0Y818.png" target="_blank"></a>

登陸server3上的mysql建立server2主節點

<a href="https://s3.51cto.com/wyfs02/M00/07/D0/wKiom1nQzvjRkNIFAACbhoLNdn8139.png" target="_blank"></a>

啟動從服務，檢視從狀态

<a href="https://s3.51cto.com/wyfs02/M00/A6/81/wKioL1nQzrOwsg_RAACq6Qx0Nmg251.png" target="_blank"></a>

3.測試

在server2上建立資料

<a href="https://s3.51cto.com/wyfs02/M02/A6/81/wKioL1nQzrPT4YSRAABaPASpp8g774.png" target="_blank"></a>

<a href="https://s3.51cto.com/wyfs02/M01/07/D0/wKiom1nQzvjwNrvzAABeS_D7jco352.png" target="_blank"></a>

在server3上檢視server2上的資料

<a href="https://s1.51cto.com/wyfs02/M01/A6/81/wKioL1nQzrPiiM5sAAB03su4dnQ008.png" target="_blank"></a>

檢視從機的gtid更新表，已經有更新記錄

<a href="https://s3.51cto.com/wyfs02/M01/07/D0/wKiom1nQzvnDpmHdAABRtvl5AHY174.png" target="_blank"></a>

4.開啟多線程并發複制

slave-parallel-type

slave-parallel-workers

<a href="https://s1.51cto.com/wyfs02/M01/A6/81/wKioL1nQ1ALCPmgVAAA3ZU_0QBc828.png" target="_blank"></a>

<a href="https://s1.51cto.com/wyfs02/M02/A6/81/wKioL1nQ1ALiOyS9AABCslugbIQ816.png" target="_blank"></a>

重新開機後檢視show processlist程序，顯示16

<a href="https://s1.51cto.com/wyfs02/M02/07/D0/wKiom1nQ1EegsbaNAACzkbUnsk8733.png" target="_blank"></a>

<a href="https://s1.51cto.com/wyfs02/M01/07/D0/wKiom1nQ1EeD3_fEAACsdzKeWho837.png" target="_blank"></a>

number of workers 為16

四.半同步 半同步主要是保證資料完整性防止資料丢失

1.半同步複制概念

       在說明半同步複制之前我們先來了解一下，什麼是同步複制？同步複制：同步複制可以定義為資料在同一時刻被送出到一台或多台機器，通常這是通過衆所周知的“兩階段送出”做到的。雖然這确實給你在多系統中保持一緻性，但也由于增加了額外的消息交換而造成性能下降。使用MyISAM或者InnoDB存儲引擎的MySQL本身并不支援同步複制，然而有些技術，例如分布式複制塊裝置（簡稱DRBD），可以在下層的檔案系統提供同步複制，允許第二個MySQL伺服器在主伺服器丢失的情況下接管（使用第二伺服器的複本）。了解了同步複制我們正下面來說一下，什麼是半同步複制？

       MYSQL 5.5開始，支援半自動複制。之前版本的MySQL Replication都是異步（asynchronous）的，主庫在執行完一些事務後，是不會管備庫的進度的。如果備庫不幸落後，而更不幸的是主庫此時又出現Crash（例如當機），這時備庫中的資料就是不完整的。簡而言之，在主庫發生故障的時候，我們無法使用備庫來繼續提供資料一緻的服務了。Semisynchronous Replication(半同步複制)則一定程度上保證送出的事務已經傳給了至少一個備庫。Semi synchronous中，僅僅保證事務的已經傳遞到備庫上，但是并不確定已經在備庫上執行完成了。

       此外，還有一種情況會導緻主備資料不一緻。在某個session中，主庫上送出一個事務後，會等待事務傳遞給至少一個備庫，如果在這個等待過程中主庫Crash，那麼也可能備庫和主庫不一緻，這是很緻命的。如果主備網絡故障或者備庫挂了，主庫在事務送出後等待10秒（rpl_semi_sync_master_timeout的預設值）後，就會繼續。這時，主庫就會變回原來的異步狀态。

MySQL在加載并開啟Semi-sync插件後，每一個事務需等待備庫接收日志後才傳回給用戶端。如果做的是小事務，兩台主機的延遲又較小，則Semi-sync可以實作在性能很小損失的情況下的零資料丢失。

異步與半同步異同

       預設情況下MySQL的複制是異步的，Master上所有的更新操作寫入Binlog之後并不確定所有的更新都被複制到Slave之上。異步操作雖然效率高，但是在Master/Slave出現問題的時候，存在很高資料不同步的風險，甚至可能丢失資料。

       MySQL5.5引入半同步複制功能的目的是為了保證在master出問題的時候，至少有一台Slave的資料是完整的。在逾時的情況下也可以臨時轉入異步複制，保障業務的正常使用，直到一台salve追趕上之後，繼續切換到半同步模式。

2.在主機server2上開啟半同步

添加半同步插件

<a href="https://s1.51cto.com/wyfs02/M01/A6/81/wKioL1nQ1dSBSqvOAABEM0CcejI673.png" target="_blank"></a>

<a href="https://s1.51cto.com/wyfs02/M01/07/D0/wKiom1nQ1hnDK89oAAA11abI0c8088.png" target="_blank"></a>

檢視半同步狀态為OFF

       開啟半同步

<a href="https://s1.51cto.com/wyfs02/M00/A6/81/wKioL1nQ1dSyLgHCAAEWjzp8Z1k773.png" target="_blank"></a>

3.在主機server3上開啟半同步

<a href="https://s4.51cto.com/wyfs02/M02/07/D0/wKiom1nQ1hrT1-raAAA3bjt3x0c970.png" target="_blank"></a>

<a href="https://s4.51cto.com/wyfs02/M02/07/D0/wKiom1nQ1hrxGJOeAABYjvpLUG0703.png" target="_blank"></a>

4.在主機server3上重新開機mysql的IO接口正常

<a href="https://s4.51cto.com/wyfs02/M01/A6/81/wKioL1nQ1dWglWeSAAA0_Vh9JE0334.png" target="_blank"></a>

5.測試半同步

<a href="https://s3.51cto.com/wyfs02/M02/07/D0/wKiom1nQ2JWDi8xlAABBcfG2REI213.png" target="_blank"></a>

主機半同步狀态開啟

<a href="https://s3.51cto.com/wyfs02/M01/A6/81/wKioL1nQ2FDC0MpKAAD-_xP3Ss4578.png" target="_blank"></a>

主機建立資料很快同步到從機server3上

<a href="https://s3.51cto.com/wyfs02/M00/07/D0/wKiom1nQ2JaQahdQAADsg4-S6cg589.png" target="_blank"></a>

檢視從機半同步狀态開啟

關閉server3的IO接口

<a href="https://s3.51cto.com/wyfs02/M01/A6/81/wKioL1nQ2FGQNHBwAABKcngkuXQ340.png" target="_blank"></a>

在主機server2上插入資料

<a href="https://s3.51cto.com/wyfs02/M02/07/D0/wKiom1nQ2JaQd_aIAAAo-BrqBCE828.png" target="_blank"></a>

等待10s半同步後，server3無響應，server2轉為異步傳輸

主機已經有了資料

<a href="https://s3.51cto.com/wyfs02/M02/A6/81/wKioL1nQ2FHTAaAYAABStYBKbIk194.png" target="_blank"></a>

檢視從機無剛才主機server2上插入的資料

再次啟動IO接口，資料傳同步過來

<a href="https://s3.51cto.com/wyfs02/M02/A6/81/wKioL1nQ2FGRCo7wAABzON4aFdg216.png" target="_blank"></a>

檢視從機的半同步狀态

<a href="https://s3.51cto.com/wyfs02/M01/07/D0/wKiom1nQ2JbwomcQAADNa285_Es916.png" target="_blank"></a>

6.半同步的永久設定

在server2配置檔案中添加半同步選項開啟

vim /etc.my.cnf

<a href="https://s3.51cto.com/wyfs02/M02/07/D1/wKiom1nQ-EGSjcKPAAAv86HuwiE831.png" target="_blank"></a>

在server3上也開啟，并開啟隻讀模式

<a href="https://s3.51cto.com/wyfs02/M02/A6/83/wKioL1nQ9_yxWhOmAABn35AWl4Q336.png" target="_blank"></a>

server2

10000ms為半同步的等待時間，逾時後變為異步模式

<a href="https://s1.51cto.com/wyfs02/M02/A6/83/wKioL1nQ9_2jfZjpAABt-v0E34Q445.png" target="_blank"></a>

server3的半同步也已經設定為自動開啟

            隻讀模式自動開啟

<a href="https://s1.51cto.com/wyfs02/M01/07/D1/wKiom1nQ-ELjDvHHAAB-92NVTpQ922.png" target="_blank"></a>

7.expire_logs_days表示保留時間

<a href="https://s1.51cto.com/wyfs02/M00/07/D1/wKiom1nQ-ELiLiO4AAA5EBxiplM025.png" target="_blank"></a>

<a href="https://s1.51cto.com/wyfs02/M00/A6/83/wKioL1nQ9_2g9rk5AAA8SsRjUmU159.png" target="_blank"></a>

8.慢查詢

慢查詢已經開啟

<a href="https://s1.51cto.com/wyfs02/M02/A6/83/wKioL1nQ9_7gp7bHAABYfdVCyp8577.png" target="_blank"></a>

選擇一個sleep子產品，時間設定為預設的10秒

<a href="https://s4.51cto.com/wyfs02/M01/07/D1/wKiom1nQ-ESC-DAhAAAgyp3dFYk905.png" target="_blank"></a>

檢視慢查詢狀态為有一個慢查詢

<a href="https://s4.51cto.com/wyfs02/M00/A6/83/wKioL1nQ9_-gUDlXAAAsajKGJdM804.png" target="_blank"></a>

通過慢查詢日志

<a href="https://s4.51cto.com/wyfs02/M00/07/D1/wKiom1nQ-ESweNzbAADzAYF83cM259.png" target="_blank"></a>

<a href="https://s4.51cto.com/wyfs02/M02/07/D1/wKiom1nQ-ESCbO3UAAA4OA-uwXY107.png" target="_blank"></a>

<a href="https://s4.51cto.com/wyfs02/M01/A6/83/wKioL1nQ9__S6iyGAABfCMcvMWI794.png" target="_blank"></a>

      本文轉自一百個小排 51CTO部落格，原文連結：http://blog.51cto.com/anfishr/1970102，如需轉載請自行聯系原作者