返回ProxySQL系列文章：http://www.cnblogs.com/f-ck-need-u/p/7586194.html

1.ProxySQL+组复制前言

在以前的ProxySQL版本中，要支持MySQL组复制(MGR,MySQL Group Replication)需要借助第三方脚本对组复制做健康检查并自动调整配置，但是从ProxySQL v1.4.0开始，已原生支持MySQL组复制的代理，在main库中也已提供mysql_group_replication_hostgroups表来控制组复制集群中的读、写组。

Admin> show tables ;

+--------------------------------------------+

| tables                                     |

+--------------------------------------------+

| global_variables                           |

| mysql_collations                           |

| mysql_group_replication_hostgroups         |

| mysql_query_rules                          |

...

| runtime_mysql_group_replication_hostgroups |

...

| scheduler                                  |

+--------------------------------------------+

admin> show tables from monitor;

+------------------------------------+

| tables                             |

+------------------------------------+

| mysql_server_connect_log           |

| mysql_server_group_replication_log |

| mysql_server_ping_log              |

| mysql_server_read_only_log         |

| mysql_server_replication_lag_log   |

+------------------------------------+

尽管已原生支持MGR，但仍然需要在MGR节点中创建一张额外的系统视图sys.gr_member_routing_candidate_status为ProxySQL提供监控指标。创建该视图的脚本addition_to_sys.zip我已上传。在后文需要创建该系统视图的地方，我会将这个脚本的内容贴出来。

本文先解释mysql_group_replication_hostgroups表中各字段的意义，然后按照实验环境做好各种组的分配。最后根据实验环境快速搭建单主模型的组复制环境，以及ProxySQL代理单主模型组复制的配置步骤。因为本文描述了ProxySQL代理单主、多主MGR的情形，所以搭建ProxySQL代理多主MGR也是没有任何问题的。

本文实验环境：

roles	IP_address
proxysql	192.168.100.21
node1	192.168.100.22
node2	192.168.100.23
node3	192.168.100.24

1.1 mysql_group_replication_hostgroups表

该表的定义语句：

Admin> show create table mysql_group_replication_hostgroups\G

*************************** 1. row ***************************

       table: mysql_group_replication_hostgroups

Create Table: CREATE TABLE mysql_group_replication_hostgroups (

    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,

    backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL,

    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0),

    offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0),

    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,

    max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1,

    writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1)) NOT NULL DEFAULT 0,

    max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0,

    comment VARCHAR,

    UNIQUE (reader_hostgroup),

    UNIQUE (offline_hostgroup),

    UNIQUE (backup_writer_hostgroup))

各字段的意义如下：

writer_hostgroup：默认的写组。后端read_only=0的节点会自动分配到这个组中。
backup_writer_hostgroup：如果后端MySQL集群有多个节点可写并设置了max_writes字段的值，ProxySQL将会把其余的所有节点(超出max_writes)都放进备写组backup_writer_hostgroup中作为备份节点。
reader_hostgroup：负责读的组。查询规则或者只具有只读权限的用户的读请求都会路由到该主机组中的节点。后端read_only=1的节点会自动分配到这个组中。
offline_hostgroup：当ProxySQL监控并决定了某节点为OFFLINE后，会将其放进组offline_hostgroup中。
active：当启用后，ProxySQL会监控该主机组，并在不同组之间合理地移动节点。
max_writers：该字段的值决定写组writer_hostgroup中最大允许的节点数量，超出该数量的但允许写的节点都会放进备份组backup_writer_hostgroup中。
writer_is_also_reader：决定一个节点升级为写节点(放进writer_hostgroup)后是否仍然保留在reader_hostgroup组中提供读服务。
max_transactions_behind：当某节点延后于写节点时，为了防止读取到过期数据，ProxySQL可能会自动避开该节点。该字段决定最多延后写节点多少个事务(具体延后的事务数量可以从MySQL的sys.gr_member_routing_candidate_status表中的transaction_behind字段获取)，延后的事务数量超出该值时，ProxySQL就会自动避开这个节点。
comment：该字段用于说明、注释，可随便定义。

需要注意的是，writer_hostgroup是主键字段，reader_hostgroup、offline_hostgroup、backup_writer_hostgroup具有唯一性，它们的值都是INT数值。

所以，ProxySQL代理每一个后端MGR集群时，都必须为这个MGR定义读组、写组、备写组、离线组，且这四个组的值各不相同、不允许NULL、具有唯一性。此外，每个username还有一个默认组，一般这个默认组会设置为写组，这样在定义select规则时比较容易。

