The Too many connections error (1040) means MySQL or MariaDB has reached the number of connections it will accept. Raising max_connections may create headroom during an incident, but it does not fix a leak, an oversized pool, queries holding connections, or a memory-constrained VPS. Preserve an administrative session, capture evidence, and reduce pressure before changing the limit.
This guide covers MySQL 8.x and current MariaDB releases on systemd-based Linux systems. Unit names, paths, privileges, and variables can differ by package, container, or provider. The commands are documented examples checked statically; they were not run against a real server. MariaDB’s official guide to handling too many connections likewise starts from observing the limit and workload before sizing.
What each symptom suggests
| Symptom | Reading | Next check |
|---|---|---|
Threads_connected approaches max_connections while Threads_running stays low. | Many sessions are connected, but few are doing work. | Group by user, host, and COMMAND; calculate total pool capacity. |
| Both counters are high. | There is real concurrency, waiting, or slow work. | Inspect states, elapsed time, slow queries, and CPU/I/O load. |
Max_used_connections reached the limit during one spike. | It may be legitimate demand or a retry burst. | Correlate timestamps with traffic, deploys, and application errors. |
Many connections show Sleep. | This may be normal reuse or excessive retention; it does not prove a leak. | Compare age, creation rate, and pool settings. |
| The setting disappears after restart. | Only the in-memory global changed, or an ineffective file was edited. | Identify the product and effective configuration source. |
Before you intervene
You need provider console or local access, an already-open administrative session if one exists, a change window, and a recoverable backup. Keep a second system session open; do not close the only useful SQL connection. If this is your first incident, review what you control on a VPS.
Never pass a password as -pPASSWORD or paste one into history. Use socket authentication, an existing login path, or a protected option file. Do not publish SHOW FULL PROCESSLIST: hosts, databases, and SQL may be sensitive.
Prepare and test SQL administration before an incident. MariaDB reserves one connection above max_connections for an account with SUPER or CONNECTION ADMIN; some installations also support a preconfigured extra port. MySQL reserves one connection on the ordinary interface for CONNECTION_ADMIN or the deprecated SUPER. Its separate administrative interface exists only when admin_address was configured at startup and requires SERVICE_CONNECTION_ADMIN. Verify release, privileges, TLS/networking, and protected credentials; managed services may restrict these features. The MariaDB system variable reference documents its mechanisms.
A system console does not create a SQL slot. If you preserved no session and never tested reserved access, use the console to reduce or stop application traffic first and release a connection; do not restart the database blindly.
1. Confirm the product, limit, and current pressure
Run read-only queries first from the secured session. The version and version_comment prevent you from applying MySQL syntax to MariaDB. Max_used_connections is a high-water mark since startup or a status reset, not current concurrency. Connection_errors_max_connections records limit rejections where the release exposes it; if no row returns, check the available equivalent and server logs instead of treating it as zero.
SELECT VERSION() AS server_version, @@version_comment AS product_comment;
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS
WHERE Variable_name IN (
'Threads_connected',
'Threads_running',
'Max_used_connections',
'Connection_errors_max_connections'
);
SHOW FULL PROCESSLIST;
The MariaDB server status variable reference documents scope and meaning. Take multiple samples across a comparable interval, not one snapshot. Without PROCESS or the corresponding privilege, you can see only your own sessions; a short list is not proof that the server is clear.
This summary omits SQL text and shows which combination holds sessions. A host can still be sensitive, so keep the output in an administrative channel.
SELECT
USER,
HOST AS client_host,
DB, COMMAND, STATE,
COUNT(*) AS connections,
MAX(TIME) AS oldest_seconds
FROM INFORMATION_SCHEMA.PROCESSLIST
GROUP BY USER, client_host, DB, COMMAND, STATE
ORDER BY connections DESC, oldest_seconds DESC;
2. Separate pool pressure, leaks, slow work, and real spikes
Calculate pool capacity across the whole application: replicas × processes or workers × per-process pool maximum, plus jobs, migrations, monitoring, and administrative headroom. PHP-FPM may connect from every worker; a Node.js service commonly owns one pool per process. A rolling deployment can temporarily double the replica count and its potential demand.
Sleep only means a session is waiting for another statement. It becomes suspicious when the count or age grows without returning to baseline, the application creates faster than it reuses, or idle timeouts exceed the workload's need. If Threads_running remains high, investigate locks and slow queries: making work complete sooner or constraining concurrency can release connections. For WordPress, keep this incident separate from broader slow WordPress troubleshooting.
3. Restore service with the smallest change
- Reduce or queue nonessential traffic and stop aggressive retries; do not create a reconnect storm.
- Correct or shrink the pool and deploy gradually. Count every replica before setting its maximum.
- If an operation is blocked or abnormally slow, identify its ID, owner, host, database, state, elapsed time, and current statement in a private view.
- Confirm with the workload owner what it is doing, whether a transaction remains open, and which effects are acceptable. Only then choose query or connection termination.
Revalidate immediately before acting. INFO is sensitive and the excerpt must stay in the administrative channel. A race still exists: the session may begin another statement between the SELECT and KILL; pause the source or abandon the action if the owner or operation changes.
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE,
LEFT(INFO, 512) AS current_statement_excerpt
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE ID = <CONFIRMED_PROCESSLIST_ID>;
KILL QUERY <CONFIRMED_PROCESSLIST_ID>;
-- Separate alternative; do not run it together with the line above:
-- KILL CONNECTION <CONFIRMED_PROCESSLIST_ID>;
KILL QUERY cancels the statement but leaves the session—and possibly its transaction—open, so it does not free a max_connections slot. KILL CONNECTION closes the session after cleanup, but requires explicit confirmation of that effect. Without PROCESS you see only your threads; affecting another account's sessions in MySQL requires CONNECTION_ADMIN or deprecated SUPER. The official KILL reference documents privileges and delays.
Do not apply this example to unidentified maintenance or DDL. MySQL warns that interrupting REPAIR TABLE or OPTIMIZE TABLE on MyISAM can corrupt the table, and nontransactional changes may not roll back. Never generate lists or loops of KILL; do not use kill -9, delete sockets, or restart blindly.
4. Decide whether the limit can safely increase
Before raising max_connections, observe process RSS, available memory, swap, OOM activity, CPU, I/O, and behavior under comparable load. MySQL and MariaDB combine global memory with buffers allocated by some per-thread or per-connection operations; there is no universal RAM-per-connection constant. MariaDB's official memory allocation guide recommends sizing the connection limit alongside workload and buffers.
set -euo pipefail
UNIT='REPLACE_WITH_EXACT_DATABASE_UNIT'
[[ -n "$UNIT" && "$UNIT" != *REPLACE_WITH* ]] || { echo 'Replace UNIT after identifying it' >&2; exit 1; }
systemctl show "$UNIT" --property=MainPID,MemoryCurrent,MemoryPeak,ActiveState
ps -C mysqld -C mariadbd -o pid,etimes,rss,vsz,%mem,%cpu,cmd
free -h
vmstat 1 5
journalctl --unit="$UNIT" --since='-30 min' --no-pager | tail -n 200
KERNEL_LOG="$(journalctl --dmesg --since='-30 min' --no-pager)" || { echo 'journalctl failed' >&2; exit 1; }
if ! grep -Ei 'oom|out of memory|killed process' <<<"$KERNEL_LOG"; then
echo 'No OOM pattern found in the selected kernel window' >&2
fi
Inside a container, inspect cgroup usage and limits as well; free host memory may be irrelevant. A database proxy or external pool also changes the relationship between application connections and server sessions.
5. Apply and reverse a temporary increase
SET GLOBAL changes the running value for new connections in both current products, subject to privileges; the MariaDB server system variable reference documents the dynamic scope of max_connections. Record the previous value and change ticket. The placeholder produces an error unless replaced with a confirmed integer; derive that integer from measured memory and a corrected total pool budget.
SELECT @@GLOBAL.max_connections AS previous_runtime_limit;
SET GLOBAL max_connections = <CONFIRMED_NEW_INTEGER_LIMIT>;
SELECT @@GLOBAL.max_connections AS active_runtime_limit;
If RSS, swap, latency, or OOM behavior deteriorates, restore the recorded integer rather than a remembered default.
SET GLOBAL max_connections = <RECORDED_PREVIOUS_RUNTIME_LIMIT>;
SELECT @@GLOBAL.max_connections AS restored_runtime_limit;
Does your database need more headroom?
Compare VPS plans and size RAM, CPU, and storage around your application’s measured load.
6. MySQL persistence and rollback
On a MySQL release that supports it, SET PERSIST changes the global value and writes it to mysqld-auto.cnf. The MySQL 8.4 SET reference documents both effects. Do not use this path on MariaDB, and never edit mysqld-auto.cnf manually.
Before changing state, record the version, runtime value, and whether a persisted row already exists. Then verify both the row and the file in the reported data directory.
SELECT VERSION(), @@version_comment, @@GLOBAL.max_connections, @@GLOBAL.datadir;
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.persisted_variables
WHERE VARIABLE_NAME = 'max_connections';
SET PERSIST max_connections = <CONFIRMED_NEW_INTEGER_LIMIT>;
SELECT @@GLOBAL.max_connections;
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.persisted_variables
WHERE VARIABLE_NAME = 'max_connections';
set -euo pipefail
DATADIR='/REPLACE/WITH/EXACT/DATADIR_FROM_SQL'
[[ "$DATADIR" = /* && "$DATADIR" != *REPLACE_WITH* ]] || { echo 'Replace DATADIR with the SQL result' >&2; exit 1; }
PERSIST_FILE="${DATADIR%/}/mysqld-auto.cnf"
sudo test -f "$PERSIST_FILE" && sudo test ! -L "$PERSIST_FILE" || { echo 'mysqld-auto.cnf was not verified' >&2; exit 1; }
sudo stat -- "$PERSIST_FILE"
sudo sha256sum -- "$PERSIST_FILE"
Rollback depends on the initial state. If there was no persisted row, remove only the row you created and restore the old global value. If a row already existed, restore that persisted value and then the recorded runtime because SET PERSIST changes both states; do not delete the row. The placeholders keep both cases closed by default; in Case A, replace the name marker with the literal max_connections only after confirming that no row existed.
-- Case A: there was NO persisted row before the change.
RESET PERSIST <CONFIRM_VARIABLE_NAME_max_connections>;
SET GLOBAL max_connections = <RECORDED_PREVIOUS_RUNTIME_LIMIT>;
SELECT @@GLOBAL.max_connections AS restored_runtime_limit;
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.persisted_variables
WHERE VARIABLE_NAME = 'max_connections';
-- Case B: a row DID exist; run this case instead of Case A.
SET PERSIST max_connections = <RECORDED_PREVIOUS_PERSISTED_LIMIT>;
SET GLOBAL max_connections = <RECORDED_PREVIOUS_RUNTIME_LIMIT>;
SELECT @@GLOBAL.max_connections AS restored_runtime_limit;
SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM performance_schema.persisted_variables
WHERE VARIABLE_NAME = 'max_connections';
7. MariaDB persistence and rollback
MariaDB does not use MySQL's SET PERSIST. Its option-file documentation explains paths, order, and groups. Obtain the effective file and --defaults-* selectors from the real ExecStart; stop if it contains a wrapper, unresolved variables, or selectors you cannot reproduce. The final max_connections definition must match.
[mariadbd]
max_connections = CONFIRMED_NEW_INTEGER_LIMIT
Run the following procedure from a dedicated root shell during an exclusive window. It edits a candidate copy, takes a cooperative flock, preserves base, backup, and candidate hashes, and uses a protected client option file for SQL verification. Use a group the binary confirms it reads. Unreproducible selectors fail before replacement.
set -euo pipefail
[[ $EUID -eq 0 ]] || { echo 'Run from a dedicated root shell in the change window' >&2; exit 1; }
CONFIG='/REPLACE/WITH/EFFECTIVE/mariadb-server.cnf'
UNIT='REPLACE_WITH_EXACT_MARIADB_UNIT'
SERVER_BIN='/REPLACE/WITH/EXECSTART/mariadbd'
CLIENT_BIN='/REPLACE/WITH/EXACT/mariadb'
ADMIN_CNF='/REPLACE/WITH/PROTECTED/admin.cnf'
EDITOR_BIN='/REPLACE/WITH/EXACT/editor'
NEW_LIMIT='REPLACE_WITH_VERIFIED_INTEGER'
SELECTOR_MODE='REPLACE_WITH_none_OR_exact'
DEFAULTS_ARGS=()
# For exact mode, reproduce only the selectors found in ExecStart, in order:
# DEFAULTS_ARGS=('--defaults-file=/exact/file' '--defaults-group-suffix=exact')
LOCK_DIR='/run/teramont-mariadb-guide'
LOCK_FILE="${LOCK_DIR}/max-connections.lock"
die() { echo "$*" >&2; exit 1; }
hash_file() { sha256sum -- "$1" | awk '{print $1}'; }
hash_text() { sha256sum | awk '{print $1}'; }
get_defaults() { "$SERVER_BIN" "${DEFAULTS_ARGS[@]}" --print-defaults; }
validate_reader() { "$SERVER_BIN" "${DEFAULTS_ARGS[@]}" --help --verbose >/dev/null; }
last_max() {
tr ' ' '\n' | awk -F= '/^--max[-_]connections=/{value=$2} END{if(value!="") print value}'
}
read_runtime() {
"$CLIENT_BIN" --defaults-extra-file="$ADMIN_CNF" --protocol=socket \
--batch --skip-column-names -e 'SELECT @@GLOBAL.max_connections;'
}
set_runtime() {
local value="$1"
[[ "$value" =~ ^[1-9][0-9]*$ ]] || return 1
"$CLIENT_BIN" --defaults-extra-file="$ADMIN_CNF" --protocol=socket \
--batch --skip-column-names \
-e "SET GLOBAL max_connections = ${value}; SELECT @@GLOBAL.max_connections;"
}
[[ "$CONFIG" = /* && "$CONFIG" != *REPLACE* && -f "$CONFIG" && ! -L "$CONFIG" ]] || die 'Replace CONFIG with an effective regular file'
[[ "$UNIT" != *REPLACE* && -n "$UNIT" ]] || die 'Replace UNIT'
[[ "$SERVER_BIN" = /* && "$SERVER_BIN" != *REPLACE* && -x "$SERVER_BIN" ]] || die 'Replace SERVER_BIN'
[[ "$CLIENT_BIN" = /* && "$CLIENT_BIN" != *REPLACE* && -x "$CLIENT_BIN" ]] || die 'Replace CLIENT_BIN'
[[ "$EDITOR_BIN" = /* && "$EDITOR_BIN" != *REPLACE* && -x "$EDITOR_BIN" ]] || die 'Replace EDITOR_BIN'
[[ "$ADMIN_CNF" = /* && "$ADMIN_CNF" != *REPLACE* && -f "$ADMIN_CNF" && ! -L "$ADMIN_CNF" ]] || die 'Replace ADMIN_CNF'
[[ "$NEW_LIMIT" =~ ^[1-9][0-9]*$ ]] || die 'Replace NEW_LIMIT with a verified integer'
ADMIN_MODE="$(stat -c '%a' -- "$ADMIN_CNF")" || die 'Cannot read ADMIN_CNF mode'
[[ "$(stat -c '%u' -- "$ADMIN_CNF")" == '0' ]] || die 'ADMIN_CNF must be owned by root'
(( (8#$ADMIN_MODE & 077) == 0 )) || die 'ADMIN_CNF must not be accessible by group or others'
for TRUSTED_BIN in "$SERVER_BIN" "$CLIENT_BIN" "$EDITOR_BIN"; do
[[ "$(stat -Lc '%u' -- "$TRUSTED_BIN")" == '0' ]] || die 'A trusted binary is not owned by root'
BIN_MODE="$(stat -Lc '%a' -- "$TRUSTED_BIN")" || die 'Cannot read trusted binary mode'
(( (8#$BIN_MODE & 022) == 0 )) || die 'A trusted binary is writable by group or others'
done
command -v flock >/dev/null || die 'flock is required'
install -d -o root -g root -m 700 -- "$LOCK_DIR"
[[ -d "$LOCK_DIR" && ! -L "$LOCK_DIR" ]] || die 'Unsafe lock directory'
[[ "$(stat -c '%u:%g:%a' -- "$LOCK_DIR")" == '0:0:700' ]] || die 'Unsafe lock directory ownership or mode'
umask 077
exec {LOCK_FD}<>"$LOCK_FILE"
chmod 600 -- "$LOCK_FILE"
[[ -f "$LOCK_FILE" && ! -L "$LOCK_FILE" ]] || die 'Unsafe lock file'
[[ "$(stat -c '%u:%g:%a' -- "$LOCK_FILE")" == '0:0:600' ]] || die 'Unsafe lock ownership or mode'
flock -n "$LOCK_FD" || die 'Another cooperative MariaDB change holds the lock'
systemctl is-active --quiet "$UNIT" || die 'The database unit is not active before the change'
EXEC_START="$(systemctl show "$UNIT" --property=ExecStart --value)" || die 'Cannot read ExecStart'
[[ -n "$EXEC_START" && "$EXEC_START" != *'$'* ]] || die 'ExecStart is empty or contains unresolved variables; stop'
grep -F -- "path=$SERVER_BIN" <<<"$EXEC_START" >/dev/null || die 'SERVER_BIN does not match ExecStart'
grep -Eq -- '--max[-_]connections(=|[[:space:]])' <<<"$EXEC_START" && die 'ExecStart overrides max_connections; do not edit an option file'
grep -Eq -- '--defaults-(file|extra-file|group-suffix)[[:space:]]' <<<"$EXEC_START" && die 'A defaults selector lacks = and cannot be reproduced safely'
mapfile -t EXEC_SELECTORS < <(grep -oE -- '--defaults-(file|extra-file|group-suffix)=[^ ;]+' <<<"$EXEC_START" || true)
case "$SELECTOR_MODE" in
none)
((${#DEFAULTS_ARGS[@]} == 0 && ${#EXEC_SELECTORS[@]} == 0)) || die 'ExecStart has defaults selectors; use exact mode'
;;
exact)
((${#DEFAULTS_ARGS[@]} > 0 && ${#DEFAULTS_ARGS[@]} == ${#EXEC_SELECTORS[@]})) || die 'Selectors are incomplete'
for i in "${!DEFAULTS_ARGS[@]}"; do
[[ "${DEFAULTS_ARGS[$i]}" == "${EXEC_SELECTORS[$i]}" ]] || die 'Selector order does not reproduce ExecStart'
done
;;
*) die 'Set SELECTOR_MODE to none or exact after inspecting ExecStart' ;;
esac
validate_reader || die 'The starting effective options do not parse'
BASE_DEFAULTS="$(get_defaults)" || die 'Cannot capture starting defaults'
BASE_DEFAULTS_HASH="$(printf '%s' "$BASE_DEFAULTS" | hash_text)"
OLD_RUNTIME="$(read_runtime)" || die 'Cannot read the starting SQL value'
[[ "$OLD_RUNTIME" =~ ^[1-9][0-9]*$ ]] || die 'Starting SQL value is not an integer'
STATE_DIR="$(mktemp -d /root/mariadb-max-connections.XXXXXXXX)"
chmod 700 "$STATE_DIR"
BACKUP="${STATE_DIR}/$(basename "$CONFIG").before"
BACKUP_HASH_FILE="${STATE_DIR}/backup.hash"
BASE_DEFAULTS_HASH_FILE="${STATE_DIR}/base-defaults.hash"
OLD_RUNTIME_FILE="${STATE_DIR}/old-runtime.value"
APPLIED_COPY="${STATE_DIR}/$(basename "$CONFIG").applied"
APPLIED_HASH_FILE="${STATE_DIR}/applied.hash"
CANDIDATE_DEFAULTS_HASH_FILE="${STATE_DIR}/candidate-defaults.hash"
NEW_LIMIT_FILE="${STATE_DIR}/new-limit.value"
BASE_HASH="$(hash_file "$CONFIG")"
cp --archive -- "$CONFIG" "$BACKUP"
BACKUP_HASH="$(hash_file "$BACKUP")"
[[ "$BASE_HASH" == "$BACKUP_HASH" ]] || die 'Backup does not match the locked baseline'
printf '%s\n' "$BACKUP_HASH" >"$BACKUP_HASH_FILE"
printf '%s\n' "$BASE_DEFAULTS_HASH" >"$BASE_DEFAULTS_HASH_FILE"
printf '%s\n' "$OLD_RUNTIME" >"$OLD_RUNTIME_FILE"
printf '%s\n' "$NEW_LIMIT" >"$NEW_LIMIT_FILE"
chmod 400 "$BACKUP" "$BACKUP_HASH_FILE" "$BASE_DEFAULTS_HASH_FILE" "$OLD_RUNTIME_FILE" "$NEW_LIMIT_FILE"
[[ "$(hash_file "$CONFIG")" == "$BACKUP_HASH" ]] || die 'CONFIG diverged before candidate editing; stop'
CANDIDATE="$(mktemp --tmpdir="$(dirname "$CONFIG")" ".$(basename "$CONFIG").candidate.XXXXXXXX")"
cp --archive -- "$BACKUP" "$CANDIDATE"
"$EDITOR_BIN" "$CANDIDATE"
CANDIDATE_HASH="$(hash_file "$CANDIDATE")"
[[ "$CANDIDATE_HASH" != "$BACKUP_HASH" ]] || die 'The candidate did not change'
cp --archive -- "$CANDIDATE" "$APPLIED_COPY"
[[ "$(hash_file "$APPLIED_COPY")" == "$CANDIDATE_HASH" ]] || die 'Applied copy differs from candidate'
printf '%s\n' "$CANDIDATE_HASH" >"$APPLIED_HASH_FILE"
chmod 400 "$APPLIED_COPY" "$APPLIED_HASH_FILE"
restore_original() {
local reason="$1" restore_tmp restored_defaults restored_runtime
[[ "$(hash_file "$CONFIG")" == "$CANDIDATE_HASH" ]] || die "$reason; CONFIG diverged, so automatic restore stopped"
restore_tmp="$(mktemp --tmpdir="$(dirname "$CONFIG")" ".$(basename "$CONFIG").restore.XXXXXXXX")"
cp --archive -- "$BACKUP" "$restore_tmp"
[[ "$(hash_file "$restore_tmp")" == "$BACKUP_HASH" ]] || die 'Prepared backup copy failed its hash'
[[ "$(hash_file "$CONFIG")" == "$CANDIDATE_HASH" ]] || die 'CONFIG diverged immediately before backup replacement'
mv --force -- "$restore_tmp" "$CONFIG"
[[ "$(hash_file "$CONFIG")" == "$BACKUP_HASH" ]] || die 'Restored CONFIG does not match backup'
validate_reader || die 'Restored backup does not parse; unit was not restarted'
restored_defaults="$(get_defaults)" || die 'Cannot read restored defaults'
[[ "$(printf '%s' "$restored_defaults" | hash_text)" == "$BASE_DEFAULTS_HASH" ]] || die 'Restored defaults differ from the baseline'
if ! systemctl restart "$UNIT" || ! systemctl is-active --quiet "$UNIT"; then
die 'Backup was restored but the unit did not return active; use the provider console'
fi
set_runtime "$OLD_RUNTIME" >/dev/null || die 'Backup is active but the previous runtime value could not be restored'
restored_runtime="$(read_runtime)" || die 'Cannot verify runtime after recovery'
[[ "$restored_runtime" == "$OLD_RUNTIME" ]] || die 'Runtime mismatch after recovery'
die "$reason; backup and previous runtime were restored"
}
[[ "$(hash_file "$CONFIG")" == "$BACKUP_HASH" ]] || die 'CONFIG diverged immediately before candidate replacement'
mv --force -- "$CANDIDATE" "$CONFIG"
[[ "$(hash_file "$CONFIG")" == "$CANDIDATE_HASH" ]] || restore_original 'Candidate replacement hash mismatch'
if ! validate_reader; then
restore_original 'Candidate option validation failed'
fi
CANDIDATE_DEFAULTS="$(get_defaults)" || restore_original 'Candidate defaults could not be read'
CANDIDATE_DEFAULTS_HASH="$(printf '%s' "$CANDIDATE_DEFAULTS" | hash_text)"
[[ "$CANDIDATE_DEFAULTS_HASH" != "$BASE_DEFAULTS_HASH" ]] || restore_original 'Effective defaults did not change'
[[ "$(printf '%s' "$CANDIDATE_DEFAULTS" | last_max)" == "$NEW_LIMIT" ]] || restore_original 'The last effective max_connections value is not NEW_LIMIT'
printf '%s\n' "$CANDIDATE_DEFAULTS_HASH" >"$CANDIDATE_DEFAULTS_HASH_FILE"
chmod 400 "$CANDIDATE_DEFAULTS_HASH_FILE"
[[ "$(hash_file "$CONFIG")" == "$CANDIDATE_HASH" ]] || restore_original 'CONFIG diverged before restart'
[[ "$(systemctl show "$UNIT" --property=ExecStart --value)" == "$EXEC_START" ]] || restore_original 'ExecStart changed before restart'
if ! systemctl restart "$UNIT" || ! systemctl is-active --quiet "$UNIT"; then
restore_original 'Candidate restart or active check failed'
fi
[[ "$(hash_file "$CONFIG")" == "$CANDIDATE_HASH" ]] || restore_original 'CONFIG diverged after restart'
ACTIVE_LIMIT="$(read_runtime)" || restore_original 'SQL verification failed after restart'
[[ "$ACTIVE_LIMIT" == "$NEW_LIMIT" ]] || restore_original 'SQL value does not match NEW_LIMIT'
printf 'Applied and verified. Rollback state: %s\n' "$STATE_DIR"
Keep the printed state directory. This rollback requires the same binary, unit, selectors, and protected client file. It stops on divergence; if validation, restart, active-state, or SQL verification fails after restoring the backup, it reinstalls the applied bytes only while the known hash still matches and checks the service again.
set -euo pipefail
[[ $EUID -eq 0 ]] || { echo 'Run from a dedicated root shell in the rollback window' >&2; exit 1; }
CONFIG='/REPLACE/WITH/SAME/EFFECTIVE/mariadb-server.cnf'
UNIT='REPLACE_WITH_SAME_MARIADB_UNIT'
SERVER_BIN='/REPLACE/WITH/SAME/EXECSTART/mariadbd'
CLIENT_BIN='/REPLACE/WITH/SAME/mariadb'
ADMIN_CNF='/REPLACE/WITH/SAME/PROTECTED/admin.cnf'
STATE_DIR='/root/mariadb-max-connections.REPLACE_WITH_EXACT_SUFFIX'
SELECTOR_MODE='REPLACE_WITH_none_OR_exact'
DEFAULTS_ARGS=()
# For exact mode, reproduce the same ExecStart selectors in the same order.
LOCK_DIR='/run/teramont-mariadb-guide'
LOCK_FILE="${LOCK_DIR}/max-connections.lock"
die() { echo "$*" >&2; exit 1; }
hash_file() { sha256sum -- "$1" | awk '{print $1}'; }
hash_text() { sha256sum | awk '{print $1}'; }
get_defaults() { "$SERVER_BIN" "${DEFAULTS_ARGS[@]}" --print-defaults; }
validate_reader() { "$SERVER_BIN" "${DEFAULTS_ARGS[@]}" --help --verbose >/dev/null; }
last_max() {
tr ' ' '\n' | awk -F= '/^--max[-_]connections=/{value=$2} END{if(value!="") print value}'
}
read_runtime() {
"$CLIENT_BIN" --defaults-extra-file="$ADMIN_CNF" --protocol=socket \
--batch --skip-column-names -e 'SELECT @@GLOBAL.max_connections;'
}
set_runtime() {
local value="$1"
[[ "$value" =~ ^[1-9][0-9]*$ ]] || return 1
"$CLIENT_BIN" --defaults-extra-file="$ADMIN_CNF" --protocol=socket \
--batch --skip-column-names \
-e "SET GLOBAL max_connections = ${value}; SELECT @@GLOBAL.max_connections;"
}
[[ "$CONFIG" = /* && "$CONFIG" != *REPLACE* && -f "$CONFIG" && ! -L "$CONFIG" ]] || die 'Replace CONFIG exactly'
[[ "$UNIT" != *REPLACE* && -n "$UNIT" ]] || die 'Replace UNIT exactly'
[[ "$SERVER_BIN" = /* && "$SERVER_BIN" != *REPLACE* && -x "$SERVER_BIN" ]] || die 'Replace SERVER_BIN exactly'
[[ "$CLIENT_BIN" = /* && "$CLIENT_BIN" != *REPLACE* && -x "$CLIENT_BIN" ]] || die 'Replace CLIENT_BIN exactly'
[[ "$ADMIN_CNF" = /* && "$ADMIN_CNF" != *REPLACE* && -f "$ADMIN_CNF" && ! -L "$ADMIN_CNF" ]] || die 'Replace ADMIN_CNF exactly'
[[ "$STATE_DIR" = /root/mariadb-max-connections.* && "$STATE_DIR" != *REPLACE* && -d "$STATE_DIR" ]] || die 'Replace STATE_DIR exactly'
ADMIN_MODE="$(stat -c '%a' -- "$ADMIN_CNF")" || die 'Cannot read ADMIN_CNF mode'
[[ "$(stat -c '%u' -- "$ADMIN_CNF")" == '0' ]] || die 'ADMIN_CNF must be owned by root'
(( (8#$ADMIN_MODE & 077) == 0 )) || die 'ADMIN_CNF must not be accessible by group or others'
for TRUSTED_BIN in "$SERVER_BIN" "$CLIENT_BIN"; do
[[ "$(stat -Lc '%u' -- "$TRUSTED_BIN")" == '0' ]] || die 'A trusted binary is not owned by root'
BIN_MODE="$(stat -Lc '%a' -- "$TRUSTED_BIN")" || die 'Cannot read trusted binary mode'
(( (8#$BIN_MODE & 022) == 0 )) || die 'A trusted binary is writable by group or others'
done
command -v flock >/dev/null || die 'flock is required'
install -d -o root -g root -m 700 -- "$LOCK_DIR"
[[ -d "$LOCK_DIR" && ! -L "$LOCK_DIR" ]] || die 'Unsafe lock directory'
[[ "$(stat -c '%u:%g:%a' -- "$LOCK_DIR")" == '0:0:700' ]] || die 'Unsafe lock directory ownership or mode'
umask 077
exec {LOCK_FD}<>"$LOCK_FILE"
chmod 600 -- "$LOCK_FILE"
[[ -f "$LOCK_FILE" && ! -L "$LOCK_FILE" ]] || die 'Unsafe lock file'
[[ "$(stat -c '%u:%g:%a' -- "$LOCK_FILE")" == '0:0:600' ]] || die 'Unsafe lock ownership or mode'
flock -n "$LOCK_FD" || die 'Another cooperative MariaDB change holds the lock'
EXEC_START="$(systemctl show "$UNIT" --property=ExecStart --value)" || die 'Cannot read ExecStart'
[[ -n "$EXEC_START" && "$EXEC_START" != *'$'* ]] || die 'ExecStart is empty or contains unresolved variables; stop'
grep -F -- "path=$SERVER_BIN" <<<"$EXEC_START" >/dev/null || die 'SERVER_BIN does not match ExecStart'
grep -Eq -- '--max[-_]connections(=|[[:space:]])' <<<"$EXEC_START" && die 'ExecStart overrides max_connections; rollback needs manual review'
grep -Eq -- '--defaults-(file|extra-file|group-suffix)[[:space:]]' <<<"$EXEC_START" && die 'A defaults selector lacks = and cannot be reproduced safely'
mapfile -t EXEC_SELECTORS < <(grep -oE -- '--defaults-(file|extra-file|group-suffix)=[^ ;]+' <<<"$EXEC_START" || true)
case "$SELECTOR_MODE" in
none)
((${#DEFAULTS_ARGS[@]} == 0 && ${#EXEC_SELECTORS[@]} == 0)) || die 'ExecStart has defaults selectors; use exact mode'
;;
exact)
((${#DEFAULTS_ARGS[@]} > 0 && ${#DEFAULTS_ARGS[@]} == ${#EXEC_SELECTORS[@]})) || die 'Selectors are incomplete'
for i in "${!DEFAULTS_ARGS[@]}"; do
[[ "${DEFAULTS_ARGS[$i]}" == "${EXEC_SELECTORS[$i]}" ]] || die 'Selector order does not reproduce ExecStart'
done
;;
*) die 'Set SELECTOR_MODE to none or exact after inspecting ExecStart' ;;
esac
BACKUP="${STATE_DIR}/$(basename "$CONFIG").before"
BACKUP_HASH_FILE="${STATE_DIR}/backup.hash"
BASE_DEFAULTS_HASH_FILE="${STATE_DIR}/base-defaults.hash"
OLD_RUNTIME_FILE="${STATE_DIR}/old-runtime.value"
APPLIED_COPY="${STATE_DIR}/$(basename "$CONFIG").applied"
APPLIED_HASH_FILE="${STATE_DIR}/applied.hash"
CANDIDATE_DEFAULTS_HASH_FILE="${STATE_DIR}/candidate-defaults.hash"
NEW_LIMIT_FILE="${STATE_DIR}/new-limit.value"
for file in "$BACKUP" "$BACKUP_HASH_FILE" "$BASE_DEFAULTS_HASH_FILE" "$OLD_RUNTIME_FILE" "$APPLIED_COPY" "$APPLIED_HASH_FILE" "$CANDIDATE_DEFAULTS_HASH_FILE" "$NEW_LIMIT_FILE"; do
[[ -f "$file" && ! -L "$file" ]] || die "Invalid state file: $file"
done
BACKUP_HASH="$(<"$BACKUP_HASH_FILE")"
BASE_DEFAULTS_HASH="$(<"$BASE_DEFAULTS_HASH_FILE")"
OLD_RUNTIME="$(<"$OLD_RUNTIME_FILE")"
APPLIED_HASH="$(<"$APPLIED_HASH_FILE")"
CANDIDATE_DEFAULTS_HASH="$(<"$CANDIDATE_DEFAULTS_HASH_FILE")"
NEW_LIMIT="$(<"$NEW_LIMIT_FILE")"
for value in "$BACKUP_HASH" "$BASE_DEFAULTS_HASH" "$APPLIED_HASH" "$CANDIDATE_DEFAULTS_HASH"; do
[[ "$value" =~ ^[0-9a-f]{64}$ ]] || die 'A recorded hash is invalid'
done
[[ "$OLD_RUNTIME" =~ ^[1-9][0-9]*$ && "$NEW_LIMIT" =~ ^[1-9][0-9]*$ ]] || die 'A recorded SQL value is invalid'
[[ "$(hash_file "$BACKUP")" == "$BACKUP_HASH" ]] || die 'Backup hash mismatch'
[[ "$(hash_file "$APPLIED_COPY")" == "$APPLIED_HASH" ]] || die 'Applied-copy hash mismatch'
[[ "$(hash_file "$CONFIG")" == "$APPLIED_HASH" ]] || die 'CONFIG changed since apply; automatic rollback stopped'
validate_reader || die 'Current applied options do not parse'
CURRENT_DEFAULTS="$(get_defaults)" || die 'Cannot read current defaults'
[[ "$(printf '%s' "$CURRENT_DEFAULTS" | hash_text)" == "$CANDIDATE_DEFAULTS_HASH" ]] || die 'Current defaults differ from the applied record'
[[ "$(printf '%s' "$CURRENT_DEFAULTS" | last_max)" == "$NEW_LIMIT" ]] || die 'The last current max_connections value differs from the applied record'
PRE_ROLLBACK_RUNTIME="$(read_runtime)" || die 'Cannot read runtime before rollback'
[[ "$PRE_ROLLBACK_RUNTIME" =~ ^[1-9][0-9]*$ ]] || die 'Pre-rollback runtime is invalid'
restore_applied() {
local reason="$1" applied_tmp applied_defaults active_runtime
[[ "$(hash_file "$CONFIG")" == "$BACKUP_HASH" ]] || die "$reason; CONFIG diverged, so applied-byte recovery stopped"
applied_tmp="$(mktemp --tmpdir="$(dirname "$CONFIG")" ".$(basename "$CONFIG").reapply.XXXXXXXX")"
cp --archive -- "$APPLIED_COPY" "$applied_tmp"
[[ "$(hash_file "$applied_tmp")" == "$APPLIED_HASH" ]] || die 'Prepared applied copy failed its hash'
[[ "$(hash_file "$CONFIG")" == "$BACKUP_HASH" ]] || die 'CONFIG diverged immediately before applied replacement'
mv --force -- "$applied_tmp" "$CONFIG"
[[ "$(hash_file "$CONFIG")" == "$APPLIED_HASH" ]] || die 'Reapplied CONFIG hash mismatch'
validate_reader || die 'Reapplied options do not parse'
applied_defaults="$(get_defaults)" || die 'Cannot read reapplied defaults'
[[ "$(printf '%s' "$applied_defaults" | hash_text)" == "$CANDIDATE_DEFAULTS_HASH" ]] || die 'Reapplied defaults mismatch'
if ! systemctl restart "$UNIT" || ! systemctl is-active --quiet "$UNIT"; then
die 'Applied bytes were restored but the unit did not return active; use the provider console'
fi
set_runtime "$PRE_ROLLBACK_RUNTIME" >/dev/null || die 'Applied config is active but its previous runtime could not be restored'
active_runtime="$(read_runtime)" || die 'Cannot verify runtime after reapplying'
[[ "$active_runtime" == "$PRE_ROLLBACK_RUNTIME" ]] || die 'Runtime mismatch after reapplying'
die "$reason; applied bytes and pre-rollback runtime were restored"
}
RESTORE_TMP="$(mktemp --tmpdir="$(dirname "$CONFIG")" ".$(basename "$CONFIG").rollback.XXXXXXXX")"
cp --archive -- "$BACKUP" "$RESTORE_TMP"
[[ "$(hash_file "$RESTORE_TMP")" == "$BACKUP_HASH" ]] || die 'Prepared backup copy failed its hash'
[[ "$(hash_file "$CONFIG")" == "$APPLIED_HASH" ]] || die 'CONFIG diverged immediately before rollback replacement'
mv --force -- "$RESTORE_TMP" "$CONFIG"
[[ "$(hash_file "$CONFIG")" == "$BACKUP_HASH" ]] || restore_applied 'Rollback replacement hash mismatch'
if ! validate_reader; then
restore_applied 'Restored backup option validation failed'
fi
RESTORED_DEFAULTS="$(get_defaults)" || restore_applied 'Restored defaults could not be read'
[[ "$(printf '%s' "$RESTORED_DEFAULTS" | hash_text)" == "$BASE_DEFAULTS_HASH" ]] || restore_applied 'Restored defaults differ from baseline'
[[ "$(systemctl show "$UNIT" --property=ExecStart --value)" == "$EXEC_START" ]] || restore_applied 'ExecStart changed before rollback restart'
if ! systemctl restart "$UNIT" || ! systemctl is-active --quiet "$UNIT"; then
restore_applied 'Rollback restart or active check failed'
fi
set_runtime "$OLD_RUNTIME" >/dev/null || restore_applied 'Previous runtime could not be restored'
ACTIVE_LIMIT="$(read_runtime)" || restore_applied 'SQL verification failed after rollback'
[[ "$ACTIVE_LIMIT" == "$OLD_RUNTIME" ]] || restore_applied 'SQL runtime does not match the recorded previous value'
printf 'Rollback applied and verified. Previous runtime: %s\n' "$OLD_RUNTIME"
The lock lives in a root-only directory under /run, opens without truncation, and apply/rollback reuse the same file; it coordinates only operators using that path. ADMIN_CNF must be root-owned, and absolute binaries must not be group- or other-writable. Hash checks protect against detectable outside changes. If state diverges or the unit cannot return active, stop overwriting and use the provider console and recovery procedure. Containers and managed databases require their own persistent source and deployment mechanism.
8. Verify end to end
Repeat the same authenticated application operation. Watch Threads_connected, Threads_running, rejections, RSS, swap, latency, and both application and server errors for an interval comparable to the incident. Confirm persistence after a restart only when that restart is already authorized; do not restart solely to test. Include jobs, replicas, and connections passing through a proxy.
If the error returns, check partial PROCESSLIST visibility; pools multiplied by workers; long-lived Sleep sessions; slow work when Threads_running is high; an ineffective file or group; OOM or swap pressure; container limits; and a proxy with its own pool. The right limit is one that accommodates measured concurrency with headroom and stable memory, not the largest number the server accepts.
Prevent another connection outage
Alert on sustained Threads_connected / max_connections utilization, rejected connections, and RSS or swap growth. Document the total connection budget by service, process, and replica; use per-user limits when they isolate workloads without blocking administration. Test backoff and recovery in staging, retain rollback evidence, and recalculate after every scaling or pool change.
When legitimate demand no longer fits with stable memory, size RAM, CPU, and storage from measurements before comparing VPS resources. Adding capacity is reasonable for real load; it is not a substitute for fixing a leak.
Frequently asked questions
Does restarting fix Too many connections?
It may clear sessions and briefly restore service, but it removes evidence and does not fix the cause. Use it only as a controlled change with a hypothesis and rollback.
How many connections are too many?
Enough to exceed the application's budget or destabilize memory and latency. There is no universal count; measure concurrency, buffers, RSS, and operating headroom.
Does Sleep prove a connection leak?
No. Pools keep idle sessions for reuse. Investigate when their count or age grows without returning to baseline.
Does raising max_connections allocate all the RAM immediately?
Not necessarily. Global memory and operation-dependent per-thread allocations behave differently; risk emerges under real concurrency. Validate with load and metrics, not a fixed multiplication.
