proxysql

不正经运维君

已于 2024-02-21 10:11:58 修改

阅读量561

点赞数 12

文章标签： mysql

于 2024-02-19 11:48:41 首次发布

本文链接：https://blog.csdn.net/qq_21442867/article/details/136167149

版权

文章详细描述了如何在Proxysql中安装、配置MySQL服务器集群，包括创建数据库、表，设置路由规则，以及管理应用用户和监控连接。重点展示了如何利用路由规则实现对特定查询的负载均衡和访问控制。

摘要由CSDN通过智能技术生成

这里是引用

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 SERVERS TO RUNTIME;
SAVE MYSQL SERVERS 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.46

mysql> 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.1

mysql> SELECT * FROM stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 2       | 4    |
| 3       | 0    |
+---------+------+

查看路由规则

mysql> SELECT * FROM mysql_query_rules\G
*************************** 1. row ***************************
              rule_id: 2
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT \* FROM db1\.tab_20240102
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 20
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
  gtid_from_hostgroup: NULL
                  log: NULL
                apply: 1
           attributes: 
              comment: NULL
*************************** 2. row ***************************
              rule_id: 3
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^(INSERT|UPDATE|DELETE) .* FROM db1\.(?!tab_20240102)
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 10
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
  gtid_from_hostgroup: NULL
                  log: NULL
                apply: 1
           attributes: 
              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;
```