mysql5.7 MGR multi-master + proxysql

结构

主机名	IP	端口	作用
proxysql	172.17.0.7	6032,6033	代理
master	172.17.0.3	3306	MGR集群
slave1	172.17.0.4	3306
slave2	172.17.0.3	3306
slave3	172.17.0.6	3306

MGR部署为多主，检查集群状态

mgr其中一个节点运行sql 脚本，在sys库中建立function，地址如下：

https://github.com/zhishutech/mysql_gr_routing_check

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 ;

master> source /root/addition_to_sys.sql;

查看各个节点状态， 由于采用multi-master 模式，所有的节点都可写入，所以read_only 为NO

安装 proxysql

[root@proxysql ~]# yum -y install proxysql-2.0.10-1-centos7.x86_64.rpm

启动 proxysql

systemctl start proxysql.service

建立监控账号：

MGR 其中一个节点建立 monitor 账号（proxysql 配置文件默认的 cat /etc/proxysql.conf）

create user 'monitor'@'172.17.0.%' identified by 'monitor';
grant select on sys.* to   'monitor'@'172.17.0.%'

proxysql 中需要有 mysql环境，登录ProxySQL管理端口进行配置

mysql -h 127.0.0.1 -u admin -padmin -P6032

MGR 分组规划

writer_hostgroup --------------- 100
backup_writer_hostgroup -------- 101
reader_hostgroup --------------- 102
offline_hostgroup -------------- 103

1. mysql_group_replication_hostgroups 表添加分组

insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,max_writers,writer_is_also_reader,max_transactions_behind,comment) values(100,101,102,103,1,0,100,'MGR');

2. 添加 MGR中的服务器到proxysql，由于所有节点都是可写的，hostgroup_id 为100.

insert into mysql_servers(hostgroup_id,hostname,port,comment) values(100,'172.17.0.3',3306,'master');

insert into mysql_servers(hostgroup_id,hostname,port,comment) values(100,'172.17.0.4',3306,'slave1');

insert into mysql_servers(hostgroup_id,hostname,port,comment) values(100,'172.17.0.5',3306,'slave2');

insert into mysql_servers(hostgroup_id,hostname,port,comment) values(100,'172.17.0.6',3306,'slave3');

3. mysql_users

 insert into mysql_users(username,password,default_hostgroup,default_schema,comment) values('fengjian','123456',100,'T800','add connection user');

4. 保存到runtime 和 disk

save  mysql users to disk;
save mysql server to disk;

load mysql users to runtime;
load mysql servers to runtime;

5. 检查

6. 设置读写规则mysql_query_rules ：

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)   values(1,1,'^SELECT.*FOR UPDATE$',100,1),(2,1,'^SELECT',102,1);

保存到disk 和runtime
slave mysql query rules to disk;
load mysql query rules to runtime;

注意 runtime_mysql_servers 中，没有读的库，所以需要设置 writer_is_also_reader 只为1，

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

update mysql_group_replication_hostgroups set writer_is_also_reader=1;

save mysql servers to disk;
laod mysql servers to runtime;

writer_is_also_reader：决定一个节点升级为写节点(放进writer_hostgroup)后是否仍然保留在reader_hostgroup组中提供读服务。

7. 登录测试

有mysql客户端的机器：
mysql -h 172.17.0.7 -u fengjian -p123456 -P6033

测试读，发现一个问题，客户端连接后，读的库不变，客户端再连接，可能连接到其他的读库上


mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id for update;
+-------------+
| @@server_id |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)

参考：

https://www.lagou.com/lgeduarticle/9142.html

https://www.cnblogs.com/zhouwanchun/p/11456378.html