本文實(shí)例講述了mysql 復(fù)制原理與實(shí)踐應(yīng)用。分享給大家供大家參考，具體如下：

復(fù)制功能是將一個(gè)mysql數(shù)據(jù)庫(kù)上的數(shù)據(jù)復(fù)到一個(gè)或多個(gè)mysql從數(shù)據(jù)庫(kù)上。

復(fù)制的原理：在主服務(wù)器上執(zhí)行的所有DDL和DML語(yǔ)句都會(huì)被記錄到二進(jìn)制日志中，這些日志由連接到它的從服務(wù)器獲取，并復(fù)制到從庫(kù)，并保存為中繼日志，

這個(gè)過(guò)程由一個(gè)稱(chēng)為 IO線(xiàn)程 的線(xiàn)程負(fù)責(zé)，還有一個(gè)稱(chēng)為 SQL線(xiàn)程 的則按順序執(zhí)行中繼日志中的語(yǔ)句。

復(fù)制有多種拓?fù)湫问剑?/p>

1、傳統(tǒng)復(fù)制，一主多從，一個(gè)主服務(wù)器多個(gè)從服務(wù)器。

2、鏈?zhǔn)綇?fù)制，一臺(tái)服務(wù)器從主庫(kù)復(fù)制，而另一臺(tái)服務(wù)器又從這臺(tái)復(fù)制，中間服務(wù)器又叫中繼主庫(kù)。

3、主主復(fù)制，兩個(gè)主庫(kù)互相接受寫(xiě)入和復(fù)制。

4、多源復(fù)制，一個(gè)從庫(kù)，從多個(gè)主庫(kù)復(fù)制。

一、復(fù)制如何操作

1、在主庫(kù)上啟用二進(jìn)制日志記錄。

2、在主庫(kù)上創(chuàng)建一個(gè)復(fù)制用戶(hù)。

3、在從訓(xùn)上設(shè)置唯一的 server_id。

4、從主庫(kù)中備份數(shù)據(jù)。

5、在從庫(kù)上恢復(fù)主庫(kù)備份的數(shù)據(jù)。

6、執(zhí)行CHANGE MASTER TO命令。

7、開(kāi)始復(fù)制。

二、具體的操作步驟如下：

1、在 主庫(kù) 上，啟用二進(jìn)制日志并設(shè)置server_id。

#設(shè)置server_id
server_id = 1
#開(kāi)啟binlog日志
log-bin = mysql-bin

2、在主庫(kù)上創(chuàng)建復(fù)制用戶(hù)

create user '用戶(hù)名'@'%' identified by '密碼';
grant replication slave on *.* to '用戶(hù)名'@'%';

3、在從庫(kù)上設(shè)置server_id

#設(shè)置server_id
server_id = 10

4、備份主庫(kù)上的數(shù)據(jù)

mysqldump -u root -p --all-databases --routines --events --triggers --single-transaction --master-data > 導(dǎo)出路徑

5、在從庫(kù)上恢復(fù)主庫(kù)導(dǎo)出的數(shù)據(jù)

mysql -u root -p -f < 主庫(kù)備份文件.sql

6、在從庫(kù)上執(zhí)行 CHANGE MASTER TO 命令

CHANGE MASTER TO 
MASTER_HOST='主庫(kù)IP',
MASTER_USER='主庫(kù)復(fù)制用戶(hù)',
MASTER_PASSWORD='密碼',
MASTER_LOG_FILE='二進(jìn)制日志名稱(chēng)',
MASTER_LOG_POS=二進(jìn)制日志位置;

二進(jìn)制日志名稱(chēng)和二進(jìn)制日志位置，已經(jīng)在備份主庫(kù)文件中包含了，類(lèi)似如下所示：

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=47845;

7、從庫(kù)上運(yùn)行 start slave，然后 show slave status\G; 查看復(fù)制狀態(tài);

三、設(shè)置主主復(fù)制

假設(shè)主庫(kù)分別是master1和master2。

1、設(shè)置master2為只讀

set @@global.read_only = on;

2、在master2上創(chuàng)建復(fù)制用戶(hù)，如果存在，則不用創(chuàng)建了

create user '用戶(hù)名'@'%' identified by '密碼';
grant replication slave on *.* to '用戶(hù)名'@'%';

