本篇内容主要讲解“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的多源复制方法”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！