MySQL Router compatibility

The mysql_router plugin registers ProxySQL with InnoDB Cluster and uses cluster metadata and health to maintain servers, application accounts, hostgroups, and rules. A hostgroup groups backend servers for routing.

This plugin is available from source in the published ProxySQL 4.0.12 prerelease. It is not included in downloadable release packages. It supports InnoDB Cluster Metadata 2.2 with single-primary Group Replication and supported MySQL Shell-managed asynchronous read replicas. It does not support multi-primary Group Replication, InnoDB ReplicaSet, ClusterSet, MySQL X Router endpoints, or takeover of an existing MySQL Router installation. Routing Guidelines are not imported; use ProxySQL query rules for application policy.

Build and install

The following steps use Ubuntu 24.04 with systemd. Install the build tools and libraries; other distributions need their corresponding development packages:

sudo apt-get update
sudo apt-get install -y \
  build-essential git cmake autoconf automake libtool pkg-config \
  bison flex python3 perl wget curl unzip zstd \
  libssl-dev libgnutls28-dev libmysqlclient-dev libunwind-dev \
  uuid-dev libncurses-dev libicu-dev libevent-dev libtirpc-dev zlib1g-dev

Build the core and plugin together from a fresh source tree, using the same release and build mode:

git clone --branch v4.0.12 https://github.com/sysown/proxysql.git proxysql-router
cd proxysql-router
make PROXYSQL40=1 -j2

The outputs are src/proxysql and plugins/mysql_router/proxysql_mysql_router.so. Loading a plugin does not upgrade the core.

Install on a new host

For a fresh installation, install the binary, built plugins, sample configuration, service account, and service files:

sudo make PROXYSQL40=1 install

This replaces /etc/proxysql.cnf. For an existing deployment, follow the upgrade instructions instead.

The service runs as proxysql. Prepare its data directory and configuration permissions:

sudo install -d -o proxysql -g proxysql -m 0750 /var/lib/proxysql
sudo chown root:proxysql /etc/proxysql.cnf
sudo chmod 0640 /etc/proxysql.cnf

Before bootstrap or first startup, edit /etc/proxysql.cnf. Keep the top-level paths aligned with the installed service:

datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
plugins=("/usr/lib/proxysql/plugins/proxysql_mysql_router.so")

If plugins already exists, add the Router path to that list. In the existing admin_variables section, replace the default Admin password and bind Admin locally:

admin_credentials="admin:REPLACE_WITH_A_STRONG_ADMIN_PASSWORD"
mysql_ifaces="127.0.0.1:6032"

Upgrade an existing installation

Drain application work, stop ProxySQL cleanly, and back up its configuration file, database, and secret key before replacing files. Preserve the existing data directory, permissions, and service settings. Install the matching core and every plugin your configuration loads from the same release and build mode. For the core and Router library at standard paths:

sudo install -m 0755 src/proxysql /usr/bin/proxysql
sudo install -d /usr/lib/proxysql/plugins
sudo install -m 0755 plugins/mysql_router/proxysql_mysql_router.so \
  /usr/lib/proxysql/plugins/proxysql_mysql_router.so

Install matching versions of any other loaded plugins before restarting. Add the Router library to the configuration’s plugins list if it is new. Do not use the fresh-install target to preserve an existing configuration.

Bootstrap the cluster registration

Use a bootstrap database account with permission to read cluster metadata, register the Router, and create and grant privileges to its metadata service account. This is separate from the application accounts that will connect through ProxySQL.

For an existing deployment, substitute its actual data directory throughout. Replace the seed, account, secret path, Router name, and CA file. The proxysql account must be able to read both files. Run bootstrap as that account:

sudo -u proxysql bash <<'BOOTSTRAP'
exec 3< /run/secrets/mysql-router-bootstrap-password
proxysql -f -c /etc/proxysql.cnf -D /var/lib/proxysql \
  --load-plugin mysql_router \
  --bootstrap [email protected]:3306 \
  --bootstrap-password-fd 3 \
  --router-name proxysql-router-1 \
  --conf-bind-address 127.0.0.1 \
  --ssl-mode VERIFY_IDENTITY \
  --ssl-ca /etc/proxysql/cluster-ca.pem
bootstrap_status=$?
exec 3<&-
exit "$bootstrap_status"
BOOTSTRAP

Descriptor 3 supplies the password; the shell closes it and returns bootstrap’s exit status. Without --bootstrap-password-fd, bootstrap prompts on a terminal. Passwords in user:password@host are rejected. Keep -f for foreground execution and the actual bootstrap result.

Bootstrap registers the instance, creates or validates its service account, and persists the initial configuration. It then exits without serving client traffic. After successful bootstrap, start the configured service:

sudo systemctl daemon-reload
sudo systemctl start proxysql

