ProxySQL for MySQL Load Balancing

ProxySQL is an advanced MySQL proxy that provides connection pooling, query routing, caching, and load balancing across multiple MySQL servers. It enables intelligent traffic distribution, read-write splitting, query rewriting, and high availability without modifying application code. This comprehensive guide covers installation, admin interface configuration, backend server management, query rules setup, read-write splitting, monitoring, and production deployment strategies.

Table of Contents

Architecture and Benefits

ProxySQL sits between applications and MySQL databases, handling all client connections while managing backend connections intelligently. It provides connection pooling to reduce overhead, caches frequently-executed queries to improve performance, and routes queries based on configurable rules. The advanced feature set enables read-write splitting where SELECT queries go to replicas and DML statements go to the primary, improving throughput without application changes.

ProxySQL maintains awareness of backend server health through continuous monitoring, automatically removing unresponsive servers and restoring them when they recover. The admin interface allows runtime configuration changes without restarting services, enabling maintenance and optimization with zero downtime.

Installation

Install ProxySQL on Ubuntu/Debian:

# Add ProxySQL repository
curl -fsSL https://repo.proxysql.com/PL/proxysql-2.6.1-1.deb_version -o /tmp/proxysql.deb

# Or use package manager
wget https://github.com/sysown/proxysql/releases/download/v2.6.1/proxysql_2.6.1-ubuntu22_amd64.deb
sudo dpkg -i proxysql_2.6.1-ubuntu22_amd64.deb

# Update and verify
sudo apt-get update
proxysql --version

# Enable and start
sudo systemctl enable proxysql
sudo systemctl start proxysql
sudo systemctl status proxysql

On CentOS/RHEL:

# Add repository
wget https://github.com/sysown/proxysql/releases/download/v2.6.1/proxysql-2.6.1-1.el8.x86_64.rpm
sudo dnf install -y ./proxysql-2.6.1-1.el8.x86_64.rpm

# Verify
proxysql --version

# Enable and start
sudo systemctl enable proxysql
sudo systemctl start proxysql

Create dedicated user and directories:

# User created during installation as 'proxysql'
# Create data directory
sudo mkdir -p /var/lib/proxysql
sudo chown proxysql:proxysql /var/lib/proxysql
sudo chmod 700 /var/lib/proxysql

# Create logs directory
sudo mkdir -p /var/log/proxysql
sudo chown proxysql:proxysql /var/log/proxysql

Admin Interface Setup

Configure the ProxySQL admin interface:

# Edit ProxySQL configuration
sudo nano /etc/proxysql.cnf

Add admin interface configuration:

admin_variables=
{
	# Admin connection port
	mysql_ifaces="127.0.0.1:6032,/var/run/proxysql/proxysql_admin.sock"
	
	# Admin credentials
	admin_credentials="admin:admin;radmin:radmin"
	
	# MySQL version string
	mysql_server_version="8.0.30"
	
	# Polling interval for health checks
	mysql_poll_interval_ms=5000
	
	# Logging
	debug=false
}

mysql_variables=
{
	# Listen port for client connections
	interfaces="0.0.0.0:3306,/var/run/proxysql/proxysql.sock"
	
	# Connection pooling
	max_connections=10000
	default_query_timeout=30000
	connection_max_age_ms=3600000
	
	# Performance
	query_cache_size_MB=1000
	query_cache_default_ttl=60000
	
	# Authentication
	default_authentication_plugin="mysql_native_password"
}

Restart ProxySQL to apply configuration:

sudo systemctl restart proxysql

# Monitor startup
sudo journalctl -u proxysql -f

Connect to the admin interface:

# Connect to admin port
mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- Show admin tables
SHOW DATABASES;
SHOW TABLES FROM admin;

-- Exit
EXIT;

Backend Server Configuration

Add MySQL backend servers to ProxySQL:

# Connect to admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032

Configure backend MySQL servers:

-- Add MySQL servers (primary)
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, status, comment)
VALUES (0, '192.168.1.10', 3306, 1000, 'ONLINE', 'Primary MySQL Server');

-- Add MySQL servers (replicas for read operations)
INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, status, comment)
VALUES (1, '192.168.1.11', 3306, 1000, 'ONLINE', 'MySQL Replica 1');

INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight, status, comment)
VALUES (1, '192.168.1.12', 3306, 1000, 'ONLINE', 'MySQL Replica 2');

-- View configured servers
SELECT * FROM mysql_servers;

-- Save configuration to disk
SAVE MYSQL SERVERS TO MEMORY;
LOAD MYSQL SERVERS TO RUNTIME;

Configure monitoring for backend servers:

-- Set up health check credentials
INSERT INTO mysql_users(username, password, default_hostgroup, max_connections, comment)
VALUES ('monitor_user', 'monitor_password', 0, 10, 'Monitoring User');

-- Set monitoring query
UPDATE global_variables SET variable_value='SELECT 1' 
WHERE variable_name='mysql-monitor_username';

