Name: EVE AI Core
Author: Jamaurice Holt

MySQL ProxySQL Load Balancing Setup

ProxySQL is a high-performance, protocol-aware proxy for MySQL that sits between your application and database servers. It provides connection pooling, query routing, read/write splitting, and query caching - all essential features for scaling MySQL deployments.

🎯 Why ProxySQL?

ProxySQL solves critical challenges in MySQL environments:

Challenge	ProxySQL Solution
Connection Overhead	Connection pooling multiplexes thousands of app connections to few backend connections
Read/Write Scaling	Automatic query routing to primary for writes, replicas for reads
Query Performance	Built-in query cache with TTL configuration
Failover	Automatic backend health monitoring and failover
Query Control	Query rewriting, blocking, and rate limiting

💡 Key Benefit: ProxySQL can reduce your MySQL connection count by 90% while improving application response times through intelligent connection reuse.

🔧 Architecture Overview

┌─────────────────────────────────────────────────────────────────────┐
│                        APPLICATION LAYER                             │
├─────────────────────────────────────────────────────────────────────┤
│  ┌─────────┐  ┌─────────┐  ┌─────────┐  ┌─────────┐  ┌─────────┐  │
│  │  App 1  │  │  App 2  │  │  App 3  │  │  App 4  │  │  App N  │  │
│  └────┬────┘  └────┬────┘  └────┬────┘  └────┬────┘  └────┬────┘  │
│       │            │            │            │            │        │
│       └────────────┴────────────┼────────────┴────────────┘        │
│                                 │                                   │
│                    ┌────────────▼────────────┐                     │
│                    │       PROXYSQL          │                     │
│                    │   (Port 6033 - MySQL)   │                     │
│                    │   (Port 6032 - Admin)   │                     │
│                    └────────────┬────────────┘                     │
│                                 │                                   │
│            ┌────────────────────┼────────────────────┐             │
│            │                    │                    │             │
│     ┌──────▼──────┐     ┌──────▼──────┐     ┌──────▼──────┐       │
│     │   PRIMARY   │     │  REPLICA 1  │     │  REPLICA 2  │       │
│     │  (Writer)   │     │  (Reader)   │     │  (Reader)   │       │
│     │  HG 10      │     │  HG 20      │     │  HG 20      │       │
│     └─────────────┘     └─────────────┘     └─────────────┘       │
│                                                                     │
└─────────────────────────────────────────────────────────────────────┘

📦 Installation

Install ProxySQL on Ubuntu/Debian

# Add ProxySQL repository
wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key' | apt-key add -
echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./ | tee /etc/apt/sources.list.d/proxysql.list

# Install ProxySQL
apt-get update
apt-get install proxysql

# Start and enable ProxySQL
systemctl start proxysql
systemctl enable proxysql

# Verify installation
proxysql --version

Install on CentOS/RHEL

# Add ProxySQL repository
cat > /etc/yum.repos.d/proxysql.repo << 'EOF'
[proxysql]
name=ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key
EOF

# Install ProxySQL
yum install proxysql

# Start and enable
systemctl start proxysql
systemctl enable proxysql

🔧 Initial Configuration

Connect to ProxySQL Admin Interface

# Default admin credentials: admin/admin
mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQL Admin> '

Configure Backend MySQL Servers

-- Connect to ProxySQL admin interface
-- Add primary server (hostgroup 10 for writes)
INSERT INTO mysql_servers (
    hostgroup_id,
    hostname,
    port,
    weight,
    max_connections,
    comment
) VALUES (
    10,
    '192.168.1.101',
    3306,
    1000,
    1000,
    'Primary MySQL Server'
);

-- Add replica servers (hostgroup 20 for reads)
INSERT INTO mysql_servers (
    hostgroup_id,
    hostname,
    port,
    weight,
    max_connections,
    comment
) VALUES
    (20, '192.168.1.102', 3306, 1000, 1000, 'Replica 1'),
    (20, '192.168.1.103', 3306, 1000, 1000, 'Replica 2'),
    (20, '192.168.1.104', 3306, 500, 1000, 'Replica 3 - Lower Weight');

-- Also add primary to read group as fallback
INSERT INTO mysql_servers (
    hostgroup_id,
    hostname,
    port,
    weight,
    max_connections,
    comment
) VALUES (
    20,
    '192.168.1.101',
    3306,
    100,
    500,
    'Primary - Read Fallback'
);