3、確保master2已開(kāi)啟二進(jìn)制日志，檢查master2上的二進(jìn)制日志的坐標(biāo)

show master status;

4、根據(jù)第2步的信息，在master1上執(zhí)行 CHANGE MASTER TO 命令

CHANGE MASTER TO 
MASTER_HOST='MASTER2主機(jī)IP',
MASTER_USER='MASTER2復(fù)制用戶(hù)',
MASTER_PASSWORD='密碼',
MASTER_LOG_FILE='二進(jìn)制日志名稱(chēng)',
MASTER_LOG_POS=二進(jìn)制日志位置;

5、在master1上開(kāi)啟 slave 模式

start slave;

6、設(shè)置master2為可讀寫(xiě)

set @@global.read_only = off;

四、設(shè)置多源復(fù)制

設(shè)置 server3 為 server1 和 server2 的從庫(kù)。

1、設(shè)置 server1 和 server2 的二進(jìn)制日志和server_id，具體操作可參考上面。

2、在 server1 和 server2 上創(chuàng)建復(fù)制用戶(hù)，具體操作可參考上面。

3、在 server3 上設(shè)置 server_id。

4、備份 server1 和 server2 的數(shù)據(jù)。

5、在 server3 上恢復(fù) server1 和 server2 上備份的數(shù)據(jù)。

6、在 server3 上，將復(fù)制存儲(chǔ)庫(kù)從 FILE 改為 TABLE，

stop slave;
set global master_info_repository = 'TABLE';
set global relay_log_info_repository = 'TABLE';

 還需要在配置文件中修改：

[mysqld]
master-info-repository = TABLE
relay-log-info-repository = TABLE

7、在 server3 上，執(zhí)行 CHANGE MASTER TO 命令，并命名通道名

CHANGE MASTER TO 
MASTER_HOST='server1主機(jī)IP',
MASTER_USER='server1復(fù)制用戶(hù)',
MASTER_PASSWORD='密碼',
MASTER_LOG_FILE='server1二進(jìn)制日志名稱(chēng)',
MASTER_LOG_POS=server1二進(jìn)制日志位置 FOR CHANNEL 'server1';

CHANGE MASTER TO 
MASTER_HOST='server2主機(jī)IP',
MASTER_USER='server2復(fù)制用戶(hù)',
MASTER_PASSWORD='密碼',
MASTER_LOG_FILE='server2二進(jìn)制日志名稱(chēng)',
MASTER_LOG_POS=server2二進(jìn)制日志位置 FOR CHANNEL 'server2';

8 、在 server3 上，為每個(gè)通道執(zhí)行 START SLAVE FOR CHANNEL 語(yǔ)句

start slave for channel 'server1';
start slave for channel 'server2';

9、查看同步狀態(tài)，show slave status\G;

要獲取指定通道的從庫(kù)狀態(tài)，show slave status for channel '通道名稱(chēng)'\G;

五、設(shè)置復(fù)制篩選器

可以選擇要復(fù)制哪些表或數(shù)據(jù)庫(kù)，在主庫(kù)上，可以使用--binlog-do-db 和 --binlog-ignore-db 選項(xiàng)來(lái)選擇要記錄變更的數(shù)據(jù)庫(kù)，以控制二進(jìn)制日志。更好的方法是控制從庫(kù)。

1、復(fù)制指定數(shù)據(jù)庫(kù)

CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);

2、復(fù)制指定表

CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = ('db1.table1');

3、如果想使用通配符來(lái)選擇表

CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.tb_%');

4、忽略數(shù)據(jù)庫(kù)

CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (db1, db2);

5、忽略指定表

CHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE = ('db1.table1');

六、將從庫(kù)由主從復(fù)制切換到鏈?zhǔn)綇?fù)制

比如現(xiàn)在服務(wù)器A為主庫(kù)，服務(wù)器B和服務(wù)器C為從庫(kù)，復(fù)制于服務(wù)器A?，F(xiàn)在想把服務(wù)器C作為服務(wù)器B的從庫(kù)。

1、在服務(wù)器C上停止從庫(kù)運(yùn)行

stop slave;
show slave status\G;

記錄下Relay_Master_Log_File和Exec_Master_Log_Pos的值

2、在服務(wù)器B上停止從庫(kù)運(yùn)行

stop slave;
show slave status\G;

