Configure MySQL connection pooling with ProxySQL 2.6 for high availability and performance optimization

Intermediate 45 min Apr 27, 2026 1,180 views
Ubuntu 24.04 Debian 12 AlmaLinux 9 Rocky Linux 9

Set up ProxySQL 2.6 to manage MySQL connection pooling, load balancing, and automatic failover. This tutorial covers backend server configuration, health checks, query routing rules, and monitoring for production MySQL environments.

Prerequisites

  • Multiple MySQL servers (master and replicas)
  • Root or sudo access
  • Basic MySQL administration knowledge
  • Network connectivity between ProxySQL and MySQL servers

What this solves

MySQL connection pooling with ProxySQL eliminates connection overhead, manages database load distribution, and provides automatic failover when backend servers go down. ProxySQL acts as an intelligent proxy layer between your applications and MySQL servers, handling connection reuse, query routing, and health monitoring without requiring application changes.

Step-by-step installation

Update system packages

Start by updating your package manager to ensure you get the latest versions and security patches.

sudo apt update && sudo apt upgrade -y
sudo dnf update -y

Install ProxySQL 2.6

Add the official ProxySQL repository and install the latest 2.6 version. This provides the most recent features and security updates.

wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key' | sudo apt-key add -
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/$(lsb_release -sc)/ ./" | sudo tee /etc/apt/sources.list.d/proxysql.list
sudo apt update
sudo apt install -y proxysql2
cat > /etc/yum.repos.d/proxysql.repo << 'EOF'
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/centos/$releasever
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key
EOF
sudo dnf install -y proxysql2

Create ProxySQL admin user

Create a dedicated user for ProxySQL operations. This follows security best practices by avoiding root access.

sudo groupadd proxysql
sudo useradd -r -g proxysql -s /bin/false -d /var/lib/proxysql proxysql
sudo chown -R proxysql:proxysql /var/lib/proxysql
sudo chmod 755 /var/lib/proxysql

Configure ProxySQL main settings

Set up the primary configuration file with admin interface, MySQL interface, and basic clustering settings. This configuration enables both admin access and MySQL proxy functionality.

datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"

admin_variables=
{
    admin_credentials="proxysql-admin:StrongAdminPass123!"
    mysql_ifaces="0.0.0.0:6032"
    refresh_interval=2000
    debug=false
    hash_passwords=true
}

mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033"
    default_schema="information_schema"
    stacksize=1048576
    server_version="8.0.35"
    connect_timeout_server=3000
    monitor_username="proxysql_monitor"
    monitor_password="MonitorPass123!"
    monitor_history=600000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    monitor_read_only_interval=1500
    monitor_read_only_timeout=500
    ping_interval_server_msec=120000
    ping_timeout_server=500
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
}

mysql_servers =
(
    {
        address = "203.0.113.10"
        port = 3306
        hostgroup = 0
        weight = 1000
        status = "ONLINE"
        compression = 0
        max_connections = 200
        max_replication_lag = 10
        use_ssl = 1
        comment = "MySQL Master Server"
    },
    {
        address = "203.0.113.11"
        port = 3306
        hostgroup = 1
        weight = 900
        status = "ONLINE"
        compression = 0
        max_connections = 200
        max_replication_lag = 10
        use_ssl = 1
        comment = "MySQL Replica Server 1"
    },
    {
        address = "203.0.113.12"
        port = 3306
        hostgroup = 1
        weight = 900
        status = "ONLINE"
        compression = 0
        max_connections = 200
        max_replication_lag = 10
        use_ssl = 1
        comment = "MySQL Replica Server 2"
    }
)

mysql_users:
(
    {
        username = "app_user"
        password = "AppUserPass123!"
        default_hostgroup = 0
        max_connections = 1000
        default_schema = "production_db"
        active = 1
        use_ssl = 1
    },
    {
        username = "read_user"
        password = "ReadUserPass123!"
        default_hostgroup = 1
        max_connections = 500
        default_schema = "production_db"
        active = 1
        use_ssl = 1
    }
)

mysql_query_rules:
(
    {
        rule_id = 1
        active = 1
        match_pattern = "^SELECT.*"
        destination_hostgroup = 1
        apply = 1
        comment = "Route SELECT queries to read replicas"
    },
    {
        rule_id = 2
        active = 1
        match_pattern = "^INSERT|UPDATE|DELETE.*"
        destination_hostgroup = 0
        apply = 1
        comment = "Route write queries to master"
    }
)

Set correct permissions for configuration