-- Apply changes to runtime
LOAD MYSQL SERVERS TO RUNTIME;

-- Save to disk for persistence
SAVE MYSQL SERVERS TO DISK;

-- Verify configuration
SELECT * FROM mysql_servers;

Configure MySQL Users

-- Create monitoring user on MySQL servers first
-- Run this on your MySQL primary:
/*
CREATE USER 'proxysql_monitor'@'%' IDENTIFIED BY 'MonitorPassword123!';
GRANT REPLICATION CLIENT ON *.* TO 'proxysql_monitor'@'%';
GRANT PROCESS ON *.* TO 'proxysql_monitor'@'%';

CREATE USER 'app_user'@'%' IDENTIFIED BY 'AppPassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_database.* TO 'app_user'@'%';
*/

-- Configure monitoring user in ProxySQL
UPDATE global_variables SET variable_value='proxysql_monitor'
    WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='MonitorPassword123!'
    WHERE variable_name='mysql-monitor_password';

-- Configure monitoring intervals
UPDATE global_variables SET variable_value='2000'
    WHERE variable_name='mysql-monitor_connect_interval';
UPDATE global_variables SET variable_value='1000'
    WHERE variable_name='mysql-monitor_ping_interval';
UPDATE global_variables SET variable_value='1000'
    WHERE variable_name='mysql-monitor_read_only_interval';

-- Add application user
INSERT INTO mysql_users (
    username,
    password,
    default_hostgroup,
    max_connections,
    active
) VALUES (
    'app_user',
    'AppPassword123!',
    10,  -- Default to writer hostgroup
    1000,
    1
);

-- Apply and save
LOAD MYSQL VARIABLES TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL USERS TO DISK;

🚀 Read/Write Splitting Configuration

Configure Query Rules for Read/Write Split

-- Rule 1: Route SELECT queries to reader hostgroup (20)
INSERT INTO mysql_query_rules (
    rule_id,
    active,
    match_pattern,
    destination_hostgroup,
    apply
) VALUES (
    100,
    1,
    '^SELECT.*',
    20,
    1
);

-- Rule 2: Route SELECT ... FOR UPDATE to writer (10)
INSERT INTO mysql_query_rules (
    rule_id,
    active,
    match_pattern,
    destination_hostgroup,
    apply
) VALUES (
    50,
    1,
    '^SELECT.*FOR UPDATE',
    10,
    1
);

-- Rule 3: Route all writes to primary (10)
INSERT INTO mysql_query_rules (
    rule_id,
    active,
    match_pattern,
    destination_hostgroup,
    apply
) VALUES (
    200,
    1,
    '^(INSERT|UPDATE|DELETE|REPLACE|TRUNCATE|ALTER|CREATE|DROP)',
    10,
    1
);

-- Rule 4: Transaction handling - all queries in transaction go to writer
INSERT INTO mysql_query_rules (
    rule_id,
    active,
    match_pattern,
    destination_hostgroup,
    apply
) VALUES (
    10,
    1,
    '^(BEGIN|START TRANSACTION)',
    10,
    1
);

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

-- Verify rules
SELECT rule_id, match_pattern, destination_hostgroup, apply
FROM mysql_query_rules
ORDER BY rule_id;

Advanced Query Rules

-- Route specific database to specific hostgroup
INSERT INTO mysql_query_rules (
    rule_id,
    active,
    schemaname,
    destination_hostgroup,
    apply
) VALUES (
    300,
    1,
    'analytics_db',
    30,  -- Dedicated analytics replica hostgroup
    1
);

-- Block dangerous queries
INSERT INTO mysql_query_rules (
    rule_id,
    active,
    match_pattern,
    error_msg,
    apply
) VALUES (
    5,
    1,
    'DELETE.*FROM.*WHERE 1.*=.*1',
    'Blocked: DELETE without proper WHERE clause',
    1
);

-- Rate limit specific queries
INSERT INTO mysql_query_rules (
    rule_id,
    active,
    match_pattern,
    max_queries_per_sec,
    destination_hostgroup,
    apply
) VALUES (
    400,
    1,
    '^SELECT.*FROM expensive_report',
    10,  -- Max 10 queries per second
    20,
    1
);

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

