玩一玩 PROXYSQL

部署配置文件如下是配置文件的格式:proxysql.cnfdatadir/var/lib/proxysql errorlog/log/proxysql.log admin_variables { admin_credentialsadmin:admin # 管理员账号 mysql_ifaces0.0.0.0:6032 # mysql 协议管理端口 } mysql_variables { threads4 max_connections2048 interfaces0.0.0.0:6033 # 代理端口 default_schemainformation_schema monitor_usernamemonitor monitor_passwordmonitor # mysql-eventslog_filename/log/events.log mysql-eventslog_format2 # JSON可选 mysql-eventslog_default_log1 # 全量记录可选 } # 先留空你后面可用管理端口 6032 动态写入后端/用户/规则 mysql_servers ( ) mysql_users ( )启动服务docker run -d --rm --name proxysql \ --network host \ --cpus1 -m1024m \ -p 6032:6032 -p 6033:6033 \ -v ./proxysql.cnf:/etc/proxysql.cnf:ro \ -v ./data:/var/lib/proxysql \ -v ./log/:/log/ \ proxysql/proxysql:latest配置规则使用写入 sql 的方式来配置config.sh#!/usr/bin/env bash set -euo pipefail # ProxySQL 管理端连接信息 PROXYSQL_HOST${PROXYSQL_HOST:-127.0.0.1} PROXYSQL_PORT${PROXYSQL_PORT:-6032} PROXYSQL_ADMIN_USER${PROXYSQL_ADMIN_USER:-admin} PROXYSQL_ADMIN_PASS${PROXYSQL_ADMIN_PASS:-admin} # 你要创建/更新的业务用户 APP_USER${APP_USER:-app} APP_PASS${APP_PASS:-app_pw} DEFAULT_HOSTGROUP${DEFAULT_HOSTGROUP:-10} mysql -h ${PROXYSQL_HOST} -P ${PROXYSQL_PORT} \ -u ${PROXYSQL_ADMIN_USER} -p${PROXYSQL_ADMIN_PASS} \ --protocoltcp --batch --raw --silent SQL -- 1) 指定日志文件名相对 datadir也可以给绝对路径 SET mysql-eventslog_filename/log/events.log; -- 2) JSON 格式更好进 ELK/Loki SET mysql-eventslog_format2; -- 3) 默认记录所有查询 SET mysql-eventslog_default_log1; SET mysql-query_cache_size_MB 512; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK; INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, max_connections) VALUES (10, myserver.com, 3306, 100, 20); INSERT INTO mysql_users(username, password, default_hostgroup, active) VALUES (my_user_name, password, 10, 1); LOAD MYSQL USERS TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; SAVE MYSQL USERS TO DISK; -- 规则尽量靠前rule_id 小 INSERT INTO mysql_query_rules (rule_id, active, match_pattern, re_modifiers, cache_ttl, apply) VALUES (100, 1, ^SELECT\\s, CASELESS, 1000000, 1); -- 所有的 select 查询对结果缓存 1000000 毫秒 LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; -- 可选打印确认 SELECT username, default_hostgroup, active FROM mysql_users; SQL执行配置:bash config.sh客户端客户端的 sql 服务器配置把 host 修改为 proxysql 的 ip。例子中是 127.0.0.1端口修改为 proxysql 的端口, 例子中是 6033.开启客户端运行后在 proxysql 对应的 ./log/events.log.00000001 中可以看到如下样式的日志{ client:127.0.0.1:49606, digest:0x5F0C91291BB515C4, duration_us:13992, endtime:2026-02-04 07:18:50.359984, endtime_timestamp_us:1770189530359984, errno:0, event:COM_QUERY, hostgroup_id:10, query:SELECT * FROM table1\nWHERE player_id 1234\n ORDER BY update_time DESC\n LIMIT 1, rows_affected:0, rows_sent:1, schemaname:my_db, server:server.com:3306, starttime:2026-02-04 07:18:50.345992, starttime_timestamp_us:1770189530345992, thread_id:4, username:my_user_name }duration_us 字段记录了 sql 的执行时间query 字段可以看见具体的 sql总结这个组件至少能够在测试阶段做很多事情把日志接入到日志分析系统可以对慢查询等进行统计