1.2 ProxySQL代理MGR时必须考虑的问题

ProxySQL代理MGR时有几种情况：ProxySQL代理的MGR运行模式是单主模型还是多主模型，ProxySQL代理的是一个还是多个MGR集群。需要单独考虑这些不同的情况，它们影响ProxySQL如何分配组，甚至配置步骤不同。

1.2.1 ProxySQL代理单主模型的MGR

MRG以单主模型运行时，有两个相关特性：

1.非master节点会自动设置read_only=1
2.master节点故障时，会自动选举新的master节点，选举时根据权重(group_replication_member_weigth，较老版本的组复制根据server_uuid按字典排序，值小的优先选为master)决定谁是新的master

所以ProxySQL代理单主MGR时，ProxySQL中 要设置对后端节点read_only值的监控 。因为ProxySQL会根据read_only值自动调整读、写组中的节点，所以代理单主模型时非常方便。当然，如果不想让ProxySQL来自动调整MGR节点所属组，则无需设置read_only监控，见下文"ProxySQL代理单个MGR集群"中的描述。

由于只有一个写节点，所以用不上备写组，但仍然需要定义好它。例如：

写组 -->hg=10

备写组 -->hg=20

读组 -->hg=30

离线组 -->hg=40

1.2.2 ProxySQL代理多主模型的MGR

多主模型的MGR，可以同时有多个写节点，并且允许少数节点出现故障。

仍然假设组的分配情况：

写组 -->hg=10

备写组 -->hg=20

读组 -->hg=30

离线组 -->hg=40

192.168.100.{22,23,24}分别命名为node1、node2、node3节点。

假设max_writers=2，则node1、node2、node3其中2个节点(假设node1、node2)在写组hg=10中，node3在备写组hg=20中。此时必须设置writer_is_also_reader=1，否则没有节点负责读操作，所以hg=30中有node1、node2、node3共3个节点。假如node2节点故障，node3节点将从hg=20转移到hg=10，读组hg=30也只有node1和node3，node2会转移到hg=40中，并ProxySQL不断监控它是否上线。

所以，ProxySQL代理多主模型的MGR时，必须设置writer_is_also_reader=1。

1.2.3 ProxySQL代理单个MGR集群

ProxySQL代理单个MGR集群时，如果不定制复杂的路由规则，完全由ProxySQL来控制读、写组的节点分配，那么在mysql_group_replication_hostgroups表中只能有一条记录。

但是如果想要实现复杂的需求，例如想要将开销大的select语句路由到某个固定的slave上或自定义的某个hostgroup中，就不能再让ProxySQL来管理MGR，这时不能在mysql_group_replication_hostgroups中插入和该MGR集群有关的记录(如果可以，也不要去监控read_only值)，而是在mysql_servers中定义好目标组。这种情况下，ProxySQL不关心后端是MGR还是普通的MySQL实例。

1.2.4 ProxySQL代理多个MGR集群

很不幸，ProxySQL的mysql_group_replication_hostgroups表对多MGR集群并不友好。因为ProxySQL通过监控read_only值来自动调整节点所属组。如果ProxySQL代理两个MGR集群X、Y，在mysql_group_replication_hostgroups添加一条记录后，MGR集群X、Y中的节点都会加入到这条记录所定义的组中，于是两个MGR集群就混乱了。添加多条记录也无济于事，因为这个表中并没有识别集群的方法。其实mysql_replication_hostgroups也一样存在这样的问题。

这时只能在mysql_servers中对不同MGR集群中的各个节点定义好所属组，然后在规则中指定路由目标。也就是说，用不上mysql_group_replication_hostgroups表，也无需去监控read_only值。

1.3 配置组复制

本文配置的单主模型的组复制。

1.设置主机名和DNS解析(必须保证各mysql实例的主机名不一致，且能通过主机名找到各成员)

# node1上：

hostnamectl set-hostname --static node1.longshuai.com

hostnamectl -H root@192.168.100.23 set-hostname node2.longshuai.com

hostnamectl -H root@192.168.100.24 set-hostname node3.longshuai.com

# 写/etc/hosts

# node1上：

cat >>/etc/hosts<<eof

    192.168.100.22 node1.longshuai.com

    192.168.100.23 node2.longshuai.com

    192.168.100.24 node3.longshuai.com

eof

scp /etc/hosts 192.168.100.23:/etc

