proxysql安装与配置

insert into mysql_servers (hostgroup_id, hostname, port) values(10,'172.34.88.155',3306);
insert into mysql_servers (hostgroup_id, hostname, port) values(10,'172.34.88.165',3306);
insert into mysql_servers (hostgroup_id, hostname, port) values(10,'172.34.88.222',3306);

load mysql servers to runtime;
save mysql servers to disk;

select * from mysql_servers;

=====================mysql
set global validate_password.policy=0;
set global validate_password.length=4;

create user 'monitor'@'%' identified by 'Monitor@2020';
create user 'proxysql'@'%' identified by 'Proxysql@2020';
create user 'yjtxz'@'%' identified by 'Yjtxz@2022';
create user 'sjz'@'%' IDENTIFIED BY 'RT1jTqi7_Xeh';

grant all privileges on . to 'monitor'@'%';
grant all privileges on . to 'proxysql'@'%';
GRANT ALL PRIVILEGES ON 'yjtxz'.* TO 'yjtxz'@'%';

flush privileges;

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

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 NOT IN ('ONLINE', 'RECOVERING')) >=
((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 member_id=my_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,
Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert'
from performance_schema.replication_group_member_stats where member_id=my_id();

=====================proxysql
set mysql-monitor_username='monitor';
set mysql-monitor_password='Monitor@2020';
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('proxysql', 'Proxysql@2020', 10);
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('zx_db', '&j=3NA^/c', 10);
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sjz', 'RT1jTqi7_Xeh', 10);

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

load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;

select hostgroup_id,hostname,port,status from runtime_mysql_servers;
select hostname,port,viable_candidate,read_only,transactions_behind,error from mysql_server_group_replication_log order by time_start_us desc;

读写分离规则

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

load mysql query rules to runtime;
save mysql query rules to disk;

查看路由日志

select hostgroup,digest_text from stats_mysql_query_digest order by digest_text limit 5;