本文實例講述了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上的二進制日志的坐標
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 模式
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上主庫狀態。
一旦延遲被追上，就停止從庫的運行。
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、停止從庫運行
2、設置延遲時間，以秒為單位
CHANGE MASTER TO MASTER_DELAY = 3600;
start slave;
3、檢查從庫狀態
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相關內容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL常用函數大匯總》、《MySQL日志操作技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》及《MySQL數據庫鎖相關技巧匯總》
希望本文所述對大家MySQL數據庫計有所幫助。
您可能感興趣的文章:- MySQL主從復制的原理及配置方法（比較詳細）
- mysql中復制表結構的方法小結
- MySQL復制表結構和內容到另一張表中的SQL語句
- MySQL快速復制數據庫數據表的方法
- Mysql主從復制(master-slave)實際操作案例
- mysql主從同步復制錯誤解決一例
- mysql跨數據庫復制表(在同一IP地址中)示例
- mysql同步復制搭建方法指南詳細步驟
- 簡單講解MySQL的數據庫復制方法
- 詳解MySQL雙活同步復制四種解決方案
- MySQL 數據庫雙向鏡像、循環鏡像(復制)
