Configure MySQL Connection Pooling with ProxySQL 2.6

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 binding	Check `admin_credentials` and `mysql_ifaces` in config
Backend servers show OFFLINE_HARD	Monitor user missing or network issues	Verify monitor user exists: `SHOW GRANTS FOR 'proxysql_monitor'@'%';`
Queries not routing correctly	Query rules not loaded or pattern mismatch	`LOAD MYSQL QUERY RULES TO RUNTIME;` and check patterns
High connection pool usage	max_connections too low or connection leaks	Increase `max_connections` per server in mysql_servers table
SSL connection failures	Certificate path wrong or permissions	Check certificate files exist and are readable by proxysql user
Monitor checks failing	Email not configured or bc calculator missing	Install mailutils and bc: `sudo apt install mailutils bc`
Permission denied on log files	Wrong ownership on ProxySQL directories	`sudo chown -R proxysql:proxysql /var/lib/proxysql`

Never use chmod 777. It gives every user on the system full access to your files. Instead, fix ownership with chown and use minimal permissions like 640 for config files and 755 for directories.

Monitor ProxySQL performance

ProxySQL provides extensive statistics tables for monitoring performance, connection usage, and query patterns. Regular monitoring helps identify bottlenecks and optimization opportunities. You can integrate this data with Prometheus and Grafana dashboards for comprehensive database monitoring.

Key metrics to monitor include connection pool utilization, query response times, backend server health, and failed connection attempts. The stats tables update in real-time and provide historical data for trend analysis.

For production deployments, consider implementing automated failover testing and backup strategies. The MySQL replication setup guide covers backend database high availability configuration that complements ProxySQL's connection management.

Next steps

Running this in production?

Want this handled for you? Setting up ProxySQL once is straightforward. Keeping it patched, monitored, backed up and tuned across environments is the harder part. See how we run infrastructure like this for European SaaS and e-commerce teams.

Automated install script

Run this to automate the entire setup

install.sh

#!/usr/bin/env bash

set -euo pipefail

# Colors for output
readonly RED='\033[0;31m'
readonly GREEN='\033[0;32m'
readonly YELLOW='\033[1;33m'
readonly NC='\033[0m'

# Global variables
MYSQL_ADMIN_USER=""
MYSQL_ADMIN_PASS=""
MYSQL_MONITOR_USER=""
MYSQL_MONITOR_PASS=""
MYSQL_BACKEND_SERVERS=""

# Cleanup function
cleanup() {
    echo -e "${RED}[ERROR] Installation failed. Cleaning up...${NC}"
    systemctl stop proxysql 2>/dev/null || true
    if [[ "$PKG_MGR" == "apt" ]]; then
        apt-mark hold proxysql2 2>/dev/null || true
    fi
}

trap cleanup ERR

usage() {
    echo "Usage: $0 --mysql-admin-user USER --mysql-admin-pass PASS --mysql-monitor-user USER --mysql-monitor-pass PASS --backends 'host1:port,host2:port'"
    echo ""
    echo "Required arguments:"
    echo "  --mysql-admin-user     MySQL admin username for ProxySQL"
    echo "  --mysql-admin-pass     MySQL admin password"
    echo "  --mysql-monitor-user   MySQL monitoring username"
    echo "  --mysql-monitor-pass   MySQL monitoring password"
    echo "  --backends             Comma-separated list of MySQL backends (host:port)"
    echo ""
    echo "Example:"
    echo "  $0 --mysql-admin-user root --mysql-admin-pass secret --mysql-monitor-user monitor --mysql-monitor-pass monitor123 --backends '192.168.1.10:3306,192.168.1.11:3306'"
    exit 1
}

log_info() {
    echo -e "${GREEN}[INFO]${NC} $1"
}

log_warn() {
    echo -e "${YELLOW}[WARN]${NC} $1"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}

check_root() {
    if [[ $EUID -ne 0 ]]; then
        log_error "This script must be run as root or with sudo"
        exit 1
    fi
}

