Mysql全同步复制

MySQL数据库的全同步复制的部署

1.配置server1：（因为server1之前做过主从复制，所以要清空环境）

<1>停掉之前的mysql，清空/var/lib/mysql目录下所有的mysql缓存记录
注意:必须先停掉mysqld服务，然后再删除数据（这是因为关闭mysqld服务时，会自动保存数据）


<2>获取uuid

<3>编辑配置文件

[root@server1 mysql]# vim /etc/my.cnf
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="4dae3288-5270-432c-b780-d531a87ada48"   #唯一标识一个组(三个节点保持一致,使用上一步生成的uuid)
loose-group_replication_start_on_boot=off     #避免重启自动组复制
loose-group_replication_local_address= "172.25.83.1:24901"   #用于组间通信的地址
loose-group_replication_group_seeds= "172.25.83.1:24901,172.25.83.2:24901,172.25.83.3:24901"    #donor地址
loose-group_replication_bootstrap_group=off     #引导节点设置
loose-group_replication_ip_whitelist="127.0.0.1,172.25.83.0/24"    #允许接收组的ip来源
loose-group_replication_enforce_update_everywhere_checks=ON    #在多主模式下为多主自动更新启用或禁用严格一致性检查   
loose-group_replication_single_primary_mode=OFF          #关闭单主模式

<4>开启mysql，获取初始密码，登陆数据库修改初始密码，安装全同步插件及配置

[root@server1 mysql]# systemctl start mysqld
[root@server1 mysql]# cat /var/log/mysqld.log | grep password    #获取初始密码
[root@server1 mysql]# mysql -p
mysql> alter user root@localhost identified by 'Xinjiaojiao+523';
mysql> SET SQL_LOG_BIN=0;    #关闭日志同步
mysql> CREATE USER 'xin'@'%' IDENTIFIED BY 'Xinjiaojiao+523';   #创建用户xin,密码为Xinjiaojiao+523
mysql> GRANT REPLICATION SLAVE ON *.* TO 'xin'@'%';     #给用户xin授予复制的权限
mysql> FLUSH PRIVILEGES;         #刷新授权表
mysql> SET SQL_LOG_BIN=1;        #开启日志同步
mysql> CHANGE MASTER TO MASTER_USER='xin', MASTER_PASSWORD='Xinjiaojiao+523' FOR CHANNEL 'group_replication_recovery';      
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; #安装组复制的插件
mysql> SHOW PLUGINS;       #查看插件
 group_replication          | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL  
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;   #查看状态显示为ONLINE表示配置成功

<5>登录数据库，创建数据库，创建表，并往表中插入数据以便后面的测试

[root@server1 mysql]# mysql -uroot -pXinjiaojiao+523
mysql> CREATE DATABASE test;
mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL);
mysql> INSERT INTO t1 VALUES (1, 'Luis');
mysql> SELECT * FROM t1;

2.配置server2：（因为server2之前做过主从复制，所以要清空环境）
<1>停掉mysql，清空/var/lib/mysql目录下所有的mysql缓存记录

<2>编辑配置文件

[root@server2 mysql]# vim /etc/my.cnf
server_id=2
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="4dae3288-5270-432c-b780-d531a87ada48"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.83.2:24901"
loose-group_replication_group_seeds= "172.25.83.1:24901,172.25.83.2:24901,172.25.83.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.83.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF

<3>开启数据库，获取初始密码，登陆数据库，修改密码，安装全同步插件并配置

[root@server2 mysql]# systemctl start mysqld
[root@server2 mysql]# grep password /var/log/mysqld.log 
mysql> alter user root@localhost identified by 'Xinjiaojiao+523';
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER 'xin'@'%' IDENTIFIED BY 'Xinjiaojiao+523';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'xin'@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='xin', MASTER_PASSWORD='Xinjiaojiao+523' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> reset master;
mysql> START GROUP_REPLICATION;

mysql> SELECT * FROM performance_schema.replication_group_members;   #查看状态显示为ONLINE表示配置成功

3.配置server3：
<1>安装数据库

[root@server3 ~]# yum install mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-5.7.24-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm mysql-community-server-5.7.24-1.el7.x86_64.rpm -y

<2>编辑配置文件

[root@server3 ~]# vim /etc/my.cnf
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="4dae3288-5270-432c-b780-d531a87ada48"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.83.3:24901"
loose-group_replication_group_seeds= "172.25.83.1:24901,172.25.83.2:24901,172.25.83.3:24901"
loose-group_replication_bootstrap_group=off
loose-group_replication_ip_whitelist="127.0.0.1,172.25.83.0/24"
loose-group_replication_enforce_update_everywhere_checks=ON
loose-group_replication_single_primary_mode=OFF

<3>开启mysql，获取初始密码，登陆数据库，修改密码，安装组复制插件并配置

[root@server3 ~]# systemctl start mysqld
[root@server3 ~]# grep password /var/log/mysqld.log
[root@server3 ~]# mysql -p
mysql> alter user root@localhost identified by 'Xinjiaojiao+523';
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER 'xin'@'%' IDENTIFIED BY 'Xinjiaojiao+523';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'xin'@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='xin', MASTER_PASSWORD='Xinjiaojiao+523' FOR CHANNEL 'group_replication_recovery';
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
mysql> reset master;
mysql> START GROUP_REPLICATION;

mysql> SELECT * FROM performance_schema.replication_group_members;   #查看状态显示为ONLINE表示配置成功

配置完毕。

4.测试：

1.server1端查看组复制状态（三台状态都是online）

显示三台主机在线，配置成功。三个数据库插入的内容，都可以同步。