一、復制概述

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

復制的主要步驟如下：

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

示意圖：

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

使用復制可以帶來如下好處：

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

二、二進制日志格式

二進制日志在記錄事務變更時有statement、row、mixed三種格式，通過binlog_format系統(tǒng)變量來設置：

statement

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

• row（推薦，也是MySQL8默認的日志格式）

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

mixed

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

二進制日志除了復制還會在數(shù)據(jù)庫故障崩潰時進行恢復使用，因此建議將二進制日志和數(shù)據(jù)文件保存在不同的磁盤，減少I/O爭用。三種日志格式中，理論上基于行的復制（row）更優(yōu)，因為幾乎沒有基于行的復制模式無法處理的場景。對于所有的SQL構造、觸發(fā)器、存儲過程等都能正確執(zhí)行，這也是MySQL8默認的日志格式。

三、復制的配置

MySQL最基本的復制是單路、異步、基于日志位置的復制。其架構是1臺主庫，1臺或多臺從庫通過指定日志文件及位置連接到主庫。

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

• 192.168.3.71（主庫 主機名master）
• 192.168.3.72（從庫 主機名slave01）

3.1 配置主庫

為主庫配置唯一的server_id并打開二進制日志，配置數(shù)據(jù)庫配置文件（Redhat/CentOS默認是/etc/my.cnf）在[mysqld]選項下加入下列參數(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ù)是可選項，根據(jù)自身需要選擇：

• server_id需要在整個復制拓撲中保持唯一，一種通用的建議是采用IP地址的后8位，只要遵循某種規(guī)則保持唯一即可。
• log_bin用于打開二進制日志并明確指定日志名稱，默認日志采用主機名命名，建議明確指定日志名，否則后期主機更名容易帶來問題。
• sync_binlog（強烈推薦）保證每次提交事務會將binlog同步到磁盤，保證服務器崩潰時不丟失事務，還可以防止主從不一致。
• innodb_flush_log_at_trx_commit 保證每次提交事務Innodb將日志寫入redo log，只針對innodb表。

3.2 配置從庫

修改從庫的配置參數(shù)，必要時重啟服務器。在[mysqld]選項下加入下列參數(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是必須的，其他都是可選項，自己可以根據(jù)需要選擇：

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

3.3 創(chuàng)建復制專用用戶

在主庫上創(chuàng)建用戶并賦予replication slave權限：

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

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

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

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

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

下面示例采用mysqldump轉(zhuǎn)儲的方式，在主庫開啟一個會話執(zhí)行下面語句：

flush tables with read lock;
show master status;

第一句會阻止所有的數(shù)據(jù)庫變更，第二句顯示當前的日志名稱和位置，記錄下來，這個就是復制的起點。

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

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

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

將上述轉(zhuǎn)儲文件傳輸?shù)絺鋷觳耄?/p>

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

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

mysql < dbdump.sql

3.5 將從庫指向主庫并啟動復制：

在從庫上執(zhí)行change master to（8.0.23版本以上使用change replication source to），指定主庫位置及我們在第三步建立的復制用戶：

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就是上一步主庫show master status顯示的日志名和偏移量，由于我們在轉(zhuǎn)儲時加了--master-data選項，所以備份文件中自動會帶上這個坐標，不加也可以。

啟動復制：

start slave;
show slave status \G;

start slave 語句會啟動從庫上的I/O線程和SQL線程，并且連接到主庫（主庫上啟動binlog dump線程）。

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

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

最新評論