Hi! 👋 We are doing a big documentation refresh. Help us improve — what's missing or could be better? Let us know! Simply send an email or start a conversation in Google Groups!

MySQL X Protocol

Availability

ProxySQL 4.0 ships a dynamically loaded MySQL X Protocol plugin. It provides separate X Protocol listeners, Protobuf frame handling, MYSQL41 and PLAIN authentication, static route-to-hostgroup selection, per-thread backend connection pools, frontend and backend TLS paths, and live Admin statistics.

This plugin is separate from ProxySQL’s classic MySQL listener. It consumes the classic MySQL runtime user and server state, but it has its own opt-in users, routes, endpoint overrides, variables, listeners, runtime projections, and statistics.

Installation and Plugin Loading

The v4.0.10 source build creates plugins/mysqlx/ProxySQL_MySQLX_Plugin.so:

make -C plugins/mysqlx PROXYSQL40=1

Build the plugin in the same v4.0.10 tree and with the same feature flags and C++ toolchain as ProxySQL. Copy the resulting library to a protected location, add its absolute path to proxysql.cnf, and restart ProxySQL:

plugins = (
    "/usr/lib64/proxysql/ProxySQL_MySQLX_Plugin.so"
)

The plugin list is file-only startup configuration; there is no Admin command for loading the library into a running process. ProxySQL loads the plugin before Admin schema materialization, creates its tables, initializes its in-memory store, copies its disk tables into editable memory tables, installs runtime state, creates the worker pool, and binds active routes.

See ProxySQL Plugin Chassis for ABI, permissions, failure, and kill-switch details.

Configuration Model

The plugin follows ProxySQL’s normal layers:

DISK tables <-> editable mysqlx_* MEMORY tables <-> plugin-owned runtime state

The four editable tables exist in both the Admin in-memory database and the on-disk configuration database:

  • mysqlx_users
  • mysqlx_routes
  • mysqlx_backend_endpoints
  • mysqlx_variables

Their runtime_mysqlx_* counterparts exist only in the Admin database. They are read-only projections of plugin-owned state, rebuilt on demand before an Admin SELECT; they are not writable runtime storage. LOAD ... TO RUNTIME reads the editable table directly, while SAVE ... FROM RUNTIME dumps that state directly into the editable table.

At process start, the plugin copies all four disk tables to their editable memory tables and then installs them. An empty disk table therefore clears its memory counterpart. On a running process, each MySQL X LOAD command updates only its named slice of runtime state.

User and endpoint installation also consume core runtime projections. Run the core loads first whenever those sources change:

LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQLX USERS TO RUNTIME;
LOAD MYSQLX BACKEND ENDPOINTS TO RUNTIME;

Users

mysqlx_users opts an existing classic MySQL frontend identity into X Protocol and adds X-specific policy. Its exact v4.0.10 schema is:

CREATE TABLE mysqlx_users (
    username VARCHAR NOT NULL PRIMARY KEY,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    require_tls INT CHECK (require_tls IN (0,1)) NOT NULL DEFAULT 0,
    allowed_auth_methods VARCHAR NOT NULL DEFAULT '',
    default_route VARCHAR,
    policy_profile VARCHAR,
    backend_auth_mode VARCHAR NOT NULL DEFAULT 'mapped',
    backend_username VARCHAR,
    backend_password VARCHAR,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT ''
);

The runtime projection has the same columns and constraints under its own table name:

CREATE TABLE runtime_mysqlx_users (
    username VARCHAR NOT NULL PRIMARY KEY,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    require_tls INT CHECK (require_tls IN (0,1)) NOT NULL DEFAULT 0,
    allowed_auth_methods VARCHAR NOT NULL DEFAULT '',
    default_route VARCHAR,
    policy_profile VARCHAR,
    backend_auth_mode VARCHAR NOT NULL DEFAULT 'mapped',
    backend_username VARCHAR,
    backend_password VARCHAR,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT ''
);

It contains only active identities installed in the store and is refreshed before it is selected.

An X user must have both:

  1. an active, frontend=1 row in runtime_mysql_users, supplying the username, password, default hostgroup, and connection limit; and
  2. an active row in mysqlx_users, supplying the X opt-in and overlay settings.