Secure the configuration file to protect database credentials. Only the proxysql user and root should have access to sensitive connection details.

sudo chown proxysql:proxysql /etc/proxysql.cnf
sudo chmod 640 /etc/proxysql.cnf

Create MySQL monitor user on backend servers

ProxySQL needs a monitoring user on each MySQL server to perform health checks and read-only detection. Run these commands on each MySQL backend server.

mysql -u root -p << 'EOF'
CREATE USER 'proxysql_monitor'@'%' IDENTIFIED BY 'MonitorPass123!';
GRANT USAGE ON *.* TO 'proxysql_monitor'@'%';
GRANT SELECT ON performance_schema.* TO 'proxysql_monitor'@'%';
FLUSH PRIVILEGES;
EOF

Start and enable ProxySQL service

Enable ProxySQL to start automatically on boot and start the service. The initial startup will create the SQLite database and apply the configuration.

sudo systemctl enable proxysql
sudo systemctl start proxysql
sudo systemctl status proxysql

Configure connection pool settings

Connect to ProxySQL admin interface and optimize connection pool parameters for your workload. These settings control how connections are managed and reused.

mysql -u proxysql-admin -p'StrongAdminPass123!' -h 127.0.0.1 -P 6032 --prompt='ProxySQL Admin> '

Once connected to the admin interface, run these optimization commands:

UPDATE global_variables SET variable_value='4' WHERE variable_name='mysql-threads';
UPDATE global_variables SET variable_value='2048' WHERE variable_name='mysql-max_connections';
UPDATE global_variables SET variable_value='10000' WHERE variable_name='mysql-free_connections_pct';
UPDATE global_variables SET variable_value='300' WHERE variable_name='mysql-connection_max_age_ms';
UPDATE global_variables SET variable_value='10000' WHERE variable_name='mysql-ping_interval_server_msec';
UPDATE global_variables SET variable_value='500' WHERE variable_name='mysql-ping_timeout_server';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
exit;

Configure advanced query routing rules

Add more sophisticated routing rules for better load distribution and performance. These rules handle different query patterns and database operations.

mysql -u proxysql-admin -p'StrongAdminPass123!' -h 127.0.0.1 -P 6032 << 'EOF'
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply, comment) VALUES
(10, 1, '^SELECT.*FOR UPDATE', 0, 1, 'SELECT FOR UPDATE to master'),
(11, 1, '^SELECT.*LOCK IN SHARE MODE', 0, 1, 'SELECT with shared locks to master'),
(12, 1, '^SELECT.*FROM.*WHERE.*=.*LAST_INSERT_ID', 0, 1, 'Queries using LAST_INSERT_ID to master'),
(20, 1, '^SELECT COUNT\\(\\*\\)', 1, 1, 'COUNT queries to replicas'),
(21, 1, '^SELECT.*LIMIT', 1, 1, 'LIMIT queries to replicas'),
(30, 1, '^BEGIN|START TRANSACTION', 0, 1, 'Transactions to master'),
(31, 1, '^COMMIT', 0, 1, 'Commits to master'),
(32, 1, '^ROLLBACK', 0, 1, 'Rollbacks to master');
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
EOF

Configure health check settings

Set up comprehensive health monitoring to detect failed servers and automatically remove them from the pool. This ensures high availability by preventing connections to unhealthy backends.

mysql -u proxysql-admin -p'StrongAdminPass123!' -h 127.0.0.1 -P 6032 << 'EOF'
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='3' WHERE variable_name='mysql-monitor_ping_max_failures';
UPDATE global_variables SET variable_value='5000' WHERE variable_name='mysql-monitor_ping_timeout';
UPDATE global_variables SET variable_value='1500' WHERE variable_name='mysql-monitor_read_only_interval';
UPDATE global_variables SET variable_value='3' WHERE variable_name='mysql-monitor_read_only_max_timeout_count';
UPDATE global_variables SET variable_value='500' WHERE variable_name='mysql-monitor_read_only_timeout';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
EOF

Set up ProxySQL stats database

Enable detailed statistics collection for monitoring query performance, connection usage, and identifying bottlenecks. This data is essential for performance tuning.

mysql -u proxysql-admin -p'StrongAdminPass123!' -h 127.0.0.1 -P 6032 << 'EOF'
UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-commands_stats';
UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-query_digests';
UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-query_digests_normalize_digest_text';
UPDATE global_variables SET variable_value='1048576' WHERE variable_name='mysql-query_digests_max_digest_length';
UPDATE global_variables SET variable_value='10000' WHERE variable_name='mysql-query_digests_max_query_length';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
EOF

