溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容主要講解“MySQL5.7的多源復制方法”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MySQL5.7的多源復制方法”吧!
本實驗假設已經完成操作系統和MySQL安裝部署。
MySQL5.7的多源復制技術搭建部署，然后簡單測試。
操作系統 | MySQL版本 | 服務器地址 | 服務器角色 |
Centos7 | 5.7.18 | 192.168.102.23 | source 1 |
Centos7 | 5.7.18 | 192.168.102.24 | source 2 |
Centos7 | 5.7.18 | 192.168.102.25 | target |
服務器地址 | 服務器角色 | MySQL庫 | 賬戶 |
192.168.102.23 | source 1 | emily | repl23 |
192.168.102.24 | source 2 | evelyn | repl24 |
192.168.102.25 | target |
|
|
source 1.emily -->target
source 2.evelyn -->target
username:
source 1:repl23
source 2:repl24
分別從source1和source2分別備份出emily和evelyn庫，然后分別copy到target中。其中操作步驟如下：
source 1：
##備份 [root@dsm-db-102023 11:26:50 /root] #mysqldump -uroot –pmysql --single-transaction --master-data=2 --databases emily > /root/dump/emily.sql ##傳輸 [root@dsm-db-102023 11:34:38 /root/dump] #scp /root/dump/emily.sql root@192.168.102.25:/root/dump/ |
source 2：
##備份 [root@test-mysql-10224 11:19:47 /root] #mysqldump -uroot -pmysql --single-transaction --master-data=2 --databases evelyn > /root/dump/evelyn.sql ##傳輸 [root@dsm-db-102023 11:34:38 /root/dump] #scp /root/dump/emily.sql root@192.168.102.25:/root/dump/ |
分別在source1和source2中創建同步賬戶repl23、repl24
source1
root@192.168.102.23:3306 [emily]>create user repl23 identified by "repl"; root@192.168.102.23:3306 [(none)]>grant replication slave on *.* to 'repl23'@'%'; |
source2
root@192.168.102.24:3306 [evelyn]>create user repl24 identified by "repl"; root@192.168.102.24:3306 [evelyn]>grant replication slave on *.* to 'repl24'@'%'; |
## 恢復evelyn庫 root@192.168.102.25:3306 [(none)]>reset master [root@dsm-db-102025 14:01:24 /root/dump] #mysql -uroot -pmysql < evelyn.sql ## 查看gtid_purged root@192.168.102.25:3306 [(none)]>show global variables like '%gtid_purged%'; +---------------+------------------------------------------+ | Variable_name | Value | +---------------+------------------------------------------+ | gtid_purged | 921a9068-24d2-11e7-99b5-005056b59593:1-287, bd783f44-258f-11e7-914b-005056b5d312:1-28071 | +---------------+------------------------------------------+
## 恢復emily庫 root@192.168.102.25:3306 [(none)]>reset master [root@dsm-db-102025 14:21:22 /root/dump] #mysql -uroot -pmysql < /root/dump/emily.sql; ##設置source1和source2的gtid_purged root@192.168.102.25:3306 [(none)]>show variables like '%gtid_purged%'; +---------------+------------------------------------------+ | Variable_name | Value | +---------------+------------------------------------------+ | gtid_purged | 7937ac78-3c39-11e7-b59e-005056b5d25f:1-4 | +---------------+------------------------------------------+ ## set global gtid_purged='921a9068-24d2-11e7-99b5-005056b59593:1-287,bd783f44-258f-11e7-914b-005056b5d312:1-28071,7937ac78-3c39-11e7-b59e-005056b5d25f:1-4';
root@192.168.102.25:3306 [(none)]>reset master; Query OK, 0 rows affected (0.01 sec)
root@192.168.102.25:3306 [(none)]>set global gtid_purged='921a9068-24d2-11e7-99b5-005056b59593:1-287,bd783f44-258f-11e7-914b-005056b5d312:1-28071,7937ac78-3c39-11e7-b59e-005056b5d25f:1-4'; Query OK, 0 rows affected (0.00 sec)
root@192.168.102.25:3306 [(none)]>show variables like '%gtid_purged%'; +---------------+------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+------------------------------------------------------------------------------------------------------------------------------------+ | gtid_purged | 7937ac78-3c39-11e7-b59e-005056b5d25f:1-4, 921a9068-24d2-11e7-99b5-005056b59593:1-287, bd783f44-258f-11e7-914b-005056b5d312:1-28071 | +---------------+------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
root@192.168.102.25:3306 [(none)]> |
修改MySQL存儲master-info和relay-info的方式，即從文件存儲改為表存儲
## 在線修改 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 |
? change master
登錄slave進行同步操作，分別change master到兩臺master主機，多源復制需要標注
FOR CHANNEL ‘CHANNEL_NAME’區分
##source 1 root@192.168.102.25:3306 [(none)]>CHANGE MASTER TO MASTER_HOST='192.168.102.23',MASTER_USER='repl23', MASTER_PASSWORD='repl',master_auto_position=1 FOR CHANNEL 'repl23'; ##source 2 root@192.168.102.25:3306 [(none)]>CHANGE MASTER TO MASTER_HOST='192.168.102.24',MASTER_USER='repl24', MASTER_PASSWORD='repl',master_auto_position=1 FOR CHANNEL 'repl24'; |
? 啟動slave
啟動所有同步: start slave;
啟動單個同步: start slave for channel ‘channel_name’;
##啟動source 1 root@192.168.102.25:3306 [(none)]>start slave for channel 'repl23'; ##啟動source 2 root@192.168.102.25:3306 [(none)]>start slave for channel 'repl24'; |
? 檢查slave狀態
檢查所有slave: show slave status\G;
檢查單個slave: show slave status for chennel ‘channel_name’\G;
## source 1
##source 1 root@192.168.102.23:3306 [emily]>insert into emily(id,name)values(2,'evelyn'); Query OK, 1 row affected (0.00 sec)
root@192.168.102.23:3306 [emily]>select * from emily; +------+--------+ | id | name | +------+--------+ | 1 | emily | | 2 | evelyn | +------+--------+ 2 rows in set (0.00 sec)
##target驗證
root@192.168.102.25:3306 [emily]>select * from emily; +------+--------+ | id | name | +------+--------+ | 1 | emily | | 2 | evelyn | +------+--------+ 2 rows in set (0.00 sec)
|
## source 2 root@192.168.102.24:3306 [evelyn]>insert into evelyn(id,name)values(2,'emily'); Query OK, 1 row affected (1.00 sec)
root@192.168.102.24:3306 [evelyn]>select * from evelyn; +------+--------+ | id | name | +------+--------+ | 1 | evelyn | | 2 | emily | +------+--------+ 2 rows in set (0.00 sec) ## target端 root@192.168.102.25:3306 [evelyn]>select * from evelyn; +------+--------+ | id | name | +------+--------+ | 1 | evelyn | | 2 | emily | +------+--------+ 2 rows in set (0.00 sec) |
select * from performance_schema.replication_connection_status\G;
##GTID STOP SLAVE FOR CHANNEL ‘CHANNEL_NAME’; SET SESSION GTID_NEXT=’’; BEGIN;COMMIT; SET SESSION GTID_NEXT=’AUTOMATIC’; START SLAVE FOR CHANNEL ‘CHANNEL_NAME’; ## binlog+position stop slave sql_thread FOR CHANNEL ‘CHANNEL_NAME’;; set global sql_slave_skip_counter=1; start slave sql_thread FOR CHANNEL ‘CHANNEL_NAME’;;
## root@192.168.102.25:3306 [(none)]>set session gtid_next='bd783f44-258f-11e7-914b-005056b5d312:28083'; Query OK, 0 rows affected (0.00 sec)
root@192.168.102.25:3306 [(none)]>begin;commit; Query OK, 0 rows affected (0.00 sec)
root@192.168.102.25:3306 [(none)]>set session gtid_next=automatic; Query OK, 0 rows affected (0.00 sec)
root@192.168.102.25:3306 [(none)]>start slave for channel 'repl24'; Query OK, 0 rows affected (0.00 sec) |
1、 Last_IO_Error: Relay log write failure: could not queue event from master ##repl24 Last_IO_Error: Fatal error: Failed to run 'after_read_event' hook ##repl23 stop slave start slave 最后發現是開啟一個源開啟了半同步復制，一個源沒有開題半同步復制。 2、 清除slave信息 reset slave ## 創建多源復制過程中，發現有一個slave沒有channel_name，使用如下語句清除slave信息 reset slave all for channel ''; 3、 root@192.168.102.24:3306 [evelyn]>uninstall plugin rpl_semi_sync_master; Query OK, 0 rows affected (0.01 sec) root@192.168.102.24:3306 [evelyn]>uninstall plugin rpl_semi_sync_slave; Query OK, 0 rows affected (0.00 sec) |
到此，相信大家對“MySQL5.7的多源復制方法”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