Canonical-only and X-only users are omitted from the runtime X identity map. After changing mysql_users, load classic MySQL users before loading MySQL X users so runtime_mysql_users is current.

allowed_auth_methods is a comma-separated, case-insensitive allowlist of MYSQL41 and PLAIN; an empty string allows either implemented method. require_tls=1 requires proxy-terminated frontend TLS for that user. default_route is mandatory in practice for normal proxy-terminated sessions: authentication fails with error 4000 when it is empty. policy_profile is stored and projected but has no policy-engine behavior in v4.0.10.

Routes and Backend Endpoints

mysqlx_routes defines listeners and their destination hostgroups:

CREATE TABLE mysqlx_routes (
    name VARCHAR NOT NULL PRIMARY KEY,
    bind VARCHAR NOT NULL,
    destination_hostgroup INT NOT NULL,
    fallback_hostgroup INT,
    strategy VARCHAR NOT NULL DEFAULT 'first_available',
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT '',
    tls_mode VARCHAR CHECK (
        tls_mode IN ('inherit','disabled','preferred','required','passthrough')
    ) NOT NULL DEFAULT 'inherit'
);

The route runtime projection is:

CREATE TABLE runtime_mysqlx_routes (
    name VARCHAR NOT NULL PRIMARY KEY,
    bind VARCHAR NOT NULL,
    destination_hostgroup INT NOT NULL,
    fallback_hostgroup INT,
    strategy VARCHAR NOT NULL DEFAULT 'first_available',
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT '',
    tls_mode VARCHAR CHECK (
        tls_mode IN ('inherit','disabled','preferred','required','passthrough')
    ) NOT NULL DEFAULT 'inherit'
);

It is projected from the active route map. LOAD MYSQLX ROUTES TO RUNTIME also reconciles listeners: it binds new or changed active routes and removes deactivated or deleted listeners.

For proxy-terminated sessions, mysqlx_users.default_route chooses the route after frontend authentication. Both round_robin and the legacy spelling round_robin_with_fallback select endpoints round-robin; first_available and every other stored spelling select the first endpoint rather than producing a validation error. The plugin tries fallback_hostgroup when it is nonnegative and the destination hostgroup has no online endpoint, regardless of the strategy spelling. For TLS passthrough, no proxy-side identity exists, so the listener’s route name chooses the backend before the end-to-end TLS handshake.

The plugin derives candidate servers from online rows in runtime_mysql_servers, ordered by hostgroup, descending weight, hostname, and classic port. mysqlx_backend_endpoints overrides the X port and TLS flag for a matching (hostname, mysql_port) pair:

CREATE TABLE mysqlx_backend_endpoints (
    hostname VARCHAR NOT NULL,
    mysql_port INT NOT NULL,
    mysqlx_port INT NOT NULL DEFAULT 33060,
    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostname, mysql_port)
);

The endpoint runtime projection is:

CREATE TABLE runtime_mysqlx_backend_endpoints (
    hostname VARCHAR NOT NULL,
    mysql_port INT NOT NULL,
    mysqlx_port INT NOT NULL DEFAULT 33060,
    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostname, mysql_port)
);

It projects only the configured overrides. A server without an override uses X Protocol port 33060 and the use_ssl value from runtime_mysql_servers. Reload MySQL servers before MySQL X endpoints whenever the core topology changes.

Variables

The editable variables table is:

CREATE TABLE mysqlx_variables (
    variable_name VARCHAR NOT NULL PRIMARY KEY,
    variable_value VARCHAR NOT NULL DEFAULT ''
);

Its runtime projection is:

CREATE TABLE runtime_mysqlx_variables (
    variable_name VARCHAR NOT NULL PRIMARY KEY,
    variable_value VARCHAR NOT NULL DEFAULT ''
);

The released store recognizes exactly five rows:

