mysql组复制

1.准备三台虚拟机

server1-3：

安装mysql

保证mysql为初始化

 

2.修改配置文件

server1:

vim /etc/my.cnf

systemctl start mysqld

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="4c73ddd1-0094-40bd-9372-2b89c0e11534"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.70.1:24901"
loose-group_replication_group_seeds="172.25.70.1:24901,172.25.70.2:24901,172.25.70.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.70.0/24";
 

2.数据库配置

#过滤到初始化mysql密码，并登陆

grep password /var/log/mysql.log

mysql -p

#进行以下操作

mysql>SET SQL_LOG_BIN=0; ##不记录二进制日志
mysql>ALTER USER [email protected] identified by 'Redhat_001';

mysql>CREATE USER [email protected]'%' IDENTIFIED BY 'Redhat_001';
mysql>GRANT REPLICATION SLAVE ON *.* TO [email protected]'%';
mysql>FLUSH PRIVILEGES;
mysql>SET SQL_LOG_BIN=1;

mysql>CHANGE MASTER TO MASTER_USER='rpl_user',MASTER_PASSWORD='Redhat_001' FOR CHANNEL 'group_replication_recovery';
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql>show plugins;
mysql>SET GLOBAL group_replication_bootstrap_group=ON;
mysql>start group_replication; #错误提示后，stop group_replication;，然后排错
mysql>set global group_replication_bootstrap_group=off;

 

#当出现以下错误，可以参考一下我的解决方法

 

# 先stop，然后重值master、slave

#然后重新grant以及change master

# 确认模块已经导入后，启动成功

 

 

server2、3:

server_id=2  # server_id=3
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="aaaaaa-aaaa-aaaaaaa-aaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="172.25.70.2:24901"     ## 为serve3的ip
loose-group_replication_group_seeds="172.25.70.1:24901,172.25.70.2:24901,172.25.70.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.70.0/24";

 

mysql配置

mysql>SET SQL_LOG_BIN=0; ##不记录二进制日志
mysql>ALTER USER [email protected] identified by 'Redhat_001';

mysql>CREATE USER [email protected]'%' IDENTIFIED BY 'Redhat_001';
mysql>GRANT REPLICATION SLAVE ON *.* TO [email protected]'%';
mysql>FLUSH PRIVILEGES;
mysql>SET SQL_LOG_BIN=1;

mysql>CHANGE MASTER TO MASTER_USER='rpl_user',MASTER_PASSWORD='Redhat_001' FOR CHANNEL 'group_replication_recovery';
mysql>INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql>show plugins;
mysql>start group_replication; #错误提示后，stop group_replication;，然后排错