mysql 復(fù)制原理與實踐應(yīng)用詳解

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

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

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

這個過程由一個稱為 IO線程 的線程負(fù)責(zé)，還有一個稱為 SQL線程 的則按順序執(zhí)行中繼日志中的語句。

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

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

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

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

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

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

1、在主庫上啟用二進制日志記錄。

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

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

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

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

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

7、開始復(fù)制。

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

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

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

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

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

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

#設(shè)置server_id
server_id = 10

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

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

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

mysql -u root -p -f  主庫備份文件.sql

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

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

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

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

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

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

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

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

set @@global.read_only = on;

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

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

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

show master status;

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

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

5、在master1上開啟 slave 模式

start slave;

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

set @@global.read_only = off;

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

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

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

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

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

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

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

6、在 server3 上，將復(fù)制存儲庫從 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主機IP',
MASTER_USER='server1復(fù)制用戶',
MASTER_PASSWORD='密碼',
MASTER_LOG_FILE='server1二進制日志名稱',
MASTER_LOG_POS=server1二進制日志位置 FOR CHANNEL 'server1';

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

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

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

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

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

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

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

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

CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);

2、復(fù)制指定表

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

3、如果想使用通配符來選擇表

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

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

CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (db1, db2);

5、忽略指定表

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

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

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

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

stop slave;
show slave status\G;

記錄下Relay_Master_Log_File和Exec_Master_Log_Pos的值

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

stop slave;
show slave status\G;

記錄下Relay_Master_Log_File和Exec_Master_Log_Pos的值

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

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

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

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

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

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

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

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

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

start slave;
show slave status\G;

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

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

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

stop slave;
show master status\G;

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

一旦延遲被追上，就停止從庫的運行。

stop slave;

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

show slave status\G;
start slave;

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

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

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

start slave;
show slave status\G;

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

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

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

1、停止從庫運行

stop slave;

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

CHANGE MASTER TO MASTER_DELAY = 3600;
start slave;

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

show slave status\G;

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

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

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

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

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

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

為了避免，可以使用基于 GTID 的復(fù)制，mysql 使用 GTID 自動檢測二進制日志的位置。

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

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

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

set @@global.read_only = on;

3、重新啟動所有從庫，使 GTID 生效。

4、重新啟動主庫。

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

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

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

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

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

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

1、在主庫上，安裝 rpl_semi_sync_master 插件

install plugin rpl_semi_sync_master SONAME 'semisync_master.so';

windows下請使用如下：

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、開啟半同步復(fù)制并調(diào)整超時時間

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

4、在從庫上，安裝 rpl_semi_sync_slave 插件

install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';

windows下請使用如下：

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、在從庫上，啟用半同步復(fù)制，并重新啟動從庫IO線程

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

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

show status like 'rpl_semi_sync_master_clients';

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

show status like 'rpl_semi_sync_master_status';

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

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

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