📊 Query Caching

Enable and Configure Query Cache

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

-- Set cache size (in bytes, 256MB)
UPDATE global_variables SET variable_value='268435456'
    WHERE variable_name='mysql-query_cache_size_MB';

-- Create query rule for caching specific queries
INSERT INTO mysql_query_rules (
    rule_id,
    active,
    match_pattern,
    cache_ttl,
    destination_hostgroup,
    apply
) VALUES (
    500,
    1,
    '^SELECT.*FROM products WHERE category_id',
    60000,  -- Cache for 60 seconds (milliseconds)
    20,
    1
);

-- Cache configuration lookups for 5 minutes
INSERT INTO mysql_query_rules (
    rule_id,
    active,
    match_pattern,
    cache_ttl,
    destination_hostgroup,
    apply
) VALUES (
    501,
    1,
    '^SELECT.*FROM configuration',
    300000,  -- 5 minutes
    20,
    1
);

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

-- Monitor cache hit ratio
SELECT * FROM stats_mysql_query_digest
ORDER BY sum_time DESC LIMIT 10;

SELECT * FROM stats_mysql_global
WHERE variable_name LIKE '%cache%';

🔐 Security Configuration

Secure ProxySQL Admin Interface

-- Change default admin password
UPDATE global_variables
SET variable_value='NewSecureAdminPassword123!'
WHERE variable_name='admin-admin_credentials';

-- Restrict admin interface to specific IPs
UPDATE global_variables
SET variable_value='127.0.0.1;192.168.1.0/24'
WHERE variable_name='admin-mysql_ifaces';

-- Create additional admin users with limited permissions
INSERT INTO admin_credentials (username, password, active, type)
VALUES ('dba_readonly', 'ReadOnlyPass123!', 1, 'stats');

LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

Configure SSL/TLS

-- Enable SSL for frontend (application) connections
UPDATE global_variables SET variable_value='/etc/proxysql/ssl/server-cert.pem'
    WHERE variable_name='mysql-ssl_p2s_cert';
UPDATE global_variables SET variable_value='/etc/proxysql/ssl/server-key.pem'
    WHERE variable_name='mysql-ssl_p2s_key';
UPDATE global_variables SET variable_value='/etc/proxysql/ssl/ca.pem'
    WHERE variable_name='mysql-ssl_p2s_ca';

-- Enable SSL for backend (MySQL server) connections
UPDATE global_variables SET variable_value='1'
    WHERE variable_name='mysql-have_ssl';

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

📈 Monitoring and Statistics

Essential Monitoring Queries

-- Connection pool statistics
SELECT
    hostgroup,
    srv_host,
    srv_port,
    status,
    ConnUsed,
    ConnFree,
    ConnOK,
    ConnERR,
    Queries,
    Bytes_data_sent,
    Bytes_data_recv
FROM stats_mysql_connection_pool;

-- Query digest statistics (find slow queries)
SELECT
    hostgroup,
    schemaname,
    username,
    SUBSTR(digest_text, 1, 80) AS query,
    count_star AS executions,
    ROUND(sum_time/1000000, 2) AS total_time_sec,
    ROUND(sum_time/count_star/1000, 2) AS avg_time_ms,
    ROUND(min_time/1000, 2) AS min_time_ms,
    ROUND(max_time/1000, 2) AS max_time_ms
FROM stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 20;

-- Server status and health
SELECT
    hostgroup_id,
    hostname,
    port,
    status,
    weight,
    max_replication_lag
FROM mysql_servers;

-- Check read/write distribution
SELECT
    hostgroup,
    SUM(Queries) as total_queries,
    SUM(Bytes_data_sent) as bytes_sent,
    SUM(Bytes_data_recv) as bytes_recv
FROM stats_mysql_connection_pool
GROUP BY hostgroup;

Create Monitoring Dashboard View

-- Create helpful views for monitoring
-- Connection summary
SELECT
    'Active Connections' as metric,
    SUM(ConnUsed) as value
FROM stats_mysql_connection_pool
UNION ALL
SELECT
    'Free Connections',
    SUM(ConnFree)
FROM stats_mysql_connection_pool
UNION ALL
SELECT
    'Total Queries',
    SUM(Queries)
FROM stats_mysql_connection_pool
UNION ALL
SELECT
    'Query Cache Hits',
    variable_value
