MySQL-13mysql主從復(fù)制問題

大家好，本片文章看一下主從復(fù)制的問題，
1. 主從復(fù)制復(fù)習(xí)
2. 主從復(fù)制數(shù)據(jù)一致性
3. 主從復(fù)制延遲問題
4. 主從復(fù)制總結(jié)

1. 主從復(fù)制復(fù)習(xí)

1.1 什么是主從復(fù)制?

MySQL 主從復(fù)制是指數(shù)據(jù)可以從一個MySQL數(shù)據(jù)庫服務(wù)器主節(jié)點復(fù)制到一個或多個從節(jié)點。MySQL 默認(rèn) 采用異步復(fù)制方式，這樣從節(jié)點不用一直訪問主服務(wù)器來更新自己的數(shù)據(jù)，數(shù)據(jù)的更新可以在遠(yuǎn)程連接上 進(jìn)行，從節(jié)點可以復(fù)制 主數(shù)據(jù)庫中的所有數(shù)據(jù)庫或者特定的數(shù)據(jù)庫，或者特定的表。

主要用途

1. 讀寫分離
2. 數(shù)據(jù)實時備份，當(dāng)系統(tǒng)中某個節(jié)點發(fā)生故障時，可以方便的故障切換
3. 高可用HA
4. 架構(gòu)擴展

隨著系統(tǒng)中業(yè)務(wù)訪問量的增大，如果是單機部署數(shù)據(jù)庫，就會導(dǎo)致I/O訪問頻率過高。有了主從復(fù) 制，增加多個數(shù)據(jù)存儲節(jié)點，將負(fù)載分布在多個從節(jié)點 上，降低單機磁盤I/O訪問的頻率，提高單個機器的I/O性能。

1.2 主從復(fù)制原理

MySQL主從復(fù)制涉及到三個線程，一個運行在主節(jié)點(log dump thread)，其余兩個(I/O thread, SQL thread)運行在從節(jié)點


主從圖.png
  • 1,主節(jié)點 binary log dump 線程

當(dāng)從節(jié)點連接主節(jié)點時，主節(jié)點會創(chuàng)建一個log dump 線程，用于發(fā)送bin-log的內(nèi)容。在讀取bin-log中的 操作時，此線程會對主節(jié)點上的bin-log加鎖，當(dāng)讀取完成，甚至在發(fā)動給從節(jié)點之前，鎖會被釋放。

    1. 從節(jié)點I/O線程

當(dāng)從節(jié)點上執(zhí)行 start slave 命令之后，從節(jié)點會創(chuàng)建一個I/O線程用來連接主節(jié)點，請求主庫中更新的bin-log。I/O線程接收到主節(jié)點binlog dump 進(jìn)程發(fā)來的更新之后，保存在本地relay-log中。

    1. 從節(jié)點SQL線程

SQL線程負(fù)責(zé)讀取relay log中的內(nèi)容，解析成具體的操作并執(zhí)行，最終保證主從數(shù)據(jù)的一致性。對于每一個 主從連接，都需要三個進(jìn)程來完成。當(dāng)主節(jié)點有多個從節(jié)點時，主節(jié)點會為每一個當(dāng)前連接的從節(jié)點建一 個binary log dump 進(jìn)程，而每個從節(jié)點都有自己的I/O進(jìn)程，SQL進(jìn)程。從節(jié)點用兩個線程將從主庫拉取 更新和執(zhí) 行分成獨立的任務(wù)，這樣在執(zhí)行同步數(shù)據(jù)任務(wù)的時候，不會降低讀操作的性能。比如，如果從節(jié) 點沒有運行，此時I/O進(jìn)程可以很快從主節(jié)點獲取更新，盡管SQL進(jìn)程還沒有執(zhí)行。如果在SQL進(jìn)程執(zhí)行之 前從節(jié)點服務(wù)停止，至少I/O進(jìn) 程已經(jīng)從主節(jié)點拉取到了最新的變更并且保存在本地relay日志中，當(dāng)服務(wù)再 次起來之后，就可以完成數(shù)據(jù)的同步。

主從復(fù)制的步驟

1. 主庫將所有的寫操作記錄在binlog日志中，并生成log dump線程，將binlog日志傳給從庫的I/O線程
2. 從庫生成兩個線程，一個是I/O線程，另一個是SQL線程
3. I/O線程去請求主庫的binlog日志，并將binlog日志中的文件寫入relay log(中繼日志)中
4. SQL線程會讀取relay loy中的內(nèi)容，并解析成具體的操作，來實現(xiàn)主從的操作一致，達(dá)到最終數(shù)據(jù)一致的目的

