MYSQL 中间件 为什么选择 PROXYSQL VS INNODB CLUSTER

没有磨难，怎么能得到心灵的平静，吃过苦，才懂得甜的味道,Just waitting .

实际上proxysql 本身支持的MYSQL的高可用方式中，早早就支持了MGR的方式了，之前是写过PROXYSQL 支持 MYSQL 5.7 高可用的方式，使用了有也有很长一段时间，很稳定。有同学反映，在搭建MYSQL8.019时，照方抓药发现问题无法解决。下面先将安装的流程来一边，在说到底为什么，老方法不行了。

首先常规安装完proxysql 的安装包后，进行相关的配置与上一篇有部分是不一致的。

三台MYSQL 8.019

10.5.1.100

10.5.1.101

10.5.1.102

登陆到proxysql

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

将三台机器录入到 mysql_servers 中

INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.198.102',600,3306,1000);

INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.198.101',602,3306,1000);

INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight) VALUES ('192.168.198.100',602,3306,1000);

这里主要和mysql_group_replication_hostgroups，中的

writer_hostgroup

backup_writer_hostgroup

reader_hostgroup

offline_hostgroup

四个参数有关，其中由于MGR 本身存在可以有多主的情况, 虽然 proxysql 也支持，但这里不会讨论，仅仅会讨论单主的情况。

writer hostgroup 组 同一个时刻只能有一台机器

backup_writer_hostgroup 在有多主的情况下有用，这里暂不讨论

reader_hostgroup 只读组

offline_hostgroup 服务器已经不再组内，或失联

所以在录入 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 (600,601,602,603,1,1,1,0);

这里需要注意几点

1 这里write_hostgroup, backup_write_hostgroup , reader_hostgroup, offline_hostgroup 必须是不同的，否则是无法进行数据插入的。

2 max_writers 默认为1 ，在同一个时刻只能有一个写库

3 writer_is_also_reader ，读写操作在一起

4 max_transactions_behind 与MGR 本身的事务一致性设置有关

最后还需要设置monitor账户，以及访问mysql的账号即可（具体就不赘述，可以参考上一篇）

通过预设的账号登陆到PROXYSQL 中

登陆后可以进行相关的DDL 以及查询操作

我们进行下一步的测试

1 我们停止掉MGR 中的写库primary , 测试系统是否可以进行切换

停止 192.168.198.101 primary

2 我们继续通过 6033 端口进行写操作

不断继续插入数据，查看是否有失败的情况，结果 在主节点进行切换期间，写操作失败

3 写操作后续是否能正常工作 结果可以

具体PROXYSQL 是如何对MGR 节点进行判断的

根据查看相关日志，默认的情况55秒进行一次连接判断测试，每台服务器4-5秒一次查询当前的服务器的情况。

实际上判断每台服务器的语句判断的有三个值， 服务器是否还在这个集群中，服务器是不是read_only 还有 transaction_behind 的值等。

其他的服务器的 viable_candidate 的值就会变为 NO

其实这里要讨论的一个关键的问题，就是在MYSQL 5.7中通过proxysql 来行切换是没有问题，但换到 8.019 系统就无法进行正常的切换和使用。主要的问题就在于下面的这段，下面的脚本是需要在MYSQL 的sys库来生成的，目的就是提供查询值，来让proxysql判断MYSQL 节点是否在正常工作。

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 ;

变化点在加粗的位置,添加了过滤本机的信息，弥补MYSQL5.7 的系统表与MYSQL8.109之间的不同。

USE sys;

DELIMITER $$

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 rgms USING(member_id)

WHERE rgms.MEMBER_ID=@@SERVER_UUID);

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 rgms

where rgms.MEMBER_ID=(select gv.VARIABLE_VALUE

from `performance_schema`.global_variables gv

where gv.VARIABLE_NAME='server_uuid');$$

DELIMITER ;