一、復(fù)制概述

MySQL的復(fù)制就是將來(lái)自一個(gè)MySQL數(shù)據(jù)庫(kù)服務(wù)器（主庫(kù)）的數(shù)據(jù)復(fù)制到一個(gè)或多個(gè)MySQL數(shù)據(jù)庫(kù)服務(wù)器（從庫(kù)）。其工作原理是通過(guò)binlog（二進(jìn)制日志）記錄事務(wù)變更然后傳送到從庫(kù)并重放事務(wù)，保持?jǐn)?shù)據(jù)一致。

復(fù)制的主要步驟如下：

• 主庫(kù)事務(wù)提交，MySQL將事務(wù)變更記錄到binlog。
• 主庫(kù)上日志轉(zhuǎn)儲(chǔ)線程（binlog dump）將日志傳遞給從庫(kù)I/O線程。
• 從庫(kù)I/O線程將日志中的事件記錄到本地的中繼日志中（relay log）。
• 從庫(kù)SQL線程從中繼日志中讀取事務(wù)，應(yīng)用變更，保持?jǐn)?shù)據(jù)和主庫(kù)一致。

示意圖：

如果binlog dump線程追趕上了主庫(kù)，它將進(jìn)入睡眠狀態(tài)，直到主庫(kù)發(fā)送信號(hào)量通知其有新的事件產(chǎn)生時(shí)才會(huì)被喚醒，備庫(kù)I/O線程會(huì)將接收到的事件記錄到中繼日志中。

使用復(fù)制可以帶來(lái)如下好處：

• 復(fù)制可以將讀操作分布到多個(gè)服務(wù)器上，對(duì)讀密集型業(yè)務(wù)有更好的承載能力。
• 由于讀的壓力分離至從庫(kù)，主庫(kù)可以分配更多的資源來(lái)響應(yīng)寫(xiě)請(qǐng)求。
• 提高安全，可以利用延遲復(fù)制等特性，快速恢復(fù)主庫(kù)上的誤操作。
• 高可用，復(fù)制+故障切換系統(tǒng)，可以讓系統(tǒng)宕機(jī)時(shí)快速恢復(fù)，響應(yīng)請(qǐng)求。

二、二進(jìn)制日志格式

二進(jìn)制日志在記錄事務(wù)變更時(shí)有statement、row、mixed三種格式，通過(guò)binlog_format系統(tǒng)變量來(lái)設(shè)置：

statement

基于SQL語(yǔ)句的復(fù)制（Statement-Based Replication，SBR），將修改數(shù)據(jù)的SQL語(yǔ)句都會(huì)被記錄到binlog中，優(yōu)點(diǎn)是不需要記錄每行的數(shù)據(jù)變化，這樣二進(jìn)制日志會(huì)比較少。缺點(diǎn)是在某些情況下不能很好工作，例如last_insert_id()、now()等非確定性函數(shù)，以及用戶(hù)自定義函數(shù)（User-Defined Function，UDF）、存儲(chǔ)過(guò)程、觸發(fā)器時(shí)也易出問(wèn)題。

• row（推薦，也是MySQL8默認(rèn)的日志格式）

基于行的復(fù)制（Row-Based Replication，RBR）。該格式不記錄SQL語(yǔ)句，僅記錄哪條數(shù)據(jù)被修改了，修改成了什么樣子，能清楚地記錄每一行數(shù)據(jù)的修改前后細(xì)節(jié)。優(yōu)點(diǎn)是不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過(guò)程、函數(shù)或觸發(fā)器的調(diào)用和觸發(fā)無(wú)法被正確復(fù)制的問(wèn)題。缺點(diǎn)是通常會(huì)產(chǎn)生大量的日志。

mixed

混合復(fù)制（Mixed-Based Replication，MBR）。它是STATEMENT和ROW這兩種格式的混合體，默認(rèn)使用STATEMENT格式保存二進(jìn)制日志，對(duì)于STATEMENT格式無(wú)法正確復(fù)制的操作，會(huì)自動(dòng)切換到基于ROW格式的復(fù)制操作，MySQL會(huì)根據(jù)執(zhí)行的SQL語(yǔ)句選擇日志保存方式。

