ProxySQL for MySQL Load Balancing

ProxySQL is an advanced MySQL proxy that proporciona 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. Esta guía completa cubre installation, admin interface configuration, backend server management, query rules setup, read-write splitting, monitoring, and production deployment strategies.

Tabla de Contenidos

• Architecture and Benefits
• Instalación
• Admin Interface Configuración
• Backend Server Configuración
• Connection Pooling
• Query Rules and Routing
• Read-Write Splitting
• Query Caching
• Monitoreo and Statistics
• Failover and High Availability
• Conclusión

Architecture and Benefits

ProxySQL sits between applications and MySQL databases, handling all client connections while managing backend connections intelligently. It proporciona 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 réplicas 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 servicios, enabling maintenance and optimization with zero downtime.

Instalación

Instala ProxySQL on Ubuntu/Debian:

# Add ProxySQL repositorio
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

# Actualiza and verifica
sudo apt-get update
proxysql --version

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

On CentOS/RHEL:

# Add repositorio
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

# Verifica
proxysql --version

# Habilita and start
sudo systemctl enable proxysql
sudo systemctl start proxysql

Crea dedicated user and directories:

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

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

Admin Interface Configuración

Configura the ProxySQL admin interface:

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

Add admin interface configuration:

admin_variables=
{
	# Admin connection puerto
	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 puerto 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"
}

Reinicia ProxySQL to apply configuration:

sudo systemctl restart proxysql

# Monitorea startup
sudo journalctl -u proxysql -f

Conecta to the admin interface:

# Conecta to admin puerto
mysql -u admin -padmin -h 127.0.0.1 -P 6032

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

-- Exit
EXIT;

Backend Server Configuración

Add MySQL backend servers to ProxySQL:

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

Configura backend MySQL servers:

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

-- Add MySQL servers (réplicas for read operations)
INSERT INTO mysql_servers(hostgroup_id, hostname, puerto, weight, status, comment)
VALUES (1, '192.168.1.11', 3306, 1000, 'ONLINE', 'MySQL Réplica 1');

INSERT INTO mysql_servers(hostgroup_id, hostname, puerto, weight, status, comment)
VALUES (1, '192.168.1.12', 3306, 1000, 'ONLINE', 'MySQL Réplica 2');

-- View configured servers
SELECT * FROM mysql_servers;

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

Configura monitoring for backend servers:

-- Set up health verifica credentials
INSERT INTO mysql_users(username, password, default_hostgroup, max_connections, comment)
VALUES ('monitor_user', 'monitor_password', 0, 10, 'Monitoreo 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';

-- Habilita 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;

Configura 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

Configura connection pooling behavior:

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

-- View connection pool statistics
SELECT * FROM stats_mysql_connection_pool;

-- Configura 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';

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

-- Monitorea 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

Crea rules to route queries intelligently:

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

-- Enruta all SELECT queries to réplica hostgroup (1)
INSERT INTO mysql_query_rules(match_pattern, destination_hostgroup, apply, active, comment)
VALUES ('^SELECT.*', 1, 1, 1, 'Enruta SELECTs to réplicas');

-- Enruta 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, 'Enruta analytics queries to primary');

-- Enruta specific users to specific hostgroup
INSERT INTO mysql_query_rules(username, destination_hostgroup, apply, active, comment)
VALUES ('readonly_user', 1, 1, 1, 'Enruta readonly user to réplicas');

-- 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;

Crea advanced routing rules:

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

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

-- Enruta BEGIN/COMMIT transactions
INSERT INTO mysql_query_rules(
  match_pattern,
  destination_hostgroup,
  apply,
  active,
  comment
)
VALUES (
  '^(BEGIN|COMMIT|ROLLBACK)',
  0,
  1,
  1,
  'Enruta 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:

-- Configuración 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,
  'Enruta writes to primary'
);

-- Explicit read routing
INSERT INTO mysql_query_rules(
  match_pattern,
  destination_hostgroup,
  apply,
  active,
  comment
) VALUES (
  '^SELECT',
  1,
  1,
  1,
  'Enruta reads to réplicas'
);

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

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

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

Query Caching

Configura query result caching:

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

-- Habilita 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'
);

-- Deshabilita 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;

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

-- Clear cache if needed
FLUSH MYSQL QUERY_CACHE;

Monitoreo and Statistics

Monitorea ProxySQL performance:

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

-- View connection statistics
SELECT * FROM stats_mysql_connection_pool;

-- Monitorea 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;

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

-- View processlist
SHOW PROCESSLIST;

-- Monitorea admin interface
SELECT * FROM stats_admin_connections;

-- View global statistics
SELECT * FROM stats_mysql_global;

Crea monitoring queries:

#!/bin/bash
# Monitorea 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

Configura automatic failover:

-- Habilita 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 verifica failure
UPDATE mysql_servers SET status='OFFLINE_HARD' WHERE status='OFFLINE_SOFT';

-- Monitorea 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;

Configura high availability with multiple ProxySQL instances:

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

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

# Configura 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

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

Conclusión

ProxySQL proporciona 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.