FROM stats_mysql_global
WHERE variable_name = 'Query_Cache_count_GET_OK';

Prometheus Metrics Export

# ProxySQL exports metrics on port 6070 by default
# Configure in proxysql.cnf:
# admin_variables=
# {
#     restapi_enabled=true
#     restapi_port=6070
# }

# Example Prometheus scrape config:
# - job_name: 'proxysql'
#   static_configs:
#     - targets: ['proxysql-server:6070']

⚠️ Troubleshooting

Common Issues and Solutions

-- Issue: Connection errors to backend
-- Check server status
SELECT * FROM mysql_servers WHERE status != 'ONLINE';

-- Check monitor logs
SELECT * FROM mysql_server_connect_log
ORDER BY time_start_us DESC LIMIT 10;

SELECT * FROM mysql_server_ping_log
ORDER BY time_start_us DESC LIMIT 10;

-- Issue: High latency
-- Check connection pool saturation
SELECT hostgroup, srv_host, ConnUsed, ConnFree, MaxConnUsed
FROM stats_mysql_connection_pool
WHERE ConnFree = 0;

-- Issue: Queries not being routed correctly
-- Check which rule is matching
SELECT
    rule_id,
    match_pattern,
    hits,
    destination_hostgroup
FROM stats_mysql_query_rules
WHERE hits > 0
ORDER BY rule_id;

-- Test query routing
SELECT * FROM stats_mysql_query_digest
WHERE digest_text LIKE '%your_query%';

Reset and Rebuild

-- Reset all query rules
DELETE FROM mysql_query_rules;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

-- Reset all servers
DELETE FROM mysql_servers;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

-- Reset statistics
SELECT * FROM stats_mysql_query_digest_reset;

🚀 High Availability ProxySQL Setup

ProxySQL Cluster Configuration

# /etc/proxysql.cnf - Cluster configuration

admin_variables=
{
    admin_credentials="admin:AdminPassword123!;cluster_user:ClusterPassword123!"
    mysql_ifaces="0.0.0.0:6032"
    cluster_username="cluster_user"
    cluster_password="ClusterPassword123!"
    cluster_check_interval_ms=200
    cluster_check_status_frequency=100
    cluster_mysql_query_rules_save_to_disk=true
    cluster_mysql_servers_save_to_disk=true
    cluster_mysql_users_save_to_disk=true
    cluster_proxysql_servers_save_to_disk=true
}

-- Add cluster nodes
INSERT INTO proxysql_servers (hostname, port, weight, comment) VALUES
    ('proxysql-1', 6032, 1, 'ProxySQL Node 1'),
    ('proxysql-2', 6032, 1, 'ProxySQL Node 2'),
    ('proxysql-3', 6032, 1, 'ProxySQL Node 3');

LOAD PROXYSQL SERVERS TO RUNTIME;
SAVE PROXYSQL SERVERS TO DISK;

-- Verify cluster status
SELECT * FROM stats_proxysql_servers_metrics;

✅ Best Practices

Performance Tuning

-- Optimize connection handling
UPDATE global_variables SET variable_value='2048'
    WHERE variable_name='mysql-max_connections';

UPDATE global_variables SET variable_value='true'
    WHERE variable_name='mysql-connection_max_age_ms';

UPDATE global_variables SET variable_value='0'
    WHERE variable_name='mysql-free_connections_pct';

-- Optimize thread handling
UPDATE global_variables SET variable_value='8'
    WHERE variable_name='mysql-threads';

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

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

Checklist

[ ] Change default admin credentials
[ ] Configure SSL/TLS for all connections
[ ] Set up monitoring and alerting
[ ] Configure appropriate query routing rules
[ ] Test failover scenarios
[ ] Document all query rules
[ ] Regular backup of ProxySQL configuration
[ ] Monitor connection pool utilization

🎓 Conclusion

ProxySQL is an essential component for scaling MySQL deployments. With proper configuration of read/write splitting, connection pooling, and query caching, you can achieve significant performance improvements while maintaining high availability.

💡 Pro Tip: Start with a simple read/write split configuration, then gradually add more advanced features like query caching and rewriting as you understand your workload patterns.

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in MySQL, SQL optimization, and enterprise database solutions.

Need help with MySQL load balancing? Contact us for expert database consulting.