mysql_group_replication

• 环境
  三台rhel6.5虚拟机，全部安装5.7.17数据库

mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm

• /etc/my.cnf

[root@server4 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


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="b364e20a-059f-11e8-af16-525400f2a3c0"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "172.25.200.4:24901"  #三个机器不一样
loose-group_replication_group_seeds= "172.25.200.4:24901,172.25.200.1:24902,172.25.200.2:24903"
loose-group_replication_bootstrap_group=off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks=FALSE
loose-group_replication_ip_whitelist='172.25.200.0/24'

• uuid获取

mysql> select uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 8ed13d97-0632-11e8-8f86-5254004c0507 |
+--------------------------------------+
1 row in set (0.00 sec)

数据库操作

mysql> SET SQL_LOG_BIN=0;  #先停止二进制记录
Query OK, 0 rows affected (0,00 sec)
mysql> CREATE USER rpl_user@'%';
Query OK, 0 rows affected (0,00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'rpl_pass';
Query OK, 0 rows affected, 1 warning (0,00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0,00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0,00 sec)
CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_pass' \\
FOR CHANNEL 'group_replication_recovery';
INSTALL PLUGIN group_replication SONAME 'group_replication.so';#安装组复制软件
SHOW PLUGINS;
SET GLOBAL group_replication_bootstrap_group=ON; #第一个操作，其他两个不操作，后面的节点默认为off，不用管
START GROUP_REPLICATION;

报错解决

1:不能启动服务

mysql> START GROUP_REPLICATION;
ERROR 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
Query OK, 0 rows affected (0.00 sec)

2：节点状态不对

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 397a2af3-062c-11e8-863a-5254004c0507 | server2     |        3306 | RECOVERING   |
| group_replication_applier | d00a92cc-059f-11e8-8958-5254002799c4 | server4     |        3306 | ONLINE       |
| group_replication_applier | d389cdfa-0629-11e8-b0b4-525400f2a3c0 | server1     |        3306 | RECOVERING   |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
节点操作
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from plugin;
+-------------------+----------------------+
| name              | dl                   |
+-------------------+----------------------+
| group_replication | group_replication.so |
| validate_password | validate_password.so |
+-------------------+----------------------+
2 rows in set (0.00 sec)
mysql> delete from plugin where name='validate_password';
Query OK, 1 row affected (0.36 sec)

• 效果

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 397a2af3-062c-11e8-863a-5254004c0507 | server2     |        3306 | ONLINE       |
| group_replication_applier | d00a92cc-059f-11e8-8958-5254002799c4 | server4     |        3306 | ONLINE       |
| group_replication_applier | d389cdfa-0629-11e8-b0b4-525400f2a3c0 | server1     |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)