記錄下Relay_Master_Log_File和Exec_Master_Log_Pos的值

3、將服務(wù)器B的日志位置與服務(wù)器C的進(jìn)行比較，找出哪一個(gè)是服務(wù)器A最新同步，通常，服務(wù)器C先停止從庫(kù)運(yùn)行，服務(wù)器B的日志會(huì)更靠前。

4、在服務(wù)器C上，使用 START SLAVE UNTIL 語(yǔ)句將其同步到服務(wù)器B的日志位置：

START SLAVE UNTIL MASTER_LOG_FILE='上一步中服務(wù)器B日志名稱(chēng)', MASTER_LOG_POS=上一步中服務(wù)器B日志位置;

5、在服務(wù)器C上，檢查 show slave status 中 Exec_Master_Log_Pos 和 Until_Log_Pos 兩者應(yīng)該相同。

6、在服務(wù)器B上，查看主庫(kù)狀態(tài)，啟動(dòng)從庫(kù)。

show master status;
start slave;
show slave status\G;

7、在服務(wù)器C上，停止從庫(kù)運(yùn)行，執(zhí)行 CHANGE MASTER TO 命令。

stop slave;
CHANGE MASTER TO
MASTER_HOST='服務(wù)器B的IP',
MASTER_USER='服務(wù)器B復(fù)制用戶(hù)',
MASTER_PASSWORD='密碼',
MASTER_LOG_FILE='上一步中通過(guò)show master status獲取日志名稱(chēng)',
MASTER_LOG_POS=上一步中通過(guò)show master status獲取日志位置;

8、在服務(wù)器C上，啟動(dòng)復(fù)制并查看狀態(tài)

start slave;
show slave status\G;

七、將鏈?zhǔn)綇?fù)制切換到主從復(fù)制

服務(wù)器A->服務(wù)器B->服務(wù)器C，如果想讓服務(wù)器C直接作為服務(wù)器A的從庫(kù)，該怎么做？

1、在服務(wù)器B上，停止從庫(kù)運(yùn)行，并記錄主庫(kù)狀態(tài)

stop slave;
show master status\G;

2、服務(wù)器C上，確保從庫(kù)的延遲已被追上，Relay_Master_Log_File和Exec_Master_Log_Pos應(yīng)該等于服務(wù)器B上主庫(kù)狀態(tài)。

一旦延遲被追上，就停止從庫(kù)的運(yùn)行。

stop slave;

3、在服務(wù)器B上，從 show slave status 中獲取服務(wù)器A的日志坐標(biāo)值(Relay_Master_Log_File和Exec_Master_Log_Pos)，并啟動(dòng)從庫(kù)

show slave status\G;
start slave;

4、在服務(wù)器C上，停止從庫(kù)運(yùn)行，并執(zhí)行 CHANGE MASTER TO 命令，指向服務(wù)器A

stop slave;
CHANGE MASTER TO 
MASTER_HOST='服務(wù)器A的IP',
MASTER_USER='服務(wù)器A的復(fù)制用戶(hù)',
MASTER_PASSWORD='密碼',
MASTER_LOG_FILE='上一步中獲取的日志',
MASTER_LOG_POS=上一步中獲取的日志位置;

5、在服務(wù)器C上，開(kāi)啟從庫(kù)，并查看狀態(tài)。

start slave;
show slave status\G;

八、設(shè)置延遲復(fù)制

為什么需要延遲復(fù)制，有可能主庫(kù)上執(zhí)行了一條災(zāi)難性語(yǔ)句，你必須通過(guò)備份中的時(shí)間點(diǎn)恢復(fù)，如果數(shù)據(jù)庫(kù)大小過(guò)大，這將導(dǎo)致長(zhǎng)時(shí)間停機(jī)。

為了避免出現(xiàn)這種情況，可以使用一個(gè)延遲的從庫(kù)，如果發(fā)生了災(zāi)難，并且延遲的從庫(kù)還沒(méi)有執(zhí)行這條災(zāi)難性語(yǔ)句，則可以先停止復(fù)制，讓從庫(kù)跳過(guò)該災(zāi)難語(yǔ)句，最后把從庫(kù)提升為主庫(kù)。

1、停止從庫(kù)運(yùn)行

stop slave;

2、設(shè)置延遲時(shí)間，以秒為單位

CHANGE MASTER TO MASTER_DELAY = 3600;
start slave;

