MySQL主從複製之非同步複製

原文： https://www.cnblogs.com/hmwh/p/9198705.html

MYSQL主從複製方式有預設的複製方式非同步複製，5.5版本之後半同步複製，5.6版本之後新增GTID複製，包括5.7版本的多源複製。

MYSQL版本：5.7.20

作業系統版本：linux 6.7 64bit

 

1、非同步複製

 

MYSQL 預設的複製方式，就是主庫寫入binlog日誌後即可成功返回客戶端，無須等待binlog日誌傳遞給從庫的過程。但這樣一旦主庫發生當機，就有可能出現資料丟失的情況。

 

1.1搭建非同步主從

1、 server-id 不一樣

2、 開啟binlog,建議開啟log_slave_updates,讓從庫也寫binlog，方便後期擴充套件架構

3、 binlog格式為row。

 

主庫操作：

建立主從複製賬號

create user 'rep'@'192.16.20.%' identified by 'mysql';

 

grant replication slave on *.* to 'rep'@'192.16.20.%';

 

 

初始化：

mysqldump -S /tmp/mysql3307.sock --single-transaction -uroot -pmysql --master-data=2 -A > salve.sql （我全庫匯入的有問題，只用的test庫）

 

注意：必須加引數 –master-data=2,讓備份出來的檔案中記錄備份這一刻binlog檔案與position號，為搭建主從環境做準備。檢視備份檔案中記錄的當前binlog檔案和position號。

 

scp salve.sql 172.16.20.21:/binlogbak

 

 

mysql -S /tmp/mysql3307.sock -uroot -pmysql < slave_test.sql

 

 

在資料庫命令列執行配置主從命令。

 

change master to

    master_host='172.16.20.32',

    master_user='rep',

    master_password='mysql',

    master_port=3307,

    master_log_file='mysql-binlog.000010',

    MASTER_LOG_POS=797;

 

start slave;

stop slave;

reset slave all; 清空從庫的所有配置資訊。

 

start slave;

 

當前從庫I/O和SQL thread都是呈現Yes狀態，代表從庫上面操作已經完成了。

 

 

Master_Log_File= Relay_Master_Log_File;

Read_Master_Log_Pos= Exec_Master_Log_Pos

證明目前沒有主從延遲狀態。

 

Slave_IO_Running：從庫上I/O thread 負責請求和接收主庫傳遞來的binlog資訊。

Slave_SQL_Running：從庫上SQL thread負責應用relay中的binlog的資訊。

 

1.2主從複製故障處理

1 、主從故障之主鍵衝突，錯誤程式碼為1062

 

原因：由於誤操作，從從庫上執行寫操作，導致再在主庫上執行相同的操作，由於主鍵衝突，主從複製狀態會報錯。所以生產環境建議在從庫上開啟read only,避免在從庫執行寫操作。

 

在主庫上建表t1;

現在從庫插入資料，後面再在主庫上插入相同的語句。

主庫:

 

 

從庫：

 

 

主庫執行相同語句：

 

 

從庫報錯如下：

 

 

報錯程式碼：

Last_Errno: 1062

處理辦法：

 

利用percona-toolkit 工具：

mount /dev/sr0 /mnt

yum -y install perl-DBD-MySQL

./pt-slave-restart -S /tmp/mysql3307.sock -uroot -pmysql

 

 

在檢視主從狀態，已經恢復正常：

 

 

2 、主從故障之主庫更新資料，從庫找不到而報錯，錯誤程式碼為10032

上一個錯誤是主從都有相同的資料，我們可以直接透過percona-toolkit工具跳過錯誤。但如果從庫上少資料，就不能跳過錯誤了，需要找到缺少的資料，在從庫上從新執行一遍。

故障原因：由於誤操作，在從庫上執行delete 刪除操作，導致主從資料不一致。這時再在主庫執行同條資料的更新操作，由於從庫沒有該資料，SQL無法再從庫上實現。

 

模擬故障：

