proxysql基础实践

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.2.x/centos/$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

yum install -y proxysql OR yum install proxysql-version

# 注意这里如有报错，yum源信息请到官网去复制
# 然后 yum clean all  ,  yum makecache
# 最终再次执行该语句

# 1.安装proxysql安装依赖包
wget https://github.com/sysown/proxysql/releases/download/v2.2.0/proxysql-2.2.0-1-centos7.aarch64.rpm
yum install perl-DBD-MySQL -y
rpm -ivh proxysql-2.2.0-1-centos7.aarch64.rpm

 # 2.启动 proxysql 
rpm -ql proxysql 
systemctl start proxysql.service

service proxysql start
service proxysql stop
service proxysql restart
# 也可以使用 systemctl

# 登录, --prompt 只是加个显示前缀而已
mysql -uadmin -padmin -P6032 --prompt='\u@mysqldb \R:\m:\s [\d]>'

# 登陆后启停管理
proxysql stop
proxysql restart

select @@admin-admin_credentials;    # 当前用户名和密码
set admin-admin_credentials='admin:admin;myuser:myuser'; # 添加myuser用户

load admin variables to runtime;  # 使修改立即生效
save admin variables to disk;     # 使修改永久保存到磁盘

# If you are using the init script run:
/etc/init.d/proxysql initial
# or
service proxysql initial

# If you are using the systemd unit file run:
systemctl start proxysql-initial
# or
service proxysql-initial start

以下命令语法可用于在各个层之间移动 ProxySQL 配置，<item> 是您希望配置的配置项的占位符：

[1] `LOAD <item> FROM MEMORY`/`LOAD <item> TO RUNTIME`
将配置项从内存数据库加载到运行时数据结构
[2] `SAVE <item> TO MEMORY` / `SAVE <item> FROM RUNTIME`
将配置项从运行时保存到内存数据库
[3] `LOAD <item> TO MEMORY`/`LOAD <item> FROM DISK`
将持久化的配置项从磁盘数据库加载到内存数据库
[4] `从内存中保存 <item>`/`SAVE <item> 到磁盘`
将配置项从内存数据库保存到磁盘数据库
[5] `从配置中加载 <项目>`
将配置文件中的配置项加载到内存数据库中

load xxx to runtime;
save xxx to disk;

disk -> 是sqlite3 数据库 ，默认位置是$DATADIR/proxysql.db( /var/lib/proxysql/proxysql.db)
config file 是一个传统配置文件：一般不更改

-- proxysql 的监控账户
create user 'monitor'@'192.168.148.%' identified by '123456';
grant all privileges on *.* to 'monitor'@'192.168.148.%' with grant option;
 
-- proxysql 的对外访问账户
create user 'proxysql'@'192.168.148.%' identified by '123456';
grant all privileges on *.* to 'proxysql'@'192.168.148.%' with grant option;
 
flush privileges;

--------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql_servers | CREATE TABLE mysql_servers (
    hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
    hostname VARCHAR NOT NULL,
    port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
    gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,
    status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
    weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
    compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
    max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
    use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
    max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostgroup_id, hostname, port) ) |

use main;
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.148.39',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.148.27',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'192.168.148.30',3306);
select * from mysql_servers;
load mysql servers to runtime;
save mysql servers to disk;

-- 规范 应该把 comment 列也写上

-- 方法1
set mysql-monitor_username='monitor';
set mysql-monitor_password='123456';
set mysql-monitor_enabled='true';

-- 方法2
use main;
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='123456' WHERE variable_name='mysql-monitor_password';

-- 修改后，保存到runtime和disk 
load mysql variables to runtime; 
save mysql variables to disk;

-- 查看监控日志
select * from monitor.mysql_server_connect_log;

SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';

UPDATE global_variables SET variable_value='2000' 
WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');

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

| mysql_replication_hostgroups | CREATE TABLE mysql_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
    check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
    comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup)) |

insert into mysql_replication_hostgroups ( writer_hostgroup, reader_hostgroup, comment) values (10,20,'proxy');
 
load mysql servers to runtime;
save mysql servers to disk;

select * from main.runtime_mysql_replication_hostgroups; -- 运行时
select * from main.mysql_replication_hostgroups; -- 内存
select * from mysql_replication_hostgroups; -- 存储

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','',10);
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','123456',10);
load mysql users to runtime;
save mysql users to disk;

