Expert ProxySQL Load Balancer Services

-- Configure backend servers in hostgroups
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES
  (0, 'primary.db.internal', 3306, 1000),    -- Writer hostgroup
  (1, 'replica1.db.internal', 3306, 500),    -- Reader hostgroup
  (1, 'replica2.db.internal', 3306, 500);    -- Reader hostgroup

-- Read/Write splitting query rules
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES
  (100, 1, '^SELECT .* FOR UPDATE', 0, 1),   -- FOR UPDATE goes to writer
  (200, 1, '^SELECT', 1, 1),                  -- All other SELECTs to readers
  (300, 1, '.*', 0, 1);                       -- Everything else to writer

-- Load configuration to runtime
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

-- Enable query cache globally
UPDATE global_variables SET variable_value='true'
  WHERE variable_name='mysql-query_cache_enabled';
UPDATE global_variables SET variable_value='268435456'
  WHERE variable_name='mysql-query_cache_size_MB';

-- Cache specific query patterns
INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, cache_ttl, apply) VALUES
  (500, 1, '^SELECT .* FROM products WHERE category_id', 60000, 1),
  (501, 1, '^SELECT .* FROM users WHERE id = \?', 30000, 1),
  (502, 1, '^SELECT COUNT\(\*\) FROM', 120000, 1);

-- Apply changes
LOAD MYSQL QUERY RULES TO RUNTIME;
LOAD MYSQL VARIABLES TO RUNTIME;

-- Set per-user connection limits
INSERT INTO mysql_users (username, password, default_hostgroup, max_connections) VALUES
  ('app_user', 'hashed_password', 0, 100),
  ('reporting', 'hashed_password', 2, 20),
  ('admin', 'hashed_password', 0, 5);

-- Set per-server connection limits
UPDATE mysql_servers SET max_connections=50 WHERE hostgroup_id=0;
UPDATE mysql_servers SET max_connections=100 WHERE hostgroup_id=1;

-- Throttle expensive queries
INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, max_lag_ms, delay, apply) VALUES
  (600, 1, 'SELECT .* JOIN .* JOIN .* JOIN', 100, 1000, 1),
  (601, 1, 'SELECT .* ORDER BY .* LIMIT [0-9]{5,}', 50, 2000, 1);

LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;