先在從庫伺服器的test庫下的t表中，執行delete刪除語句操作。

 

再在主庫上執行：相同資料的update更新操作。

 

 

從庫報錯如下：

 

 

報錯程式碼1032。

解決辦法：

 

根據報錯資訊所知道Binlog檔案和position（7153）號，在主庫上，透過mysqlbinlog 命令，找到在主庫上執行的那條SQL語句導致的主從報錯。

 

/usr/local/mysql5.7/bin/mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /mydata/mysql/mysql3307/logs/mysql-binlog.000010 |grep -A 10 7153

 

 

 

insert into t2 values(1,'bbb');

生產上如果丟失數以萬計條的資料，建議重新搭建主從確保資料一致性。

 

 

從庫跳過錯誤：

./pt-slave-restart -S /tmp/mysql3307.sock -uroot -pmysql

 

 

重新同步成功：

show slave status\G;

 

 

1.3主從故障之主從server-id一致

由於粗心，安裝的時候用模板並沒有修改servier-id。（修改從庫server-id成不同的值）

 

1.4主從故障之跨庫操作，丟失資料

原因：在主庫中設定binlog-do-db引數，使用的binlog記錄格式為statement模式，導致在主庫上執行跨庫操作時，從庫沒有複製成功，丟失資料。

 

故障操作描述：

在主庫的引數檔案中新增binlog-do-db=test，代表只複製zs這個庫，並且主庫binlog_format 設定為statement。

---

---

目前很多公司中的生產環境中都使用了MySQL Replication ，也叫 MySQL 複製，搭建配置方便等很多特性讓 MySQL Replication 的應用很廣泛，我們曾經使用過一主拖20多個從庫來分擔業務壓力。關於 MySQL Replication 的文章網路上也有很多，但大多數都是講如何搭建MySQL Replication，並沒有說清楚如何才能搭建出高可靠的MySQL Replication。這篇文章也對半同步複製，無損複製，多源複製做了講解。

複製有哪些用途

• 讀寫分離

• 災備

• 高可用

• 線下統計

• 備份

複製是如何工作的

從上圖中可以看到，複製的主要步驟：

1. master 將改變記錄到二進位制日誌 binary log 中

2. slave 上的IO執行緒將主庫上的日誌複製到自己的 relay log 中

3. slave 上SQL執行緒回放中繼日誌的內容，使 slave 上的資料與 master 達到一致

binlog 二進位制日誌檔案，用於記錄 MySQL 的資料變更。

relay-log 中繼日誌檔案，slave 的I/O執行緒讀取 master 的 binlog，記錄到 relay-log 中，然後 SQL 執行緒會讀取 relay-log 日誌的內容並應用到 slave 伺服器。

binlog 記錄的格式

STATEMENT（記錄操作的SQL語句）

• 優點 減少了 binlog 日誌量，節約IO，提高效能，易於理解

• 缺點 不是所有的DML語句都能被複制，有些函式UUID() 、FOUND_ROWS()、USER() 也無法被複制

ROW（記錄操作的每一行資料的變化資訊，RC 隔離級別，必須是 row 格式）

• 優點 任何情況都可以被複制，ROW 模式是最安全可靠的

• 缺點 產生大量的日誌，特別是 copy data 的 DDL 會讓日誌暴漲

• 建議表一定要有主鍵

MIXED （混合模式）

• 先使用 STATEMENT 模式記錄 binlog ，對於 STATEMENT 模式無法複製的操作使用 ROW 模式儲存 binlog，MySQL 會根據執行的 SQL 語句選擇日誌記錄方式。Bug 較多，不建議使用。

binlog Events

我們都知道　binlog 日誌用於記錄所有對 MySQL 的操作的變更，而這每一個變更都會對應的事件，也就是 Event，index檔案記錄了所有的 binlog 位置，每個 binlog 會有 header event，rotate 三個 event，binlog 的結構如下。

常見的Event如下：

• Format_desc：全新的binlog日誌檔案