二進(jìn)制日志除了復(fù)制還會(huì)在數(shù)據(jù)庫(kù)故障崩潰時(shí)進(jìn)行恢復(fù)使用，因此建議將二進(jìn)制日志和數(shù)據(jù)文件保存在不同的磁盤(pán)，減少I(mǎi)/O爭(zhēng)用。三種日志格式中，理論上基于行的復(fù)制（row）更優(yōu)，因?yàn)閹缀鯖](méi)有基于行的復(fù)制模式無(wú)法處理的場(chǎng)景。對(duì)于所有的SQL構(gòu)造、觸發(fā)器、存儲(chǔ)過(guò)程等都能正確執(zhí)行，這也是MySQL8默認(rèn)的日志格式。

三、復(fù)制的配置

MySQL最基本的復(fù)制是單路、異步、基于日志位置的復(fù)制。其架構(gòu)是1臺(tái)主庫(kù)，1臺(tái)或多臺(tái)從庫(kù)通過(guò)指定日志文件及位置連接到主庫(kù)。

現(xiàn)有2臺(tái)數(shù)據(jù)庫(kù)環(huán)境如下，示例基本異步復(fù)制的配置步驟：

• 192.168.3.71（主庫(kù) 主機(jī)名master）
• 192.168.3.72（從庫(kù) 主機(jī)名slave01）

3.1 配置主庫(kù)

為主庫(kù)配置唯一的server_id并打開(kāi)二進(jìn)制日志，配置數(shù)據(jù)庫(kù)配置文件（Redhat/CentOS默認(rèn)是/etc/my.cnf）在[mysqld]選項(xiàng)下加入下列參數(shù)：

[mysqld]
server_id=71
log_bin = bin-log
sync_binlog = 1
innodb_flush_log_at_trx_commit =1

其中server_id和log_bin參數(shù)是必選，其他參數(shù)是可選項(xiàng)，根據(jù)自身需要選擇：

• server_id需要在整個(gè)復(fù)制拓?fù)渲斜３治ㄒ?，一種通用的建議是采用IP地址的后8位，只要遵循某種規(guī)則保持唯一即可。
• log_bin用于打開(kāi)二進(jìn)制日志并明確指定日志名稱(chēng)，默認(rèn)日志采用主機(jī)名命名，建議明確指定日志名，否則后期主機(jī)更名容易帶來(lái)問(wèn)題。
• sync_binlog（強(qiáng)烈推薦）保證每次提交事務(wù)會(huì)將binlog同步到磁盤(pán)，保證服務(wù)器崩潰時(shí)不丟失事務(wù)，還可以防止主從不一致。
• innodb_flush_log_at_trx_commit 保證每次提交事務(wù)Innodb將日志寫(xiě)入redo log，只針對(duì)innodb表。

3.2 配置從庫(kù)

修改從庫(kù)的配置參數(shù)，必要時(shí)重啟服務(wù)器。在[mysqld]選項(xiàng)下加入下列參數(shù)：

server_id = 72
relay_log = relay-bin
log_bin = bin-log
log_slave_updates = 1
read_only = 1
skip_slave_start = 1

上面的配置只有server_id=72是必須的，其他都是可選項(xiàng)，自己可以根據(jù)需要選擇：

• relay_log用于指定中繼日志名稱(chēng)以避免主機(jī)更名帶來(lái)的問(wèn)題。
• log_bin和log_slave_update用來(lái)控制slave在復(fù)制時(shí)同時(shí)也將事件寫(xiě)入自己的二進(jìn)制日志（級(jí)聯(lián)復(fù)制使用）。
• read_only=1備庫(kù)建議開(kāi)啟，用來(lái)防止普通用戶(hù)修改數(shù)據(jù)，但具有super權(quán)限的用戶(hù)依然是可以修改的。
• skip_slave_start阻止備庫(kù)啟動(dòng)時(shí)自動(dòng)開(kāi)啟復(fù)制，如果備庫(kù)在崩潰后處于不一致的狀態(tài)下自動(dòng)啟動(dòng)復(fù)制，可能會(huì)導(dǎo)致更多的損壞。

3.3 創(chuàng)建復(fù)制專(zhuān)用用戶(hù)

在主庫(kù)上創(chuàng)建用戶(hù)并賦予replication slave權(quán)限：

create user 'repuser'@'192.168.3.%' identified  by 'repP@ssword';
grant replication slave on *.* to 'repuser'@'192.168.3.%';

