一、实验环境
主机名 | IP地址 |
---|---|
serverA | 192.168.200.111 |
serverB | 192.168.200.112 |
serverC | 192.168.200.113 |
二、前期准备
- 所有主机配置主机名与IP地址对应关系的hosts文件
[root@serverA ~]# vim /etc/hosts
192.168.200.111 serverA
192.168.200.112 serverB
192.168.200.113 serverC
#将hosts文件传到其它主机
scp /etc/hosts serverB:/etc/
scp /etc/hosts serverC:/etc/
- 所有主机必须打通ssh通道(这步必须做)
#serverA
ssh-keygen
ssh-copy-id serverB
ssh-copy-id serverC
#serverB
ssh-keygen
ssh-copy-id serverA
ssh-copy-id serverC
#serverC
ssh-keygen
ssh-copy-id serverA
ssh-copy-id serverC
三、修改mysql配置文件
- serverA
vim /etc/my.cnf
#在[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="4e5fdc9e-fd46-424b-9114-1a5a7e180e6f"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.200.111:24901"
loose-group_replication_group_seeds="192.168.200.111:24901,192.168.200.112:24901,192.168.200.113:24901"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everwhere_checks=on
loose_group_replication_ip_whitelist="192.168.200.0/24,127.0.0.1/8"
- serverB
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="4e5fdc9e-fd46-424b-9114-1a5a7e180e6f"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.200.112:24901"
loose-group_replication_group_seeds="192.168.200.111:24901,192.168.200.112:24901,192.168.200.113:24901"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everwhere_checks=on
loose_group_replication_ip_whitelist="192.168.200.0/24,127.0.0.1/8"
- serverC
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="4e5fdc9e-fd46-424b-9114-1a5a7e180e6f"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.200.113:24901"
loose-group_replication_group_seeds="192.168.200.111:24901,192.168.200.112:24901,192.168.200.113:24901"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=off
loose-group_replication_enforce_update_everwhere_checks=on
loose_group_replication_ip_whitelist="192.168.200.0/24,127.0.0.1/8"
- 所有主机重启mysql服务
systemctl restart mysqld
四、mysql数据库内设置
- serverA
#进入数据库
mysql -uroot -p123
#开始配置
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'Mysql+007'; ##加用户权限
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES; #刷新
Query OK, 0 rows affected (0.00 sec)
mysql> reset master; ##删除日志
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1; ##打开日志不再记录
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Mysql+007'FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; ##加载模块
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW PLUGINS; ##查看模块加载情况
| group_replication | ACTIVE | GROUP REPLICATION | group_replication.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.00 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON; ##打开模块
Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION; ##打开组
Query OK, 0 rows affected (2.05 sec)
-------------------------------------------------------------------------------------------------------------
#如果打开组报错
##报错,是因为没有白名单,加入白名单就可以了
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.
#关闭模块
STOP GROUP_REPLICATION;
SET GLOBAL group_replication_ip_whitelist="192.168.200.0/24,127.0.0.1/8"; ##加入白名单
#打开模块
SET GLOBAL group_replication_bootstrap_group=ON;
-----------------------------------------------------------------------------------------------------------------------
select * from performance_schema.replication_group_members; #查看状态
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 53256d5b-f1be-11e9-bf23-000c29ed63fb | serverA | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
- serverB
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'Mysql+007';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Mysql+007'FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (5.70 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 53256d5b-f1be-11e9-bf23-000c29ed63fb | serverA | 3306 | ONLINE |
| group_replication_applier | 958a789b-f34c-11e9-9749-000c2972dc77 | serverB | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.01 sec)
- serverC
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'Mysql+007';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Mysql+007'FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.03 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.08 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 53256d5b-f1be-11e9-bf23-000c29ed63fb | serverA | 3306 | ONLINE |
| group_replication_applier | 6b7d8829-f183-11e9-b548-000c29178b2a | serverC | 3306 | RECOVERING |
| group_replication_applier | 958a789b-f34c-11e9-9749-000c2972dc77 | serverB | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.02 sec)
mysql> STOP GROUP_REPLICATION;
Query OK, 0 rows affected (9.18 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (2.80 sec)
mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 53256d5b-f1be-11e9-bf23-000c29ed63fb | serverA | 3306 | ONLINE |
| group_replication_applier | 6b7d8829-f183-11e9-b548-000c29178b2a | serverC | 3306 | ONLINE |
| group_replication_applier | 958a789b-f34c-11e9-9749-000c2972dc77 | serverB | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
五、测试
- serverA
mysql> create database ceshiA;
Query OK, 1 row affected (0.00 sec)
#serverC创建完后查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ceshiA |
| ceshiB |
| ceshiC |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.09 sec)
- serverB
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ceshiA |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.09 sec)
mysql> create database ceshiB;
Query OK, 1 row affected (0.00 sec)
- serverC
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| ceshiA |
| ceshiB |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
mysql> create database ceshiC;
Query OK, 1 row affected (0.00 sec)