配置主從的步驟

1. 確保從數(shù)據(jù)庫與主數(shù)據(jù)庫里的數(shù)據(jù)一致
2. 在主數(shù)據(jù)庫里創(chuàng)建一個同步賬戶授權(quán)給從數(shù)據(jù)庫使用
3. 配置主數(shù)據(jù)庫(修改配置文件)與開啟binlog日志
4. 配置從數(shù)據(jù)庫(修改配置文件) 5. 配置從節(jié)點

2. 主從復(fù)制數(shù)據(jù)一致性校驗

  • 在理想情況下，備庫和主庫的數(shù)據(jù)應(yīng)該是完全一樣的。但事實上備庫可能發(fā)生錯誤并導(dǎo)致數(shù)據(jù)不一致。即 使沒有明顯的錯誤，備庫同樣可能因為MySQL自身的特性導(dǎo)致數(shù)據(jù)不一致，例如MySQL的Bug感、網(wǎng)絡(luò)中 斷、服務(wù)器崩潰，非正常關(guān)閉或者其他一些錯誤。
  • 按照我們的經(jīng)驗來看，主備一致應(yīng)該是一種規(guī)范，而不是例外，也就是說，檢查你的主備庫一致性應(yīng)該是 一個日常工作，特別是當(dāng)使用備庫來做備份時尤為重要，因為肯定不希望從一個已經(jīng)損壞的備庫里獲得備 份數(shù)據(jù)。

下面介紹一種工具，不過還有其他的工具，可以自己百度。
我們可以使用percona-toolkit工具做校驗，而該工具包含

    1. pt-table-checksum 負(fù)責(zé)檢測MySQL主從數(shù)據(jù)一致性
    1. pt-table-sync負(fù)責(zé)擋住從數(shù)據(jù)不一致時修復(fù)數(shù)據(jù)，讓他們保存數(shù)據(jù)的一致性
    1. pt-heartbeat負(fù)責(zé)監(jiān)控MySQL主從同步延遲

下面我們進(jìn)行安裝一下，也可以自己百度找一下更方便的安裝方法。
安裝

yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI -y
yum localinstall percona-toolkit-3.2.1-1.el7.x86_64.rpm
yum list | grep percona-toolkit

pt-table-checksum --help

使用

pt-table-checksum [options] [dsn]

pt-table-checksum:在主(master)上通過執(zhí)行校驗的查詢對復(fù)制的一致性進(jìn)行檢查，對比主從的校驗 值，從而產(chǎn)生結(jié)果。DSN指向的是主的地址，該工具的退出狀態(tài)不為零，如果發(fā)現(xiàn)有任何差別，或者如果出 現(xiàn)任何警告或錯誤，更多信息請查看官方資料。

現(xiàn)在我們可以準(zhǔn)備一個動作:來模擬數(shù)據(jù)不一致的問題，同時需要確保主從是配置好了的 -》 思路就是創(chuàng) 建一個test的庫隨便添加一個t表

create database `mytest`; create table t (
id int primary key,
name varchar(20)
);

首先配置的是主庫 192.168.22.101 //根據(jù)自己的ip來。

mysql> use `mytest`;
mysql> insert  into t  values(1,6);
mysql> insert  into t  values(2,2); 
mysql> insert  into t  values(4,4); 
mysql> select  * from t;
+----+------+ 
| id | name | 
+----+------+
|  1  |  6  |
|  2  |  2  |
|  4  |  4  |
+----+------+
3 rows in set (0.00 sec)

其次是從庫 192.168.22.102,此時因為主從復(fù)制的原因，在上面主庫進(jìn)行的配置會復(fù)制到從庫。

mysql> use `mytest`;
mysql> insert  into t values(3,3);
mysql> select  * from t;
+----+------+ 
| id | name | 
+----+------+ 
|  1  |  6  |
|  2  |  2  | 
|  3  |  3  | 
|  4  |  4  | 
+----+------+ 
4 rows in set
(0.02 sec)

從上面可以看到實際上數(shù)據(jù)是不同步的，也就是主庫的數(shù)據(jù)少于從庫的數(shù)據(jù)

使用工具檢測

注意常用的參數(shù)解釋:

