Skip to content

ProxySQL — installing and operating the MySQL router

How we deploy ProxySQL in front of a MySQL or MariaDB topology, configure hostgroups and users, and route reads to replicas without changing application code.

ProxySQL is the workhorse of MySQL routing — connection pool, query router, failover detector, and operational lever all in one daemon. This article is the install and bring-up we follow when a client needs ProxySQL in front of MySQL or MariaDB to spread reads across replicas, hide failover from the application, and give the operator a place to throttle, mirror, or rewrite queries without redeploying code. We will install the package, define hostgroups, configure users, and verify a query actually lands on the host we expect.

How to verify

ProxySQL exposes two interfaces — the admin interface on 6032 for configuration via SQL, and the client interface on 6033 for traffic. Both speak the MySQL protocol.

sudo systemctl status proxysql
ss -ltnp | grep -E ':(6032|6033)\b'
mysql -h 127.0.0.1 -P 6032 -u admin -padmin -e "SELECT * FROM runtime_mysql_servers;"
mysql -h 127.0.0.1 -P 6033 -u app -p -e "SELECT @@hostname;"
mysql -h 127.0.0.1 -P 6032 -u admin -padmin -e "SELECT * FROM stats_mysql_connection_pool;"

A backend in ONLINE state with non-zero ConnUsed is serving traffic. OFFLINE_SOFT is shedding new connections gracefully; SHUNNED is hard-quarantined after repeated failures.

What’s happening

ProxySQL’s data model lives in three layers. Memory is what you edit interactively — INSERT, UPDATE, DELETE against the mysql_servers, mysql_users, mysql_query_rules tables. Runtime is what the proxy actually uses to make decisions — promoted from memory by LOAD MYSQL SERVERS TO RUNTIME. Disk is what survives restart — flushed by SAVE MYSQL SERVERS TO DISK. A change you forget to LOAD does not take effect; a change you forget to SAVE evaporates on restart. Every config change is a two-step.

Backends live in hostgroups. A hostgroup is a numbered pool of MySQL servers — by convention, hostgroup 0 is the writeable primary, hostgroup 1 is the read replicas. A query rule matches incoming SQL and routes it to a hostgroup; a rule that matches ^SELECT and routes to hostgroup 1 is the entire read/write split in one line. Users authenticate against ProxySQL using credentials defined in mysql_users; the user has a default_hostgroup which is where queries land in the absence of a more specific rule.

For replication topology awareness, ProxySQL ships mysql_replication_hostgroups — declare which hostgroup is “writer” and which is “reader”, and ProxySQL queries each backend’s read_only variable on a schedule. A backend that flips to read_only=ON moves to the reader hostgroup; the one that flips to OFF moves to the writer hostgroup. The proxy hides the failover from the client.

The procedure

  1. Install ProxySQL from the official repository — the 2.x line is the production target.

    curl -fsSL 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 proxysql
    sudo systemctl enable proxysql
    sudo systemctl start proxysql
  2. Change the admin password before anything else — the default admin:admin is in every script kiddie’s toolkit.

    -- mysql -h 127.0.0.1 -P 6032 -u admin -padmin
    UPDATE global_variables SET variable_value='admin:STRONG_ADMIN_PASS'
      WHERE variable_name='admin-admin_credentials';
    LOAD ADMIN VARIABLES TO RUNTIME;
    SAVE ADMIN VARIABLES TO DISK;
  3. Define the backend hostgroups — writers in 10, readers in 20.

    INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) VALUES
      (10, '10.0.0.11', 3306, 200),
      (20, '10.0.0.12', 3306, 200),
      (20, '10.0.0.13', 3306, 200);
    LOAD MYSQL SERVERS TO RUNTIME;
    SAVE MYSQL SERVERS TO DISK;
  4. Declare the replication hostgroup pair so ProxySQL handles failover automatically based on read_only.

    INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment)
      VALUES (10, 20, 'production replication');
    LOAD MYSQL SERVERS TO RUNTIME;
    SAVE MYSQL SERVERS TO DISK;
  5. Create the monitor user on the backend with REPLICATION CLIENT so ProxySQL can read read_only.

    -- on the MySQL primary
    CREATE USER 'monitor'@'%' IDENTIFIED BY 'MON_PASS';
    GRANT REPLICATION CLIENT, SELECT ON *.* TO 'monitor'@'%';

    Then tell ProxySQL about it:

    UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
    UPDATE global_variables SET variable_value='MON_PASS' WHERE variable_name='mysql-monitor_password';
    LOAD MYSQL VARIABLES TO RUNTIME;
    SAVE MYSQL VARIABLES TO DISK;
  6. Add the application user and let it default to the writer hostgroup — query rules will peel reads off later.

    INSERT INTO mysql_users (username, password, default_hostgroup, max_connections)
      VALUES ('app', 'APP_PASS', 10, 1000);
    LOAD MYSQL USERS TO RUNTIME;
    SAVE MYSQL USERS TO DISK;
  7. Add the read/write split rule — any SELECT not inside a transaction goes to readers.

    INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
      VALUES (10, 1, '^SELECT.*FOR UPDATE', 10, 1),
             (20, 1, '^SELECT', 20, 1);
    LOAD MYSQL QUERY RULES TO RUNTIME;
    SAVE MYSQL QUERY RULES TO DISK;
  8. Confirm reads land on a replica and writes land on the primary.

    mysql -h 127.0.0.1 -P 6033 -u app -pAPP_PASS -e "SELECT @@hostname;"
    mysql -h 127.0.0.1 -P 6033 -u app -pAPP_PASS -e "INSERT INTO test.routing(t) VALUES(NOW());"
    mysql -h 127.0.0.1 -P 6032 -u admin -pSTRONG_ADMIN_PASS \
      -e "SELECT hostgroup, srv_host, ConnUsed, Queries FROM stats_mysql_connection_pool;"

Common pitfalls

  • Forgetting LOAD ... TO RUNTIME — your INSERT ran fine and changed nothing. ProxySQL reads from runtime, not memory.
  • Default admin password in production — change it before exposing 6032 to anything beyond localhost.
  • Replication hostgroups without a monitor user — ProxySQL cannot detect read_only changes, both backends sit in the writer hostgroup, and replicas can take writes the application sends them.
  • Routing transactions to readers — SET autocommit=0 followed by SELECT is a write in everything but name; the rule for FOR UPDATE is necessary but not sufficient. Sticky transactions (transaction_persistent=1 on the user) keep multi-statement transactions on the writer.
  • No mysql_query_rules.apply=1 — without apply=1, the rule routes but ProxySQL keeps evaluating later rules and the destination can be overridden by a less specific match.

ProxySQL gives the operator a SQL-level handle on a database fleet — the place we add rate limits, mirror traffic to a canary, throttle one tenant’s noisy query, all without an application deploy. In the engagements we run, ProxySQL sits under our managed operations practice: hostgroups, rules, and users are tracked in version control, the admin interface is locked down to the management network, and stats_mysql_query_digest is mined for the queries worth shaping. For the broader pattern of operating database fleets like this, see /en/services/managed-operations/.