VariableStore defaultv4.0.10 behavior
mysqlx_thread_pool_size4Read when the plugin starts; the created pool is clamped to 1–64 threads. A runtime LOAD does not resize an existing pool.
mysqlx_connect_timeout10000Stored, saved, and projected, but the released session path sets new backend connections to a hard-coded 10,000 ms. Changing this row does not change that path.
mysqlx_tls_modeDISABLEDStored, saved, and projected, but the released frontend session path does not consult it. Frontend behavior is described below.
mysqlx_tls_backend_modeas_clientRead for every new backend acquisition. Accepted values are disabled, preferred, required, and as_client (case-insensitive). An invalid value makes LOAD MYSQLX VARIABLES TO RUNTIME fail without installing the new set.
mysqlx_max_cached_connections_per_thread100Applied when worker threads are created. A runtime LOAD does not change the limit of existing workers.

The table accepts other names, but LOAD MYSQLX VARIABLES TO RUNTIME ignores them. SAVE MYSQLX VARIABLES TO MEMORY replaces the table with the five canonical rows, so it deletes all unknown rows. In particular, mysqlx_tls_cert, mysqlx_tls_key, and mysqlx_tls_ca are reserved but unwired: they are not certificate inputs, are not projected, and do not survive that SAVE operation.

Admin Commands

Each entity has four canonical commands. Replace <ENTITY> with USERS, ROUTES, BACKEND ENDPOINTS, or VARIABLES:

LOAD MYSQLX <ENTITY> TO RUNTIME;
SAVE MYSQLX <ENTITY> TO MEMORY;
LOAD MYSQLX <ENTITY> FROM DISK;
SAVE MYSQLX <ENTITY> TO DISK;

The runtime LOAD aliases are exactly:

LOAD MYSQLX <ENTITY> FROM MEMORY;
LOAD MYSQLX <ENTITY> FROM MEM;
LOAD MYSQLX <ENTITY> TO RUN;

The runtime SAVE aliases are exactly:

SAVE MYSQLX <ENTITY> TO MEM;
SAVE MYSQLX <ENTITY> FROM RUNTIME;
SAVE MYSQLX <ENTITY> FROM RUN;

The disk commands have no aliases. Matching is case-insensitive and whitespace-normalized, and a trailing semicolon is accepted.

TO RUNTIME installs the editable Admin table into plugin-owned state; it does not populate a runtime_mysqlx_* table. TO MEMORY and FROM RUNTIME dump plugin-owned state into the editable table; they do not read the runtime projection. Selecting a runtime projection refreshes it from the store immediately before the query.

LOAD ... FROM DISK and SAVE ... TO DISK atomically replace the destination table. An empty source clears the destination rather than leaving old rows behind. See Admin Commands for the full explicit command list.

Authentication

The plugin advertises and implements:

  • MYSQL41: challenge-response authentication using a 20-byte challenge. The classic mysql_users.password may be cleartext or a * plus 40-hex-character mysql_native_password hash.
  • PLAIN: accepted only over proxy-terminated TLS. The cleartext credential is compared through the same stored-hash derivation.

Per-user allowed_auth_methods and require_tls checks run after identity resolution. Normal sessions resolve default_route and an available backend before returning AuthenticateOk, so a missing route or backend fails authentication cleanly instead of opening a route-less session.

Backend authentication modes are:

ModeReleased behavior
mappedNormally reuses the frontend username and canonical mysql_users password. In the released selector, a nonempty overlay backend_username or backend_password is still preferred, so leave those fields null or empty for strict mapped behavior.
service_accountUses mysqlx_users.backend_username and backend_password; an empty field falls back to the mapped value.
pass_throughReserved and round-tripped, but not implemented. The plugin rejects the session with error 1045 instead of silently downgrading to mapped authentication.

The schema does not constrain backend_auth_mode; an unrecognized spelling is parsed as mapped.

TLS passthrough is different: ProxySQL cannot inspect the encrypted authentication exchange, so the client authenticates directly to the backend and the proxy-side user overlay is not involved.

Frontend and Backend TLS

Proxy-terminated frontend TLS

MySQL X workers use ProxySQL’s shared core SSL context, not certificate paths from mysqlx_variables. Configure the normal ProxySQL frontend certificate and key and use PROXYSQL RELOAD TLS when appropriate; see SSL Configuration.

The exact v4.0.10 route behavior is:

