ProxySQL’s three-layer configuration model makes changes safe and atomic, but every edit requires explicit promotion through two transitions. A missed step creates silent drift between what is staged (MEMORY), what is active (RUNTIME), and what persists on disk (DISK). The drift produces no error, no log entry, and no metric. The first visible symptom is often a restart that reverts to a stale configuration.

Two failure patterns dominate. First: an operator edits a MEMORY table but forgets LOAD ... TO RUNTIME. The change sits staged but inactive. The admin interface accepted it without complaint, so the operator assumes it took effect. Later, a restart reloads from DISK and the unsaved MEMORY change is discarded. Second: the operator loads to RUNTIME (the change takes effect) but forgets SAVE ... TO DISK. Everything works until a restart, and the change vanishes. Because RUNTIME matched MEMORY during that window, there was no signal that DISK was behind. Neither form of drift has a native ProxySQL metric. Detection requires comparing MEMORY tables against RUNTIME tables directly.

Config layers and drift types

ProxySQL stores configuration in three layers:

  • MEMORY: the main schema tables (mysql_servers, mysql_users, mysql_query_rules, global_variables, etc.). Edits via the admin interface land here. Contents are editable but not active.
  • RUNTIME: the runtime_* mirror tables (runtime_mysql_servers, runtime_mysql_users, etc.). These reflect what ProxySQL is actually using to route traffic, authenticate clients, and manage connection pools. Read-only.
  • DISK: the SQLite database file loaded on startup. If a change was never saved here, it does not survive a restart.

Drift falls into two categories:

  1. MEMORY differs from RUNTIME: an edit was made but not loaded. The change is staged but inactive.
  2. RUNTIME differs from DISK: a change was loaded and is active, but was never saved. It will be lost on restart.

Both forms are invisible under normal operation. No warning log, no error counter, no stats_mysql_global variable reports drift.

flowchart TD
    A["Edit MEMORY"] -->|LOAD TO RUNTIME| B["RUNTIME active"]
    B -->|SAVE TO DISK| C["DISK persisted"]
    C -->|survives restart| D["No drift"]
    A -->|forgot LOAD| E["DRIFT: staged\nbut inactive"]
    B -->|forgot SAVE| F["DRIFT: active\nbut not durable"]
    F -->|restart| G["config reverts\nsilently"]

Common causes

CauseWhat it looks likeFirst thing to check
Forgot LOAD ... TO RUNTIMEBehavior unchanged after edit. MEMORY table shows new value, RUNTIME table shows old.CHECKSUM TABLE mysql_servers; CHECKSUM TABLE runtime_mysql_servers;
Forgot SAVE ... TO DISKChange is active now. After restart, behavior reverts. RUNTIME was correct, DISK was stale.Compare RUNTIME tables against disk state after any restart.
Partial module LOADOperator loaded servers but forgot users, or loaded users but forgot query rules. Mixed drift across config modules.Check all modules independently. Each has its own LOAD and SAVE command.
Cluster auto-save maskingIn ProxySQL Cluster, admin-cluster_mysql_users_save_to_disk defaults to true. A user added to MEMORY and loaded to RUNTIME gets auto-saved to DISK during cluster sync, creating a false sense that explicit SAVE is unnecessary.Check admin-cluster_mysql_*_save_to_disk variable values.

Diagnosing drift

All commands connect to the admin interface on port 6032. Commands below use -p<password> for clarity; in production, prefer --defaults-file or MYSQL_PWD to avoid exposing credentials in the process list.

Step 1: check for recent restarts

mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name = 'ProxySQL_Uptime';"

A recent restart with unexpected behavior changes is the classic signature of RUNTIME-to-DISK drift that surfaced when DISK was loaded.

Step 2: compare MEMORY and RUNTIME checksums

mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "CHECKSUM TABLE mysql_servers; CHECKSUM TABLE runtime_mysql_servers;"

Repeat for each config module:

mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "CHECKSUM TABLE mysql_users; CHECKSUM TABLE runtime_mysql_users;"
mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "CHECKSUM TABLE mysql_query_rules; CHECKSUM TABLE runtime_mysql_query_rules;"

If checksums match for a module, MEMORY and RUNTIME are in sync. If they differ, there is an unapplied change in MEMORY. The standard modules to check: mysql_servers, mysql_users, mysql_query_rules, global_variables.

Step 3: find specific row differences

There is no built-in diff command. Use targeted queries:

# Find servers in MEMORY that are not in RUNTIME (new additions not yet loaded)
mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "SELECT hostgroup_id, hostname, port FROM mysql_servers
      WHERE (hostgroup_id, hostname, port) NOT IN (
        SELECT hostgroup_id, hostname, port FROM runtime_mysql_servers
      );"

Step 4: check cluster sync state (if applicable)

mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "SELECT * FROM stats_proxysql_servers_checksums;"

Checksum mismatches between cluster peers indicate config divergence. Each node may be routing traffic differently.

Step 5: check cluster auto-save behavior

mysql -u admin -p<password> -h 127.0.0.1 -P 6032 \
  -e "SELECT variable_name, variable_value FROM global_variables
      WHERE variable_name LIKE 'admin-cluster%save_to_disk';"