--nocheck-replication-filters :不檢查復(fù)制過濾器，建議啟用。后面可以用--databases來指定 需要檢查的數(shù)據(jù)庫。
--no-check-binlog-format : 不檢查復(fù)制的binlog模式，要是binlog模式是ROW，則會報錯。 --replicate-check-only :只顯示不同步的信息。
--replicate= :把checksum的信息寫入到指定表中，建議直接寫到被檢查的數(shù)據(jù)庫當(dāng)中。 --databases= :指定需要被檢查的數(shù)據(jù)庫，多個則用逗號隔開。
--tables= :指定需要被檢查的表，多個用逗號隔開 --host | h= :Master的地址
--user | u= :用戶名
--passwork | p=:密碼
--Post | P= :端口

檢測

[root@localhost home]# pt-table-checksum --nocheck-replication-filters -- replicate=check_data.checksums --databases=test --tables=t --user=mytest --password=rot 
Checking if all tables can be checksummed ...
Starting checksum ...
Replica localhost.localdomain has binlog_format MIXED which could cause pt-table- checksum to break replication. Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation. If you understand the risks, specify --no-check-binlog-format to disable this check.
Replica localhost.localdomain has binlog_format MIXED which could cause pt-table- checksum to break replication. Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation. If you understand the risks, specify --no-check-binlog-format to disable this check.

上面的錯誤信息主要是因為，檢測主庫與從庫的binlog日志的模式 - 通常來說可以不用改binlog添加 --no- check-binlog-format 跳過檢測

[root@localhost home]# pt-table-checksum --nocheck-replication-filters -- replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t - -user=mytest --password=rot
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED 11-11T23:27:10 0 1 3 1 1 0
TIME TABLE 0.064 mytest.t
TS :完成檢查的時間。
ERRORS :檢查時候發(fā)生錯誤和警告的數(shù)量。
DIFFS :0表示一致，1表示不一致。當(dāng)指定--no-replicate-check時，會一直為0，當(dāng)指定-- replicate-check-only會顯示不同的信息。
ROWS :表的行數(shù)。
CHUNKS :被劃分到表中的塊的數(shù)目。
SKIPPED :由于錯誤或警告或過大，則跳過塊的數(shù)目。
TIME :執(zhí)行的時間。
TABLE :被檢查的表名。

那些信息數(shù)據(jù)會記錄在checksums表中
執(zhí)行完畢后，自己可以查看表數(shù)據(jù)，這里不演示了。

3. pt-table-sync工具恢復(fù)數(shù)據(jù)

我們可以通過使用另一個工具pt-table-sync進(jìn)行數(shù)據(jù)的同步 手冊地址:https://www.percona.com/doc/percona-toolkit/LATEST/pt-table-sync.html 在主庫中執(zhí)行

[root@localhost home]# pt-table-sync --sync-to-master h=192.168.29.103,u=mytest,p=rot,P=3306 --databases=mytest --print
DELETE FROM `mytest`.`t` WHERE `id`='3' LIMIT 1 /*percona-toolkit src_db:mytest src_tbl:t src_dsn:P=3306,h=192.168.29.102,p=...,u=mytest dst_db:mytest dst_tbl:t dst_dsn:P=3306,h=192.168.29.103,p=...,u=mytest lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:76531 user:root host:localhost.localdomain*/;

如上的操作解釋:

pt-table-sync [options] dsn [dsn]

該工具先maseter的信息， 然后再是從庫上的信息;參數(shù)建議

--replicate= :指定通過pt-table-checksum得到的表，這2個工具差不多都會一直用。 --databases= : 指定執(zhí)行同步的數(shù)據(jù)庫，多個用逗號隔開。
--tables= :指定執(zhí)行同步的表，多個用逗號隔開。
--sync-to-master :指定一個DSN，即從的IP，他會通過show processlist或show slave status 去自動的找主。
h=127.0.0.1 :服務(wù)器地址，命令里有2個ip，第一次出現(xiàn)的是Master的地址，第2次是Slave的地 址。
u=root :帳號。
p=123456 :密碼。
--print :打印，但不執(zhí)行命令。 --execute :執(zhí)行命令。

建議:

  1. 修復(fù)數(shù)據(jù)的時候，用--print打印出來，這樣就可以知道那些數(shù)據(jù)有問題
  2. 修復(fù)數(shù)據(jù)之前一定要備份數(shù)據(jù)庫 ; 然后再 手動執(zhí)行或者 添加 --execute

我們也可以把這個編輯成腳本，定期通過centos定時器定期檢查, 對于我們來說我們執(zhí)行在意的是通過pt- table-checksums 顯示信息中的DIFFS信息.

 [root@localhost home]# pt-table-checksum --nocheck-replication-filters -- replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t - -user=mytest --password=rot