• Rotate ：日誌分割

• Table_map：表，列等後設資料

• Query：查詢

• Write_rows: 插入

• Update_rows：更新

• Delete_rows：刪除

在瞭解了以上基礎的內容後，我們可以帶著以下的三個問題去學習複製到底是怎樣工作的。

• 事務是如何提交的？事務提交先寫 binlog 還是 redo log？

• 為什麼 MySQL 有 binlog，還有redo log?

• 如何保證這兩部分的日誌做到一致性？

事務是如何提交的？事務提交先寫 binlog 還是 redo log？

以上的圖片中可以看到，事務的提交主要分三個主要步驟：

1. InnoDB 層寫 prepare log，此時SQL已經成功執行，並生成 xid 資訊及 redo 和 undo 的記憶體日誌，寫入 redo log file

2. MySQL Server 層寫 binlog （write --》 fsync）

3. InnoDB 層寫 commit log， InnoDB儲存引擎內提交，使 undo 和 redo 永久寫入磁碟

為什麼 MySQL 有 binlog，還有 redo log?

這個是因為 MySQL 體系結構的原因，MySQL 是多儲存引擎的，不管使用那種儲存引擎，都會有 binlog，而不一定有 redo log， 簡單的說，binlog 是 MySQL Server 層的，redo log 是 InnoDB 層的。

如何保證這兩部分的日誌做到一致性？

事務提交的過程上面已經說到，需要寫 redo log 還要寫 binlog，那麼如何保證資料的一致性呢，如果不能保證寫這兩個檔案在同一 事務中，那麼就會造成資料不一致，這個不一致包括MySQL crash時和主從複製的資料不一致。

面試時經常會問的一個問題，影響MySQL寫入效能、資料一致性的引數有哪些？無疑是  雙一引數  innodb_flush_log_at_trx_commit 和 sync_binlog， 這兩個引數是控制 MySQL 磁碟寫入策略以及資料安全性的關鍵引數，MySQL 為了保證 master 和 slave 的資料一致性，就必須保證 binlog 和 InnoDB redo 日誌的一致性。 

引數說明如下：

innodb_flush_log_at_trx_commit（redo）

• 0 log buffer 每秒一次地寫入 log file 中，且進行 flush 操作

• 1 每次事務提交時都會把 log buffer 的資料寫入 log file，並進行 flush 操作

• 2 每次事務提交時 MySQL 都會把 log buffer 的資料寫入 log file，不進行 flush 操作

sync_binlog （binlog）

• 0 重新整理binlog_cache中的資訊到磁碟由os決定

• N 每N次事務提交重新整理binlog_cache中的資訊到磁碟

那麼如何保證 binlog 和 InnoDB redo 日誌的一致性，MySQL 使用 內部分散式事物來保證一致性，MySQL 在 prepare 階段會生成 xid，xid 會寫入prepare log中，也會寫入到binlog中，當恢復時會對比此事務的xid在兩個檔案中是否都有，如果都存在該xid對應的事物會提交，反之會rollback此事務，下面是幾種情況分析：

1. 當事務在 prepare 階段 crash，將該事務 rollback。

2. 當事務在 binlog 階段 crash，此時日誌還沒有成功寫入到磁碟中，啟動時會 rollback 此事務

3. 當事務在 binlog 日誌已經 fsync() 到磁碟後 crash，但是InnoDB沒有來得及 commit,此時 MySQL 啟動時會重新將該事務重做並 commit，使 InnoDB 儲存引擎的 redo log 和 binlog 始終保持一致。

總結起來說就是如果一個事物在 prepare log 階段中落盤成功，並在 MySQL Server 層中的 binlog 也寫入成功，那這個事務必定commit成功。

組提交

現在回頭看事務是如何提交的那張圖，會發現innodb層每個事務是並行的，但是在寫binlog時，MySQL Server層就變成了序列，這是因為每次提交都會去申請prepare_commit_mutex這把鎖造成的，在MySQL 5.6版本中，提供了Binary Log Group Commit 也就是組提交，Group Commit分為了三個階段。