UPDATE global_variables SET variable_value='SELECT 1' 
WHERE variable_name='mysql-monitor_password';

-- Enable monitoring
UPDATE global_variables SET variable_value='true' 
WHERE variable_name='mysql-monitor_enabled';

-- Apply changes
SAVE GLOBAL VARIABLES TO MEMORY;
LOAD GLOBAL VARIABLES TO RUNTIME;

-- Check monitoring status
SHOW MYSQL PROCESSLIST;

Configure server connection pooling:

-- Set connection pool parameters
UPDATE global_variables SET variable_value='100' 
WHERE variable_name='mysql-max_connections';

UPDATE global_variables SET variable_value='25' 
WHERE variable_name='mysql-default_pool_size';

UPDATE global_variables SET variable_value='5' 
WHERE variable_name='mysql-min_pool_size';

-- Apply changes
SAVE GLOBAL VARIABLES TO MEMORY;
LOAD GLOBAL VARIABLES TO RUNTIME;

Connection Pooling

Configure connection pooling behavior:

-- Connect to admin interface
mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- View connection pool statistics
SELECT * FROM stats_mysql_connection_pool;

-- Configure connection multiplexing
UPDATE global_variables SET variable_value='1000' 
WHERE variable_name='mysql-max_connections';

-- Set idle timeout (milliseconds)
UPDATE global_variables SET variable_value='900000' 
WHERE variable_name='mysql-connection_max_age_ms';

-- Configure queue timeout
UPDATE global_variables SET variable_value='10000' 
WHERE variable_name='mysql-connection_queue_timeout_ms';

-- Monitor pool utilization
SELECT hostgroup_id, servers, status, 
       sum(ConnUsed) as connections_used,
       sum(ConnFree) as connections_free
FROM stats_mysql_connection_pool
GROUP BY hostgroup_id, servers;

-- SAVE GLOBAL VARIABLES TO MEMORY;
LOAD GLOBAL VARIABLES TO RUNTIME;

Query Rules and Routing

Create rules to route queries intelligently:

-- Connect to admin
mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- Route all SELECT queries to replica hostgroup (1)
INSERT INTO mysql_query_rules(match_pattern, destination_hostgroup, apply, active, comment)
VALUES ('^SELECT.*', 1, 1, 1, 'Route SELECTs to replicas');

-- Route specific database to primary
INSERT INTO mysql_query_rules(match_pattern, match_digest, destination_hostgroup, apply, active, comment)
VALUES (NULL, '^SELECT.*analytics.*', 0, 1, 1, 'Route analytics queries to primary');

-- Route specific users to specific hostgroup
INSERT INTO mysql_query_rules(username, destination_hostgroup, apply, active, comment)
VALUES ('readonly_user', 1, 1, 1, 'Route readonly user to replicas');

-- Cache-busting for writes
INSERT INTO mysql_query_rules(match_pattern, negate_match_pattern, cache_ttl, apply, active, comment)
VALUES ('^SELECT', 0, 60000, 1, 1, 'Cache SELECT queries');

-- View configured rules
SELECT rule_id, match_pattern, destination_hostgroup, active FROM mysql_query_rules;

-- Load rules into runtime
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO MEMORY;

-- View rule statistics
SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY hits DESC;

Create advanced routing rules:

-- Route based on user
INSERT INTO mysql_query_rules(username, destination_hostgroup, apply, active, comment)
VALUES ('app_user', 0, 1, 1, 'Route app_user to primary');

-- Route based on database
INSERT INTO mysql_query_rules(
  match_pattern, 
  destination_hostgroup, 
  apply, 
  active, 
  comment
) VALUES (
  'USE myapp',
  0,
  1,
  1,
  'Route myapp database to primary'
);

-- Route BEGIN/COMMIT transactions
INSERT INTO mysql_query_rules(
  match_pattern,
  destination_hostgroup,
  apply,
  active,
  comment
)
VALUES (
  '^(BEGIN|COMMIT|ROLLBACK)',
  0,
  1,
  1,
  'Route transactions to primary'
);

-- Reload rules
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO MEMORY;

Read-Write Splitting

Implement intelligent read-write splitting:

-- Setup write rules (match DML statements)
INSERT INTO mysql_query_rules(
  match_pattern,
  destination_hostgroup,
  apply,
  active,
  comment
) VALUES (
  '^(INSERT|UPDATE|DELETE|REPLACE)',
  0,
  1,
  1,
  'Route writes to primary'
);

-- Explicit read routing
INSERT INTO mysql_query_rules(
  match_pattern,
  destination_hostgroup,
  apply,
  active,
  comment
) VALUES (
  '^SELECT',
  1,
  1,
  1,
  'Route reads to replicas'
);

-- Handle transactions (must use primary)
INSERT INTO mysql_query_rules(
  match_pattern,
  destination_hostgroup,
  apply,
  active,
  comment
) VALUES (
  '^START TRANSACTION|BEGIN',
  0,
  1,
  1,
  'Route transaction start to primary'
);

