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)
显示三台主机在线,配置成功。三个数据库插入的内容,都可以同步。