mysqlx_routes.tls_modeReleased behavior
disabledSuppresses TLS advertisement and rejects a client that requests TLS.
passthroughAdvertises TLS, forwards the client’s TLS capability request to the route’s backend, and then splices encrypted bytes end to end.
inherit, preferred, requiredAdvertise proxy-terminated TLS when the shared core SSL context exists. In v4.0.10 these three values otherwise follow the same path: inherit does not consult mysqlx_tls_mode, and required does not reject a client merely for remaining plaintext. Use per-user require_tls=1 when ProxySQL performs authentication and TLS is mandatory.

mysqlx_tls_mode is therefore a stored compatibility/reserved variable in this release, not an effective deployment-wide frontend policy control. The reserved mysqlx_tls_cert, mysqlx_tls_key, and mysqlx_tls_ca rows are also unwired.

Passthrough preserves the backend certificate, SNI, ALPN, and end-to-end encryption because ProxySQL never decrypts application traffic. It also disables proxy-side authentication, pooling, multiplexing, frame inspection, per-query routing, and query-level observability for that session. Only TCP-level session and byte visibility remains.

Proxy-to-backend TLS

mysqlx_tls_backend_mode is wired for normal proxy-terminated sessions:

ValueBackend action
disabledUse plaintext unless the endpoint override promotes the connection to TLS.
preferredRequest TLS; fall back on the same connection only when the backend returns X Protocol error 5001 for unavailable TLS. Other errors remain fatal.
requiredRequire TLS and fail rather than downgrade.
as_clientMirror the frontend connection’s encryption state; this is the default.

mysqlx_backend_endpoints.use_ssl=1 promotes that endpoint to TLS under any mode; it never demotes a TLS decision. When backend TLS is required but the shared SSL context is unavailable, the connection fails closed.

The pool key includes (hostgroup, user, schema, tls_active), preventing encrypted and plaintext backend connections from being mixed.

Routing and Connection Pooling

After authentication, a normal session stays on the hostgroup selected by its default_route. The plugin does not apply classic MySQL query rules and does not perform per-statement hostgroup routing. SQL, CRUD, prepared-statement, cursor, expect, and view frames are forwarded to the selected backend; connection capabilities, authentication, close, and compression negotiation are handled locally as needed.

Every worker owns an idle backend cache. A cached connection is reused only for the same hostgroup, user, schema, and TLS state, and only when it is healthy, idle, outside a transaction, and has no prepared statement. Otherwise the plugin opens and authenticates a new backend connection. The configured cache limit is per worker and is applied when workers are created.

Statistics and Process List

Both statistics tables live in the stats database and are on-demand projections. Selecting one refreshes it from current plugin state immediately before the query.

stats_mysqlx_routes has this exact schema:

CREATE TABLE stats_mysqlx_routes (
    name VARCHAR NOT NULL,
    destination_hostgroup INT NOT NULL,
    ConnOK INT NOT NULL DEFAULT 0,
    ConnERR INT NOT NULL DEFAULT 0,
    ConnUsed INT NOT NULL DEFAULT 0,
    Bytes_data_sent BIGINT NOT NULL DEFAULT 0,
    Bytes_data_recv BIGINT NOT NULL DEFAULT 0
);

ConnOK counts freshly established backend connections, ConnERR connection failures, and ConnUsed successful cache reuses. ConnUsed is not the number of currently active sessions. The byte counters track traffic recorded for the route; route counters survive a destination-hostgroup edit while the reported hostgroup metadata is updated.

stats_mysqlx_processlist has one row per active plugin session:

CREATE TABLE stats_mysqlx_processlist (
    username VARCHAR NOT NULL,
    route VARCHAR NOT NULL,
    worker_id INT NOT NULL,
    backend_host VARCHAR NOT NULL,
    backend_port INT NOT NULL,
    auth_mode VARCHAR NOT NULL,
    connection_state VARCHAR NOT NULL,
    bytes_in BIGINT NOT NULL DEFAULT 0,
    bytes_out BIGINT NOT NULL DEFAULT 0,
    session_age_ms BIGINT NOT NULL DEFAULT 0
);

Query the projections through the Admin interface:

SELECT * FROM runtime_mysqlx_users;
SELECT * FROM runtime_mysqlx_routes;
SELECT * FROM runtime_mysqlx_backend_endpoints;
SELECT * FROM runtime_mysqlx_variables;
SELECT * FROM stats_mysqlx_routes;
SELECT * FROM stats_mysqlx_processlist;