3.4 同步數(shù)據(jù)

大部分情況下主庫(kù)都是都不是空的，這就需要在開(kāi)啟復(fù)制前獲取主庫(kù)的快照并還原到從庫(kù)，保證復(fù)制開(kāi)始時(shí)數(shù)據(jù)一致。主要方法有3種：

• 直接復(fù)制數(shù)據(jù)文件（需要關(guān)閉主庫(kù)暫停業(yè)務(wù)）
• 使用mysqldump工具轉(zhuǎn)儲(chǔ)（便捷、但數(shù)據(jù)量大時(shí)速度較慢）
• 使用xtrabackup等第三方工具轉(zhuǎn)儲(chǔ)（便捷、速度較快）

第一種方法由于需要關(guān)庫(kù)，意味著業(yè)務(wù)要生產(chǎn)業(yè)務(wù)要暫停，通常不會(huì)采用。第二種采用mysqldump轉(zhuǎn)儲(chǔ)，適合數(shù)據(jù)量中等的情況。如果不能關(guān)庫(kù)，采用mysqldump轉(zhuǎn)儲(chǔ)又太慢，可以試著采用第三方工具xtrabackup，由于是采用物理層面的數(shù)據(jù)文件備份，所以速度比mysqldump快很多。

下面示例采用mysqldump轉(zhuǎn)儲(chǔ)的方式，在主庫(kù)開(kāi)啟一個(gè)會(huì)話執(zhí)行下面語(yǔ)句：

flush tables with read lock;
show master status;

第一句會(huì)阻止所有的數(shù)據(jù)庫(kù)變更，第二句顯示當(dāng)前的日志名稱(chēng)和位置，記錄下來(lái)，這個(gè)就是復(fù)制的起點(diǎn)。

此時(shí)，另開(kāi)一個(gè)會(huì)話獲取數(shù)據(jù)快照，注意備份時(shí)第一個(gè)執(zhí)行flush table with read lock的會(huì)話不能退出，否則可能會(huì)發(fā)生數(shù)據(jù)變更。

mysqldump --all-databases --master-data > dbdump.sql

導(dǎo)出之后第一個(gè)會(huì)話就可以退出了，或者執(zhí)行unlock tables，讓主庫(kù)繼續(xù)執(zhí)行業(yè)務(wù)。

將上述轉(zhuǎn)儲(chǔ)文件傳輸?shù)絺鋷?kù)并導(dǎo)入：

scp dbdump.sql root@'192.168.3.72':/root

登錄到備庫(kù)上還原數(shù)據(jù)，此時(shí)主備庫(kù)的數(shù)據(jù)已經(jīng)相同，可以啟動(dòng)復(fù)制了：

mysql < dbdump.sql

3.5 將從庫(kù)指向主庫(kù)并啟動(dòng)復(fù)制：

在從庫(kù)上執(zhí)行change master to（8.0.23版本以上使用change replication source to），指定主庫(kù)位置及我們?cè)诘谌浇⒌膹?fù)制用戶(hù)：

change master to
master_host = '192.168.3.71',
master_user='repuser',
master_password='repP@ssword',
master_log_file='mysql-bin.000009',
master_log_pos=1174;

最后兩句master_log_file,master_log_pos就是上一步主庫(kù)show master status顯示的日志名和偏移量，由于我們?cè)谵D(zhuǎn)儲(chǔ)時(shí)加了--master-data選項(xiàng)，所以備份文件中自動(dòng)會(huì)帶上這個(gè)坐標(biāo)，不加也可以。

啟動(dòng)復(fù)制：

start slave;
show slave status \G;

start slave 語(yǔ)句會(huì)啟動(dòng)從庫(kù)上的I/O線程和SQL線程，并且連接到主庫(kù)（主庫(kù)上啟動(dòng)binlog dump線程）。

show slave status，我們可以看到備庫(kù)的I/O和SQL線程都已經(jīng)起來(lái)了，Slave_IO_State顯示正在等待主庫(kù)發(fā)送更多的事件。MySQL的基礎(chǔ)異步復(fù)制就完成了。

到此這篇關(guān)于MySQL實(shí)現(xiàn)異步復(fù)制的示例的文章就介紹到這了,更多相關(guān)MySQL 異步復(fù)制內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！