溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本篇內(nèi)容主要講解“MySQL5.7的多源復(fù)制方法”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學(xué)習(xí)“MySQL5.7的多源復(fù)制方法”吧!
本實驗假設(shè)已經(jīng)完成操作系統(tǒng)和MySQL安裝部署。
MySQL5.7的多源復(fù)制技術(shù)搭建部署，然后簡單測試。
操作系統(tǒng) | MySQL版本 | 服務(wù)器地址 | 服務(wù)器角色 |
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 |
服務(wù)器地址 | 服務(wù)器角色 | 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中創(chuàng)建同步賬戶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'@'%'; |
## 恢復(fù)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 | +---------------+------------------------------------------+
## 恢復(fù)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; ##設(shè)置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主機，多源復(fù)制需要標(biāo)注
FOR CHANNEL ‘CHANNEL_NAME’區(qū)分
##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狀態(tài)
檢查所有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 最后發(fā)現(xiàn)是開啟一個源開啟了半同步復(fù)制，一個源沒有開題半同步復(fù)制。 2、 清除slave信息 reset slave ## 創(chuàng)建多源復(fù)制過程中，發(fā)現(xiàn)有一個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的多源復(fù)制方法”有了更深的了解，不妨來實際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