detect_distro() {
    if [[ ! -f /etc/os-release ]]; then
        log_error "Cannot detect Linux distribution"
        exit 1
    fi
    
    . /etc/os-release
    
    case "$ID" in
        ubuntu|debian)
            PKG_MGR="apt"
            PKG_INSTALL="apt install -y"
            PKG_UPDATE="apt update"
            PKG_UPGRADE="apt upgrade -y"
            PROXYSQL_SERVICE="proxysql"
            ;;
        almalinux|rocky|centos|rhel|ol)
            PKG_MGR="dnf"
            PKG_INSTALL="dnf install -y"
            PKG_UPDATE="dnf check-update || true"
            PKG_UPGRADE="dnf update -y"
            PROXYSQL_SERVICE="proxysql"
            ;;
        amzn)
            PKG_MGR="yum"
            PKG_INSTALL="yum install -y"
            PKG_UPDATE="yum check-update || true"
            PKG_UPGRADE="yum update -y"
            PROXYSQL_SERVICE="proxysql"
            ;;
        fedora)
            PKG_MGR="dnf"
            PKG_INSTALL="dnf install -y"
            PKG_UPDATE="dnf check-update || true"
            PKG_UPGRADE="dnf update -y"
            PROXYSQL_SERVICE="proxysql"
            ;;
        *)
            log_error "Unsupported distribution: $ID"
            exit 1
            ;;
    esac
}

parse_args() {
    while [[ $# -gt 0 ]]; do
        case $1 in
            --mysql-admin-user)
                MYSQL_ADMIN_USER="$2"
                shift 2
                ;;
            --mysql-admin-pass)
                MYSQL_ADMIN_PASS="$2"
                shift 2
                ;;
            --mysql-monitor-user)
                MYSQL_MONITOR_USER="$2"
                shift 2
                ;;
            --mysql-monitor-pass)
                MYSQL_MONITOR_PASS="$2"
                shift 2
                ;;
            --backends)
                MYSQL_BACKEND_SERVERS="$2"
                shift 2
                ;;
            -h|--help)
                usage
                ;;
            *)
                log_error "Unknown parameter: $1"
                usage
                ;;
        esac
    done
    
    if [[ -z "$MYSQL_ADMIN_USER" || -z "$MYSQL_ADMIN_PASS" || -z "$MYSQL_MONITOR_USER" || -z "$MYSQL_MONITOR_PASS" || -z "$MYSQL_BACKEND_SERVERS" ]]; then
        log_error "All required parameters must be provided"
        usage
    fi
}

update_system() {
    log_info "[1/6] Updating system packages..."
    $PKG_UPDATE
    $PKG_UPGRADE
}

install_proxysql_debian() {
    log_info "[2/6] Installing ProxySQL 2.6 (Debian-based)..."
    
    # Install required packages
    $PKG_INSTALL wget gnupg lsb-release
    
    # Add repository key
    wget -O - 'https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/repo_pub_key' | apt-key add -
    
    # Add repository
    echo "deb https://repo.proxysql.com/ProxySQL/proxysql-2.6.x/$(lsb_release -sc)/ ./" > /etc/apt/sources.list.d/proxysql.list
    chmod 644 /etc/apt/sources.list.d/proxysql.list
    
    # Update and install
    $PKG_UPDATE
    $PKG_INSTALL proxysql2
}

install_proxysql_rhel() {
    log_info "[2/6] Installing ProxySQL 2.6 (RHEL-based)..."
    
    # Create repository file
    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
    
    chmod 644 /etc/yum.repos.d/proxysql.repo
    
    # Install ProxySQL
    $PKG_INSTALL proxysql2
}

install_proxysql() {
    if [[ "$PKG_MGR" == "apt" ]]; then
        install_proxysql_debian
    else
        install_proxysql_rhel
    fi
}

configure_proxysql() {
    log_info "[3/6] Configuring ProxySQL..."
    
    # Create backup of original config
    if [[ -f /etc/proxysql.cnf ]]; then
        cp /etc/proxysql.cnf /etc/proxysql.cnf.backup
    fi
    
    # Create ProxySQL configuration
    cat > /etc/proxysql.cnf << EOF
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"

admin_variables=
{
    admin_credentials="admin:admin;radmin:radmin"
    mysql_ifaces="127.0.0.1:6032"
    refresh_interval=2000
    web_enabled=true
    web_port=6080
}

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.25"
    connect_timeout_server=3000
    monitor_username="${MYSQL_MONITOR_USER}"
    monitor_password="${MYSQL_MONITOR_PASS}"
    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
}
EOF
    
    chmod 600 /etc/proxysql.cnf
    chown proxysql:proxysql /etc/proxysql.cnf
}

start_and_enable_service() {
    log_info "[4/6] Starting and enabling ProxySQL service..."
    
    systemctl daemon-reload
    systemctl enable $PROXYSQL_SERVICE
    systemctl start $PROXYSQL_SERVICE
    
    # Wait for service to start
    sleep 5
}

