行业知识
proxysql 2.6部署代理MGR集群读写分离
2026-11-16 07:07:14
来源:互联网转载
官方文档
https://proxysql.com/documentation/ProxySQL-Configuration/
()下载安装proxysql
https://github.com/sysown/proxysql/releases/download/v2.6.0/proxysql-2.6.0-1-centos7.x86_64.rpmyum -y localinstall proxysql-2.6.0-1-centos7.x86_64.rpm# 软链接数据目录rm -rf /var/lib/proxysql/mkdir -p /data/proxysqlln -s /data/proxysql/ /var/lib/chown -R proxysql.proxysql /var/lib/proxysql/chown -R proxysql.proxysql /var/lib/proxysql# 启动proxysqlsystemctl start proxysqlsystemctl status proxysqlsystemctl enable proxysql
防火墙放行
firewall-cmd --permanent --zone=public --add-port={6032..6033}/tcp && firewall-cmd --reload 下载安装mysql客户端
tar -zxvf mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar.gzrpm -ivh mysql-community-client-8.0.19-1.el7.x86_64.rpm --nodeps --force
连接proxy管理接口配置
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '# 确认mysql_servers、mysql_replication_hostgroups、mysql_query_rules三张表的配置是否为空SELECT * FROM mysql_servers;SELECT * from mysql_replication_hostgroups;SELECT * from mysql_query_rules;
proxysql添加后端mysql
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.200.31',3308);INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.200.32',3308);INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.200.33',3308);load mysql servers to runtime;save mysql servers to disk;
MGR集群主节点添加监控账号
create user 'monitor'@'192.168.200.%' identified with mysql_native_password by 'UBXWlwDw4X93s%#GtXyx';GRANT SELECT on sys.* to 'monitor'@'192.168.200.%';GRANT SELECT on performance_schema.* to 'monitor'@'192.168.200.%';
proxysql添加设置监控账号
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';UPDATE global_variables SET variable_value='UBXWlwDw4X93s%#GtXyx' WHERE variable_name='mysql-monitor_password';load mysql variables to runtime;save mysql variables to disk;
MGR集群主节点添加监控视图
# addition_to_sys.sql MGR创建监控视图sql文件https://lefred.be/content/mysql-group-replication-native-support-in-proxysql/
cat > addition_to_sys.sql =((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),'YES', 'NO' ) FROM performance_schema.replication_group_members JOINperformance_schema.replication_group_member_stats USING(member_id));END$$CREATE VIEW gr_member_routing_candidate_status AS SELECTsys.gr_member_in_primary_partition() as viable_candidate,IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROMperformance_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 ;EOF cat > addition_to_sys2.sql =((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),'YES', 'NO' ) FROM performance_schema.replication_group_members JOINperformance_schema.replication_group_member_stats rgms USING(member_id) WHERE rgms.MEMBER_ID=@@SERVER_UUID);END$$CREATE VIEW gr_member_routing_candidate_status AS SELECTsys.gr_member_in_primary_partition() as viable_candidate,IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROMperformance_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 ;EOF 加载视图sql
source /root/addition_to_sys.sql;
查看视图监控状态
()select * from gr_member_routing_candidate_status;
如果报错
ERROR 1242 (21000): Subquery returns more than 1 row
source /root/addition_to_sys2.sql;
proxysql设置读写组
# 查看读写复制组表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(10,20,30,40,1,1,0,0);load mysql servers to runtime;save mysql servers to disk;
MGR集群状态查询
mysql> select * from sys.gr_member_routing_candidate_status;+------------------+-----------+---------------------+----------------------+| viable_candidate | read_only | transactions_behind | transactions_to_cert |+------------------+-----------+---------------------+----------------------+| YES | NO | 0 | 0 |+------------------+-----------+---------------------+----------------------+1 row in set (0.04 sec)
proxysql状态查询
Admin> select hostgroup_id, hostname, status from runtime_mysql_servers;+--------------+----------------+--------+| hostgroup_id | hostname | status |+--------------+----------------+--------+| 10 | 192.168.200.32 | ONLINE || 20 | 192.168.200.31 | ONLINE || 20 | 192.168.200.33 | ONLINE |+--------------+----------------+--------+
MGR主节点创建业务账号
create user 'gadmin'@'192.168.200.%' identified with mysql_native_password by 'UBXWlwDw4X93s%#GtXyx';GRANT all privileges on *.* to 'gadmin'@'192.168.200.%';
proxysql添加业务账号
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('gadmin','UBXWlwDw4X93s%#GtXyx',10);LOAD MYSQL USERS TO RUNTIME;SAVE MYSQL USERS TO DISK;SELECT * FROM mysql_users; proxysql添加读写分离规则
INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'gadmin','^SELECT.*FOR UPDATE$',10,1);INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'gadmin','^SELECT',30,1);load mysql query rules to runtime;save mysql query rules to disk;# 查看规则SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username='gadmin' ORDER BY rule_id;# 查看路由状态SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
测试读写分离
mysql -ugadmin -p'UBXWlwDw4X93s%#GtXyx' -h192.168.200.30 -P6033 -e 'create database proxysql_mgr_test;'mysql -ugadmin -p'UBXWlwDw4X93s%#GtXyx' -h192.168.200.30 -P6033 -e 'use proxysql_mgr_test;select * from tb01;'mysql -ugadmin -p'UBXWlwDw4X93s%#GtXyx' -h192.168.200.30 -P6033 -e 'show databases;'mysql -ugadmin -p'UBXWlwDw4X93s%#GtXyx' -h192.168.200.30 -P6033 -e 'drop database proxysql_mgr_test;'
上一篇:文山认证服务器地址在哪里?