mysql数据库复制基本原理_MySQL的复制原理以及流程
原創(chuàng)文章出自公眾號(hào)：「碼農(nóng)富哥」，如需轉(zhuǎn)載請(qǐng)注明出處！
文章如果對(duì)你有收獲，可以收藏轉(zhuǎn)發(fā)，這會(huì)給我一個(gè)大大鼓勵(lì)喲！另外可以關(guān)注我公眾號(hào)「碼農(nóng)富哥」 (搜索id：coder2025)，我會(huì)持續(xù)輸出Python，算法，計(jì)算機(jī)基礎(chǔ)的 原創(chuàng) 文章
MySQL復(fù)制概述
簡(jiǎn)單來(lái)說(shuō)就是保證主服務(wù)器(Master)和從服務(wù)器(Slave)的數(shù)據(jù)是一致性的，向Master插入數(shù)據(jù)后，Slave會(huì)自動(dòng)從Master把修改的數(shù)據(jù)同步過(guò)來(lái)(有一定的延遲)，通過(guò)這種方式來(lái)保證數(shù)據(jù)的一致性，就是Mysql復(fù)制
Mysql 復(fù)制能解決什么問(wèn)題
一、高可用和故障切換
復(fù)制能夠幫避免MySql單點(diǎn)失敗，因?yàn)閿?shù)據(jù)都是相同的，所以當(dāng)Master掛掉后，可以指定一臺(tái)Slave充當(dāng)Master繼續(xù)保證服務(wù)運(yùn)行，因?yàn)閿?shù)據(jù)是一致性的(如果當(dāng)插入Master就掛掉，可能不一致，因?yàn)橥揭残枰獣r(shí)間)，當(dāng)然這種配置不是簡(jiǎn)單的把一臺(tái)Slave充當(dāng)Master，畢竟還要考慮后續(xù)的Salve同步Master
二、負(fù)載均衡
因?yàn)樽x寫(xiě)分離也算是負(fù)載均衡的一種，所以就不單獨(dú)寫(xiě)了，因?yàn)橐话愣际怯卸嗯_(tái)Slave的，所以可以將讀操作指定到Slave服務(wù)器上(需要代碼控制)，然后再用負(fù)載均衡來(lái)選擇那臺(tái)Slave來(lái)提供服務(wù)，同時(shí)也可以吧一些大量計(jì)算的查詢(xún)指定到某臺(tái)Slave，這樣就不會(huì)影響Master的寫(xiě)入以及其他查詢(xún)
在開(kāi)發(fā)工作中，有時(shí)候會(huì)遇見(jiàn)某個(gè)sql 語(yǔ)句需要鎖表，導(dǎo)致暫時(shí)不能使用讀的服務(wù)，這樣就會(huì)影響現(xiàn)有業(yè)務(wù)，使用主從復(fù)制，讓主庫(kù)負(fù)責(zé)寫(xiě)，從庫(kù)負(fù)責(zé)讀，這樣，即使主庫(kù)出現(xiàn)了鎖表的情景，通過(guò)讀從庫(kù)也可以保證業(yè)務(wù)的正常運(yùn)作。
Master負(fù)責(zé)寫(xiě)操作的負(fù)載，也就是說(shuō)一切寫(xiě)的操作都在Master上進(jìn)行，而讀的操作則分?jǐn)偟絊lave上進(jìn)行。這樣一來(lái)的可以大大提高讀取的效率。在一般的互聯(lián)網(wǎng)應(yīng)用中，經(jīng)過(guò)一些數(shù)據(jù)調(diào)查得出結(jié)論，讀/寫(xiě)的比例大概在 10：1左右 ，也就是說(shuō)大量的數(shù)據(jù)操作是集中在讀的操作，這也就是為什么我們會(huì)有多個(gè)Slave的原因。但是為什么要分離讀和寫(xiě)呢？熟悉DB的研發(fā)人員都知道，寫(xiě)操作涉及到鎖的問(wèn)題，(快照讀VS當(dāng)前讀，寫(xiě)操作一定是當(dāng)前讀，所以會(huì)產(chǎn)生鎖。)不管是行鎖還是表鎖還是塊鎖，都是比較降低系統(tǒng)執(zhí)行效率的事情。我們這樣的分離是把寫(xiě)操作集中在一個(gè)節(jié)點(diǎn)上，而讀操作其其他的N個(gè)節(jié)點(diǎn)上進(jìn)行，從另一個(gè)方面有效的提高了讀的效率，保證了系統(tǒng)的高可用性。
三、數(shù)據(jù)備份
一般我們都會(huì)做數(shù)據(jù)備份，可能是寫(xiě)定時(shí)任務(wù)，一些特殊行業(yè)可能還需要手動(dòng)備份，有些行業(yè)要求備份和原數(shù)據(jù)不能在同一個(gè)地方，所以主從就能很好的解決這個(gè)問(wèn)題，不僅備份及時(shí)，而且還可以多地備份，保證數(shù)據(jù)的安全
四、業(yè)務(wù)模塊化
可以一個(gè)業(yè)務(wù)模塊讀取一個(gè)Slave，再針對(duì)不同的業(yè)務(wù)場(chǎng)景進(jìn)行數(shù)據(jù)庫(kù)的索引創(chuàng)建和根據(jù)業(yè)務(wù)選擇MySQL存儲(chǔ)引擎， 不同的slave可以根據(jù)不同需求設(shè)置不同索引和存儲(chǔ)引擎
隨著系統(tǒng)中業(yè)務(wù)訪問(wèn)量的增大，如果是單機(jī)部署數(shù)據(jù)庫(kù)，就會(huì)導(dǎo)致I/O訪問(wèn)頻率過(guò)高。有了主從復(fù)制，增加多個(gè)數(shù)據(jù)存儲(chǔ)節(jié)點(diǎn)，將負(fù)載分布在多個(gè)從節(jié)點(diǎn)上，降低單機(jī)磁盤(pán)I/O訪問(wèn)的頻率，提高單個(gè)機(jī)器的I/O性能。
主從配置需要注意的點(diǎn)
(1)主從服務(wù)器操作系統(tǒng)版本和位數(shù)一致；
(2) Master和Slave數(shù)據(jù)庫(kù)的版本要一致；
(3) Master和Slave數(shù)據(jù)庫(kù)中的數(shù)據(jù)要一致；
(4) Master開(kāi)啟二進(jìn)制日志，Master和Slave的server_id在局域網(wǎng)內(nèi)必須唯一；
復(fù)制如何工作
復(fù)制有三個(gè)步驟：
1、Master將數(shù)據(jù)改變記錄到二進(jìn)制日志(binary log)中，也就是配置文件log-bin指定的文件，這些記錄叫做二進(jìn)制日志事件(binary log events)
2、Slave通過(guò)I/O線程讀取Master中的binary log events并寫(xiě)入到它的中繼日志(relay log)
3、Slave重做中繼日志中的事件，把中繼日志中的事件信息一條一條的在本地執(zhí)行一次，完成數(shù)據(jù)在本地的存儲(chǔ)，從而實(shí)現(xiàn)將改變反映到它自己的數(shù)據(jù)(數(shù)據(jù)重放)
image
復(fù)制涉及到三個(gè)線程：
主節(jié)點(diǎn) binary log dump 線程(IO線程)
當(dāng)從節(jié)點(diǎn)連接主節(jié)點(diǎn)時(shí)，主節(jié)點(diǎn)會(huì)創(chuàng)建一個(gè)log dump 線程，用于發(fā)送bin-log的內(nèi)容。在讀取bin-log中的操作時(shí)，此線程會(huì)對(duì)主節(jié)點(diǎn)上的bin-log加鎖，當(dāng)讀取完成，甚至在發(fā)動(dòng)給從節(jié)點(diǎn)之前，鎖會(huì)被釋放。
從節(jié)點(diǎn)I/O線程
當(dāng)從節(jié)點(diǎn)上執(zhí)行start slave命令之后，從節(jié)點(diǎn)會(huì)創(chuàng)建一個(gè)I/O線程用來(lái)連接主節(jié)點(diǎn)，請(qǐng)求主庫(kù)中更新的bin-log。I/O線程接收到主節(jié)點(diǎn)binlog dump 進(jìn)程發(fā)來(lái)的更新之后，保存在本地relay-log中。
從節(jié)點(diǎn)SQL線程
SQL線程負(fù)責(zé)讀取relay log中的內(nèi)容，解析成具體的操作并執(zhí)行，最終保證主從數(shù)據(jù)的一致性。
mysql復(fù)制過(guò)程.png
我們根據(jù)上圖來(lái)分析一下整個(gè)主從復(fù)制的過(guò)程：
Master記錄二進(jìn)制日志， 每次提交事務(wù)完成數(shù)據(jù)更新前，Master將數(shù)據(jù)更新的時(shí)間記錄到二進(jìn)制日志中，MySQL會(huì)按事務(wù)提交的順序而非每條語(yǔ)句的執(zhí)行順序來(lái)記錄二進(jìn)制日志。記錄二進(jìn)制日志后，主庫(kù)會(huì)告訴存儲(chǔ)引擎可以提交事務(wù)了。
在Slave服務(wù)器上執(zhí)行start slave命令開(kāi)啟主從復(fù)制開(kāi)關(guān)，開(kāi)始進(jìn)行主從復(fù)制。
此時(shí)，Slave服務(wù)器的IO線程會(huì)通過(guò)在master上已經(jīng)授權(quán)的復(fù)制用戶(hù)權(quán)限請(qǐng)求連接Master服務(wù)器，并請(qǐng)求從執(zhí)行binlog日志文件中的指定位置(日志文件名和位置就是在配置主從復(fù)制服務(wù)時(shí)執(zhí)行change master命令指定的)之后開(kāi)始發(fā)送binlog日志內(nèi)容。
Master服務(wù)器接收來(lái)自Slave服務(wù)器的IO線程的請(qǐng)求后，其負(fù)責(zé)復(fù)制的IO線程會(huì)根據(jù)Slave服務(wù)器的IO線程請(qǐng)求的信息分批讀取指定binlog日志文件指定位置之后的binlog日志信息，然后返回給Slave端的IO線程。返回的信息中除了binlog日志內(nèi)容外，還有在Master服務(wù)器端記錄的IO線程。返回的信息中除了binlog中的下一個(gè)指定更新位置。
當(dāng)Slave服務(wù)器的IO線程獲取到Master服務(wù)器上IO線程發(fā)送的日志內(nèi)容、日志文件及位置點(diǎn)后，會(huì)將binlog日志內(nèi)容依次寫(xiě)到Slave端自身的Relay Log(即中繼日志)文件(Mysql-relay-bin.xxx)的最末端，并將新的binlog文件名和位置記錄到master-info文件中，以便下一次讀取master端新binlog日志時(shí)能告訴Master服務(wù)器從新binlog日志的指定文件及位置開(kāi)始讀取新的binlog日志內(nèi)容
Slave服務(wù)器端的SQL線程會(huì)實(shí)時(shí)檢測(cè)本地Relay Log 中IO線程新增的日志內(nèi)容，然后及時(shí)把Relay LOG 文件中的內(nèi)容解析成sql語(yǔ)句，并在自身Slave服務(wù)器上按解析SQL語(yǔ)句的位置順序執(zhí)行應(yīng)用這樣sql語(yǔ)句，并在relay-log.info中記錄當(dāng)前應(yīng)用中繼日志的文件名和位置點(diǎn)
點(diǎn)評(píng)：用于傳輸binlog的線程就是IO線程，包括slave發(fā)起的到master的鏈接，這個(gè)就是slave的IO線程，同時(shí)，master跟slave連接后產(chǎn)生一個(gè)線程，這個(gè)線程也就是master的IO線程，這個(gè)連接過(guò)程不需要解析SQL，所以沒(méi)有SQL線程。 而到了slave重放relay log中繼日志的時(shí)候，需要把日志轉(zhuǎn)換成SQL然后執(zhí)行到，這時(shí)候就是slave的SQL線程在起作用了！
這種復(fù)制架構(gòu)實(shí)現(xiàn)了獲取事件和重放事件的解偶，一個(gè)是獲取binlog的IO線程，一個(gè)是重放中繼日志的SQL線程，允許這兩個(gè)過(guò)程異步進(jìn)行。也就是說(shuō)I/O線程能夠獨(dú)立于SQL線程之外工作。但這種架構(gòu)頁(yè)限制了復(fù)制的過(guò)程，其中最重要的一點(diǎn)是主庫(kù)上并發(fā)運(yùn)行的查詢(xún)?cè)購(gòu)膸?kù)只能串行化執(zhí)行，因?yàn)橹挥幸粋€(gè)SQL線程重放中繼日志中的事件。這是很多工作負(fù)載的性能瓶頸所在。因?yàn)槭冀K受限于單線程。
復(fù)制類(lèi)型
1、基于語(yǔ)句的復(fù)制 Statement-base Replication(SBR)
在Master上執(zhí)行的SQL語(yǔ)句，在Slave上執(zhí)行同樣的語(yǔ)句。MySQL默認(rèn)采用基于語(yǔ)句的復(fù)制，效率比較高。一旦發(fā)現(xiàn)沒(méi)法精確復(fù)制時(shí)，會(huì)自動(dòng)選著基于行的復(fù)制
優(yōu)點(diǎn)是只需要記錄會(huì)修改數(shù)據(jù)的sql語(yǔ)句到binlog，減少binlog日志量(如果修改了一個(gè)表的記錄，也只是一條SQL的記錄)，節(jié)約I/O 。不僅能用于復(fù)制，還能實(shí)時(shí)還原數(shù)據(jù)庫(kù)。因?yàn)樗涗浟怂械腟QL，還原數(shù)據(jù)庫(kù)就很容易。
缺點(diǎn)是如果一個(gè)語(yǔ)句很復(fù)雜，那么slave執(zhí)行的時(shí)候就會(huì)很耗資源，而基于行復(fù)制的話，只會(huì)記錄變更的行記錄。還有UPDATE語(yǔ)句在WHERE沒(méi)有使用索引的情況下，會(huì)比基于行使用更多的行鎖
2、基于行的復(fù)制
把改變的內(nèi)容復(fù)制到Slave，而不是把命令在Slave上執(zhí)行一遍。從MySQL5.0開(kāi)始支持
優(yōu)點(diǎn)是 只會(huì)記錄變更的行記錄，哪怕一個(gè)語(yǔ)句很復(fù)雜，但是它最后只影響幾條記錄，那么行的復(fù)制，只會(huì)把影響到幾條記錄記錄到binlog，降低slave重放日志時(shí)的資源消耗。
缺點(diǎn)是：它的日志很大，因?yàn)榭赡芤粭lSQL修改了一個(gè)表的記錄，但是它要把表的所有變更的記錄都記錄下來(lái)。而且它無(wú)法看到執(zhí)行了什么SQL，不利于數(shù)據(jù)庫(kù)的還原
3、混合類(lèi)型的復(fù)制
默認(rèn)采用基于語(yǔ)句的復(fù)制，一旦發(fā)現(xiàn)基于語(yǔ)句的無(wú)法精確的復(fù)制時(shí)，就會(huì)采用基于行的復(fù)制
混合方式就是有mysql自動(dòng)選擇RBR方式和SBR方式，能夠充分發(fā)揮兩種方式的優(yōu)點(diǎn)，一般情況下都使用該種方式實(shí)現(xiàn)主從復(fù)制
相應(yīng)地，binlog的格式也有三種：STATEMENT，ROW，MIXED。
啟動(dòng)多個(gè)Mysql實(shí)例
要配置主從復(fù)制，我們?cè)诒緳C(jī)開(kāi)多個(gè)Mysql實(shí)例來(lái)操作就可以了，讓他們監(jiān)聽(tīng)不同端口
多開(kāi)實(shí)例可以看我另一篇教程：同一臺(tái)Ubuntu 啟動(dòng)多個(gè)mysql
主從復(fù)制配置
現(xiàn)在我們兩個(gè)實(shí)例Mysql
server1 : 127.0.0.1 3306 master
server2 : 127.0.0.1 3301 slave
配置master
在主庫(kù)創(chuàng)建一個(gè)復(fù)制帳號(hào)，這個(gè)帳號(hào)是給從庫(kù)的IO線程建立連接到主庫(kù)時(shí)用的，從庫(kù)會(huì)用這個(gè)帳號(hào)連接主庫(kù)并讀取主庫(kù)的二進(jìn)制日志：
grant replication slave, replication client on *.* to 'repl'@'localhost' identified by '123456';
主庫(kù)添加配置：
# 設(shè)置server_id，一般設(shè)置為IP, 要獨(dú)一無(wú)二的
server-id = 10
# 開(kāi)啟二進(jìn)制日志功能，最好是絕對(duì)路徑
log_bin = /var/log/mysql/mysql-bin.log
# 主從復(fù)制的格式(mixed,statement,row，默認(rèn)格式是statement)
binlog_format=mixed
# 二進(jìn)制日志自動(dòng)刪除/過(guò)期的天數(shù)。默認(rèn)值為0，表示不自動(dòng)刪除。
expire_logs_days=7
# 為每個(gè)session 分配的內(nèi)存，在事務(wù)過(guò)程中用來(lái)存儲(chǔ)二進(jìn)制日志的緩存
binlog_cache_size=1M
# 復(fù)制過(guò)濾：不需要備份的數(shù)據(jù)庫(kù)，不輸出(mysql庫(kù)一般不同步)
binlog-ignore-db=mysql
啟用二進(jìn)制日志后，重啟后， show master status; 可以看到二進(jìn)制相關(guān)信息
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 107 | | |
+------------------+----------+--------------+------------------+
添加從庫(kù)配置
# 設(shè)置server_id，一般設(shè)置為IP, 要獨(dú)一無(wú)二的
server-id = 10
log_bin = /var/log/mysql/mysql-bin.log
# 中繼日志路徑
relay_log = /home/mysql/3301/mysql-relay-bin
# 允許從庫(kù)將其重放的事件也記錄到自身的二進(jìn)制日志中
log_slave_updates = 1
read_only = 1
從庫(kù)開(kāi)啟復(fù)制
mysql> CHANGE MASTER TO
MASTER_HOST='$host',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='msyql-bin.00001',
MASTER_LOG_POS=0;
MASTER_LOG_POS設(shè)為0，是從日志開(kāi)頭開(kāi)始復(fù)制，MASTER_LOG_FILE是master的二進(jìn)制文件
# 啟動(dòng)復(fù)制
mysql> start slave;
# 查看復(fù)制狀態(tài)
mysql> show slave status;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 107
Relay_Log_File: mysql-relay-bin.000020
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Master_Server_Id: 10
# Slave_IO_Running: Yes，Slave_SQL_Running: Yes 說(shuō)明同步正常進(jìn)行
# Seconds_Behind_Master: 0 就是完全同步了
這時(shí)就完成了主從復(fù)制的配置，當(dāng)主服務(wù)器有更新，從庫(kù)也會(huì)更新。
我們還可以從線程列表看出復(fù)制線程，主庫(kù)上可以看到由從庫(kù)I/O線程向主庫(kù)發(fā)起的連接。
mysql> show processlist \G
*************************** 1. row ***************************
Id: 44
User: repl
Host: localhost:32866
db: NULL
Command: Binlog Dump
Time: 73032
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
同樣，我們看看從庫(kù)的線程，有兩個(gè)，一個(gè)I/O線程，一個(gè)SQL線程：
mysql> show processlist \G
*************************** 1. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 73422
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 72417
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
這兩個(gè)線程都是再system user 帳號(hào)下運(yùn)行，I/O線程是寫(xiě)日志到中繼日志的線程， SQL線程是重放SQL的線程。
從已經(jīng)運(yùn)行已久的服務(wù)器開(kāi)始復(fù)制
那么，至此我們已經(jīng)完成了Mysql的主從配置。
但是上面是配置兩臺(tái)剛好安裝號(hào)的服務(wù)器，數(shù)據(jù)相同，并且知道當(dāng)前主庫(kù)二進(jìn)制日志。
更典型的案例是，一個(gè)運(yùn)行已經(jīng)一段時(shí)間的主庫(kù)，要用一臺(tái)新安裝的從庫(kù)與之同步，此時(shí)這臺(tái)從庫(kù)還沒(méi)有數(shù)據(jù)。
所以我們得想辦法，線初始化從庫(kù)： 從主庫(kù)復(fù)制數(shù)據(jù)、使用最近依次備份來(lái)啟動(dòng)從庫(kù)。
這需要三個(gè)條件來(lái)讓主庫(kù)和從庫(kù)保持同步：
復(fù)制數(shù)據(jù)到從庫(kù)
mysqldump --single-transaction --all-databases --master-data=1 -uroot -p123456|mysql -S /home/mysql/3301/mysqld.sock -uroot -p123456
主從同步的延遲等問(wèn)題、原因及解決方案：
相關(guān)參數(shù)：
首先在服務(wù)器上執(zhí)行show slave satus;可以看到很多同步的參數(shù)：
Master_Log_File： SLAVE中的I/O線程當(dāng)前正在讀取的主服務(wù)器二進(jìn)制日志文件的名稱(chēng)
Read_Master_Log_Pos： 在當(dāng)前的主服務(wù)器二進(jìn)制日志中，SLAVE中的I/O線程已經(jīng)讀取的位置
Relay_Log_File： SQL線程當(dāng)前正在讀取和執(zhí)行的中繼日志文件的名稱(chēng)
Relay_Log_Pos： 在當(dāng)前的中繼日志中，SQL線程已讀取和執(zhí)行的位置
Relay_Master_Log_File： 由SQL線程執(zhí)行的包含多數(shù)近期事件的主服務(wù)器二進(jìn)制日志文件的名稱(chēng)
Slave_IO_Running： I/O線程是否被啟動(dòng)并成功地連接到主服務(wù)器上
Slave_SQL_Running： SQL線程是否被啟動(dòng)
Seconds_Behind_Master： 從屬服務(wù)器SQL線程和從屬服務(wù)器I/O線程之間的時(shí)間差距，單位以秒計(jì)。
從庫(kù)同步延遲情況出現(xiàn)的
● show slave status顯示參數(shù)Seconds_Behind_Master不為0，這個(gè)數(shù)值可能會(huì)很大
● show slave status顯示參數(shù)Relay_Master_Log_File和Master_Log_File顯示bin-log的編號(hào)相差很大，說(shuō)明bin-log在從庫(kù)上沒(méi)有及時(shí)同步，所以近期執(zhí)行的bin-log和當(dāng)前IO線程所讀的bin-log相差很大
● mysql的從庫(kù)數(shù)據(jù)目錄下存在大量mysql-relay-log日志，該日志同步完成之后就會(huì)被系統(tǒng)自動(dòng)刪除，存在大量日志，說(shuō)明主從同步延遲很厲害
MySql數(shù)據(jù)庫(kù)從庫(kù)同步的延遲問(wèn)題
1)、MySQL數(shù)據(jù)庫(kù)主從同步延遲原理mysql主從同步原理：主庫(kù)針對(duì)寫(xiě)操作，順序?qū)慴inlog，從庫(kù)單線程去主庫(kù)順序讀”寫(xiě)操作的binlog”，從庫(kù)取到binlog在本地原樣執(zhí)行(隨機(jī)寫(xiě))，來(lái)保證主從數(shù)據(jù)邏輯上一致。mysql的主從復(fù)制都是單線程的操作，主庫(kù)對(duì)所有DDL和DML產(chǎn)生binlog，binlog是順序?qū)?#xff0c;所以效率很高，slave的Slave_IO_Running線程到主庫(kù)取日志，效率比較高，下一步，問(wèn)題來(lái)了，slave的Slave_SQL_Running線程將主庫(kù)的DDL和DML操作在slave實(shí)施。DML和DDL的IO操作是隨機(jī)的，不是順序的，成本高很多，還可能可slave上的其他查詢(xún)產(chǎn)生lock爭(zhēng)用，由于Slave_SQL_Running也是單線程的，所以一個(gè)DDL卡主了，需要執(zhí)行10分鐘，那么所有之后的DDL會(huì)等待這個(gè)DDL執(zhí)行完才會(huì)繼續(xù)執(zhí)行，這就導(dǎo)致了延時(shí)。有朋友會(huì)問(wèn)：“主庫(kù)上那個(gè)相同的DDL也需要執(zhí)行10分，為什么slave會(huì)延時(shí)？”，答案是master可以并發(fā)，Slave_SQL_Running線程卻不可以。
2)、MySQL數(shù)據(jù)庫(kù)主從同步延遲是怎么產(chǎn)生的？當(dāng)主庫(kù)的TPS并發(fā)較高時(shí)，產(chǎn)生的DDL數(shù)量超過(guò)slave一個(gè)sql線程所能承受的范圍，那么延時(shí)就產(chǎn)生了，當(dāng)然還有就是可能與slave的大型query語(yǔ)句產(chǎn)生了鎖等待。首要原因：數(shù)據(jù)庫(kù)在業(yè)務(wù)上讀寫(xiě)壓力太大，CPU計(jì)算負(fù)荷大，網(wǎng)卡負(fù)荷大，硬盤(pán)隨機(jī)IO太高次要原因：讀寫(xiě)binlog帶來(lái)的性能影響，網(wǎng)絡(luò)傳輸延遲。
MySql數(shù)據(jù)庫(kù)從庫(kù)同步的延遲解決方案
sync_binlog=1 oMySQL提供一個(gè)sync_binlog參數(shù)來(lái)控制數(shù)據(jù)庫(kù)的binlog刷到磁盤(pán)上去。默認(rèn)，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系統(tǒng)自己控制它的緩存的刷新。這時(shí)候的性能是最好的，但是風(fēng)險(xiǎn)也是最大的。一旦系統(tǒng)Crash，在binlog_cache中的所有binlog信息都會(huì)被丟失。
如果sync_binlog>0，表示每sync_binlog次事務(wù)提交，MySQL調(diào)用文件系統(tǒng)的刷新操作將緩存刷下去。最安全的就是sync_binlog=1了，表示每次事務(wù)提交，MySQL都會(huì)把binlog刷下去，是最安全但是性能損耗最大的設(shè)置。這樣的話，在數(shù)據(jù)庫(kù)所在的主機(jī)操作系統(tǒng)損壞或者突然掉電的情況下，系統(tǒng)才有可能丟失1個(gè)事務(wù)的數(shù)據(jù)。但是binlog雖然是順序IO，但是設(shè)置sync_binlog=1，多個(gè)事務(wù)同時(shí)提交，同樣很大的影響MySQL和IO性能。雖然可以通過(guò)group commit的補(bǔ)丁緩解，但是刷新的頻率過(guò)高對(duì)IO的影響也非常大。
對(duì)于高并發(fā)事務(wù)的系統(tǒng)來(lái)說(shuō)，“sync_binlog”設(shè)置為0和設(shè)置為1的系統(tǒng)寫(xiě)入性能差距可能高達(dá)5倍甚至更多。所以很多MySQL DBA設(shè)置的sync_binlog并不是最安全的1，而是2或者是0。這樣犧牲一定的一致性，可以獲得更高的并發(fā)和性能。默認(rèn)情況下，并不是每次寫(xiě)入時(shí)都將binlog與硬盤(pán)同步。因此如果操作系統(tǒng)或機(jī)器(不僅僅是MySQL服務(wù)器)崩潰，有可能binlog中最后的語(yǔ)句丟失了。要想防止這種情況，你可以使用sync_binlog全局變量(1是最安全的值，但也是最慢的)，使binlog在每N次binlog寫(xiě)入后與硬盤(pán)同步。即使sync_binlog設(shè)置為1,出現(xiàn)崩潰時(shí)，也有可能表內(nèi)容和binlog內(nèi)容之間存在不一致性。
innodb_flush_log_at_trx_commit (這個(gè)很管用)抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了調(diào)整這個(gè)值。默認(rèn)值1的意思是每一次事務(wù)提交或事務(wù)外的指令都需要把日志寫(xiě)入(flush)硬盤(pán)，這是很費(fèi)時(shí)的。特別是使用電池供電緩存(Battery backed up cache)時(shí)。設(shè)成2對(duì)于很多運(yùn)用，特別是從MyISAM表轉(zhuǎn)過(guò)來(lái)的是可以的，它的意思是不寫(xiě)入硬盤(pán)而是寫(xiě)入系統(tǒng)緩存。日志仍然會(huì)每秒flush到硬 盤(pán)，所以你一般不會(huì)丟失超過(guò)1-2秒的更新。設(shè)成0會(huì)更快一點(diǎn)，但安全方面比較差，即使MySQL掛了也可能會(huì)丟失事務(wù)的數(shù)據(jù)。而值2只會(huì)在整個(gè)操作系統(tǒng) 掛了時(shí)才可能丟數(shù)據(jù)。
從服務(wù)器關(guān)閉 logs-slave-updates ，以為著slave從master接到的更新不寫(xiě)入二進(jìn)制日志
直接禁掉從服務(wù)器的binlog，因?yàn)閺姆?wù)器安全性相對(duì)沒(méi)那么強(qiáng)
并行復(fù)制 MySQL5.6增加的特性，多個(gè)線程復(fù)制肯定能更快
最后
原創(chuàng)文章出自公眾號(hào)：「碼農(nóng)富哥」，如需轉(zhuǎn)載請(qǐng)注明出處！
文章如果對(duì)你有收獲，可以收藏轉(zhuǎn)發(fā)，這會(huì)給我一個(gè)大大鼓勵(lì)喲！另外可以關(guān)注我公眾號(hào)「碼農(nóng)富哥」 (搜索id：coder2025)，我會(huì)持續(xù)輸出Python，算法，計(jì)算機(jī)基礎(chǔ)的 原創(chuàng) 文章
掃碼關(guān)注我：碼農(nóng)富哥
總結(jié)
以上是生活随笔為你收集整理的mysql数据库复制基本原理_MySQL的复制原理以及流程的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： mysql编译方式安装_mysql编译方
- 下一篇： mysql左连接去重查询_MYSQL-多