本文主要是介绍proxysql，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
这里是引用
mysql 安装
mysql-3307
mysql -P 3307 -u root -proot -h 10.211.55.8
create database db1;
CREATE TABLE db1.tab_20240102 (id INT PRIMARY KEY,name VARCHAR(50),email VARCHAR(50),age INT
);
CREATE TABLE db1.tab_20240103 (id INT PRIMARY KEY,name VARCHAR(50),email VARCHAR(50),age INT
);
INSERT INTO db1.tab_20240103 (id, name, email, age) VALUES (1, 'Alice', 'alice@example.com', 30);
mysql-3308
create database db1;
CREATE TABLE db1.tab_20240102 (id INT PRIMARY KEY,name VARCHAR(50),email VARCHAR(50),age INT
);
INSERT INTO db1.tab_20240102 (id, name, email, age) VALUES (1, 'Alice', 'alice@example.com', 30);
安装
yum -y install proxysql-2.5.5-1-centos7.x86_64.rpm启动
systemctl status proxysql登陆
mysql -uadmin -padmin -P6032 -h127.0.0.1
创建两个主机组
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'127.0.0.1',3307);
insert into mysql_servers(hostgroup_id,hostname,port) values(20,'127.0.0.1',3308);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
创建路由规则
INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT \* FROM db1\.tab_20240102', 20, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^(INSERT|UPDATE|DELETE) .* FROM db1\.(?!tab_20240102)', 10, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
设置 monitor 用户
set mysql-monitor_username='root';
set mysql-monitor_password='root';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
创建应用用户
INSERT INTO mysql_users(username, password, default_hostgroup, active) VALUES ('my_user', 'password123', 10, 1);INSERT INTO mysql_users(username, password, default_hostgroup, active) VALUES ('root', 'root', 10, 1);LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
测试连接
mysql -P 6033 -u my_user -ppassword123 -h 10.202.11.46
mysql -P 6033 -u root -proot -h 10.202.11.46
查看日志
tail -f /var/lib/proxysql/proxysql.log
测试路由规则
mysql -P 6033 -u root -proot -h 10.202.11.46mysql> select * from db1.tab_20240102;
+----+-------+-------------------+------+
| id | name | email | age |
+----+-------+-------------------+------+
| 1 | Alice | alice@example.com | 30 |
+----+-------+-------------------+------+
1 row in set (0.03 sec)
被路由到了的 mysql-3308 只有3308 有数据
登陆后台查看匹配信息
mysql -uadmin -padmin -P6032 -h127.0.0.1mysql> SELECT * FROM stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 2 | 4 |
| 3 | 0 |
+---------+------+
查看路由规则
mysql> SELECT * FROM mysql_query_rules\G
*************************** 1. row ***************************rule_id: 2active: 1username: NULLschemaname: NULLflagIN: 0client_addr: NULLproxy_addr: NULLproxy_port: NULLdigest: NULLmatch_digest: ^SELECT \* FROM db1\.tab_20240102match_pattern: NULLnegate_match_pattern: 0re_modifiers: CASELESSflagOUT: NULLreplace_pattern: NULL
destination_hostgroup: 20cache_ttl: NULLcache_empty_result: NULLcache_timeout: NULLreconnect: NULLtimeout: NULLretries: NULLdelay: NULLnext_query_flagIN: NULLmirror_flagOUT: NULLmirror_hostgroup: NULLerror_msg: NULLOK_msg: NULLsticky_conn: NULLmultiplex: NULLgtid_from_hostgroup: NULLlog: NULLapply: 1attributes: comment: NULL
*************************** 2. row ***************************rule_id: 3active: 1username: NULLschemaname: NULLflagIN: 0client_addr: NULLproxy_addr: NULLproxy_port: NULLdigest: NULLmatch_digest: ^(INSERT|UPDATE|DELETE) .* FROM db1\.(?!tab_20240102)match_pattern: NULLnegate_match_pattern: 0re_modifiers: CASELESSflagOUT: NULLreplace_pattern: NULL
destination_hostgroup: 10cache_ttl: NULLcache_empty_result: NULLcache_timeout: NULLreconnect: NULLtimeout: NULLretries: NULLdelay: NULLnext_query_flagIN: NULLmirror_flagOUT: NULLmirror_hostgroup: NULLerror_msg: NULLOK_msg: NULLsticky_conn: NULLmultiplex: NULLgtid_from_hostgroup: NULLlog: NULLapply: 1attributes: comment: NULL
2 rows in set (0.00 sec)mysql>
```修改应用连接端口为3306
```
UPDATE global_variables SET variable_value='3306' WHERE variable_name='mysql-interfaces';LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
```这篇关于proxysql的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