The service reads /etc/proxysql.cnf, using the same data directory and plugin. The example binds Router endpoints locally. Configure application TLS and network access before exposing them; the generated default is all interfaces.

Bootstrap can change cluster registrations and accounts even if interrupted. Inspect an unsuccessful run before retrying. Use --force only for intended registration replacement, and --replace-topology for a different managed topology.

Back up the data directory’s configuration database, proxysql.db, together with proxysql-plugin-secrets.key through your protected recovery procedure. The key decrypts the saved service credential; a database backup alone cannot recover it.

Metadata connection TLS

Bootstrap’s TLS settings persist for subsequent metadata, health, and account-sync connections. They do not configure application or general backend-connection TLS.

Both verification modes require an explicit --ssl-ca file or --ssl-capath directory; they do not fall back to system trust. The certificate must match each endpoint identity used for metadata connections. Supply --ssl-cert and --ssl-key together for a client certificate. Optional --ssl-cipher, --ssl-crl, and --ssl-crlpath further configure TLS.

With REQUIRED, Connector/C 3.3 can authenticate before ProxySQL detects that a server offered no TLS and closes the connection. The server has already received the username and password challenge response; a cleartext authentication plugin can expose the password itself. This mode also does not authenticate the server. Choose VERIFY_CA or VERIFY_IDENTITY for an untrusted network path; those modes reject a server without TLS before authentication.

Endpoints

Fast-forward relays subsequent protocol traffic through the chosen backend connection. Authentication alone does not trigger the transition. Compressed frontend sessions and transitions with queued client packets are rejected; clients using compression or pipelining should use an appropriate query-aware endpoint. A topology change does not move an established forwarded connection. Applications need their usual reconnect and transaction-recovery behavior.

The reader endpoint selects a destination; it does not enforce SQL read-only permissions. Database grants and server state determine what it can execute.

On 6450, baseline rules send recognized locking reads and certain unsafe SELECT forms to the writer before routing ordinary SELECT, SHOW, DESCRIBE, EXPLAIN, and WITH prefixes to readers. Imported users default to the writer hostgroup. Review application consistency needs: an immediate read after a write or a function with side effects can need writer routing even when it looks like a read.

To override a baseline, inspect the allocated IDs first:

SELECT role,hostgroup_id FROM runtime_mysql_router_hostgroups;
SELECT rule_id,proxy_port,destination_hostgroup,attributes,comment
FROM runtime_mysql_query_rules
WHERE comment LIKE 'mysql_router:%'
ORDER BY rule_id;

Add your own narrowly scoped rule with an unused, earlier ID and apply=1. Set its destination using the inspected hostgroup, and omit the switch_to_fast_forward action if normal query processing is required. Reconciliation preserves unrelated operator rules; editing a managed baseline directly allows the plugin to restore it.

--conf-base-port 7446 selects writer 7446, reader 7447, and split 7450. Listener address or port changes take effect at restart. Reconciliation can update routing and stage listener configuration, but cannot rebind live ports.

Network partitions

The reconciler checks other known members before accepting a member’s nonquorate view. A reachable minority member may report itself online while seeing peers as unreachable. If another member reports quorum, that view is preferred. Only when no reachable member reports quorum does routing use the cluster’s unreachable_quorum_allowed_traffic policy.

This search also applies to health-only fallback: when metadata reads fail, the plugin can combine its last validated topology with current member health. It cannot discover metadata changes through that fallback, and account synchronization waits for metadata access to recover.

At startup, Router listener gates remain closed until a complete live configuration is available. A confirmed missing registration closes them. An ordinary refresh failure does not automatically close gates that were already open. Check routing state and application connectivity together; neither a listening port nor a metadata warning alone establishes readiness.

Status and diagnostics

Connect to the ProxySQL Admin interface and inspect:

SELECT * FROM runtime_mysql_router_status;
SELECT * FROM runtime_mysql_router_topology;
SELECT * FROM runtime_mysql_router_hostgroups;
SELECT * FROM runtime_mysql_router_users;
SELECT * FROM stats_mysql_router_refresh ORDER BY refresh_id DESC LIMIT 20;
SELECT * FROM stats_mysql_router_errors ORDER BY last_seen DESC;

The status view reports metadata availability, registration, gate readiness, topology and user generations, staleness, collisions, and the last error. A closed gate rejects an accepted connection before creating a MySQL session or sending its handshake.

If topology is healthy but login fails, inspect the user view. Supported authentication plugins are caching_sha2_password and mysql_native_password. Locked or expired accounts become inactive. Conflicting or unsupported account policies are reported as unresolved; a previously managed row may be retained. An operator-owned username collision is reported without taking over that row.

Request an immediate topology and account refresh after correcting a problem:

MYSQL ROUTER RECONCILE;

This does not bootstrap an unconfigured instance or restart listeners. Keep independent operator policy in separate rows from the plugin’s managed hostgroups, rules, listeners, and users.