ProxySQL — Complete Cheat Sheet & Reference

# Connect to Admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt="ProxySQL> "

# Connect as app user through ProxySQL
mysql -u appuser -ppassword -h 127.0.0.1 -P 6033

# Connect to Admin with SSL
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --ssl-mode=REQUIRED

-- Add server
INSERT INTO mysql_servers (hostgroup_id, hostname, port, status, weight, max_connections, comment)
VALUES (10, '192.168.1.100', 3306, 'ONLINE', 1, 1000, 'Master');

-- List servers
SELECT hostgroup_id, hostname, port, status, weight FROM mysql_servers;

-- Change server status
UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostname='192.168.1.100';
UPDATE mysql_servers SET status='ONLINE' WHERE hostname='192.168.1.100';

-- Remove server
DELETE FROM mysql_servers WHERE hostname='192.168.1.100';

-- Apply
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- View active servers
SELECT hostgroup_id, hostname, port, status, ConnUsed FROM runtime_mysql_servers;

-- Add user
INSERT INTO mysql_users (username, password, default_hostgroup, active, transaction_persistent)
VALUES ('appuser', 'password', 10, 1, 1);

-- List users
SELECT username, default_hostgroup, active, transaction_persistent FROM mysql_users;

-- Disable user
UPDATE mysql_users SET active=0 WHERE username='appuser';

-- Change default hostgroup
UPDATE mysql_users SET default_hostgroup=20 WHERE username='reports_user';

-- Apply
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

-- Add rule: route SELECTs to replicas
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES (1, 1, '^SELECT .* FOR UPDATE', 10, 1, 'Locking reads to master');

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, comment)
VALUES (2, 1, '^SELECT', 20, 1, 'SELECTs to replicas');

-- Add blocking rule
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, error_msg, apply)
VALUES (100, 1, '^DROP TABLE', 'Not allowed', 1);

-- Add caching rule
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, cache_ttl, destination_hostgroup, apply)
VALUES (200, 1, 'SELECT .* FROM config', 60000, 20, 1);

-- View rules with hit counts
SELECT rule_id, hits, match_pattern, destination_hostgroup, cache_ttl
FROM stats.stats_mysql_query_rules ORDER BY rule_id;

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

-- Common variables to check/set
SELECT variable_name, variable_value FROM global_variables
WHERE variable_name IN (
  'mysql-max_connections',
  'mysql-threads',
  'mysql-monitor_username',
  'mysql-monitor_password',
  'mysql-monitor_ping_interval',
  'mysql-monitor_read_only_interval',
  'admin-admin_credentials',
  'mysql-query_cache_size_MB'
);

-- Change a variable
UPDATE global_variables SET variable_value='50000'
WHERE variable_name='mysql-max_connections';

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- Connection pool
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree, Queries, Latency_us
FROM stats.stats_mysql_connection_pool;

-- Top queries
SELECT hostgroup, digest_text, count_star, avg_time
FROM stats.stats_mysql_query_digest ORDER BY count_star DESC LIMIT 10;

-- Active connections
SELECT user, hostgroup, command, time_ms, info
FROM stats.stats_mysql_processlist ORDER BY time_ms DESC;

-- Global counters
SELECT variable_name, variable_value FROM stats.stats_mysql_global
WHERE variable_name IN ('Questions','Slow_queries','Client_Connections_connected');

-- Monitor logs
SELECT hostname, port, read_only FROM monitor.mysql_server_read_only_log
ORDER BY time_start_us DESC LIMIT 5;

SELECT hostname, port, ping_success FROM monitor.mysql_server_ping_log
ORDER BY time_start_us DESC LIMIT 5;

-- MySQL Servers
LOAD MYSQL SERVERS TO RUNTIME;    SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL SERVERS FROM RUNTIME;  LOAD MYSQL SERVERS FROM DISK;

-- MySQL Users
LOAD MYSQL USERS TO RUNTIME;      SAVE MYSQL USERS TO DISK;

-- Query Rules
LOAD MYSQL QUERY RULES TO RUNTIME;  SAVE MYSQL QUERY RULES TO DISK;

-- MySQL Variables
LOAD MYSQL VARIABLES TO RUNTIME;    SAVE MYSQL VARIABLES TO DISK;

-- Admin Variables
LOAD ADMIN VARIABLES TO RUNTIME;    SAVE ADMIN VARIABLES TO DISK;

-- ProxySQL Servers (cluster)
LOAD PROXYSQL SERVERS TO RUNTIME;   SAVE PROXYSQL SERVERS TO DISK;

-- Scheduler
LOAD SCHEDULER TO RUNTIME;          SAVE SCHEDULER TO DISK;

-- Standard Master-Replica setup
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment)
VALUES (10, 20, 'read_only', 'R/W Split');

-- Group Replication / InnoDB Cluster
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 (10, 30, 20, 40, 1, 1, 0, 0);

-- Galera / PXC
INSERT INTO mysql_galera_hostgroups
  (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup,
   active, max_writers, writer_is_also_reader, max_transactions_behind)
VALUES (10, 30, 20, 40, 1, 1, 1, 0);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

PROXYSQL FLUSH LOGS;      -- Flush all log files
PROXYSQL RESTART;          -- Restart ProxySQL threads (no downtime)
PROXYSQL SHUTDOWN;         -- Full shutdown
PROXYSQL KILL SESSION 42;  -- Kill session by ID
SHOW TABLES;               -- Show Admin tables
SHOW DATABASES;            -- Show Admin databases
SELECT @@version;          -- ProxySQL version