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解析的问题