1. cat << EOF | tee /etc/yum.repos.d/proxysql.repo
    2. [proxysql_repo]
    3. name= ProxySQL YUM repository
    4. baseurl=https:/$repo.proxysql.com/ProxySQL/proxysql-2.2.x/centos/$releasever
    5. gpgcheck=1
    6. gpgkey=https:/$repo.proxysql.com/ProxySQL/repo_pub_key
    7. EOF
    1. yum install proxysql 
    2. 或者
    3. yum install proxysql-2.2.0
    1. systemctl start proxysql
    1. mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> '
    1. select * from mysql_servers;
    3. #第一组
    4. insert into
    5. mysql_servers(hostgroup_id,max_connections,comment,hostname,port)
    6. values(1000,3000,'MGR01-01','192.168.2.3',3306);
    7. insert into
    8. mysql_servers(hostgroup_id,max_connections,comment,hostname,port)
    9. values(1000,3000,'MGR01-02','192.168.2.4',3306);
    10. insert into
    11. mysql_servers(hostgroup_id,max_connections,comment,hostname,port)
    12. values(1000,3000,'MGR01-03','192.168.2.5',3306);
    14. load mysql servers to runtime;
    15. save mysql servers to disk;
    16. select * from runtime_mysql_servers;
    1. ## MGR主节点创建系统视图
    2. USE sys;
    4. DELIMITER $$
    5. CREATE FUNCTION IFZERO ( a INT, b INT ) RETURNS INT DETERMINISTIC RETURN
    6. IF
    7.     ( a = 0, b, a ) $$DELIMITER $$
    8.     CREATE FUNCTION LOCATE2 ( needle TEXT ( 10000 ), haystack TEXT ( 10000 ), OFFSET INT ) RETURNS INT DETERMINISTIC RETURN IFZERO ( LOCATE( needle, haystack, OFFSET ), LENGTH( haystack ) + 1 ) $$DELIMITER $$
    9.     CREATE FUNCTION GTID_NORMALIZE (
    10.     g TEXT ( 10000 )) RETURNS TEXT ( 10000 ) DETERMINISTIC RETURN GTID_SUBTRACT ( g, '' ) $$DELIMITER $$
    11.     CREATE FUNCTION GTID_COUNT (
    12.         gtid_set TEXT ( 10000 )) RETURNS INT DETERMINISTIC BEGIN
    13.     DECLARE
    14.         result BIGINT DEFAULT 0;
    15.     DECLARE
    16.         colon_pos INT;
    17.     DECLARE
    18.         next_dash_pos INT;
    19.     DECLARE
    20.         next_colon_pos INT;
    21.     DECLARE
    22.         next_comma_pos INT;
    24.     SET gtid_set = GTID_NORMALIZE ( gtid_set );
    26.     SET colon_pos = LOCATE2 ( ':', gtid_set, 1 );
    27.     WHILE
    28.             colon_pos != LENGTH( gtid_set ) + 1 DO
    30.             SET next_dash_pos = LOCATE2 ( '-', gtid_set, colon_pos + 1 );
    32.         SET next_colon_pos = LOCATE2 ( ':', gtid_set, colon_pos + 1 );
    34.         SET next_comma_pos = LOCATE2 ( ',', gtid_set, colon_pos + 1 );
    35.         IF
    36.             next_dash_pos < next_colon_pos 
    37.             AND next_dash_pos < next_comma_pos THEN
    39.                 SET result = result + SUBSTR(
    40.                     gtid_set,
    41.                     next_dash_pos + 1,
    42.                     LEAST( next_colon_pos, next_comma_pos ) - ( next_dash_pos + 1 )) - SUBSTR(
    43.                     gtid_set,
    44.                     colon_pos + 1,
    45.                 next_dash_pos - ( colon_pos + 1 )) + 1;
    46.             ELSE 
    47.                 SET result = result + 1;
    49.         END IF;
    51.         SET colon_pos = next_colon_pos;
    53.     END WHILE;
    54.     RETURN result;
    56. END $$DELIMITER $$
    57. CREATE FUNCTION gr_applier_queue_length () RETURNS INT DETERMINISTIC BEGIN
    58.     RETURN (
    59.         SELECT
    60.             sys.gtid_count (
    61.             GTID_SUBTRACT ( ( SELECT Received_transaction_set FROM PERFORMANCE_SCHEMA.replication_connection_status WHERE Channel_name = 'group_replication_applier' ), ( SELECT @@GLOBAL.GTID_EXECUTED ) )));
    63. END $$DELIMITER $$
    64. CREATE FUNCTION gr_member_in_primary_partition () RETURNS VARCHAR ( 3 ) DETERMINISTIC BEGIN
    65.     RETURN (
    66.         SELECT
    67.         IF
    68.             (
    69.                 MEMBER_STATE = 'ONLINE' 
    70.                 AND (( SELECT COUNT(*) FROM PERFORMANCE_SCHEMA.replication_group_members WHERE MEMBER_STATE != 'ONLINE' ) >= (( SELECT COUNT(*) FROM PERFORMANCE_SCHEMA.replication_group_members )/ 2 ) = 0 ),
    71.                 'YES',
    72.                 'NO' 
    73.             ) 
    74.         FROM
    75.             PERFORMANCE_SCHEMA.replication_group_members
    76.         JOIN PERFORMANCE_SCHEMA.replication_group_member_stats USING ( member_id ));
    78. END $$
    80. DELIMITER $$
    81. CREATE VIEW gr_member_routing_candidate_status AS SELECT
    82. sys.gr_member_in_primary_partition () AS viable_candidate,
    83. IF
    84.     ( ( 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,
    85.     sys.gr_applier_queue_length () AS transactions_behind,
    86.     Count_Transactions_in_queue AS 'transactions_to_cert' 
    87. FROM
    88.     PERFORMANCE_SCHEMA.replication_group_member_stats;
    89. $$
    90. DELIMITER;
    92. #查看
    93. select * from sys.gr_member_routing_candidate_status;
    94. #监控用户
    95. create user proxysql_monitor@'%' identified by '123456';
    96. grant select on sys.* to proxysql_monitor@'%';
    1. ## MGR主节点创建系统视图
    2. USE sys;
    4. DELIMITER $$
    5. CREATE FUNCTION IFZERO ( a INT, b INT ) 
    6. RETURNS INT 
    7. DETERMINISTIC 
    8. RETURN IF( a = 0, b, a )$$
    10. CREATE FUNCTION LOCATE2 ( needle TEXT ( 10000 ), haystack TEXT ( 10000 ), OFFSET INT )RETURNS INT 
    11. DETERMINISTIC 
    12. RETURN IFZERO( LOCATE( needle, haystack, OFFSET ), LENGTH( haystack ) + 1 )$$
    14. CREATE FUNCTION GTID_NORMALIZE (g TEXT ( 10000 )) 
    15. RETURNS TEXT ( 10000 ) 
    16. DETERMINISTIC 
    17. RETURN GTID_SUBTRACT ( g, '' ) $$
    19. CREATE FUNCTION GTID_COUNT(gtid_set TEXT ( 10000 )) 
    20. RETURNS INT 
    21. DETERMINISTIC 
    22. BEGIN
    23.     DECLARE
    24.         result BIGINT DEFAULT 0;
    25.     DECLARE
    26.         colon_pos INT;
    27.     DECLARE
    28.         next_dash_pos INT;
    29.     DECLARE
    30.         next_colon_pos INT;
    31.     DECLARE
    32.         next_comma_pos INT;
    34.     SET gtid_set = GTID_NORMALIZE ( gtid_set );
    36.     SET colon_pos = LOCATE2 ( ':', gtid_set, 1 );
    37.     WHILE
    38.             colon_pos != LENGTH( gtid_set ) + 1 DO
    40.             SET next_dash_pos = LOCATE2 ( '-', gtid_set, colon_pos + 1 );
    42.         SET next_colon_pos = LOCATE2 ( ':', gtid_set, colon_pos + 1 );
    44.         SET next_comma_pos = LOCATE2 ( ',', gtid_set, colon_pos + 1 );
    45.         IF
    46.             next_dash_pos < next_colon_pos 
    47.             AND next_dash_pos < next_comma_pos THEN
    49.                 SET result = result + SUBSTR(
    50.                     gtid_set,
    51.                     next_dash_pos + 1,
    52.                     LEAST( next_colon_pos, next_comma_pos ) - ( next_dash_pos + 1 )) - SUBSTR(
    53.                     gtid_set,
    54.                     colon_pos + 1,
    55.                 next_dash_pos - ( colon_pos + 1 )) + 1;
    56.             ELSE 
    57.                 SET result = result + 1;
    59.         END IF;
    60.         SET colon_pos = next_colon_pos;
    61.     END WHILE;
    62.     RETURN result;
    63. END$$
    65. CREATE FUNCTION gr_applier_queue_length ()
    66. RETURNS INT 
    67. DETERMINISTIC
    68. BEGIN
    69.     RETURN (
    70.         SELECT
    71.             sys.gtid_count (
    72.             GTID_SUBTRACT ( ( SELECT Received_transaction_set FROM PERFORMANCE_SCHEMA.replication_connection_status WHERE Channel_name = 'group_replication_applier' ), ( SELECT @@GLOBAL.GTID_EXECUTED ) )));
    73. END$$
    76. CREATE FUNCTION gr_member_in_primary_partition ()
    77. RETURNS VARCHAR ( 3 ) 
    78. DETERMINISTIC
    79. BEGIN
    80.     RETURN (
    81.         SELECT
    82.         IF
    83.             (
    84.                 MEMBER_STATE = 'ONLINE' 
    85.                 AND (( SELECT COUNT(*) FROM PERFORMANCE_SCHEMA.replication_group_members WHERE MEMBER_STATE != 'ONLINE' ) >= (( SELECT COUNT(*) FROM PERFORMANCE_SCHEMA.replication_group_members )/ 2 ) = 0 ),
    86.                 'YES',
    87.                 'NO' 
    88.             ) 
    89.         FROM
    90.             PERFORMANCE_SCHEMA.replication_group_members
    91.         JOIN PERFORMANCE_SCHEMA.replication_group_member_stats USING ( member_id ));
    93. END$$
    96. CREATE VIEW gr_member_routing_candidate_status AS SELECT
    97. sys.gr_member_in_primary_partition () AS viable_candidate,
    98. IF
    99.     ( ( 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,
    100.     sys.gr_applier_queue_length () AS transactions_behind,
    101.     Count_Transactions_in_queue AS 'transactions_to_cert' 
    102. FROM
    103.     PERFORMANCE_SCHEMA.replication_group_member_stats;$$
    105. DELIMITER ;
    1. set mysql-monitor_username='proxysql_monitor';
    2. set mysql-monitor_password='123456';
    4. load mysql servers to runtime;
    5. save mysql servers to disk;
    7. select * from global_variables where variable_name like 'mysql-monitor%';
    9. SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us
    10. DESC LIMIT 10;
    12. #mysql_server_connect_log
    13. SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us
    14. DESC LIMIT 6;
    16. # 控制监控时间间隔
    17. UPDATE global_variables SET variable_value='2000' WHERE variable_name
    18. IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
    21. #配置一个mgr组
    22. insert into mysql_group_replication_hostgroups(
    23. writer_hostgroup,
    24. backup_writer_hostgroup,
    25. reader_hostgroup,
    26. offline_hostgroup,
    27. active,
    28. max_writers,
    29. writer_is_also_reader,
    30. max_transactions_behind,
    31. comment)
    32. values(1000,1001,1002,1003,1,1,0,10,'mgr01');
    33. load mysql servers to runtime;
    34. save mysql servers to disk;
    37. ## 查看结果
    38. select * from mysql_group_replication_hostgroups\G
    39. select * from mysql_group_replication_hostgroups;
    1. grant all on *.* to mgr01_root@'%' identified by '123456';
    1. delete from mysql_users;
    3. insert into
    4. mysql_users(username,password,default_hostgroup,transaction_persistent
    5. ) values('mgr01_root','123456',1000,1);
    7. load mysql users to runtime;
    8. save mysql users to disk;
    11. select username,password,active,default_hostgroup,transaction_persistent,fast_forward,frontend,comment from mysql_users;
    13. #查看已执行的语句
    14. select hostgroup,digest_text from stats_mysql_query_digest ORDER BY
    15. sum_time DESC limit 10;
    1. INSERT INTO mysql_query_rules
    2. (rule_id,active,username,match_digest,destination_hostgroup,apply)
    3. VALUES (100,1,'mgr01_root','^SELECT.*FOR UPDATE$',1000,1);
    4. INSERT INTO mysql_query_rules
    5. (rule_id,active,username,match_digest,destination_hostgroup,apply)
    6. VALUES (101,1,'mgr01_root','^SELECT',1002,1);
    8. load mysql query rules to runtime;
    9. save mysql query rules to disk;
    10. select * from runtime_mysql_query_rules\G;
    12. #语句路由
    13. select hostgroup,digest_text from stats_mysql_query_digest;