If these are true (the default), the cluster sync mechanism automatically saves loaded config to DISK, which can mask missing explicit SAVE steps.

Step 6: trace admin interface access

ss -tnp | grep ':6032'

This shows currently active admin connections only, not historical sessions. To trace past access, check ProxySQL admin audit logs if configured.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
CHECKSUM TABLE comparison (MEMORY vs RUNTIME)Directly detects unapplied changes. No ProxySQL metric exposes this.Checksums differ for any config module.
ProxySQL_UptimeIdentifies restart events. Post-restart behavior changes point to RUNTIME-DISK drift that surfaced.Sudden drop to near-zero.
stats_proxysql_servers_checksums (cluster only)Shows config sync state between cluster peers.Checksum mismatch between nodes.
Servers_table_versionIncrements when LOAD MYSQL SERVERS TO RUNTIME executes. An unexpected increment means someone loaded a change.Version increments outside planned maintenance windows.
Post-restart error pattern changesIf errors, auth failures, or routing behavior shift immediately after a restart, the previous RUNTIME config may have never been saved to DISK.New error types, different backend distribution, or changed auth failure rates appearing right after uptime resets.
admin-checksum_* variables (2.x+)Enable checksum computation for cluster sync. Can indicate drift when checksums diverge.Checksum mismatch in cluster view.

Fixes

MEMORY has changes that were never loaded

The change is staged but inactive. Activate it:

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

Load only the modules that have drift. After confirming the change is correct in RUNTIME, persist it:

SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

RUNTIME has changes that were never saved to DISK

This is only discoverable after a restart has already reverted the config. The fix is to re-apply the change through the full cycle: edit MEMORY, LOAD TO RUNTIME, then SAVE TO DISK. There is no way to recover the lost RUNTIME state from before the restart.

The MEMORY change was wrong and should be discarded

Reset MEMORY to match the last-saved DISK state:

LOAD MYSQL SERVERS FROM DISK;
LOAD MYSQL USERS FROM DISK;

This loads the persisted DISK state back into MEMORY, overwriting the staged edit. Then LOAD ... TO RUNTIME to make RUNTIME match.

Warning: LOAD ... FROM DISK overwrites unsaved changes in MEMORY. Verify that the MEMORY changes you are discarding are truly unwanted before running this.

Cluster auto-save interference

If admin-cluster_mysql_users_save_to_disk or admin-cluster_mysql_servers_save_to_disk is true, cluster sync automatically saves loaded config to DISK. This masks the SAVE step and creates a false sense that changes persist without explicit SAVE. To enforce explicit SAVE control:

SET admin-cluster_mysql_users_save_to_disk = 'false';
SET admin-cluster_mysql_servers_save_to_disk = 'false';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

Prevention

Build an automated drift check. The core check is a CHECKSUM TABLE comparison between MEMORY and RUNTIME tables for each config module. Run it on a schedule and alert if checksums differ:

#!/bin/bash
# Drift detector: compare MEMORY vs RUNTIME checksums for each config module
# Run via cron or a scheduled job; alert if output is non-empty
ADMIN="mysql -u admin -p<password> -h 127.0.0.1 -P 6032 -N -B"

for mod in mysql_servers mysql_users mysql_query_rules; do
  mem=$($ADMIN -e "CHECKSUM TABLE $mod;" | awk '{print $2}')
  run=$($ADMIN -e "CHECKSUM TABLE runtime_$mod;" | awk '{print $2}')
  [ "$mem" != "$run" ] && echo "DRIFT: $mod MEMORY=$mem RUNTIME=$run"
done

If the output is empty, no drift exists. If any line appears, MEMORY has an unapplied change.

Enforce the full workflow. Every config change through the admin interface must follow: edit MEMORY, LOAD TO RUNTIME, verify behavior, SAVE TO DISK. Treat SAVE as non-optional.

Audit config changes. The admin interface accepts any valid SQL without warning. Restrict admin access to localhost only via admin-mysql_ifaces and log who connects.

Watch for restart-triggered regressions. Correlate any ProxySQL restart with immediate changes in error rates, backend status distribution, or authentication patterns. A restart that loads stale DISK config will change behavior even though no operator made a recent change. The signature: runtime_mysql_servers matched mysql_servers before the restart, but both differed from DISK.

How Netdata helps

Netdata does not directly detect MEMORY-vs-RUNTIME table drift because no ProxySQL metric exposes it. The most effective approach is to run the CHECKSUM TABLE comparison as a scheduled custom check alongside Netdata’s ProxySQL collector, and use Netdata’s per-second metrics to validate that post-restart behavior matches expectations.

Where Netdata adds value:

  • ProxySQL_Uptime tracking makes restart events immediately visible. A drop to near-zero followed by behavior changes points to RUNTIME-DISK drift surfacing.
  • Per-second metric collection captures the exact moment behavior shifts after a restart. Correlating error rate spikes, backend status changes, and auth failure patterns with the uptime reset helps confirm that drift was the cause.
  • Backend status and connection pool metrics reveal routing differences from stale config loaded on restart. If a backend that was ONLINE is missing after restart, the DISK config may not include it.
  • Cluster checksum monitoring (where available) surfaces config divergence between ProxySQL peers before it causes split-brain routing.