mysql中復制原理與實踐應用的示例分析

這篇文章主要介紹了mysql中復制原理與實踐應用的示例分析，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

復制功能是將一個mysql數據庫上的數據復到一個或多個mysql從數據庫上。

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

這個過程由一個稱為 IO線程 的線程負責，還有一個稱為 SQL線程 的則按順序執行中繼日志中的語句。

復制有多種拓撲形式：

1、傳統復制，一主多從，一個主服務器多個從服務器。

2、鏈式復制，一臺服務器從主庫復制，而另一臺服務器又從這臺復制，中間服務器又叫中繼主庫。

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

4、多源復制，一個從庫，從多個主庫復制。

一、復制如何操作

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

2、在主庫上創建一個復制用戶。

3、在從訓上設置唯一的 server_id。

4、從主庫中備份數據。

5、在從庫上恢復主庫備份的數據。

6、執行CHANGE MASTER TO命令。

7、開始復制。

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

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

#設置server_id
server_id = 1
#開啟binlog日志
log-bin = mysql-bin

2、在主庫上創建復制用戶

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

3、在從庫上設置server_id

#設置server_id
server_id = 10

4、備份主庫上的數據

mysqldump -u root -p --all-databases --routines --events --triggers --single-transaction --master-data > 導出路徑

5、在從庫上恢復主庫導出的數據

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

6、在從庫上執行 CHANGE MASTER TO 命令

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

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

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

7、從庫上運行 start slave，然后 show slave status\G; 查看復制狀態;

三、設置主主復制

假設主庫分別是master1和master2。

1、設置master2為只讀

set @@global.read_only = on;

2、在master2上創建復制用戶，如果存在，則不用創建了

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

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

show master status;

4、根據第2步的信息，在master1上執行 CHANGE MASTER TO 命令

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

5、在master1上開啟 slave 模式

start slave;

6、設置master2為可讀寫

set @@global.read_only = off;

四、設置多源復制

設置 server3 為 server1 和 server2 的從庫。

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

2、在 server1 和 server2 上創建復制用戶，具體操作可參考上面。

3、在 server3 上設置 server_id。

4、備份 server1 和 server2 的數據。

5、在 server3 上恢復 server1 和 server2 上備份的數據。

6、在 server3 上，將復制存儲庫從 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 上，執行 CHANGE MASTER TO 命令，并命名通道名

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

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

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

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

9、查看同步狀態，show slave status\G;

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

五、設置復制篩選器

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

1、復制指定數據庫

CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);

2、復制指定表

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

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

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

4、忽略數據庫

CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (db1, db2);

5、忽略指定表

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

六、將從庫由主從復制切換到鏈式復制

比如現在服務器A為主庫，服務器B和服務器C為從庫，復制于服務器A。現在想把服務器C作為服務器B的從庫。

1、在服務器C上停止從庫運行

stop slave;
show slave status\G;

記錄下Relay_Master_Log_File和Exec_Master_Log_Pos的值

2、在服務器B上停止從庫運行

stop slave;
show slave status\G;

記錄下Relay_Master_Log_File和Exec_Master_Log_Pos的值

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

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

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

5、在服務器C上，檢查 show slave status 中 Exec_Master_Log_Pos 和 Until_Log_Pos 兩者應該相同。

6、在服務器B上，查看主庫狀態，啟動從庫。

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

7、在服務器C上，停止從庫運行，執行 CHANGE MASTER TO 命令。

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

8、在服務器C上，啟動復制并查看狀態

start slave;
show slave status\G;

七、將鏈式復制切換到主從復制

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

1、在服務器B上，停止從庫運行，并記錄主庫狀態

stop slave;
show master status\G;

2、服務器C上，確保從庫的延遲已被追上，Relay_Master_Log_File和Exec_Master_Log_Pos應該等于服務器B上主庫狀態。

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

stop slave;

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

show slave status\G;
start slave;

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

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

5、在服務器C上，開啟從庫，并查看狀態。

start slave;
show slave status\G;

八、設置延遲復制

為什么需要延遲復制，有可能主庫上執行了一條災難性語句，你必須通過備份中的時間點恢復，如果數據庫大小過大，這將導致長時間停機。

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

1、停止從庫運行

stop slave;

2、設置延遲時間，以秒為單位

CHANGE MASTER TO MASTER_DELAY = 3600;
start slave;

3、檢查從庫狀態

show slave status\G;

SQL_Delay: 從庫延遲于主庫的秒數。

SQL_Remaining_Delay：延遲還剩余的秒數，當保持延遲時，這個值是NULL。

Slave_SQL_Running_State：SQL線程的狀態

九、設置 GTID 復制

全局事務標識符 GTID 是在程序中創建的唯一標識符，并與主庫上提交的每個事務相關聯。該標識符是唯一的，不僅在主庫上，在其他從庫上，它都唯一。

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

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

1、在所有數據庫中 my.cnf 中啟動 GTID

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

2、將主庫設置為只讀，確保主庫與從庫數據一致。

set @@global.read_only = on;

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

4、重新啟動主庫。

5、在從庫上執行 CHANGE MASTER TO 命令來設置 GTID 復制

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

6、在所有從庫上執行 start slave; 并查看狀態。

十、設置半同步復制

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

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

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、確認插件已激活

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

3、開啟半同步復制并調整超時時間

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、確認插件已激活

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

6、在從庫上，啟用半同步復制，并重新啟動從庫IO線程

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

7、通過如下方式查看半同步狀態

show status like 'rpl_semi_sync_master_clients';

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

show status like 'rpl_semi_sync_master_status';

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

感謝你能夠認真閱讀完這篇文章，希望小編分享的“mysql中復制原理與實踐應用的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!