scp /etc/hosts 192.168.100.24:/etc

2.提供node1、node2、node3的配置文件

node1的/etc/my.cnf内容：

[mysqld]

datadir=/data

socket=/data/mysql.sock

server-id=100                      # 各节点不一致

gtid_mode=on

enforce_gtid_consistency=on

log-bin=/data/master-bin

binlog_format=row

binlog_checksum=none

master_info_repository=TABLE

relay_log_info_repository=TABLE

relay_log=/data/relay-log

log_slave_updates=ON

sync-binlog=1

log-error=/data/error.log

pid-file=/data/mysqld.pid

transaction_write_set_extraction=XXHASH64

loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"

loose-group_replication_start_on_boot=off

loose-group_replication_member_weigth = 40     # 建议各节点设置不同值

loose-group_replication_local_address="192.168.100.22:20002"  # 各节点不一致

loose-group_replication_group_seeds="192.168.100.22:20002,192.168.100.23:20003,,192.168.100.24:20004"

node2和node3的配置文件只需修改几项需要不一致的值即可：

以下为node2的/etc/my.cnf的部分内容

server-id=110

loose-group_replication_member_weigth = 30

loose-group_replication_local_address="192.168.100.23:20003"

以下为node3的/etc/my.cnf的部分内容

server-id=120

loose-group_replication_member_weigth = 20

loose-group_replication_local_address="192.168.100.24:20004"

3.启动node1，引导组复制

先启动node1的MySQL服务。

systemctl start mysqld

连上node1节点，创建用于复制的用户。我这里创建的用户为repl，密码为P@ssword1!。

create user repl@'192.168.100.%' identified by 'P@ssword1!';

grant replication slave on *.* to repl@'192.168.100.%';

在node1上配置恢复通道。

change master to

            master_user='repl',

            master_password='P@ssword1!'

            for channel 'group_replication_recovery';

安装组复制插件。

install plugin group_replication soname 'group_replication.so';

引导、启动组复制功能。

set @@global.group_replication_bootstrap_group=on;

start group_replication;

set @@global.group_replication_bootstrap_group=off;

查看node1是否ONLINE。

select * from performance_schema.replication_group_members\G

4.添加node2、node3到复制组中

先启动node2和node3的mysql服务：

systemctl start mysqld

再在node2和node3上指定恢复通道，从Donor处恢复数据。

change master to

            master_user='repl',

            master_password='P@ssword1!'

            for channel 'group_replication_recovery';

最后，在node2和node3上安装组复制插件，并启动组复制功能即可。

install plugin group_replication soname 'group_replication.so';

start group_replication;

在任意一个节点上查看node1、node2、node3是否都是ONLINE。

select * from performance_schema.replication_group_members\G

*************************** 1. row ***************************

CHANNEL_NAME: group_replication_applier

   MEMBER_ID: a5165443-6aec-11e8-a8f6-000c29827955

 MEMBER_HOST: node1.longshuai.com

 MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

*************************** 2. row ***************************

CHANNEL_NAME: group_replication_applier

   MEMBER_ID: ba505889-6aec-11e8-a864-000c29b0bec4

 MEMBER_HOST: node2.longshuai.com

 MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

*************************** 3. row ***************************

CHANNEL_NAME: group_replication_applier

   MEMBER_ID: bf12fe97-6aec-11e8-a909-000c29e55287

 MEMBER_HOST: node3.longshuai.com

 MEMBER_PORT: 3306

MEMBER_STATE: ONLINE

至此，node1、node2、node3组成的3节点单主模型的组复制配置完成。下面配置ProxySQL。

1.4 配置ProxySQL

根据前文的分析，ProxySQL代理单主模型组复制时，如果想让ProxySQL来自动调整节点所属读、写组，需要开启read_only监控，并在mysql_group_replication_hostgroups表中插入一条记录。

假设4种组的hostgroup_id为：

写组 -->hg=10

备写组 -->hg=20

读组 -->hg=30

离线组 -->hg=40

安装ProxySQL的过程略。以下是配置ProxySQL的过程。

1.连上ProxySQL的Admin管理接口

mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt 'Admin> '

2.向 mysql_servers 表中添加后端节点node1、node2和node3

delete from mysql_servers;

insert into mysql_servers(hostgroup_id,hostname,port)

values(10,'192.168.100.22',3306),

      (10,'192.168.100.23',3306),

      (10,'192.168.100.24',3306);

load mysql servers to runtime;

save mysql servers to disk;