configure_backends() {
    log_info "[5/6] Configuring MySQL backend servers..."
    
    # Wait for ProxySQL admin interface to be ready
    for i in {1..30}; do
        if mysql -h 127.0.0.1 -P 6032 -u admin -padmin -e "SELECT 1;" &>/dev/null; then
            break
        fi
        sleep 1
    done
    
    # Configure backend servers
    IFS=',' read -ra BACKENDS <<< "$MYSQL_BACKEND_SERVERS"
    for backend in "${BACKENDS[@]}"; do
        IFS=':' read -ra BACKEND_PARTS <<< "$backend"
        host="${BACKEND_PARTS[0]}"
        port="${BACKEND_PARTS[1]:-3306}"
        
        mysql -h 127.0.0.1 -P 6032 -u admin -padmin -e "
            INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,status) VALUES
            (0,'${host}',${port},1000,'ONLINE');
        "
    done
    
    # Configure users
    mysql -h 127.0.0.1 -P 6032 -u admin -padmin -e "
        INSERT INTO mysql_users(username,password,default_hostgroup) VALUES
        ('${MYSQL_ADMIN_USER}','${MYSQL_ADMIN_PASS}',0);
    "
    
    # Configure query rules for read/write split
    mysql -h 127.0.0.1 -P 6032 -u admin -padmin -e "
        INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) VALUES
        (1,1,'^SELECT.*FOR UPDATE$',0,1),
        (2,1,'^SELECT',0,1);
    "
    
    # Load configuration to runtime
    mysql -h 127.0.0.1 -P 6032 -u admin -padmin -e "
        LOAD MYSQL SERVERS TO RUNTIME;
        SAVE MYSQL SERVERS TO DISK;
        LOAD MYSQL USERS TO RUNTIME;
        SAVE MYSQL USERS TO DISK;
        LOAD MYSQL QUERY RULES TO RUNTIME;
        SAVE MYSQL QUERY RULES TO DISK;
    "
}

configure_firewall() {
    log_info "[6/6] Configuring firewall..."
    
    if command -v firewall-cmd &> /dev/null; then
        # RHEL-based systems
        firewall-cmd --permanent --add-port=6033/tcp --quiet || true
        firewall-cmd --permanent --add-port=6032/tcp --quiet || true
        firewall-cmd --reload --quiet || true
    elif command -v ufw &> /dev/null; then
        # Ubuntu systems
        ufw allow 6033/tcp --quiet || true
        ufw allow 6032/tcp --quiet || true
    fi
}

verify_installation() {
    log_info "Verifying ProxySQL installation..."
    
    # Check service status
    if ! systemctl is-active --quiet $PROXYSQL_SERVICE; then
        log_error "ProxySQL service is not running"
        return 1
    fi
    
    # Check if ProxySQL is listening on correct ports
    if ! netstat -tlnp | grep -q ":6033.*proxysql"; then
        log_error "ProxySQL is not listening on port 6033"
        return 1
    fi
    
    # Test admin connection
    if ! mysql -h 127.0.0.1 -P 6032 -u admin -padmin -e "SELECT 1;" &>/dev/null; then
        log_error "Cannot connect to ProxySQL admin interface"
        return 1
    fi
    
    log_info "ProxySQL installation completed successfully!"
    log_info "MySQL Proxy: 127.0.0.1:6033"
    log_info "Admin Interface: 127.0.0.1:6032 (admin/admin)"
    log_info "Web Interface: http://$(hostname -I | awk '{print $1}'):6080"
}

main() {
    check_root
    detect_distro
    parse_args "$@"
    
    update_system
    install_proxysql
    configure_proxysql
    start_and_enable_service
    configure_backends
    configure_firewall
    verify_installation
    
    trap - ERR
}

main "$@"

Review the script before running. Execute with: bash install.sh

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

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

Prerequisites

What this solves

Step-by-step installation

Update system packages

Install ProxySQL 2.6

Create ProxySQL admin user

Configure ProxySQL main settings

Set correct permissions for configuration

Create MySQL monitor user on backend servers

Start and enable ProxySQL service

Configure connection pool settings

Configure advanced query routing rules

Configure health check settings

Set up ProxySQL stats database

Configure log rotation

Create ProxySQL monitoring script

ProxySQL monitoring configuration

Function to log messages

Function to send alert

Check ProxySQL service status

Check admin interface connectivity

Check backend server status

Check connection pool utilization

Check query response time

Create systemd timer for monitoring

Verify your setup

Verify admin interface connection

Test application connection through ProxySQL

Check connection pool status

Verify query routing rules

Check backend server health

Monitor query statistics

Configure SSL encryption

Enable SSL for backend connections

Configure client SSL certificates

Common issues

Monitor ProxySQL performance

Next steps

Running this in production?

Related tutorials

Implement ClickHouse backup automation with compression and S3 integration

Benchmark database performance with sysbench and fio integration

Configure MySQL binary log backup and point-in-time recovery

Don't want to manage this yourself?