Orphan database cleanup (sqlclean)

Databases outlive the things that owned them: a site deleted years ago, a cancelled instance, an experiment someone loaded by hand. sqlclean is the root-only tool that finds those orphans and — only when you tell it to — removes them. It is deliberately slow to trust: by default it deletes nothing, a live run demands per-database confirmation, and its unattended mode only acts on a manifest you have explicitly reviewed.

The three modes

sqlclean               # DRY: report only, deletes nothing
sqlclean LIVE          # interactive: confirm each ghost by typing its name
sqlclean LIVE auto     # non-interactive: acts on a reviewed DRY manifest

A DRY run writes three files: /var/log/dbs_to_remain.log (everything that stays), /var/log/db_cleanup.log (the run log), and the ghost manifest /var/log/sqlclean-ghosts.txt (the candidates). Start here, always.

In LIVE mode every ghost must be confirmed by typing its exact name — and declining only skips it for that run; to keep it out of every future run, add it to the protect list below. LIVE needs a real terminal.

Before any LIVE run: the protect list

sqlclean LIVE (and LIVE auto) refuse to start until /root/.sqlclean.protect.cnf exists — an empty file is the documented minimum. That is by design: the file's existence is your recorded sign-off that you reviewed the DRY report for non-Ægir databases. Anything Ægir does not know about — a billing app, a monitoring store, an ad-hoc import — classifies as a ghost and will be offered for deletion unless you list it:

# /root/.sqlclean.protect.cnf — one exact database name per line
billing_app        # keep: standalone billing
metrics            # keep: monitoring store

Two sharp edges: names must be exact (no globs, no patterns), and only [A-Za-z0-9_] names are accepted — a name with a hyphen or dot is silently dropped from the list, so double-check hand-created databases with unusual names after editing.

How it decides — aliases, never vhosts

The source of truth is the Drush alias set across /var/aegir and every /data/disk/<oN> instance, never the nginx vhosts — deliberately, because vhosts cannot distinguish dead from live: a disabled site keeps its database while its vhost regenerates db-free, a proxied site's real vhost is staged under a leading dot, and a cancelled instance loses its vhosts long before its purge. Every schema lands in one of three buckets:

preserved — system schemas, every alias-resolved site database, each instance's own hostmaster database, and everything on your protect list. Never offered.
held — something looks wrong (an unreadable site drushrc, an instance with a home but no .drush, a hostmaster alias resolving to nothing, a failed per-schema query). Never deleted; reported loudly — fix the cause in Ægir and re-run.
ghost — nothing vouches for it; offered for deletion. A ghost still carrying frontend tables whose platform trees are gone is tagged panel-orphan.

When a ghost is dropped, its same-named MySQL user is dropped with it — unless that user holds grants on another schema, in which case the user is kept and reported.

`LIVE auto` — the runbook mode

Unattended cleanup is triple-gated. It refuses unless all three hold:

you copied a DRY manifest to /var/log/sqlclean-ghosts.txt.reviewed (that copy is the sign-off) less than 4 hours ago;
the current detection still matches the reviewed manifest exactly;
no resolution alert fired (any held condition blocks auto mode until fixed).

The reviewed manifest is consumed after a successful run, so every future auto run needs a fresh review. Exit codes: 0 clean, 1 refused or failed outright, 2 completed but at least one drop failed.

When to run it

The high-value moment is before a whole-box migration: the replication-based path seeds and mirrors every schema on the source, orphans included, so a source-side sqlclean first both shrinks the transfer and removes stale panel schemas the migration tooling would otherwise trip over (it refuses to guess between multiple hostmaster candidates). On the per-account path orphans are structurally left behind anyway — there it is disk hygiene, not payload. See xmass server move.

Dumps & backups (mydumper) — the backup layer, including the cache-truncate exception list.
Ghost cleanup — the nightly filesystem ghost detection; this page's tool is its database-side sibling, run by hand.
PX0 proxy shrink — the proxy-conversion flow that also expects a sqlclean pass.