Skip to main content

How to Configure ProxySQL for the First Time

This guide assumes ProxySQL is installed and running. Connect to the Admin interface to begin:

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt 'ProxySQL Admin> '

Adding Backend Servers​

Add MySQL servers to the mysql_servers table, specifying a hostgroup, hostname, and port:

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.0.0.1',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.0.0.2',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.0.0.3',3306);
note

Set read_only = 0 on primary servers and read_only = 1 on replicas. ProxySQL uses this value for routing decisions.

Configuring Monitoring​

Create a dedicated monitor user in MySQL:

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

Configure the monitoring credentials and intervals in ProxySQL:

UPDATE global_variables SET variable_value='monitor'
  WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor'
  WHERE variable_name='mysql-monitor_password';
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;

Loading Servers to Runtime​

LOAD MYSQL SERVERS TO RUNTIME;

Monitor backend health through the monitor schema:

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;

Replication Hostgroups​

Configure replication topology monitoring by mapping writer and reader hostgroups:

INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment)
  VALUES (1,2,'cluster1');
LOAD MYSQL SERVERS TO RUNTIME;

ProxySQL automatically moves servers between hostgroups based on read_only:

  • read_only=0 → hostgroup 1 (writers)
  • read_only=1 → hostgroup 2 (readers)

Verify with:

SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;

Adding MySQL Users​

Create application users in MySQL:

CREATE USER 'stnduser'@'%' IDENTIFIED BY 'stnduser';
GRANT ALL PRIVILEGES ON *.* TO 'stnduser'@'%';

Add them to ProxySQL's mysql_users table:

INSERT INTO mysql_users(username,password,default_hostgroup)
  VALUES ('stnduser','stnduser',1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

The default_hostgroup controls where traffic from that user is routed by default.

Query Statistics​

ProxySQL collects statistics in the stats schema. Key tables:

TableDescription
stats_mysql_connection_poolConnection and traffic statistics per hostgroup
stats_mysql_commands_countersQuery type distribution and execution times
stats_mysql_query_digestPer-query statistics including response times

Query traffic by hostgroup:

SELECT hostgroup hg, SUM(sum_time), SUM(count_star)
  FROM stats_mysql_query_digest
  GROUP BY hostgroup;

Query Rules​

Route queries using regular expressions matched against the query digest or full text:

INSERT INTO mysql_query_rules
  (rule_id,active,username,match_digest,destination_hostgroup,apply)
  VALUES (10,1,'stnduser','^SELECT c FROM sbtest1 WHERE id=?',2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;

Rules are evaluated in ascending rule_id order. Only active=1 rules are considered. Setting apply=1 stops further rule evaluation for that query.

Query Caching​

Enable caching by setting cache_ttl (in milliseconds) on matching rules:

UPDATE mysql_query_rules SET cache_ttl=5000
  WHERE active=1 AND destination_hostgroup=2;
LOAD MYSQL QUERY RULES TO RUNTIME;

Cached query results appear in stats_mysql_query_digest with hostgroup = -1.

Query Rewriting​

Rewrite query text using match_pattern (regex) and replace_pattern:

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);
LOAD MYSQL QUERY RULES TO RUNTIME;

Prefer match_digest over match_pattern where possible — digests are smaller and matching is faster.

Next Steps​