Configure log rotation

Set up log rotation to prevent ProxySQL logs from consuming excessive disk space. This maintains system stability in production environments.

/var/lib/proxysql/proxysql.log {
    daily
    missingok
    rotate 7
    compress
    delaycompress
    notifempty
    copytruncate
    su proxysql proxysql
}

Create ProxySQL monitoring script

Build a monitoring script to track ProxySQL performance and backend server health. This provides automated alerting when issues are detected.

#!/bin/bash
set -euo pipefail

# ProxySQL monitoring configuration
PROXYSQL_ADMIN_HOST="127.0.0.1"
PROXYSQL_ADMIN_PORT="6032"
PROXYSQL_ADMIN_USER="proxysql-admin"
PROXYSQL_ADMIN_PASS="StrongAdminPass123!"
ALERT_EMAIL="admin@example.com"
LOG_FILE="/var/log/proxysql-monitor.log"

# Function to log messages
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}

# Function to send alert
send_alert() {
    local message="$1"
    log_message "ALERT: $message"
    echo "$message" | mail -s "ProxySQL Alert - $(hostname)" "$ALERT_EMAIL"
}

# Check ProxySQL service status
if ! systemctl is-active --quiet proxysql; then
    send_alert "ProxySQL service is not running"
    exit 1
fi

# Check admin interface connectivity
if ! mysql -h"$PROXYSQL_ADMIN_HOST" -P"$PROXYSQL_ADMIN_PORT" -u"$PROXYSQL_ADMIN_USER" -p"$PROXYSQL_ADMIN_PASS" -e "SELECT 1" >/dev/null 2>&1; then
    send_alert "Cannot connect to ProxySQL admin interface"
    exit 1
fi

# Check backend server status
OFFLINE_SERVERS=$(mysql -h"$PROXYSQL_ADMIN_HOST" -P"$PROXYSQL_ADMIN_PORT" -u"$PROXYSQL_ADMIN_USER" -p"$PROXYSQL_ADMIN_PASS" -sN -e "SELECT COUNT(*) FROM mysql_servers WHERE status='OFFLINE_HARD' OR status='OFFLINE_SOFT';")

if [ "$OFFLINE_SERVERS" -gt 0 ]; then
    send_alert "$OFFLINE_SERVERS backend MySQL servers are offline"
fi

# Check connection pool utilization
CONN_USAGE=$(mysql -h"$PROXYSQL_ADMIN_HOST" -P"$PROXYSQL_ADMIN_PORT" -u"$PROXYSQL_ADMIN_USER" -p"$PROXYSQL_ADMIN_PASS" -sN -e "SELECT ROUND(SUM(ConnUsed)/SUM(ConnMax)*100,2) as conn_usage FROM stats_mysql_connection_pool;")

if (( $(echo "$CONN_USAGE > 80" | bc -l) )); then
    send_alert "High connection pool utilization: ${CONN_USAGE}%"
fi

# Check query response time
AVG_TIME=$(mysql -h"$PROXYSQL_ADMIN_HOST" -P"$PROXYSQL_ADMIN_PORT" -u"$PROXYSQL_ADMIN_USER" -p"$PROXYSQL_ADMIN_PASS" -sN -e "SELECT ROUND(AVG(sum_time/count_star)/1000,2) FROM stats_mysql_query_digest WHERE last_seen > DATE_SUB(NOW(), INTERVAL 5 MINUTE);")

if (( $(echo "$AVG_TIME > 1000" | bc -l) )); then
    send_alert "High average query response time: ${AVG_TIME}ms"
fi

log_message "ProxySQL monitoring check completed successfully"
sudo chmod +x /usr/local/bin/proxysql-monitor.sh
sudo chown proxysql:proxysql /usr/local/bin/proxysql-monitor.sh

Create systemd timer for monitoring

Set up automated monitoring checks every 5 minutes using systemd timers. This provides continuous health monitoring without requiring cron.

[Unit]
Description=ProxySQL Health Monitor
After=proxysql.service
Requires=proxysql.service

[Service]
Type=oneshot
User=proxysql
Group=proxysql
ExecStart=/usr/local/bin/proxysql-monitor.sh
StandardOutput=journal
StandardError=journal
[Unit]
Description=Run ProxySQL Health Monitor every 5 minutes
Requires=proxysql-monitor.service

[Timer]
OnCalendar=*:0/5
Persistent=true

