MGR配置proxySQL-2.5.4

一.下载文件proxysql-2.5.4-1-centos7.x86_64.rpm

        wget https://github.com/sysown/proxysql/releases/download/v2.5.4/proxysql-2.5.4-1-centos7.x86_64.rpm

二.安装文件proxySQL-2.5.4
        rpm -ivh proxysql-2.5.4-1-centos7.x86_64.rpm

        警告：proxysql-2.5.4-1-centos7.x86_64.rpm: 头V4 RSA/SHA512 Signature, 密钥 ID 8217c97e: NOKEY
错误：依赖检测失败：
    perl(DBD::mysql) 被 proxysql-2.5.4-1.x86_64 需要
    perl(DBI) 被 proxysql-2.5.4-1.x86_64 需要

        yum install perl-DBI
        yum install perl-DBD-mysql

三.启动proxySQL
        systemctl start proxysql
        systemctl enable proxysql
        systemctl status proxysql

四.配置proxySQL

        0.设置proxySQL里的mysql版本，MySQL 8.0及以后的版本不再支持查询缓存（query cache）,否则java连接报错 java.sql.SQLException: Unknown system variable 'query_cache_size'

        update global_variables set variable_value="8.0.33 (ProxySQL)" where variable_name='mysql-server_version';

        set mysql-set_query_lock_on_hostgroup=0;

        1.用mysql客户端登录proxySQL

                /home/ssd/mysql/bin/mysql -uadmin -padmin -h127.0.0.1 -P6032

        2. 设置监控用户

                set mysql-monitor_username='monitor';
                set mysql-monitor_password='123456';

        3.配置主从分组信息（ProxySQL）

                insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader)values(10,20,30,40,1,1,1);
        

mysql_group_replication_hostgroups详解

write_hostgroup：
        默认情况下会将所有流量发送到这个组。具有read_only=0的节点也将分配到这个组；
backup_writer_hostgroup：
        如果集群有多个写节点(read_only=0)且超过了max_writers规定数量，则会把多出来的写节点放到备用写组里面；
reader_hostgroup：
        读取的流量应该发送到该组，只读节点(read_only=1)会被分配到该组；
offline_hostgroup：
        当ProxySQL监视到某个节点不正常时，会被放入该组；
active：
        是否启用主机组，当启用时，ProxySQL将监视主机在各族之间移动；
max_writers：
        最大写节点的数量，超过该值的节点应该被放入backup_write_hostgroup；
writer_is_also_reader：
        0：写节点只能写，读节点只能读
        1：写节点能读写，读节点只能读
        2：写节点能读写，另一个备用写节点只能读，其他节点，不能读写

        4.创建proxysql业务用户

insert into mysql_users(username,password,default_hostgroup)values('yiyinwen','123456',10);

        5.添加服务器列表
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values (10,'n1',3306,1,3000,10,'n1');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values (30,'n1',3306,10,3000,10,'n1');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment)values (30,'n0',3306,90,3000,10,'n0');

        6.配置路由规则，实现读写分离

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

        7.创建视图

-------------------------------------------------------------------------------------在mysql主节点执行-------------------------------------------------------------------------

USE sys;

DROP VIEW IF EXISTS gr_member_routing_candidate_status;
DROP FUNCTION IF EXISTS IFZERO;
DROP FUNCTION IF EXISTS LOCATE2;
DROP FUNCTION IF EXISTS GTID_NORMALIZE;
DROP FUNCTION IF EXISTS GTID_COUNT;
DROP FUNCTION IF EXISTS gr_applier_queue_length;
DROP FUNCTION IF EXISTS gr_member_in_primary_partition;
DROP FUNCTION IF EXISTS gr_transactions_to_cert;

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_transactions_to_cert() RETURNS int(11)
    DETERMINISTIC
BEGIN
  RETURN (select  performance_schema.replication_group_member_stats.COUNT_TRANSACTIONS_IN_QUEUE AS transactions_to_cert
    FROM
        performance_schema.replication_group_member_stats where MEMBER_ID=@@SERVER_UUID );
END$$

CREATE FUNCTION my_server_uuid() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$

CREATE VIEW gr_member_routing_candidate_status AS
    SELECT 
        IFNULL((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 = my_server_uuid()),
                'NO') AS viable_candidate,
        IF((SELECT 
                    ((SELECT 
                                GROUP_CONCAT(performance_schema.global_variables.VARIABLE_VALUE
                                        SEPARATOR ',')
                            FROM
                                performance_schema.global_variables
                            WHERE
                                (performance_schema.global_variables.VARIABLE_NAME IN ('read_only' , 'super_read_only'))) <> 'OFF,OFF')
                ),
            'YES',
            'NO') AS read_only,
        IFNULL(sys.gr_applier_queue_length(), 0) AS transactions_behind,
        IFNULL(sys.gr_transactions_to_cert(), 0) AS transactions_to_cert;$$

DELIMITER ;

        8.使配置生效
save mysql users to disk;
save mysql servers to disk;
save mysql query rules to disk;
save mysql variables to disk;
save admin variables to disk;
load mysql users to runtime;
load mysql servers to runtime;
load mysql query rules to runtime;
load mysql variables to runtime;
load admin variables to runtime;

        9.验证

mysql> SELECT * FROM sys.gr_member_routing_candidate_status;
proxysql> select * from mysql_server_group_replication_log order by time_start_us desc limit 6\G;
proxysql> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10\G;
proxysql> SELECT* FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;