3、檢查從庫(kù)狀態(tài)

show slave status\G;

SQL_Delay: 從庫(kù)延遲于主庫(kù)的秒數(shù)。

SQL_Remaining_Delay：延遲還剩余的秒數(shù)，當(dāng)保持延遲時(shí)，這個(gè)值是NULL。

Slave_SQL_Running_State：SQL線(xiàn)程的狀態(tài)

九、設(shè)置 GTID 復(fù)制

全局事務(wù)標(biāo)識(shí)符 GTID 是在程序中創(chuàng)建的唯一標(biāo)識(shí)符，并與主庫(kù)上提交的每個(gè)事務(wù)相關(guān)聯(lián)。該標(biāo)識(shí)符是唯一的，不僅在主庫(kù)上，在其他從庫(kù)上，它都唯一。

上面描述的所有復(fù)制，都需要指明二進(jìn)制文件和復(fù)制起點(diǎn)的位置，如果將一個(gè)從庫(kù)的主庫(kù)切換到另一個(gè)，就必須重新獲取二進(jìn)制文件位置，這會(huì)很麻煩。

為了避免，可以使用基于 GTID 的復(fù)制，mysql 使用 GTID 自動(dòng)檢測(cè)二進(jìn)制日志的位置。

1、在所有數(shù)據(jù)庫(kù)中 my.cnf 中啟動(dòng) GTID

[mysqld]
gtid_mode = ON
enforce-gtid-consistency = 1
skip_slave_start

2、將主庫(kù)設(shè)置為只讀，確保主庫(kù)與從庫(kù)數(shù)據(jù)一致。

set @@global.read_only = on;

3、重新啟動(dòng)所有從庫(kù)，使 GTID 生效。

4、重新啟動(dòng)主庫(kù)。

5、在從庫(kù)上執(zhí)行 CHANGE MASTER TO 命令來(lái)設(shè)置 GTID 復(fù)制

CHANGE MASTER TO
MASTER_HOST='主庫(kù)IP',
MASTER_PORT=3306,
MASTER_USER='復(fù)制用戶(hù)',
MASTER_PASSWORD='密碼',
MASTER_AUTO_POSITION=1;

6、在所有從庫(kù)上執(zhí)行 start slave; 并查看狀態(tài)。

十、設(shè)置半同步復(fù)制

默認(rèn)情況下，復(fù)制是異步的，主庫(kù)不知道寫(xiě)入操作是否到達(dá)從庫(kù)，如果主庫(kù)與從庫(kù)間存在延遲，主庫(kù)崩了，尚未到達(dá)從庫(kù)的那些數(shù)據(jù)就會(huì)丟失。

為了解決這種問(wèn)題，半同步復(fù)制，主庫(kù)會(huì)一直等待，直到至少有一個(gè)從庫(kù)接收到寫(xiě)入的數(shù)據(jù)。

1、在主庫(kù)上，安裝 rpl_semi_sync_master 插件

install plugin rpl_semi_sync_master SONAME 'semisync_master.so';

windows下請(qǐng)使用如下：

install plugin rpl_semi_sync_master SONAME 'semisync_master.dll';

2、確認(rèn)插件已激活

select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%semi%';

3、開(kāi)啟半同步復(fù)制并調(diào)整超時(shí)時(shí)間

set @@global.rpl_semi_sync_master_enabled=1;
set @@global.rpl_semi_sync_master_timeout=100;

4、在從庫(kù)上，安裝 rpl_semi_sync_slave 插件

install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';

windows下請(qǐng)使用如下：

install plugin rpl_semi_sync_slave SONAME 'semisync_slave.dll';

5、確認(rèn)插件已激活

select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%semi%';

6、在從庫(kù)上，啟用半同步復(fù)制，并重新啟動(dòng)從庫(kù)IO線(xiàn)程

set global rpl_semi_sync_slave_enabld = 1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

7、通過(guò)如下方式查看半同步狀態(tài)

show status like 'rpl_semi_sync_master_clients';

查看以半同步連接到主庫(kù)的客戶(hù)端數(shù)量

show status like 'rpl_semi_sync_master_status';

主庫(kù)在異步和半同步復(fù)制之間切換，on表示半同步，off表示異步。

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專(zhuān)題：《MySQL查詢(xún)技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。