對于centos來說我們可以通過 awk 命令獲取到 DIFFS 中的值，然后判斷這個值是否不等于0;則可以判斷是 否一致。

[root@localhost home]# pt-table-checksum --nocheck-replication-filters -- replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t - -user=mytest --password=rot | awk 'NR>1{sum+=$3}END{print sum}'
1

下一步要做的就是編輯shell腳本 - 額外解釋，所謂的sh腳本就是可以直接模擬centos執(zhí)行我們在命令太執(zhí)行 的命令然后根據(jù)返回的結(jié)果進(jìn)行相應(yīng)的邏輯處理，我們可以創(chuàng)建一個pt-table-checksums;注意!!! windows下編輯的sh腳本在linux中執(zhí)行可能會存在一定的問題，推薦可以直接在xshell中編輯sh腳本這樣問 題會少很多

#!/usr/bin/env bash
NUM=`pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums - -no-check-binlog-format --databases=mytest --tables=t --user=mytest --password=rot | awk 'NR>1{sum+=$3}END{print sum}'`
if [ $NUM -eq 0 ] ;then
echo "Data is ok!"
else
 echo "Data is error!"
pt-table-sync --sync-to-master h=192.168.22.101,u=mytest,p=rot,P=3306 -- databases=mytest --print
pt-table-sync --sync-to-master h=192.168.22.102,u=mytest,p=rot,P=3306 -- databases=mytest --execute
pt-table-sync --sync-to-master h=192.168.22.103,u=mytest,p=rot,P=3306 -- databases=mytest --print
pt-table-sync --sync-to-master h=192.168.22.103,u=mytest,p=rot,P=3306 -- databases=mytest --execute
fi

然后可以通過編輯crontab -e 定時執(zhí)行這個腳本就好

20 23 * * * /home/pt-check-sync.sh

表示每天晚上23:20運行這個腳本

4. 對于主從延遲的處理

master 服務(wù)器和 slave 服務(wù)器連接時，創(chuàng)建 Binlog dump thread 以發(fā)送 bin log 數(shù)據(jù):
1. 一個 Binlog dump thread 對應(yīng)一個 slave 服務(wù)器;
2. Binlog dump thread 從 bin log 獲取數(shù)據(jù)時會加鎖，獲取到數(shù)據(jù)后，立即釋放鎖。

當(dāng) slave 服務(wù)器收到 START_SLAVE 命令時，會創(chuàng)建 I/O thread 和 SQL thread:
1. I/O thread 以拉的方式，從 master 讀取事件，并存儲到 slave 服務(wù)器的 relay log 中;
2. SQL thread 從 relay log 中讀取事件并執(zhí)行;
3. slave 可以按照自己的節(jié)奏讀取和更新數(shù)據(jù)，也可以隨意操作復(fù)制進(jìn)程(啟動和停止)。

pt-heartbeat

在percona toolkit 產(chǎn)品中也提供了可以對于MySQL主從延時檢查的工具pt-heartbeat, pt-heartbeat 的工作原 理是通過使用時間戳方式在主庫上更新特定表，然后再從庫上讀取唄更新的時間戳然后與本地系統(tǒng)時間對 比來得出其延遲。

具體流程:

  1. 在住上創(chuàng)建一張hearteat表，按照一定的時間頻率更新該表的數(shù)據(jù)。監(jiān)控操作運行后，heartbeat表能促 使主從同步
  2. 連接到從庫上檢查復(fù)制的時間記錄，和從庫的當(dāng)前系統(tǒng)時間進(jìn)行比較，得出時間的差異。 注意在使用 的方式就是需要在主庫中創(chuàng)建這個表;
use test;
CREATE TABLE heartbeat (
ts VARCHAR (26) NOT NULL,
server_id INT UNSIGNED NOT NULL PRIMARY KEY,
file VARCHAR (255) DEFAULT NULL, -- SHOW MASTER STATUS
position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS );

通過pt-heartbeat可以對于mysql中的heartbeat表每隔多久更新一次(注意這個啟動操作要在主庫服務(wù)器上 執(zhí)行)

[root@localhost home]# pt-heartbeat --user=root --ask-pass --create-table --database mytest --interval=1 --interval=1 --update --replace --daemonize
[root@localhost home]# ps -ef | grep pt-heartbeat
root 76637 1 0 23:52 ? 00:00:00 perl /usr/bin/pt-heartbeat -- user=root --ask-pass --create-table --database mytest --interval=1 --interval=1 -- update --replace --daemonize
root 76643 76367 0 23:53 pts/2 00:00:00 grep --color=auto pt-heartbeat