• Flush stage：Leader執行緒遍歷FLUSH_STAGE連結串列，寫入binary log快取

• Sync stage ：將binlog快取sync到磁碟，當sync_binlog=1時所有該佇列事務的二進位制日誌快取永久寫入磁碟

• Commit stage: leader根據順序讓InnoDB儲存引擎完成Commit

下面是我們測試組提交的一張圖，可以看到組提交的TPS高不少。

1062、1053複製錯誤

可能DBA在使用MySQL Replication的過程中，在slave 當機或異常時，會遇到1062等錯誤，大家都是使用以下方式解決。

• SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1

• GTID 透過空事務方式

但為什麼資料會衝突呢？我們分解下複製的步驟，這裡有張圖很好，圖片來自於網路。

我們可以看到這裡有儲存Replication matadata的兩個 檔案，

• relay-info.log 儲存了SQL執行緒回放到的Relay_log_name和Relay_log_pos，以及對應的Master的Master_log_name和Master_log_pos。

• master-info.log儲存了連線master的使用者，密碼，埠，Master_log_name 和 Master_log_pos等資訊。

如下圖，如果SQL執行緒正在回放，回放完後，還沒來的及寫到Replication matadata的檔案中，就當機了，此時重啟slave後，就會出現1062錯誤。

在MySQL 5.6中，提供了SQL/IO thread crash-safe特性。透過將relay_log_info_repository=TABLE，relay-info將資訊寫入到mysql.slave_relay_log_info這張表中，不但可以保證一致性（寫檔案變成同一事物的原子操作），還提高了寫入效能。

如上圖。IO thread同理，稍有不同的是 relay-log-recover 設定為1後，slave 的 IO thread 讀取 events 時，會根據從 SQL thread 回放到的位置重新讀取。

複製最優的引數配置

上面講了這麼多，其實就是得出 MySQL Replication 的最可靠的引數配置。

master：

binlog_format = 
ROW 

transaction-isolation = 
READ-COMMITTE

Dexpire_logs_days = 

30 

server-id = 

327 

sync_binlog = 

1 

innodb_flush_log_at_trx_commit = 

1 

innodb_support_xa = 
1

slave：

log_slave_updates=

1 

server-id = 

328 

relay_log_recover = 

1 

relay_log_info_repository = 
TABLE 

master_info_repository = 
TABLE

read_only = 
1

如何提高複製效率？

MySQL 5.6提供了並行複製,但是這種並行只是基於database的。如果是基於單database的依然無法做到真正的並行回放，這個階段很多DBA將資料庫進行垂直拆分，將一個database拆分成幾個database，透過設定slave_parallel_workers=n，可以進行database級別的並行複製，但對於熱點業務複製延遲依然無法解決。

MySQL 5.6版本中還引入了GTID，不但降低了主從failover時，尋找filename，position的難度，更是加入到了組提交中，這也造就了MySQL 5.7版本中的Multi-Threaded Slave的出現。如下圖，一組中的事務，可以並行回放。

在下圖的測試中，MySQL 5.7的多執行緒複製極大的提升了延遲效率，在30個執行緒併發操作的時候還能保證平均延遲5.9秒左右，而單執行緒複製的延遲率基本一直在上升。

Multi-Threaded Slave 相關引數

slave-parallel-type= DATABASE /LOGICAL_CLOCK
-
- 
DATABASE -- 基於庫級別的並行複製 與
5.6相同

-
- 
LOGICAL_CLOCK -- 邏輯時鐘，主上怎麼並行執行，從上怎麼回放。



slave-parallel-workers=16  
-
- 並行複製的執行緒數



slave_preserve_commit_order=1 
-
-commit的順序保持一致

半同步