查看3个节点是否都是ONLINE

admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;

+--------------+----------------+------+--------+--------+

| hostgroup_id | hostname       | port | status | weight |

+--------------+----------------+------+--------+--------+

| 10           | 192.168.100.22 | 3306 | ONLINE | 1      |

| 10           | 192.168.100.23 | 3306 | ONLINE | 1      |

| 10           | 192.168.100.24 | 3306 | ONLINE | 1      |

+--------------+----------------+------+--------+--------+

3.监控后端节点

首先，在node1上创建ProxySQL用于监控的用户。注意，这里监控用户的权限和ProxySQL代理普通mysql实例不一样，ProxySQL代理组复制时，是从MGR的系统视图sys.gr_member_routing_candidate_status中获取监控指标，所以授予监控用户对该视图的查询权限，因为无需从show slave status中获取Seconds_Behind_Master，所以无需replication client权限。

# 在node1上执行：

mysql> create user monitor@'192.168.100.%' identified by 'P@ssword1!';

mysql> grant select on sys.* to monitor@'192.168.100.%';

然后回到ProxySQL上配置监控。

set mysql-monitor_username='monitor';

set mysql-monitor_password='P@ssword1!';

load mysql variables to runtime;

save mysql variables to disk;

4.创建系统视图sys.gr_member_routing_candidate_status

在node1节点上，创建系统视图sys.gr_member_routing_candidate_status，该视图将为ProxySQL提供组复制相关的监控状态指标。

如果前面下载了addition_to_sys.sql脚本，执行如下语句导入MySQL即可。

mysql -uroot -pP@ssword1! < addition_to_sys.sql

也可以执行如下语句创建该系统视图：

USE sys;

DELIMITER $$

CREATE FUNCTION IFZERO(a INT, b INT)

RETURNS INT

DETERMINISTIC

RETURN IF(a = 0, b, a)$$

CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)

RETURNS INT

DETERMINISTIC

RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))

RETURNS TEXT(10000)

DETERMINISTIC

RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))

RETURNS INT

DETERMINISTIC

BEGIN

  DECLARE result BIGINT DEFAULT 0;

  DECLARE colon_pos INT;

  DECLARE next_dash_pos INT;

  DECLARE next_colon_pos INT;

  DECLARE next_comma_pos INT;

  SET gtid_set = GTID_NORMALIZE(gtid_set);

  SET colon_pos = LOCATE2(':', gtid_set, 1);

  WHILE colon_pos != LENGTH(gtid_set) + 1 DO

     SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);

     SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);

     SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);

     IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN

       SET result = result +

         SUBSTR(gtid_set, next_dash_pos + 1,

                LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -

         SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;

     ELSE

       SET result = result + 1;

     END IF;

     SET colon_pos = next_colon_pos;

  END WHILE;

  RETURN result;

END$$

CREATE FUNCTION gr_applier_queue_length()

RETURNS INT

DETERMINISTIC

BEGIN

  RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT

Received_transaction_set FROM performance_schema.replication_connection_status

WHERE Channel_name = 'group_replication_applier' ), (SELECT

@@global.GTID_EXECUTED) )));

END$$

CREATE FUNCTION gr_member_in_primary_partition()

RETURNS VARCHAR(3)

DETERMINISTIC

BEGIN

  RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM

performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=

((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),

'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN

performance_schema.replication_group_member_stats USING(member_id));

END$$

CREATE VIEW gr_member_routing_candidate_status AS SELECT

sys.gr_member_in_primary_partition() as viable_candidate,

IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM

performance_schema.global_variables WHERE variable_name IN ('read_only',

'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,

sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$

DELIMITER ;

视图创建后，可以查看该视图：

node1上：

mysql> select * from sys.gr_member_routing_candidate_status;

+------------------+-----------+---------------------+----------------------+

| viable_candidate | read_only | transactions_behind | transactions_to_cert |

+------------------+-----------+---------------------+----------------------+

| YES              | NO        |                   0 |                    0 |

+------------------+-----------+---------------------+----------------------+

node2上：

mysql> select * from sys.gr_member_routing_candidate_status;

+------------------+-----------+---------------------+----------------------+

| viable_candidate | read_only | transactions_behind | transactions_to_cert |

+------------------+-----------+---------------------+----------------------+

| YES              | YES       |                   0 |                    0 |

+------------------+-----------+---------------------+----------------------+

5.向mysql_group_replication_hostgroups中插入记录

delete from mysql_group_replication_hostgroups;

insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind)