在主庫運行監(jiān)測同步延遲

[root@localhost home]# pt-heartbeat --database mytest --table=heartbeat --monitor -- user=root --password=root --master-server-id=1
0.02s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]

這其中 0.02s 表示延遲了 ，沒有延遲是為0 而 [ 0.00s, 0.00s, 0.00s ] 則表示1m,5m,15m的平均值， 而這期中 需要注意的是 --master-server-id 為主服務(wù)器的服務(wù)id就是在my.cnf中配置的 server_id的值

5. 主從延遲處理

對于從庫的延時問題最為重要的就是主庫與從庫之間連接的網(wǎng)絡(luò)環(huán)境，從庫的寫入和讀這兩個點 - 其次就 是對于主從的架構(gòu)的優(yōu)化;

注意:一旦使用了主從必然是會有一定的延時問題，因此我們就需要考慮程序?qū)τ谘舆t的容忍度。 如果是0 容忍的話建議還是不用主從了

MySQL從庫產(chǎn)生配置

網(wǎng)絡(luò)環(huán)境跳過，，，從庫的寫入主要是指insert，update，delete的語句的執(zhí)行速度這些語句的執(zhí)行速度我 們就需要考慮MySQL的執(zhí)行SQL語句的一個特點 -》 對于每一個寫的sql會默認(rèn)開啟事務(wù)并提交事務(wù) ; 而事 務(wù)是會影響到io的消耗的這和innodb_flush_log_at_trx_commit參數(shù)有關(guān)系。默認(rèn)為1 我們可以嘗試設(shè)置為0 或2可以提高效率， 另一個就是sync_binlog

  • sync_binlog 配置說明:
  • sync_binlog”:這個參數(shù)是對于MySQL系統(tǒng)來說是至關(guān)重要的，他不僅影響到Binlog對MySQL所帶來的性能 損耗，而且還影響到MySQL中數(shù)據(jù)的完整性。對于“sync_binlog”參數(shù)的各種設(shè)置的說明如下: sync_binlog=0，當(dāng)事務(wù)提交之后，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁 盤，而讓Filesystem自行決定什么時候來做同步，或者 cache滿了之后才同步到磁盤。 sync_binlog=n，當(dāng) 每進(jìn)行n次事務(wù)提交之后，MySQL將進(jìn)行一次fsync之類的磁盤同步指令來將binlog_cache中的數(shù)據(jù)強制寫入 磁盤。
  • 在MySQL中系統(tǒng)默認(rèn)的設(shè)置是sync_binlog=0，也就是不做任何強制性的磁盤刷新指令，這時候的性能是最 好的，但是風(fēng)險也是最大的。因為一旦系統(tǒng)Crash，在binlog_cache中的所有binlog信息都會被丟失。而當(dāng) 設(shè)置為“1”的時候，是最安全但是性能損耗最大的設(shè)置。因為當(dāng)設(shè)置為1的時候，即使系統(tǒng)Crash，也最多丟 失binlog_cache中未完成的一個事務(wù)，對實際數(shù)據(jù)沒有任何實質(zhì)性影響。
  • 從以往經(jīng)驗和相關(guān)測試來看，對于高并發(fā)事務(wù)的系統(tǒng)來說，“sync_binlog”設(shè)置為0和設(shè)置為1的系統(tǒng)寫入性 能差距可能高達(dá)5倍甚至更多。
  • innodb_flush_log_at_trx_commit 配置說明: 默認(rèn)值1的意思是每一次事務(wù)提交或事務(wù)外的指令都需要把日 志寫入(flush)硬盤，這是很費時的。特別是使用電池供電緩存(Battery backed up cache)時。 設(shè)成2對 于很多運用，特別是從MyISAM表轉(zhuǎn)過來的是可以的，它的意思是不寫入硬盤而是寫入系統(tǒng)緩存。日志仍然 會每秒flush到硬 盤，所以你一般不會丟失超 過1-2秒的更新。設(shè)成0會更快一點，但安全方面比較差，即使 MySQL掛了也可能會丟失事務(wù)的數(shù)據(jù)。而值2只會在整個操作系統(tǒng) 掛了時才可能丟數(shù)據(jù)。

硬件:服務(wù)器的配置要好點，

架構(gòu):

  1. 可以考慮對于一些庫進(jìn)行單獨分離。
  2. 服務(wù)的基礎(chǔ)架構(gòu)在業(yè)務(wù)和MySQL之間加入memcache或者redis的cache層。
  3. 從庫的配置要好。
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

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

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