Các Script SQL Liên Quan đến Cấu Hình MySQL MGR Cluster

Trong bài viết này, chúng ta sẽ tìm hiểu về các script SQL được sử dụng để giám sát và cấu hình ProxySQL trong môi trường MySQL MGR (MySQL Group Replication) Cluster. Các script này giúp theo dõi trạng thái của các thành viên trong cluster cũng như tính toán số lượng giao dịch đang chờ xử lý.

  1. Script chính thức hỗ trợ MySQL 5.7
USE sys;

DELIMITER $$

CREATE FUNCTION IFNULLVALUE(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a IS NULL, b, a)$$

CREATE FUNCTION FIND_POSITION(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFNULLVALUE(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION NORMALIZE_GTID(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION COUNT_GTID(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE total 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 = NORMALIZE_GTID(gtid_set);
  SET colon_pos = FIND_POSITION(':', gtid_set, 1);
  
  WHILE colon_pos != LENGTH(gtid_set) + 1 DO
    SET next_dash_pos = FIND_POSITION('-', gtid_set, colon_pos + 1);
    SET next_colon_pos = FIND_POSITION(':', gtid_set, colon_pos + 1);
    SET next_comma_pos = FIND_POSITION(',', gtid_set, colon_pos + 1);
    
    IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
      SET total = total +
        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 total = total + 1;
    END IF;
    
    SET colon_pos = next_colon_pos;
  END WHILE;
  
  RETURN total;
END$$

CREATE FUNCTION QUEUE_LENGTH_MGR()
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN (SELECT COUNT_GTID(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 IS_MEMBER_PRIMARY()
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 STATUS_CANDIDATE AS
SELECT 
  IS_MEMBER_PRIMARY() AS is_viable_candidate,
  IF((SELECT GROUP_CONCAT(VARIABLE_VALUE SEPARATOR ',') FROM performance_schema.global_variables WHERE VARIABLE_NAME IN ('read_only', 'super_read_only')) != 'OFF,OFF', 'YES', 'NO') AS read_only_state,
  QUEUE_LENGTH_MGR() AS pending_transactions,
  Count_Transactions_in_queue AS certifying_transactions
FROM performance_schema.replication_group_member_stats;$$

DELIMITER ;
  1. Phiên bản trực tuyến không hỗ trợ MySQL 8.0
USE sys;

DELIMITER $$

CREATE FUNCTION CHECK_NULL(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a IS NULL, b, a)$$

CREATE FUNCTION POSITION_IN_TEXT(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN CHECK_NULL(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION CLEANUP_GTID(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION TOTAL_GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE count_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 = CLEANUP_GTID(gtid_set);
  SET colon_pos = POSITION_IN_TEXT(':', gtid_set, 1);
  
  WHILE colon_pos != LENGTH(gtid_set) + 1 DO
    SET next_dash_pos = POSITION_IN_TEXT('-', gtid_set, colon_pos + 1);
    SET next_colon_pos = POSITION_IN_TEXT(':', gtid_set, colon_pos + 1);
    SET next_comma_pos = POSITION_IN_TEXT(',', gtid_set, colon_pos + 1);
    
    IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
      SET count_result = count_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 count_result = count_result + 1;
    END IF;
    
    SET colon_pos = next_colon_pos;
  END WHILE;
  
  RETURN count_result;
END$$

CREATE FUNCTION QUEUE_LENGTH()
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN (SELECT TOTAL_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 PRIMARY_PARTITION_STATUS()
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)
    WHERE performance_schema.replication_group_members.member_host=@@hostname);
END$$

CREATE VIEW CANDIDATES_VIEW AS
SELECT 
  PRIMARY_PARTITION_STATUS() AS is_primary_candidate,
  IF((SELECT GROUP_CONCAT(VARIABLE_VALUE SEPARATOR ',') FROM performance_schema.global_variables WHERE VARIABLE_NAME IN ('read_only', 'super_read_only')) != 'OFF,OFF', 'YES', 'NO') AS read_only_flag,
  QUEUE_LENGTH() AS queued_transactions,
  Count_Transactions_in_queue AS certifiable_transactions
FROM performance_schema.replication_group_member_stats a
JOIN performance_schema.replication_group_members b ON a.member_id = b.member_id
WHERE b.member_host IN (SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'hostname');$$

DELIMITER ;
  1. Phiên bản trực tuyến hoạt động trên MySQL 8.0.33
USE sys;

DROP VIEW IF EXISTS CANDIDATES_STATUS;
DROP FUNCTION IF EXISTS CHECK_NULL;
DROP FUNCTION IF EXISTS POSITION_IN_TEXT;
DROP FUNCTION IF EXISTS CLEANUP_GTID;
DROP FUNCTION IF EXISTS TOTAL_GTID_COUNT;
DROP FUNCTION IF EXISTS QUEUE_LENGTH;
DROP FUNCTION IF EXISTS PRIMARY_PARTITION_STATUS;
DROP FUNCTION IF EXISTS CERTIFIABLE_TRANSACTIONS;

DELIMITER $$

CREATE FUNCTION CHECK_NULL(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a IS NULL, b, a)$$

CREATE FUNCTION POSITION_IN_TEXT(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN CHECK_NULL(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION CLEANUP_GTID(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION TOTAL_GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE count_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 = CLEANUP_GTID(gtid_set);
  SET colon_pos = POSITION_IN_TEXT(':', gtid_set, 1);
  
  WHILE colon_pos != LENGTH(gtid_set) + 1 DO
    SET next_dash_pos = POSITION_IN_TEXT('-', gtid_set, colon_pos + 1);
    SET next_colon_pos = POSITION_IN_TEXT(':', gtid_set, colon_pos + 1);
    SET next_comma_pos = POSITION_IN_TEXT(',', gtid_set, colon_pos + 1);
    
    IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
      SET count_result = count_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 count_result = count_result + 1;
    END IF;
    
    SET colon_pos = next_colon_pos;
  END WHILE;
  
  RETURN count_result;
END$$

CREATE FUNCTION QUEUE_LENGTH()
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN (SELECT TOTAL_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 PRIMARY_PARTITION_STATUS()
RETURNS VARCHAR(20) CHARSET utf8mb4
DETERMINISTIC
BEGIN
  DECLARE result VARCHAR(20);
  DECLARE error INT DEFAULT 0;
  
  DECLARE CONTINUE HANDLER FOR 1242 SET error = 1242;
  
  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') INTO result
  FROM performance_schema.replication_group_members 
  JOIN performance_schema.replication_group_member_stats rgms USING(member_id)
  WHERE rgms.MEMBER_ID=@@SERVER_UUID;
  
  IF error > 0 THEN
    GET DIAGNOSTICS CONDITION 1
    @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
    SELECT @p2 INTO result;
    RETURN result;
  END IF;
  
  IF result IS NULL THEN 
    RETURN 'NO';
  END IF;
  
  RETURN result;
END$$

CREATE FUNCTION CERTIFIABLE_TRANSACTIONS()
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE transactions INT DEFAULT 0;
  
  SELECT Count_Transactions_in_queue INTO transactions
  FROM performance_schema.replication_group_member_stats
  WHERE MEMBER_ID=@@SERVER_UUID;
  
  IF transactions IS NULL THEN 
    RETURN 0;
  END IF;
  
  RETURN transactions;
END$$

CREATE VIEW CANDIDATES_STATUS AS
SELECT 
  PRIMARY_PARTITION_STATUS() AS is_viable_candidate,
  IF((SELECT GROUP_CONCAT(performance_schema.global_variables.VARIABLE_VALUE SEPARATOR ',')
      FROM performance_schema.global_variables
      WHERE VARIABLE_NAME IN ('read_only', 'super_read_only')) <> 'OFF,OFF',
    'YES', 'NO') AS read_only_flag,
  QUEUE_LENGTH() AS pending_transactions,
  CERTIFIABLE_TRANSACTIONS() AS certifiable_transactions;
$$

DELIMITER ;

Thẻ: mysql MGR proxysql

Đăng vào ngày 20 tháng 6 lúc 01:38

Thẻ Phổ Biến

©2026 Thành phố Cuồng loạn