MySQL 高可用方案 MGR（Mysql Group Replication）配置及常见问题

1 前提条件

mysql5.7.17以后版本， 建议5.7.20以上。 目前使用的是5.7.29

• 引擎必须为innodb,因为需事务支持在commit时对各节点进行冲突检查 每个表必须有主键，在进行事务冲突检测时需要利用主键值对比 ；
• 大家做数据迁移的时候，确保所有表都有主键，默认的复制表bak，一般是没有主键，请事先drop掉；
• 必须开启binlog且为row格式；
• 必须打开GTID特性，且主从状态信息存于表中；

2 单主方案+多主方案两种，建议单主方案，多主方案有不少问题。

3 服务器配置

参考文章：介绍具体的配置方案

根据以上文章，可能出现的问题，重点说明

（1）服务器主机名务必保持不同。

（2）/etc/hosts文件需要配置 各物理节点到IP地址的映射

（3）授权部分，除了授权IP，还需授权 localhost 和 127.0.0.1 如下:

 grant replication slave on *.* to MGR@'192.168.1.%' identified by 'abc!@#';
 grant replication slave on *.* to MGR@'localhost' identified by 'abc!@#';
 grant replication slave on *.* to MGR@'127.0.0.1' identified by 'abc!@#';

（4）集群正常情况如下
集群搭配完毕之后，在任一节点

select * from performance_schema.replication_group_members;

若任一节点状态为 Recovering状态，则查看问题节点mysql 日志状态，

4 常见问题：

1. 问题1：

[ERROR] Plugin group_replication reported: ‘This member has more
executed transactions than those present in the group. Local
transactions: bb874065-c485-11e8-8b52-000c2934472e:1 > Group
transactions: 3db33b36-0e51-409f-a61d-c99756e90155:1-11’ [ERROR]
Plugin group_replication reported: ‘The member contains transactions
not present in the group. The member will now exit the group.’ [Note]
Plugin group_replication reported: ‘To force this member into the
group you can use the
group_replication_allow_local_disjoint_gtids_join option’
此问题一般出现在非第一个节点，根据提示打开set global
group_replication_allow_local_disjoint_gtids_join=ON;

2. 问题2

[ERROR] Plugin group_replication reported: ‘This member has more
executed transactions than those present in the group. Local
transactions: bb874065-c485-11e8-8b52-000c2934472e:1 > Group
transactions: 3db33b36-0e51-409f-a61d-c99756e90155:1-15’ [Warning]
Plugin group_replication reported: ‘The member contains transactions
not present in the group. It is only allowed to join due to
group_replication_allow_local_disjoint_gtids_join option’ [Note]
Plugin group_replication reported: ‘This server is working as
secondary member with primary member address
localhost.localdomaion:3306.’
该故障和故障1的不同之处在于该问题出现时，参数group_replication_allow_local_disjoint_gtids_join已经设置成为on了。解决该问题的方法是执行reset master就行，然后重新在主节点和从节点开启通道，即

CHANGE MASTER TO MASTER_USER=‘MGR’, MASTER_PASSWORD=‘abcedf!@#’ FOR
CHANNEL ‘group_replication_recovery’;

rest master 主要是为了解决数据不同步问题。reset master
需要首先停应用，保证应用不更新数据，然后让落后于复制集数据的节点根据binlog重新更新数据。使用前一定要明确使用的节点，一般是异常节点和，新加入节点，在线节点谨慎使用。

3. 问题3

[Warning] Storing MySQL user name or password information in the
master info repository is not secure and is therefore not recommended.
Please consider using the USER and PASSWORD connection options for
START SLAVE; see the ‘START SLAVE Syntax’ in the MySQL Manual for more
information. [ERROR] Slave I/O for channel
‘group_replication_recovery’: Master command COM_REGISTER_SLAVE
failed: Access denied for user ‘MGR’@‘localhost’ (using password: YES)
(Errno: 1045), Error_code: 1597 [ERROR] Slave I/O thread couldn’t
register on master [Note] Slave I/O thread exiting for channel
‘group_replication_recovery’, read up to log ‘FIRST’, position
授权问题，参考 重点说明（3）解决

grant replication slave on . to MGR@‘192.168.1.%’ identified by
‘abc!@#’; grant replication slave on . to MGR@‘localhost’
identified by ‘abc!@#’; grant replication slave on . to
MGR@‘127.0.0.1’ identified by ‘abc!@#’;

4. 问题4

ERROR 1872 (HY000): Slave failed to initialize relay log info
structure from the repository 解决防范，执行reset slave

reset slave干的那些事：

删除slave_master_info ，slave_relay_log_info两个表中数据； 删除所有relay
log文件，并重新创建新的relay log文件； 不会改变gtid_executed 或者 gtid_purged的值

5 应用层面配置

（1）参考下面文章

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-master-slave-replication-connection.html

（2）如果多主方式，会默认开启loadbalance，参考下面截图

jdbc.ums.url=jdbc:mysql:replication://address=(type=master)(host=192.168.1.1)(port=3306),address=(type=master)(host=192.168.1.2)(port=3306),address=(type=master)(host=192.168.1.3)(port=3306)/ums?useUnicode=true&useSSL=false&characterEncoding=utf-8&autoReconnect=true

（3）连接参数说明

更多高级配置优化，可参考下面这篇文章 https://dbaplus.cn/news-141-2231-1.html