原創(chuàng)文章出自公眾號：「碼農富哥」，如需轉載請注明出處嗜侮！
文章如果對你有收獲，可以收藏轉發(fā)啥容，這會給我一個大大鼓勵喲锈颗！另外可以關注我公眾號「碼農富哥」 (搜索id：coder2025)，我會持續(xù)輸出Python咪惠，算法击吱，計算機基礎的 原創(chuàng) 文章
MySQL復制概述
簡單來說就是保證主服務器（Master）和從服務器（Slave）的數(shù)據(jù)是一致性的，向Master插入數(shù)據(jù)后遥昧，Slave會自動從Master把修改的數(shù)據(jù)同步過來（有一定的延遲）覆醇，通過這種方式來保證數(shù)據(jù)的一致性，就是Mysql復制
Mysql 復制能解決什么問題
一炭臭、高可用和故障切換
復制能夠幫避免MySql單點失敗永脓，因為數(shù)據(jù)都是相同的，所以當Master掛掉后鞋仍，可以指定一臺Slave充當Master繼續(xù)保證服務運行常摧，因為數(shù)據(jù)是一致性的（如果當插入Master就掛掉，可能不一致威创，因為同步也需要時間）落午，當然這種配置不是簡單的把一臺Slave充當Master，畢竟還要考慮后續(xù)的Salve同步Master
二肚豺、負載均衡
因為讀寫分離也算是負載均衡的一種溃斋，所以就不單獨寫了，因為一般都是有多臺Slave的吸申，所以可以將讀操作指定到Slave服務器上（需要代碼控制）梗劫，然后再用負載均衡來選擇那臺Slave來提供服務寞奸，同時也可以吧一些大量計算的查詢指定到某臺Slave，這樣就不會影響Master的寫入以及其他查詢
在開發(fā)工作中在跳，有時候會遇見某個sql 語句需要鎖表枪萄，導致暫時不能使用讀的服務，這樣就會影響現(xiàn)有業(yè)務猫妙，使用主從復制瓷翻，讓主庫負責寫，從庫負責讀割坠，這樣齐帚，即使主庫出現(xiàn)了鎖表的情景，通過讀從庫也可以保證業(yè)務的正常運作彼哼。
Master負責寫操作的負載对妄，也就是說一切寫的操作都在Master上進行，而讀的操作則分攤到Slave上進行敢朱。這樣一來的可以大大提高讀取的效率剪菱。在一般的互聯(lián)網(wǎng)應用中，經(jīng)過一些數(shù)據(jù)調查得出結論拴签，讀/寫的比例大概在 10：1左右 孝常，也就是說大量的數(shù)據(jù)操作是集中在讀的操作，這也就是為什么我們會有多個Slave的原因蚓哩。但是為什么要分離讀和寫呢构灸？熟悉DB的研發(fā)人員都知道，寫操作涉及到鎖的問題岸梨，（快照讀VS當前讀喜颁，寫操作一定是當前讀，所以會產(chǎn)生鎖曹阔。）不管是行鎖還是表鎖還是塊鎖半开，都是比較降低系統(tǒng)執(zhí)行效率的事情。我們這樣的分離是把寫操作集中在一個節(jié)點上次兆，而讀操作其其他的N個節(jié)點上進行稿茉，從另一個方面有效的提高了讀的效率，保證了系統(tǒng)的高可用性芥炭。
三、數(shù)據(jù)備份
一般我們都會做數(shù)據(jù)備份恃慧，可能是寫定時任務园蝠，一些特殊行業(yè)可能還需要手動備份，有些行業(yè)要求備份和原數(shù)據(jù)不能在同一個地方痢士，所以主從就能很好的解決這個問題彪薛，不僅備份及時茂装，而且還可以多地備份，保證數(shù)據(jù)的安全
四善延、業(yè)務模塊化
可以一個業(yè)務模塊讀取一個Slave少态，再針對不同的業(yè)務場景進行數(shù)據(jù)庫的索引創(chuàng)建和根據(jù)業(yè)務選擇MySQL存儲引擎， 不同的slave可以根據(jù)不同需求設置不同索引和存儲引擎
隨著系統(tǒng)中業(yè)務訪問量的增大易遣，如果是單機部署數(shù)據(jù)庫彼妻，就會導致I/O訪問頻率過高。有了主從復制豆茫，增加多個數(shù)據(jù)存儲節(jié)點侨歉，將負載分布在多個從節(jié)點上，降低單機磁盤I/O訪問的頻率揩魂，提高單個機器的I/O性能幽邓。
主從配置需要注意的點
(1)主從服務器操作系統(tǒng)版本和位數(shù)一致；
(2) Master和Slave數(shù)據(jù)庫的版本要一致火脉；
(3) Master和Slave數(shù)據(jù)庫中的數(shù)據(jù)要一致牵舵；
(4) Master開啟二進制日志，Master和Slave的server_id在局域網(wǎng)內必須唯一倦挂；
復制如何工作
復制有三個步驟：
1棋枕、Master將數(shù)據(jù)改變記錄到二進制日志(binary log)中，也就是配置文件log-bin指定的文件妒峦，這些記錄叫做二進制日志事件(binary log events)
2重斑、Slave通過I/O線程讀取Master中的binary log events并寫入到它的中繼日志(relay log)
3、Slave重做中繼日志中的事件肯骇，把中繼日志中的事件信息一條一條的在本地執(zhí)行一次窥浪，完成數(shù)據(jù)在本地的存儲，從而實現(xiàn)將改變反映到它自己的數(shù)據(jù)(數(shù)據(jù)重放)
復制涉及到三個線程：
主節(jié)點 binary log dump 線程（IO線程）
當從節(jié)點連接主節(jié)點時笛丙，主節(jié)點會創(chuàng)建一個log dump 線程漾脂，用于發(fā)送bin-log的內容。在讀取bin-log中的操作時胚鸯，此線程會對主節(jié)點上的bin-log加鎖骨稿，當讀取完成躏升，甚至在發(fā)動給從節(jié)點之前扯罐，鎖會被釋放捏萍。從節(jié)點I/O線程
當從節(jié)點上執(zhí)行start slave命令之后波材，從節(jié)點會創(chuàng)建一個I/O線程用來連接主節(jié)點复凳，請求主庫中更新的bin-log菌羽。I/O線程接收到主節(jié)點binlog dump 進程發(fā)來的更新之后升略，保存在本地relay-log中衷蜓。從節(jié)點SQL線程
SQL線程負責讀取relay log中的內容拟糕，解析成具體的操作并執(zhí)行判呕，最終保證主從數(shù)據(jù)的一致性倦踢。
我們根據(jù)上圖來分析一下整個主從復制的過程：
- Master記錄二進制日志， 每次提交事務完成數(shù)據(jù)更新前侠草，Master將數(shù)據(jù)更新的時間記錄到二進制日志中辱挥，MySQL會按事務提交的順序而非每條語句的執(zhí)行順序來記錄二進制日志。記錄二進制日志后边涕，主庫會告訴存儲引擎可以提交事務了晤碘。
- 在Slave服務器上執(zhí)行start slave命令開啟主從復制開關，開始進行主從復制奥吩。
- 此時哼蛆，Slave服務器的IO線程會通過在master上已經(jīng)授權的復制用戶權限請求連接Master服務器，并請求從執(zhí)行binlog日志文件中的指定位置（日志文件名和位置就是在配置主從復制服務時執(zhí)行change master命令指定的）之后開始發(fā)送binlog日志內容霞赫。
- Master服務器接收來自Slave服務器的IO線程的請求后腮介，其負責復制的IO線程會根據(jù)Slave服務器的IO線程請求的信息分批讀取指定binlog日志文件指定位置之后的binlog日志信息，然后返回給Slave端的IO線程端衰。返回的信息中除了binlog日志內容外叠洗，還有在Master服務器端記錄的IO線程。返回的信息中除了binlog中的下一個指定更新位置旅东。
- 當Slave服務器的IO線程獲取到Master服務器上IO線程發(fā)送的日志內容灭抑、日志文件及位置點后，會將binlog日志內容依次寫到Slave端自身的Relay Log（即中繼日志）文件（Mysql-relay-bin.xxx）的最末端抵代，并將新的binlog文件名和位置記錄到master-info文件中腾节，以便下一次讀取master端新binlog日志時能告訴Master服務器從新binlog日志的指定文件及位置開始讀取新的binlog日志內容
- Slave服務器端的SQL線程會實時檢測本地Relay Log 中IO線程新增的日志內容，然后及時把Relay LOG 文件中的內容解析成sql語句荤牍，并在自身Slave服務器上按解析SQL語句的位置順序執(zhí)行應用這樣sql語句案腺，并在relay-log.info中記錄當前應用中繼日志的文件名和位置點
點評：用于傳輸binlog的線程就是IO線程，包括slave發(fā)起的到master的鏈接康吵，這個就是slave的IO線程劈榨，同時，master跟slave連接后產(chǎn)生一個線程晦嵌，這個線程也就是master的IO線程同辣，這個連接過程不需要解析SQL，所以沒有SQL線程惭载。 而到了slave重放relay log中繼日志的時候旱函，需要把日志轉換成SQL然后執(zhí)行到，這時候就是slave的SQL線程在起作用了棕兼！
這種復制架構實現(xiàn)了獲取事件和重放事件的解偶陡舅，一個是獲取binlog的IO線程，一個是重放中繼日志的SQL線程伴挚，允許這兩個過程異步進行靶衍。也就是說I/O線程能夠獨立于SQL線程之外工作。但這種架構頁限制了復制的過程茎芋，其中最重要的一點是主庫上并發(fā)運行的查詢再從庫只能串行化執(zhí)行颅眶，因為只有一個SQL線程重放中繼日志中的事件。這是很多工作負載的性能瓶頸所在田弥。因為始終受限于單線程涛酗。
復制類型
1、基于語句的復制 Statement-base Replication（SBR）
在Master上執(zhí)行的SQL語句偷厦，在Slave上執(zhí)行同樣的語句商叹。MySQL默認采用基于語句的復制，效率比較高只泼。一旦發(fā)現(xiàn)沒法精確復制時剖笙，會自動選著基于行的復制
優(yōu)點是只需要記錄會修改數(shù)據(jù)的sql語句到binlog，減少binlog日志量（如果修改了一個表的記錄请唱，也只是一條SQL的記錄）弥咪，節(jié)約I/O 。不僅能用于復制十绑，還能實時還原數(shù)據(jù)庫聚至。因為它記錄了所有的SQL，還原數(shù)據(jù)庫就很容易本橙。
缺點是如果一個語句很復雜扳躬，那么slave執(zhí)行的時候就會很耗資源，而基于行復制的話甚亭，只會記錄變更的行記錄贷币。還有UPDATE語句在WHERE沒有使用索引的情況下，會比基于行使用更多的行鎖
2狂鞋、基于行的復制
把改變的內容復制到Slave片择，而不是把命令在Slave上執(zhí)行一遍。從MySQL5.0開始支持
優(yōu)點是 只會記錄變更的行記錄骚揍，哪怕一個語句很復雜字管，但是它最后只影響幾條記錄，那么行的復制信不，只會把影響到幾條記錄記錄到binlog嘲叔，降低slave重放日志時的資源消耗。
缺點是：它的日志很大抽活，因為可能一條SQL修改了一個表的記錄硫戈，但是它要把表的所有變更的記錄都記錄下來。而且它無法看到執(zhí)行了什么SQL下硕，不利于數(shù)據(jù)庫的還原
3丁逝、混合類型的復制
默認采用基于語句的復制汁胆，一旦發(fā)現(xiàn)基于語句的無法精確的復制時，就會采用基于行的復制
混合方式就是有mysql自動選擇RBR方式和SBR方式霜幼，能夠充分發(fā)揮兩種方式的優(yōu)點嫩码，一般情況下都使用該種方式實現(xiàn)主從復制
相應地，binlog的格式也有三種：STATEMENT罪既，ROW铸题，MIXED。
啟動多個Mysql實例
要配置主從復制琢感，我們在本機開多個Mysql實例來操作就可以了丢间，讓他們監(jiān)聽不同端口
多開實例可以看我另一篇教程：同一臺Ubuntu 啟動多個mysql
主從復制配置
現(xiàn)在我們兩個實例Mysql
server1 : 127.0.0.1 3306 master
server2 : 127.0.0.1 3301 slave
配置master
在主庫創(chuàng)建一個復制帳號，這個帳號是給從庫的IO線程建立連接到主庫時用的驹针，從庫會用這個帳號連接主庫并讀取主庫的二進制日志：
grant replication slave, replication client on *.* to 'repl'@'localhost' identified by '123456';
主庫添加配置：
# 設置server_id烘挫，一般設置為IP, 要獨一無二的
server-id = 10
# 開啟二進制日志功能，最好是絕對路徑
log_bin = /var/log/mysql/mysql-bin.log
# 主從復制的格式（mixed,statement,row牌捷，默認格式是statement）
binlog_format=mixed
# 二進制日志自動刪除/過期的天數(shù)墙牌。默認值為0，表示不自動刪除暗甥。
expire_logs_days=7
# 為每個session 分配的內存喜滨，在事務過程中用來存儲二進制日志的緩存
binlog_cache_size=1M
# 復制過濾：不需要備份的數(shù)據(jù)庫，不輸出（mysql庫一般不同步）
binlog-ignore-db=mysql
啟用二進制日志后撤防，重啟后虽风， show master status; 可以看到二進制相關信息
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 | 107 | | |
+------------------+----------+--------------+------------------+
添加從庫配置
# 設置server_id，一般設置為IP, 要獨一無二的
server-id = 10
log_bin = /var/log/mysql/mysql-bin.log
# 中繼日志路徑
relay_log = /home/mysql/3301/mysql-relay-bin
# 允許從庫將其重放的事件也記錄到自身的二進制日志中
log_slave_updates = 1
read_only = 1
從庫開啟復制
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設為0寄月，是從日志開頭開始復制辜膝，MASTER_LOG_FILE是master的二進制文件
# 啟動復制
mysql> start slave;
# 查看復制狀態(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 說明同步正常進行
# Seconds_Behind_Master: 0 就是完全同步了
這時就完成了主從復制的配置漾肮，當主服務器有更新厂抖，從庫也會更新。
我們還可以從線程列表看出復制線程克懊，主庫上可以看到由從庫I/O線程向主庫發(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
同樣，我們看看從庫的線程谭溉，有兩個墙懂，一個I/O線程，一個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
這兩個線程都是再system user 帳號下運行扮念，I/O線程是寫日志到中繼日志的線程损搬， SQL線程是重放SQL的線程。
從已經(jīng)運行已久的服務器開始復制
那么，至此我們已經(jīng)完成了Mysql的主從配置巧勤。
但是上面是配置兩臺剛好安裝號的服務器嵌灰，數(shù)據(jù)相同，并且知道當前主庫二進制日志踢关。
更典型的案例是伞鲫，一個運行已經(jīng)一段時間的主庫粘茄，要用一臺新安裝的從庫與之同步签舞，此時這臺從庫還沒有數(shù)據(jù)。
所以我們得想辦法柒瓣，線初始化從庫： 從主庫復制數(shù)據(jù)儒搭、使用最近依次備份來啟動從庫。
這需要三個條件來讓主庫和從庫保持同步：
復制數(shù)據(jù)到從庫
mysqldump --single-transaction --all-databases --master-data=1 -uroot -p123456|mysql -S /home/mysql/3301/mysqld.sock -uroot -p123456
主從同步的延遲等問題芙贫、原因及解決方案：
相關參數(shù)：
首先在服務器上執(zhí)行show slave satus;可以看到很多同步的參數(shù)：
Master_Log_File： SLAVE中的I/O線程當前正在讀取的主服務器二進制日志文件的名稱
Read_Master_Log_Pos： 在當前的主服務器二進制日志中搂鲫，SLAVE中的I/O線程已經(jīng)讀取的位置
Relay_Log_File： SQL線程當前正在讀取和執(zhí)行的中繼日志文件的名稱
Relay_Log_Pos： 在當前的中繼日志中，SQL線程已讀取和執(zhí)行的位置
Relay_Master_Log_File： 由SQL線程執(zhí)行的包含多數(shù)近期事件的主服務器二進制日志文件的名稱
Slave_IO_Running： I/O線程是否被啟動并成功地連接到主服務器上
Slave_SQL_Running： SQL線程是否被啟動
Seconds_Behind_Master： 從屬服務器SQL線程和從屬服務器I/O線程之間的時間差距磺平，單位以秒計魂仍。
從庫同步延遲情況出現(xiàn)的
● show slave status顯示參數(shù)Seconds_Behind_Master不為0，這個數(shù)值可能會很大
● show slave status顯示參數(shù)Relay_Master_Log_File和Master_Log_File顯示bin-log的編號相差很大拣挪，說明bin-log在從庫上沒有及時同步擦酌，所以近期執(zhí)行的bin-log和當前IO線程所讀的bin-log相差很大
● mysql的從庫數(shù)據(jù)目錄下存在大量mysql-relay-log日志，該日志同步完成之后就會被系統(tǒng)自動刪除菠劝，存在大量日志赊舶，說明主從同步延遲很厲害
MySql數(shù)據(jù)庫從庫同步的延遲問題
1)、MySQL數(shù)據(jù)庫主從同步延遲原理mysql主從同步原理：主庫針對寫操作赶诊，順序寫binlog笼平，從庫單線程去主庫順序讀”寫操作的binlog”，從庫取到binlog在本地原樣執(zhí)行（隨機寫）舔痪，來保證主從數(shù)據(jù)邏輯上一致寓调。mysql的主從復制都是單線程的操作，主庫對所有DDL和DML產(chǎn)生binlog锄码，binlog是順序寫夺英，所以效率很高，slave的Slave_IO_Running線程到主庫取日志巍耗，效率比較高秋麸，下一步，問題來了炬太，slave的Slave_SQL_Running線程將主庫的DDL和DML操作在slave實施灸蟆。DML和DDL的IO操作是隨機的，不是順序的，成本高很多炒考，還可能可slave上的其他查詢產(chǎn)生lock爭用可缚，由于Slave_SQL_Running也是單線程的，所以一個DDL卡主了斋枢，需要執(zhí)行10分鐘帘靡，那么所有之后的DDL會等待這個DDL執(zhí)行完才會繼續(xù)執(zhí)行，這就導致了延時瓤帚。有朋友會問：“主庫上那個相同的DDL也需要執(zhí)行10分描姚，為什么slave會延時？”戈次，答案是master可以并發(fā)轩勘，Slave_SQL_Running線程卻不可以。
2)怯邪、MySQL數(shù)據(jù)庫主從同步延遲是怎么產(chǎn)生的绊寻？當主庫的TPS并發(fā)較高時，產(chǎn)生的DDL數(shù)量超過slave一個sql線程所能承受的范圍悬秉，那么延時就產(chǎn)生了澄步，當然還有就是可能與slave的大型query語句產(chǎn)生了鎖等待。首要原因：數(shù)據(jù)庫在業(yè)務上讀寫壓力太大和泌，CPU計算負荷大村缸，網(wǎng)卡負荷大，硬盤隨機IO太高次要原因：讀寫binlog帶來的性能影響允跑，網(wǎng)絡傳輸延遲王凑。
MySql數(shù)據(jù)庫從庫同步的延遲解決方案
sync_binlog=1 oMySQL提供一個sync_binlog參數(shù)來控制數(shù)據(jù)庫的binlog刷到磁盤上去。默認聋丝，sync_binlog=0索烹，表示MySQL不控制binlog的刷新，由文件系統(tǒng)自己控制它的緩存的刷新弱睦。這時候的性能是最好的百姓，但是風險也是最大的。一旦系統(tǒng)Crash况木，在binlog_cache中的所有binlog信息都會被丟失垒拢。
如果sync_binlog>0，表示每sync_binlog次事務提交火惊，MySQL調用文件系統(tǒng)的刷新操作將緩存刷下去求类。最安全的就是sync_binlog=1了，表示每次事務提交屹耐，MySQL都會把binlog刷下去尸疆，是最安全但是性能損耗最大的設置。這樣的話，在數(shù)據(jù)庫所在的主機操作系統(tǒng)損壞或者突然掉電的情況下寿弱，系統(tǒng)才有可能丟失1個事務的數(shù)據(jù)犯眠。但是binlog雖然是順序IO，但是設置sync_binlog=1症革，多個事務同時提交筐咧，同樣很大的影響MySQL和IO性能。雖然可以通過group commit的補丁緩解噪矛，但是刷新的頻率過高對IO的影響也非常大量蕊。
對于高并發(fā)事務的系統(tǒng)來說，“sync_binlog”設置為0和設置為1的系統(tǒng)寫入性能差距可能高達5倍甚至更多摩疑。所以很多MySQL DBA設置的sync_binlog并不是最安全的1危融，而是2或者是0。這樣犧牲一定的一致性雷袋，可以獲得更高的并發(fā)和性能。默認情況下辞居，并不是每次寫入時都將binlog與硬盤同步楷怒。因此如果操作系統(tǒng)或機器(不僅僅是MySQL服務器)崩潰，有可能binlog中最后的語句丟失了瓦灶。要想防止這種情況鸠删，你可以使用sync_binlog全局變量(1是最安全的值，但也是最慢的)贼陶，使binlog在每N次binlog寫入后與硬盤同步刃泡。即使sync_binlog設置為1,出現(xiàn)崩潰時，也有可能表內容和binlog內容之間存在不一致性碉怔。innodb_flush_log_at_trx_commit （這個很管用）抱怨Innodb比MyISAM慢 100倍烘贴？那么你大概是忘了調整這個值。默認值1的意思是每一次事務提交或事務外的指令都需要把日志寫入（flush）硬盤撮胧，這是很費時的桨踪。特別是使用電池供電緩存（Battery backed up cache）時。設成2對于很多運用芹啥，特別是從MyISAM表轉過來的是可以的锻离，它的意思是不寫入硬盤而是寫入系統(tǒng)緩存。日志仍然會每秒flush到硬 盤墓怀，所以你一般不會丟失超過1-2秒的更新汽纠。設成0會更快一點，但安全方面比較差傀履，即使MySQL掛了也可能會丟失事務的數(shù)據(jù)虱朵。而值2只會在整個操作系統(tǒng) 掛了時才可能丟數(shù)據(jù)。
從服務器關閉 logs-slave-updates ，以為著slave從master接到的更新不寫入二進制日志
直接禁掉從服務器的binlog卧秘，因為從服務器安全性相對沒那么強
并行復制 MySQL5.6增加的特性呢袱，多個線程復制肯定能更快
最后
原創(chuàng)文章出自公眾號：「碼農富哥」，如需轉載請注明出處翅敌！
文章如果對你有收獲羞福，可以收藏轉發(fā)，這會給我一個大大鼓勵喲蚯涮！另外可以關注我公眾號「碼農富哥」 (搜索id：coder2025)治专，我會持續(xù)輸出Python，算法遭顶，計算機基礎的 原創(chuàng) 文章
