MySQL5.7的多源复制方法
本篇内容主要讲解“MySQL5.7的多源复制方法”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“MySQL5.7的多源复制方法”吧!
成都创新互联服务项目包括宝坻网站建设、宝坻网站制作、宝坻网页制作以及宝坻网络营销策划等。多年来，我们专注于互联网行业，利用自身积累的技术优势、行业经验、深度合作伙伴关系等，向广大中小型企业、政府机构等提供互联网行业的解决方案，宝坻网站推广取得了明显的社会效益与经济效益。目前，我们服务的客户以成都为中心已经辐射到宝坻省份的部分城市，未来相信会继续扩大服务区域并继续获得客户的支持与信任！
5.7多源复制
1.1 实验概要
1.1.1 实验假设
本实验假设已经完成操作系统和MySQL安装部署。
1.1.2 实验目的
MySQL5.7的多源复制技术搭建部署，然后简单测试。
1.1.3 环境信息
操作系统 | 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 |
1.1.4 实验规划
服务器地址 | 服务器角色 | 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
1.2 实验操作
1.2.1 源端备份数据库
分别从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/ |
1.2.2 创建同步账户
分别在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'@'%'; |
1.2.3 目标端恢复数据库
## 恢复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)]> |
1.2.4 修改MySQL存储方式
修改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 |
1.2.5 同步操作
? 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) |
1.2.7 监控
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.2.9 遇见错误
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的多源复制方法”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
当前文章：MySQL5.7的多源复制方法
网页链接：http://pwwzsj.com/article/pogjps.html