admin@127.0.0.1 [admin]>select * from mysql_users\G
*************************** 1. row ***************************
              username: proxysql
              password: 123456
                active: 1  #是否启用该账户，为0表示禁用
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1  #事务透明化
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000  #用户链接10000，可以修改

mysql -uproxysql -p123456 -P6033 -h 192.168.148.39

ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (1,1,'stnduser','^SELECT c FROM sbtest1 WHERE id=\?$',20,1);
Query OK, 1 row affected (0.00 sec)

ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (2,1,'stnduser','DISTINCT c FROM sbtest1',20,1);
Query OK, 1 row affected (0.00 sec)

-- 写入到主库insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,'^select.*for update$',10,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select.*LOCK IN SHARE MODE$',10,1);


-- 后面这2个 ID 可以相对设置大一点，方便在前面后面插入相关规则（不过也没关系，重新删除掉再在 memory 层设置好，再 load/save 到 runtime 和 disk 也不影响线上）
-- 读从库
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (100,1,'^select',20,1);

-- 非规则内的其他操作默认全放到主库，比如其他建库、建表、授权、DDL等等load mysql query rules to runtime;
save mysql query rules to disk;

select hostgroup,schemaname,username,digest_text,count_star from  stats_mysql_query_digest;

UPDATE mysql_query_rules set cache_ttl=5000 WHERE active=1 AND destination_hostgroup=20;

ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (30,1,'stnduser','DISTINCT(.*)ORDER BY c','DISTINCT\1',1);
Query OK, 1 row affected (0.00 sec)

ProxySQL Admin> SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules ORDER BY rule_id;
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| rule_id | match_digest                        | match_pattern          | replace_pattern | cache_ttl | apply |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 10      | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL                   | NULL            | 5000      | 1     |
| 20      | DISTINCT c FROM sbtest1             | NULL                   | NULL            | 5000      | 1     |
| 30      | NULL                                | DISTINCT(.*)ORDER BY c | DISTINCT\1      | NULL      | 1     |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+

Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| hits  | rule_id | match_digest                        | match_pattern          | replace_pattern | cache_ttl | apply |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 48560 | 10      | ^SELECT c FROM sbtest1 WHERE id=\?  | NULL                   | NULL            | 5000      | 1     |
| 4856  | 20      | DISTINCT c FROM sbtest1             | NULL                   | NULL            | 5000      | 0     |
| 4856  | 30      | NULL                                | DISTINCT(.*)ORDER BY c | DISTINCT\1      | NULL      | 1     |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.01 sec)

mysql_servers -- 服务器注册
global_variables -- 全局变量，mysql-monitor_username，mysql-monitor_password
mysql_users -- 提供登录 proxysql 的账户密码（同时也要有权限访问对于的 mysql 实例）
mysql_replication_hostgroups -- 复制服务器分组，会自动根据 服务器对于mysql实例的 readonly标识，把 =0的归到写组，=1 的归到读组
mysql_quer_rules -- 路由规则

 mysql_server_connect_log -- 连接日志，频率根据 global_variables 里面配置
 mysql_server_galera_log -- 不知道啥日志
 mysql_server_group_replication_log -- 复制组日志
 mysql_server_ping_log -- ping 日志
 mysql_server_read_only_log -- 只读日志
 mysql_server_replication_lag_log -- 复制落后日志

admin@mysqldb 17:52:59 [(none)]>show tables from stats;    
+--------------------------------------+
| tables                               |
+--------------------------------------+
| global_variables                     |
| stats_memory_metrics                 |
| 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_servers_checksums     |
| stats_proxysql_servers_metrics       |
| stats_proxysql_servers_status        |
+--------------------------------------+

stats_mysql_connection_pool -- 连接池，状态、次数、网络流量、请求次数
stats_mysql_commands_counters -- 语句命令的次数、执行时间等分布
stats_mysql_query_digest -- 路由查询、响应时间、 计数

SET mysql-interfaces='0.0.0.0:6401;0.0.0.0:6402';
## save it on disk and restart proxysql
SAVE MYSQL VARIABLES TO DISK;
PROXYSQL RESTART;