-- Flush rules
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO MEMORY;

-- Monitor rule hits
SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY hits DESC;

Query Caching

Configure query result caching:

-- Connect to admin
mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- Enable query cache
UPDATE global_variables SET variable_value='1000' 
WHERE variable_name='mysql-query_cache_size_MB';

-- Set default TTL (60 seconds)
UPDATE global_variables SET variable_value='60000' 
WHERE variable_name='mysql-query_cache_default_ttl';

-- Cache SELECT queries
INSERT INTO mysql_query_rules(
  match_pattern,
  cache_ttl,
  apply,
  active,
  comment
) VALUES (
  '^SELECT',
  60000,
  1,
  1,
  'Cache all SELECT queries for 60 seconds'
);

-- Disable cache for specific queries
INSERT INTO mysql_query_rules(
  match_pattern,
  cache_ttl,
  apply,
  active,
  comment,
  negate_match_pattern
) VALUES (
  '^SELECT.*NOW()|^SELECT.*RAND()',
  -1,
  1,
  1,
  'Do not cache time-dependent queries',
  0
);

-- Load rules
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO MEMORY;

-- Monitor cache performance
SELECT * FROM stats_mysql_query_cache WHERE count_hit > 0;

-- Clear cache if needed
FLUSH MYSQL QUERY_CACHE;

Monitoring and Statistics

Monitor ProxySQL performance:

-- Connect to admin
mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- View connection statistics
SELECT * FROM stats_mysql_connection_pool;

-- Monitor query performance
SELECT hostgroup_id, sum_time, count_exec, count_error 
FROM stats_mysql_query_digest 
WHERE count_exec > 0 
ORDER BY sum_time DESC 
LIMIT 20;

-- View slow queries
SELECT digest_text, count_exec, sum_time/count_exec as avg_time_ms
FROM stats_mysql_query_digest
WHERE sum_time/count_exec > 100
ORDER BY avg_time_ms DESC;

-- Monitor backend server status
SELECT * FROM stats_mysql_connection_pool 
GROUP BY hostgroup_id;

-- View processlist
SHOW PROCESSLIST;

-- Monitor admin interface
SELECT * FROM stats_admin_connections;

-- View global statistics
SELECT * FROM stats_mysql_global;

Create monitoring queries:

#!/bin/bash
# Monitor ProxySQL health

mysql -u admin -padmin -h 127.0.0.1 -P 6032 << EOF
SELECT 
  hostgroup_id,
  servers,
  ConnUsed,
  ConnFree,
  ConnOK,
  ConnERR,
  MaxConnUsed,
  status
FROM stats_mysql_connection_pool;

SELECT 
  SUM(count_exec) as total_queries,
  SUM(count_error) as total_errors,
  SUM(sum_time) as total_time_us
FROM stats_mysql_query_digest;

SHOW MYSQL SERVERS;
EOF

Failover and High Availability

Configure automatic failover:

-- Enable replication lag monitoring
UPDATE mysql_servers SET status='ONLINE' WHERE status='SHUNNED';

-- Set max replication lag threshold (100ms)
UPDATE global_variables SET variable_value='100' 
WHERE variable_name='mysql-monitor_replication_lag_interval';

-- Automatic server removal on health check failure
UPDATE mysql_servers SET status='OFFLINE_HARD' WHERE status='OFFLINE_SOFT';

-- Monitor server status changes
SELECT * FROM stats_mysql_servers;

-- Manual server weight adjustment for gradual migration
UPDATE mysql_servers SET weight=500 WHERE hostgroup_id=1 AND hostname='192.168.1.12';

-- Check replication status
SELECT hostgroup_id, hostname, replication_lag 
FROM stats_mysql_server_status;

-- Apply changes
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO MEMORY;

Configure high availability with multiple ProxySQL instances:

# Deploy ProxySQL on multiple servers
# Each instance connects to same MySQL backend servers

# Configure first ProxySQL instance
ssh server1 "sudo /usr/bin/proxysql -c /etc/proxysql.cnf"

# Configure second ProxySQL instance  
ssh server2 "sudo /usr/bin/proxysql -c /etc/proxysql.cnf"

# Use keepalived or VIP for automatic failover
sudo apt-get install -y keepalived

# Configure keepalived to monitor ProxySQL
sudo nano /etc/keepalived/keepalived.conf

Conclusion

ProxySQL provides an enterprise-grade MySQL proxy enabling advanced features like read-write splitting, connection pooling, and query caching without application modifications. Its intelligent query routing, automatic backend health monitoring, and runtime configuration capabilities make it ideal for complex MySQL deployments. By deploying ProxySQL between applications and database servers, you can improve throughput, reduce database load, and implement sophisticated traffic management strategies. Proper configuration of query rules and monitoring ensures your database infrastructure remains performant and resilient.