mysql组复制

组复制

一.环境

1.准备三台干净的mysql环境

Host1

/etc/init.d/mysqld stop
rm -fr  /var/lib/mysql/*
/etc/init.d/mysqld start

这里写图片描述

 grep "temporary password" /var/log/mysqld.log   过滤密码
 mysql -p
 alter user root@localhost identified by 'Westos+007';  修改密码
 flush privileges;   ##刷新
2.配置文件
Vim /etc/my.cnf
server-id=1
log-bin=mysql-bin

gtid_mode=ON
enforce-gtid-consistency=true
server-id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="60904750-447a-11e8-893d-a088b44bd080"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.254.1:24901"
loose-group_replication_group_seeds= "172.25.254.1:24901,172.25.254.2:24901,172.25.254.4:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks=TRUE
loose-group_replication_ip_whitelist="172.25.254.0/24"

这里写图片描述

/etc/init.d/mysqld restart  重启

这里写图片描述

host2

Host2的文件配置server-id不能和host1相同,42行要写自己的ip,此外和host1配置文件相同
这里写图片描述
启动修改密码,完成后

二.数据库操作

Host1

[root@host1 ~]# mysql -p初始化的密码
mysql> SET SQL_LOG_BIN=0;   ##不记录二进制日志
mysql> alter user root@localhost identified by 'Westos+007';
mysql> grant replication slave on *.* to repl@'%' identified by 'Westos+007';
mysql> flush privileges;
mysql> set sql_log_bin=1;   ##记录二进制文件
mysql> change master to master_user='repl',master_password='Westos+007' for channel 'group_replication_recovery';
mysql> install plugin group_replication soname 'group_replication.so';
mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;
mysql> select * from performance_schema.replication_group_members;

这里写图片描述

Host2数据库操作
[root@host2 ~]# mysql -p
mysql> SET SQL_LOG_BIN=0;   ##不记录二进制日志
mysql> alter user root@localhost identified by 'Westos+007';
mysql> grant replication slave on *.* to repl@'%' identified by 'Westos+007';
mysql> flush privileges;
mysql> set sql_log_bin=1;   ##记录二进制文件
mysql> change master to master_user='repl',master_password='Westos+007' for channel 'group_replication_recovery';
mysql> install plugin group_replication soname 'group_replication.so';
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> start group_replication;
mysql> select * from performance_schema.replication_group_members;

这里写图片描述

解决方法:因为之前做的主建冲突(数据名)所以host1要先清掉master的密码数据,再重新来一次

host1
mysql> stop group_replication;
mysql> reset master;
mysql> reset slave;
mysql> grant replication slave on *.* to repl@'%' identified by 'Westos+007';

mysql> change master to master_user='repl',master_password='Westos+007' for channel 'group_replication_recovery';
mysql> install plugin group_replication soname 'group_replication.so';
mysql> set global group_replication_bootstrap_group=ON;
mysql> start group_replication;
mysql> set global group_replication_bootstrap_group=OFF;
mysql> select * from performance_schema.replication_group_members;

这里写图片描述

Host2清除环境重做:
 mysql -p
mysql> SET SQL_LOG_BIN=0;   ##不记录二进制日志
mysql> alter user root@localhost identified by 'Westos+007';
mysql> grant replication slave on *.* to repl@'%' identified by 'Westos+007';
mysql> flush privileges;
mysql> set sql_log_bin=1;   ##记录二进制文件
mysql> change master to master_user='repl',master_password='Westos+007' for channel 'group_replication_recovery';
mysql> install plugin group_replication soname 'group_replication.so';
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> start group_replication;
mysql> select * from performance_schema.replication_group_members;

host2
这里写图片描述

host1
这里写图片描述
再加个host4

mysql -p
mysql> SET SQL_LOG_BIN=0;   ##不记录二进制日志
mysql> alter user root@localhost identified by 'Westos+007';
mysql> grant replication slave on *.* to repl@'%' identified by 'Westos+007';
mysql> flush privileges;
mysql> set sql_log_bin=1;   ##记录二进制文件
mysql> change master to master_user='repl',master_password='Westos+007' for channel 'group_replication_recovery';
mysql> install plugin group_replication soname 'group_replication.so';
mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
mysql> start group_replication;
mysql> select * from performance_schema.replication_group_members;

这里写图片描述
Host2
这里写图片描述
Host1
这里写图片描述

三台mysql服务器都没有westos数据库再host2上新建一个westos数据库

这里写图片描述

同步了
这里写图片描述

注意彼此dns解析的问题
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值