我們都知道，預設的 MySQL Replication 複製為非同步模式，非同步也就說明會有丟失資料的可能性，MySQL在5.5版本中提供了 semi-sync replication，也就是半同步，但半同步只能說減少資料丟失的風險，所以在 MySQL 5.7版本中，MySQL 提供了 lossless semi-sync replication，也就是無損複製，可最低限度的減少資料丟失（無損複製會和半同步一樣在出問題時會切換為非同步複製）。

在半同步中，至少有一個Slave節點收到binlog後再返回，不能完全避免資料丟失，超時後，切回非同步複製。在事物提交的過程中，在InnoDB層的 commit log 階段後，Master 節點需要收到至少一個Slave節點回復的ACK後，才能繼續下一個事物。

無損複製

在無損複製中，master把binlog傳送給slave，只有在slave把binlog寫到本地的relay-log裡，master才會將事務提交到儲存引擎層，然後把請求返回給客戶端，客戶端才可以看見剛才提交的事務。在一個事物提交的過程中，在MySQL Server 層的 binlog階段後，Master節點需要收到至少一個Slave節點回復的ACK後，才能繼續下一個事物。

半同步複製與無損複製的對比

ACK的時間點不同

• 半同步複製在InnoDB層的Commit Log後，等待ACK。

• 無損複製在MySQL Server層的Write binlog後，等待ACK。

主從資料一致性

• 半同步複製意味著在Master節點上，這個剛剛提交的事物對資料庫的修改，對其他事物是可見的。

• 無損複製在write binlog完成後，就傳輸binlog，但還沒有去寫commit log，意味著當前這個事物對資料庫的修改，其他事物也是不可見的。

半同步相關引數

rpl_semi_sync_master_enabled=
1

rpl_semi_sync_slave_enabled=
1

rpl_semi_sync_master_timeout=
1000

rpl_semi_sync_master_wait_for_slave_count=
1

rpl_semi_sync_master_wait_point=
AFTER_SYNC

rpl_semi_sync_master_wait_for_slave_count=
1

半同步相關事件統計

Rpl_semi_sync_master_tx_avg_wait_time
-
-開啟
Semi_sync，平均需要額外等待的時間

Rpl_semi_sync_master_net_avg_wait_time
-
-事務進入等待佇列後，到網路平均等待時間Semi-sync的網路消耗有多大。
Rpl_semi_sync_master_status
-
- 則表示當前
Semi-sync是否正常工作

Rpl_semi_sync_master_no_times
-
-可以知道一段時間內，
Semi-sync是否有超時失敗過，記錄了失敗次數。

multi-source

然而在MySQL 5.7版本中，提供了多源複製，多源複製的出現對於分庫分表的業務提供了極大的便利，目前我們已經部署了多套多源複製供統計使用。

如上圖，多源複製採用多通道的模式，和普通的複製相比，就是使用FOR CHANNEL進行了分離。

CHANGE MASTER TO .... FOR CHANNEL ‘m1';
CHANGE MASTER TO .... FOR CHANNEL ‘m2';

上面我們也說到，為了提高複製效率，很多DBA會根據業務進行DB拆分，但拆分後又面臨一個新的問題，就是join，join絕對是關係型資料庫中最常用一個特性，然而在分散式的環境中，join是最難解決的一個問題，使用多源複製就能很好的解決這個問題。

如果資料庫，表名一致如何使用多源複製？，其實只要解決了資料衝突的問題就可以使用。

如上圖的分庫分表架構，可以使用以下引數實現奇偶插入的方式去解決。

auto_increment_offset=

1…n

auto_increment_increment=
n

但這種方式需要提前考慮擴充套件性。

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub、部落格園、CSDN和個人微 信公眾號（ xiaomaimiaolhr）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： ........................................................................................................................ ● QQ群號： 230161599（滿） 、618766405 ● 微 信群：可加我微 信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ），註明新增緣由 ● 於 2019-07-01 06:00 ~ 2019-07-31 24:00 在西安完成 ● 最新修改時間：2019-07-01 06:00 ~ 2019-07-31 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（ xiaomaimiaolhr）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。 ........................................................................................................................