Verification

The following example assumes the plugin is loaded and the backend already exposes X Protocol on port 33060:

INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (0, '10.0.0.10', 3306, 100);
LOAD MYSQL SERVERS TO RUNTIME;

INSERT INTO mysql_users (username, password, default_hostgroup, active, frontend)
VALUES ('app', 'replace-me', 0, 1, 1);
LOAD MYSQL USERS TO RUNTIME;

INSERT INTO mysqlx_backend_endpoints
    (hostname, mysql_port, mysqlx_port, use_ssl)
VALUES ('10.0.0.10', 3306, 33060, 0);

INSERT INTO mysqlx_routes
    (name, bind, destination_hostgroup, strategy)
VALUES ('rw', '0.0.0.0:33060', 0, 'round_robin');

INSERT INTO mysqlx_users
    (username, allowed_auth_methods, default_route)
VALUES ('app', 'MYSQL41', 'rw');

LOAD MYSQLX BACKEND ENDPOINTS TO RUNTIME;
LOAD MYSQLX ROUTES TO RUNTIME;
LOAD MYSQLX USERS TO RUNTIME;

Confirm the installed state and listener activity:

SELECT * FROM runtime_mysqlx_users WHERE username='app';
SELECT * FROM runtime_mysqlx_routes WHERE name='rw';
SELECT * FROM runtime_mysqlx_backend_endpoints;
SELECT * FROM stats_mysqlx_routes WHERE name='rw';

Then connect with an X Protocol client, for example:

mysqlsh [email protected]:33060 --sql

After the runtime projections are correct and the client connection succeeds, persist both the classic inputs and the MySQL X overlays:

SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQLX BACKEND ENDPOINTS TO DISK;
SAVE MYSQLX ROUTES TO DISK;
SAVE MYSQLX USERS TO DISK;

SAVE MYSQL USERS TO DISK stores backend/frontend credential material in proxysql.db; restrict Admin access, database-file permissions, backups, and diagnostic output accordingly.

Limitations

The v4.0.10 MySQL X plugin has these explicit boundaries:

  • no classic MySQL query rules, query-policy engine, per-query routing, or query cache;
  • no ProxySQL Cluster synchronization for mysqlx_* tables;
  • no Group Replication notifications, metadata discovery, or automatic X topology refresh;
  • no implemented pass_through backend authentication;
  • no live plugin reload or unload;
  • policy_profile and the JSON attributes fields are stored for future extensions but do not activate a policy feature;
  • mysqlx_tls_mode, mysqlx_connect_timeout, and the reserved certificate rows do not control their advertised behavior in the released session path;
  • changing thread-pool size or per-thread cache size through a runtime LOAD does not reconfigure already-created workers.

TLS passthrough intentionally gives up proxy authentication, pooling, multiplexing, frame-level inspection, and query-level statistics.

Troubleshooting

  • Plugin tables are missing: confirm the absolute library path, permissions, v4.0.10 ABI build, and startup log. The library is loaded only at process start.
  • No listener is open: verify an active runtime route, a valid bind, and the result of LOAD MYSQLX ROUTES TO RUNTIME. Check for address-in-use errors.
  • User receives 1045: load classic MySQL users first, confirm the user exists in both runtime layers, check allowed_auth_methods and require_tls, and reject pass_through as unsupported.
  • User receives 4000/4001/4002: set default_route, confirm that route is active, then load online classic MySQL servers before MySQL X endpoints.
  • TLS is not advertised: configure the normal ProxySQL SSL context; mysqlx_tls_cert, mysqlx_tls_key, and mysqlx_tls_ca do not configure it.
  • A supposedly required route accepts plaintext: v4.0.10 does not enforce route-level required; use mysqlx_users.require_tls=1 for proxy-authenticated sessions or a passthrough route for backend-enforced end-to-end TLS.
  • Variable change appears ineffective: thread count and cache limits are startup-applied, connect timeout is hard-coded to 10 seconds, and frontend mysqlx_tls_mode is not consulted in this release.
  • Stats look stale: query the exact stats_mysqlx_routes or stats_mysqlx_processlist table to trigger its on-demand refresh.