-- 根据传入端口添加路由：
INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)
VALUES (1,1,6401,1,1), (2,1,6402,2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

-- 根据总执行时间查找前 5 个查询
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 5;

-- 根据计数查找前 5 个查询
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 5;

-- 根据最大执行时间查找前 5 个查询
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time 
FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 5;

-- 按总执行时间排序的前 5 个查询，并且最短执行时间至少为 1 毫秒
SELECT digest,SUBSTR(digest_text,0,20),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time 
FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND min_time > 1000 ORDER BY sum_time DESC LIMIT 5;

-- 按总执行时间排序的前 5 个查询，平均执行时间至少为 1 秒。还显示总执行时间的百分比
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) 
FROM stats_mysql_query_digest),3) pct FROM stats_mysql_query_digest 
WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 1000000 ORDER BY sum_time DESC LIMIT 5;


-- 总执行时间排序的前 5 个查询，平均执行时间至少为 15 毫秒，并显示占总执行时间的百分比
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time)
FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct 
FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 15000 
ORDER BY sum_time DESC LIMIT 5;

-- disgest
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES(1,1,'0x38BE36BDFFDBE638',20,1);

-- match_digest
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES(1,1,'^SELECT COUNT\(\*\)',20,1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- （1）用户分片
INSERT INTO mysql_users (username, password, active, default_hostgroup, comment)
VALUES
('accounts', 'shard0_pass', 1, 0, 'Routed to the accounts shard'),
('transactions', 'shard1_pass', 1, 1, 'Routed to the transactions shard'),
('logging', 'shard2_pass', 1, 2, 'Routed to the logging shard');

LOAD MYSQL USERS RULES TO RUNTIME;
SAVE MYSQL USERS RULES TO DISK;

-- （2）模式分片
INSERT INTO mysql_query_rules (rule_id, active, schemaname,
destination_hostgroup, apply)
VALUES
(1, 1, 'shard_0', 0, 1),
(2, 1, 'shard_1', 1, 1),
(3, 1, 'shard_2', 2, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- 一般变量：

mysql-monitor_username
指定 Monitor 模块将用于连接到后端的用户名。用户只需要USAGE连接、ping 和检查的权限read_only。REPLICATION CLIENT如果需要监控复制滞后，用户还需要特权。

mysql-monitor_password
用户mysql-monitor_username 的密码

mysql-monitor_enabled
它启用或禁用 MySQL Monitor。由于 MySQL Monitor 可以干扰直接应用在 Admin 界面上的更改，因此此变量允许临时禁用它。

-- 连接变量：

mysql-monitor_connect_interval
执行连接检查的频率，以毫秒为单位。

mysql-monitor_connect_timeout
连接超时（以毫秒为单位）。当前实现将此值四舍五入为小于或等于原始间隔的整数秒数，最小为 1 秒。这种惰性舍入是因为 SSL 连接阻塞调用而完成的。

-- ping变量：

mysql-monitor_ping_interval
执行 ping 检查的频率，以毫秒为单位。

mysql-monitor_ping_timeout
Ping 超时（以毫秒为单位）。

mysql-monitor_ping_max_failures
如果主机连续错过mysql-monitor_ping_max_failures ping，MySQL_Monitor 会通知 MySQL_Hostgroup_Manager 该节点无法访问，并且应该立即终止所有连接。
需要注意的是，如果与后端的连接不可用，MySQL_Monitor 将首先尝试连接以便 ping，因此检测节点关闭的时间可能是以下两个之一：

mysql-monitor_ping_max_failures * mysql-monitor_connect_timeout
mysql-monitor_ping_max_failures * mysql-monitor_ping_timeout

-- 只读变量：

mysql-monitor_read_only_interval
执行只读检查的频率，以毫秒为单位。

mysql-monitor_read_only_timeout
只读检查超时（以毫秒为单位）。

mysql-monitor_writer_is_also_reader
当一个节点将其read_only值从 1 更改为 0 时，此变量确定该节点是否应存在于两个主机组中：

false : 节点将被移入writer_hostgroup和移除reader_hostgroup
true : 节点将被复制writer_hostgroup并保留在reader_hostgroup

-- 复制滞后变量：

mysql-monitor_replication_lag_interval
执行复制滞后检查的频率，以毫秒为单位。

mysql-monitor_replication_lag_timeout
复制延迟检查超时（以毫秒为单位）。

-- 其他变量：

mysql-monitor_history
为了防止日志表无限制地增长，Monitor Module 将自动清除超过mysql-monitor_history毫秒的记录。
由于 ping 检查依赖于历史表来确定节点是否缺少心跳，因此如果mysql-monitor_history的值小于该值，则会自动将其调整为以下值：

( mysql-monitor_ping_max_failures + 1) * mysql-monitor_ping_timeout