[Install]
WantedBy=timers.target
sudo systemctl daemon-reload
sudo systemctl enable --now proxysql-monitor.timer
sudo systemctl status proxysql-monitor.timer

Verify your setup

Test ProxySQL functionality and connection pooling to ensure everything is working correctly.

# Check ProxySQL service status
sudo systemctl status proxysql

# Verify admin interface connection
mysql -u proxysql-admin -p'StrongAdminPass123!' -h 127.0.0.1 -P 6032 -e "SELECT * FROM mysql_servers;"

# Test application connection through ProxySQL
mysql -u app_user -p'AppUserPass123!' -h 127.0.0.1 -P 6033 -e "SELECT @@server_id, @@hostname;"

# Check connection pool status
mysql -u proxysql-admin -p'StrongAdminPass123!' -h 127.0.0.1 -P 6032 -e "SELECT * FROM stats_mysql_connection_pool;"

# Verify query routing rules
mysql -u proxysql-admin -p'StrongAdminPass123!' -h 127.0.0.1 -P 6032 -e "SELECT rule_id, match_pattern, destination_hostgroup FROM mysql_query_rules WHERE active=1;"

# Check backend server health
mysql -u proxysql-admin -p'StrongAdminPass123!' -h 127.0.0.1 -P 6032 -e "SELECT hostname, port, status, ConnUsed, ConnFree, ConnOK, ConnERR FROM mysql_servers JOIN stats_mysql_connection_pool USING(hostgroup_id, srv_host, srv_port);"

# Monitor query statistics
mysql -u proxysql-admin -p'StrongAdminPass123!' -h 127.0.0.1 -P 6032 -e "SELECT schemaname, digest_text, count_star, sum_time FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 10;"

Configure SSL encryption

Enable SSL for backend connections

Configure ProxySQL to use encrypted connections to MySQL backend servers. This protects data in transit between ProxySQL and your database servers.

mysql -u proxysql-admin -p'StrongAdminPass123!' -h 127.0.0.1 -P 6032 << 'EOF'
UPDATE mysql_servers SET use_ssl=1;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
EOF

Configure client SSL certificates

Set up SSL certificates for ProxySQL client connections when security requirements mandate encryption for application-to-proxy communication.

mysql -u proxysql-admin -p'StrongAdminPass123!' -h 127.0.0.1 -P 6032 << 'EOF'
UPDATE global_variables SET variable_value='/etc/ssl/certs/proxysql-server.pem' WHERE variable_name='mysql-ssl_p2s_cert';
UPDATE global_variables SET variable_value='/etc/ssl/private/proxysql-server.key' WHERE variable_name='mysql-ssl_p2s_key';
UPDATE global_variables SET variable_value='/etc/ssl/certs/ca-certificates.crt' WHERE variable_name='mysql-ssl_p2s_ca';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
EOF

Common issues

Symptom Cause Fix
ProxySQL won't start Configuration syntax error sudo proxysql --initial -f -c /etc/proxysql.cnf to test config
Can't connect to admin interface Wrong credentials or interface

Automated install script

Run this to automate the entire setup

#MySQL #ProxySQL #connection pooling #load balancing #high availability

Tutorial correlati

databases Advanced

Configure PostgreSQL 17 SSL encryption and certificate-based authentication

Set up a private CA, issue server and client certificates, enforce TLS 1.2+ with strong ciphers, and configure pg_hba.conf for mutual TLS client certificate authentication in PostgreSQL 17.

45 min 4 distros 99 views
databases Intermediate

Configure PostgreSQL 17 connection pooling with PgBouncer for high availability

Deploy PgBouncer in front of PostgreSQL 17 with session, transaction and statement pooling modes, SCRAM-SHA-256 authentication, keepalived-based HA, and Prometheus monitoring for production workloads.

45 min 4 distros 195 views
databases Advanced

Setup ScyllaDB backup validation and automated restore testing

Configure automated ScyllaDB backups with nodetool snapshots, implement validation scripts to verify backup integrity, and set up systemd timers for automated restore testing with Prometheus monitoring.

45 min 4 distros 453 views
Browse all tutorials in our complete directory →

Non vuoi gestirlo da solo?

Gestiamo l'infrastruttura di aziende che dipendono dall'uptime. Completamente gestita, con un referente fisso che conosce il tuo ambiente.

Avete un referente fisso che conosce il vostro ambiente

Rotterdam 04:07 · raggiungibile con un messaggio, senza modulo ticket

+31 10 477 5362 WhatsApp