大橙子网站建设,新征程启航

为企业提供网站建设、域名注册、服务器等服务

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://dzwzjz.com/article/pogjps.html
在线咨询
服务热线
服务热线:028-86922220
TOP