2、安装
[root@B-JS25-BASE79-00 ~]# yum install proxysql-2.5.2-1-centos7.x86_64.rpm
3、启动：
[root@B-JS25-BASE79-00 ~]# systemctl start proxysql
4、关闭防火墙
vim /etc/selinux/configSELINUX=disabled
[root@B-JS25-BASE79-00 ~]# systemctl disable firewalld.service
或者放行特定端口
5、在后端mysql实例上创建monitor用户;
create user 'proxysql_monitor'@'172.16.%' identified by '123456';
create user 'proxysq_dml'@'172.16.%' identified by '123456';
GRANT USAGE,process,replication slave,replication client ON *.* TO 'proxysql_monitor'@'172.16.%';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'proxysq_dml'@'172.16.%';
6、修改配置文件
7、初始化 proxysql 这样以后配置就在db里面了，sqllit3数据库!
ProxySQL配置可以存储到SQLite数据库并通过SQL语句来管理配置,
Reinitializing ProxySQL from the config file (after first startup the DB file is used instead of the config file)
从配置文件重新初始化ProxySQL(第一次启动后，将使用DB文件而不是配置文件),所以需要把配置文件中的数据初始化到 内置数据库中!
[root@B-JS25-BASE79-00 ~]# systemctl start proxysql-initial
以后就可以直接在admin库修改proxysql配置了
二、proxysql配置mgr以及主从集群读写分离
开始配置proxysql, 通过sql的方式
2.1、如下方式登录6032管理端口，可以显示是在proxsql中，防止出错!默认登录到 main库，最核心的配置库，
[root@B-JS25-BASE79-00 ~]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt 'ProxySQL Admin> '
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2446
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
ProxySQL Admin>
2.2、 确定下现在没有配置任何后端mysql实例，以及相应的路由规则!
ProxySQL Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)
ProxySQL Admin> SELECT * from mysql_replication_hostgroups; 针对主从架构
Empty set (0.00 sec)
ProxySQL Admin> SELECT * from mysql_group_replication_hostgroups; 针对mgr架构
Empty set (0.00 sec)
ProxySQL Admin> SELECT * from mysql_query_rules;
Empty set (0.00 sec)
2.3、配置默认组信息，注意如果有多套mysql集群的话，需要配置多行数据，并且注意所有的hostgroup_id，包括writer_hostgroup和reader_hostgroup等等要保持唯1!
如下所示，我同时监控了一套mgr+两套主从
1)针对mgr如下所示设置：使用的hostgroup_id为 1、2、3、4;
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 (1,2,3,4,1,1,1,10);
2)针对主从架构如下所示设置：注意如果要管理两套主从架构的mysql集群，那么就需要插入两条数据!hostgroup_id分别为 5、6和7、8两组!
mysql_replication_hostgroups中的每一行代表一对writer_hostgroup和reader_hostgroup。
insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,check_type,comment)values(5,6,'read_only','MySQL Replication fa
ailover test');
insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,check_type,comment)values(7,8,'read_only','MySQL Replication fa
ailover test');
3) 刷新配置生效，并且保存到磁盘;
load mysql servers to runtime;
save mysql servers to disk;
2.4、添加后端实例：注意原则是同一套mysql集群hostgroup_id一样，并且设置为对应表的writer_hostgroup值，
mgr集群：这里hostgroup_id=1，对应mysql_group_replication_hostgroups的writer_hostgroup=1,
ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (1,'172.16.196.192',3308,'mgr1');
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (1,'172.16.196.192',3309,'mgr1');
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (1,'172.16.196.192',3310,'mgr1');
Query OK, 1 row affected (0.00 sec)
主从集群一：这里的hostgroup_id=7，是因为mysql_replication_hostgroup的writer_hostgroup=7的那组读写配置!
INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (7,'172.16.196.192',3306,'rpl1');
INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (7,'172.16.196.192',3307,'rpl1');
主从集群二：这里的hostgroup_id=5，是因为mysql_replication_hostgroup的writer_hostgroup=5的那组读写配置!
INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (5,'172.16.165.60',3306,'rpl2');
INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (5,'172.16.165.61',3306,'rpl2');
INSERT INTO mysql_servers(hostgroup_id,hostname,port,comment) VALUES (5,'172.16.165.62',3306,'rpl3');
2.5、配置读写分离参数, 读写分离策略可以按着用户和库级别做匹配!
如下我这个做三套mysql集群的读写分离，其中：二套主从架构，一套mgr架构!
ProxySQL Admin> select hostgroup_id,hostname,port,comment from mysql_servers ;
+--------------+----------------+------+---------+
| hostgroup_id | hostname | port | comment |
+--------------+----------------+------+---------+
| 1 | 172.16.196.192 | 3310 | mgr1 |
| 1 | 172.16.196.192 | 3308 | mgr1 |
| 1 | 172.16.196.192 | 3309 | mgr1 |
| 7 | 172.16.196.192 | 3306 | rpl1 |
| 7 | 172.16.196.192 | 3307 | rpl1 |
| 5 | 172.16.165.60 | 3306 | rpl2 |
| 5 | 172.16.165.61 | 3306 | rpl2 |
| 5 | 172.16.165.62 | 3306 | rpl2 |
+--------------+----------------+------+---------+
8 rows in set (0.00 sec)
如下设置读写分离配置：可以做到用户和库级别!生产也建议这样操作;
针对mgr的话，如果是单主模式，那么读取的组为reader_hostgroup，如果是多主模式，因为所有节点都是read_only=0，那么读取的组为backup_writer_hostgroup!
针对主从的结构的话，读取的组为reader_hostgroup!
如下只配置了查询的路由规则即可，因为默认其他的没有匹配规则的sql都会发送到writer_hostgroup组!
1) 查询的sql路由到只读节点组,根据前面配置可知，只读组的id包括：3、6、8
INSERT INTO mysql_query_rules(rule_id,active,username,schemaname,active,match_pattern,destination_hostgroup,apply) VALUES
(1,1,'app_liuwenhe','liuwenhe',1,'^select',3,1),
(2,1,'app_liuwenhe_2','liuwenhe_2',1,'^select',6,1),
(3,1,'app_liu','liu',1,'^select',8,1);
2)特殊查询路由到writer_hostgroup组，写的组包括：1、5、7
insert into mysql_query_rules(rule_id,active,username,schemaname,match_pattern,destination_hostgroup,apply) values (4,1,'app_liuwenhe','liuwenhe','^SELECT.*FOR UPDATE$',1,1),
(5,1,'app_liuwenhe_2','liuwenhe_2','^SELECT.*FOR UPDATE$',5,1),
(6,1,'app_liu','liu','^SELECT.*FOR UPDATE$',7,1);
3)刷新配置生效，并且保存到磁盘;
load mysql query rules to runtime;
save mysql query rules to disk;
2.6、保存到磁盘并load到runtime ，一共操作了5张表
mysql_users
mysql_servers
mysql_query_rules
global_variables
mysql_group_replication_hostgroups
前4张都需要执行save和load操作，save是使内存数据存储到磁盘，load是内存数据加载到runtime生效
save mysql users to disk;
save mysql servers to disk;
save mysql query rules to disk;
save mysql variables to disk;
save admin variables to disk;
load mysql users to runtime;
load mysql servers to runtime;
load mysql query rules to runtime;
load mysql variables to runtime;
load admin variables to runtime;
2.7、如果前面配置文件中没有配置监控的账号，那么就需要通过sql的方式配置下!
ProxySQL Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin> UPDATE global_variables SET variable_value='123456' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.01 sec)
2.8、配置proxysql探测后端数据库的间隔，如下配置成2秒，前面配置文件已经配置了相关的参数了，这个步骤可以不做!
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
2.9、如下所示，自动探测到了现在的可写的是3308,可读的是3309和3310;针对mgr的正常显示，
ProxySQL Admin> select * from runtime_mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 172.16.196.192 | 3308 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 3 | 172.16.196.192 | 3309 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 3 | 172.16.196.192 | 3310 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.01 sec)
ProxySQL Admin> SELECT * FROM mysql_servers; #proxysql的mysql_servers中查到的值一般不会变化!
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 172.16.196.192 | 3308 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 172.16.196.192 | 3309 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 172.16.196.192 | 3310 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
2.10、proxysql 管理后台基本命令：
ProxySQL Admin> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
2.10.1、查看关于探测的表信息：在monitor库下!
ProxySQL Admin> SHOW TABLES FROM monitor;
+--------------------------------------+
| tables |
+--------------------------------------+
| mysql_server_aws_aurora_check_status |
| mysql_server_aws_aurora_failovers |
| mysql_server_aws_aurora_log |
| mysql_server_connect_log |
| mysql_server_galera_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+--------------------------------------+
9 rows in set (0.00 sec)
2.10.2、可以查看proxysql最近几次对后端mysql的探测信息
1)网络层面的ping
ProxySQL Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;
+----------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 172.16.196.192 | 3309 | 1686499200519110 | 118 | NULL |
| 172.16.196.192 | 3308 | 1686499200518669 | 108 | NULL |
| 172.16.196.192 | 3310 | 1686499200517696 | 98 | NULL |
+----------------+------+------------------+----------------------+------------+
3 rows in set (0.00 sec)
2)数据库层面的连接
ProxySQL Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
+----------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 172.16.196.192 | 3309 | 1686499202998984 | 1261 | NULL |
| 172.16.196.192 | 3310 | 1686499202976523 | 1236 | NULL |
| 172.16.196.192 | 3308 | 1686499202954041 | 1352 | NULL |
+----------------+------+------------------+-------------------------+---------------+
2.10.3、如果开启了查询缓存的功能，就可以查看通过proxysql执行的query的sql文本!
SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
2.10.3、如下查看stats库下面的相关表：
ProxySQL Admin> SHOW TABLES FROM stats;
+---------------------------------------+
| tables |
+---------------------------------------+
| global_variables |
| stats_memory_metrics |
| stats_mysql_client_host_cache |
| stats_mysql_client_host_cache_reset |
| stats_mysql_commands_counters |
| stats_mysql_connection_pool |
| stats_mysql_connection_pool_reset |
| stats_mysql_errors |
| stats_mysql_errors_reset |
| stats_mysql_free_connections |
| stats_mysql_global |
| stats_mysql_gtid_executed |
| stats_mysql_prepared_statements_info |
| stats_mysql_processlist |
| stats_mysql_query_digest |
| stats_mysql_query_digest_reset |
| stats_mysql_query_rules |
| stats_mysql_users |
| stats_proxysql_message_metrics |
| stats_proxysql_message_metrics_reset |
| stats_proxysql_servers_checksums |
| stats_proxysql_servers_clients_status |
| stats_proxysql_servers_metrics |
| stats_proxysql_servers_status |
+---------------------------------------+
24 rows in set (0.00 sec)
三、proxysql中权限控制，
目前proxysql中的账号和后端mysql账号是同一个，以后版本会分开!
如何把权限控制到库级别，可以如下三个步骤：
1、在mysql实例上创建一个针对某个库的业务账号，例如如下所示对liuwenhe库创建一个app_liuwenhe账号
create user app_liuwenhe@'172.16.196.192' identified by '123456';
grant select ,update ,delete ,insert on liuwenhe.* to app_liuwenhe@'172.16.196.192';
2、登录proxysql的管理端口，然后给mysql_users表添加账号：
mysql -u admin -padmin -h 127.0.0.1 -P6032
proxysql>insert into mysql_users(username,password,default_hostgroup) values('app_liuwenhe','123456',1);
注意 default_hostgroup的值为mysql_servers中的hostgroup_id的值对应!如下图所示：
proxysql> load mysql users to runtime; #这样操作之后，runtime_mysql_users表默认创建两条数据，一个是只允许登录proxysql，一个只允许登录后端mysql实例，具体如下所示
proxysql> save mysql users to disk;
ProxySQL Admin> select username,backend,frontend from mysql_users where username='app_liuwenhe';
+--------------+---------+----------+
| username | backend | frontend |
+--------------+---------+----------+
| app_liuwenhe | 1 | 1 |
+--------------+---------+----------+
1 row in set (0.00 sec)
ProxySQL Admin> select username,backend,frontend from runtime_mysql_users where username='app_liuwenhe';
+--------------+---------+----------+
| username | backend | frontend
|+--------------+---------+----------+
| app_liuwenhe | 1 | 0 |
| app_liuwenhe | 0 | 1 |
+--------------+---------+----------+
2 rows in set (0.00 sec)
3、然后就可以使用app_liuwenhe账号登录proxysql的业务端口了，只能看到liuwenhe库!
mysql -uapp_liuwenhe -p123456 -h172.16.196.192 -P6033
这样就能够把权限控制到库级别了!