values(10,20,30,40,1,1,0,0);

load mysql servers to runtime;

save mysql servers to disk;

上述配置中，我把writer_is_also_reader设置为false，让master只负责写操作。

admin> select * from mysql_group_replication_hostgroups\G

*************************** 1. row ***************************

       writer_hostgroup: 10

backup_writer_hostgroup: 20

       reader_hostgroup: 30

      offline_hostgroup: 40

                 active: 1

            max_writers: 1

  writer_is_also_reader: 0

max_transactions_behind: 0

                comment: NULL

再看看节点的分组调整情况：

admin> select hostgroup_id, hostname, port,status from runtime_mysql_servers;

+--------------+----------------+------+--------+

| hostgroup_id | hostname       | port | status |

+--------------+----------------+------+--------+

| 10           | 192.168.100.22 | 3306 | ONLINE |

| 30           | 192.168.100.24 | 3306 | ONLINE |

| 30           | 192.168.100.23 | 3306 | ONLINE |

+--------------+----------------+------+--------+

查看对MGR的监控指标。

Admin> select  hostname,

               port,

               viable_candidate,

               read_only,

               transactions_behind,

               error

       from mysql_server_group_replication_log

       order by time_start_us desc

       limit 6;

+----------------+------+------------------+-----------+---------------------+-------+

| hostname       | port | viable_candidate | read_only | transactions_behind | error |

+----------------+------+------------------+-----------+---------------------+-------+

| 192.168.100.24 | 3306 | YES              | YES       | 0                   | NULL  |

| 192.168.100.23 | 3306 | YES              | YES       | 0                   | NULL  |

| 192.168.100.22 | 3306 | YES              | NO        | 0                   | NULL  |

| 192.168.100.24 | 3306 | YES              | YES       | 0                   | NULL  |

| 192.168.100.23 | 3306 | YES              | YES       | 0                   | NULL  |

| 192.168.100.22 | 3306 | YES              | NO        | 0                   | NULL  |

+----------------+------+------------------+-----------+---------------------+-------+

6.配置mysql_users

在node1节点上执行：

grant all on *.* to root@'192.168.100.%' identified by 'P@ssword1!';

回到ProxySQL，向mysql_users表插入记录。

delete from mysql_users;

insert into mysql_users(username,password,default_hostgroup,transaction_persistent)

values('root','P@ssword1!',10,1);

load mysql users to runtime;

save mysql users to disk;

7.配置测试用的读写分离规则

delete from mysql_query_rules;

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)

VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),

       (2,1,'^SELECT',30,1);

load mysql query rules to runtime;

save mysql query rules to disk;

测试是否按预期进行读写分离。

mysql -uroot -pP@ssword1! -h192.168.100.21 -P6033 -e 'create database gr_test;'

mysql -uroot -pP@ssword1! -h192.168.100.21 -P6033 -e 'select user,host from mysql.user;'

mysql -uroot -pP@ssword1! -h192.168.100.21 -P6033 -e 'show databases;'

查看语句路由状态：

admin> select hostgroup,digest_text from stats_mysql_query_digest;

+-----------+----------------------------------+

| hostgroup | digest_text                      |

+-----------+----------------------------------+

| 10        | show databases                   |

| 30        | select user,host from mysql.user |

| 10        | create database gr_test          |

| 10        | select @@version_comment limit ? |

+-----------+----------------------------------+

select语句路由到读组hg=30上，show操作按照默认主机组路由到hg=10，create操作路由到hg=10这个写组。

8.测试MGR故障转移

将MGR的某个节点停掉，例如直接关闭当前master节点node1的mysql服务。

在node1上执行：

systemctl stop mysqld

然后，看看ProxySQL上的节点状态。

admin> select hostgroup_id, hostname, port,status from runtime_mysql_servers;

+--------------+----------------+------+---------+

| hostgroup_id | hostname       | port | status  |

+--------------+----------------+------+---------+

| 10           | 192.168.100.23 | 3306 | ONLINE  |

| 40           | 192.168.100.22 | 3306 | SHUNNED |

| 30           | 192.168.100.24 | 3306 | ONLINE  |

+--------------+----------------+------+---------+

结果显示node1的状态为SHUNNED，表示该节点被ProxySQL避开了。且node2节点移到了hg=10的组中，说明该节点被选举为了新的Master节点。

再将node1加回组中。在node1上执行：

shell> systemctl start mysqld

mysql> start group_replication;