Dumps with mydumper

BOA uses mydumper (parallel per-table dump) and myloader (parallel restore) as the primary local DB-dump mechanism, confirmed compatible across Percona 5.7 and 8.4. This is the local-restore layer the off-site backups suite archives on top of — not the off-site tool itself.

BOA ships two mydumper builds, selected by the host's Devuan codename and pinned in BARRACUDA.sh.txt:

• 0.21.3-2 (_MYQUICK_VRN_TWO, newer codenames)
• 0.19.3-3 (_MYQUICK_VRN_ONE, older codenames)

mydumper 1.0 removed myloader's --overwrite-tables option in favour of the equivalent --drop-table=DROP, which was only added in 0.20.1 — so the two options overlap in the middle of the range and at neither end. BOA's callers pass --drop-table=DROP, and on every system pass, not only at install time, BOA probes the installed myloader and re-derives /usr/local/bin/myloader from what that binary actually accepts:

• Both options accepted (0.21.3-2, the packaged build on current codenames): no translation is needed, so the path is a plain symlink to the real binary.
• --drop-table missing (anything before 0.20.1, including the 0.19.3-3 source build): BOA moves a source build aside to myloader.bin and writes a small wrapper at the path, translating --drop-table into --overwrite-tables.
• --overwrite-tables missing (the 1.x line): the same wrapper in reverse.

The wrappers exec the real binary, so exit codes propagate. Callers old and new therefore keep working against any build, with no required upgrade ordering.

Keeping the pair together

mydumper and myloader must come from the same build: they are one release either side of a dump, and two releases disagree about the options BOA passes. The pair that matters is /usr/local/bin/mydumper and /usr/local/bin/myloader — Provision hard-codes both, and the shell tools resolve the bare names with /usr/local/bin first.

BOA therefore targets the myloader that ships beside the mydumper the callers run, found by following /usr/local/bin/mydumper to the build it belongs to, and pins that path into the wrapper instead of re-deriving it at run time. It also clears the loser's leftovers on every pass, which is what stops the two halves drifting apart in the first place:

• On a host using the packaged build, a real binary left at /usr/local/bin/mydumper by an earlier source build is replaced by the symlink, and stale .bin companions are dropped.
• On a host that genuinely needs the source build, the packaged binaries left behind in /usr/bin are purged — but only once the source pair is proven to exist and run, so a failed build can never leave a host with no tools.
• A dangling symlink at either canonical path is cleared first, since it answers "installed" to a bare file test while pointing at nothing.

As a backstop the two versions are compared afterwards, and a surviving disagreement is reported as MyQuick pair mismatch in the upgrade log rather than left for a nightly dump to discover.

Building mydumper from source

Which of the two builds lands is decided by _myquick_install_upgrade, called from BARRACUDA.sh.txt right after Percona itself is installed — so it runs on install passes as well as upgrades. Normally the host codename picks a prebuilt .deb: _SQL_OS_CODE maps the Devuan release (excalibur, daedalus, chimaera, beowulf) onto its Debian equivalent (trixie, bookworm, bullseye, buster), and any codename BOA does not map falls through to a source build instead (_MYQUICK_SRC_INSTALL_REQUIRED=YES).

That codename arm is now the only way the source path is reached. It is an internal fallback for hosts BOA has no package for — there is nothing to set, and nothing to switch off.

Two properties of the source path explain why it is no longer offered as a choice:

• It always builds _MYQUICK_VRN_ONE (0.19.3-3), never the newer line. On a codename that would otherwise get 0.21.3-2, taking the source path holds the box a version line back — while myloader can still arrive from the package, leaving the pair split across two release lines that disagree about the import options BOA passes.
• The upgrade-pass version check follows it. On the source path BOA compares the installed mydumper against _MYQUICK_VRN_ONE and rebuilds from source when they differ, instead of comparing against the codename-selected .deb version.

The source build first pulls in a compiler toolchain (cmake, libssl-dev) and the Percona client libraries matching the running _DB_SERIES, then compiles.

The retired operator switch

/root/.install.myquick.src.info, and the _INSTALL_MYQUICK_SRC=YES variable it was briefly converted to, used to force the source path on any host. Both are retired and ignored. A stale marker file is inert and safe to leave in place; the cnf key is deleted from /root/.barracuda.cnf on the next barracuda run, so it cannot be flipped back on later.

A host on a mapped codename that took the source build while the switch was set converges onto the packaged build by itself: on the next upgrade pass the installed mydumper no longer matches the codename-selected .deb version, so BOA installs that .deb and re-points /usr/local/bin/mydumper at it. /usr/local/bin/myloader is re-derived in the same step, and on the packaged 0.21.3-2 build that means the translating wrapper is removed in favour of a plain symlink, because that build accepts both flag styles. Expect a symlink there after converging, not a wrapper script — and a leftover /usr/local/bin/myloader.bin from the old source build, which is inert. Imports keep working across the change.

Source builds themselves are not retired — a host whose codename BOA does not map still gets one, automatically.

One limit of that path, worth knowing on a host it applies to: the build body itself is gated on _MYQUICK_INSTALL_REQUIRED=YES, and that flag is only ever set on an upgrade pass (_STATUS=UPGRADE) or from the .deb helper's own fallback arm. On a fresh install pass the source path is therefore reached but does nothing — no compiler toolchain, no build, no mydumper — and it stays that way until the first upgrade pass runs. Plan for mydumper to arrive on that pass, not on the initial install, on any host the source path applies to.

What runs when

The crontab runs /var/xdrago/mysql_backup.sh directly at 01:15 — it is not called from daily.sh.

The Simple Cluster feature is discontinued (see Percona install + tuning), but its tooling is shipped-but-dormant, not removed: mysql_cluster_backup.sh (and the ProxySQL monitor check) is still fetched onto every host on each upgrade and its 02:15 crontab line is installed unconditionally.

The script self-exits immediately unless the cluster marker files (/root/.my.cluster_write_node.txt and /root/.my.cluster_root_pwd.txt) exist, so on a normal single-server host it does nothing.

Gates: when the nightly dump stands down

Before it dumps anything, mysql_backup.sh runs a fixed sequence of gates, and any one of them ends the run. An empty (or absent) <hostname>-<date> directory is usually one of these rather than a failure:

• Root filesystem over 90% full — the only gate that exits non-zero, with ERROR: Your disk space is almost full.
• /root/.proxy.cnf — proxy node; exits 0.
• /root/.pause_heavy_tasks_maint.cnf — the maintenance pause latch; exits 0.
• /root/.standby.cnf — the durable standby role marker. xmass writes it ahead of the datadir swap, so it protects a copied-in datadir before replication is even configured; exits 0.
• A configured replica — SHOW REPLICA STATUS with a SHOW SLAVE STATUS fallback and one retry. Non-empty output means the box is a replica and the run refuses, because the local TRUNCATE/OPTIMIZE this script performs would stop the SQL thread. The probe is exit-code aware and fails closed: an errored probe — broken or under-privileged /root/.my.cnf credentials — refuses too and logs Ooops, the replica role probe FAILED (credentials?). Treat that line as an alarm and fix /root/.my.cnf. With MySQLD unreachable the probe defers and is asked again once the daemon is up. Exits 0.
• Another dump already running — a live mysql_cluster_backup.sh (the guard is mutual: the cluster script stands down for this one too, so the two never overlap), or an earlier mysql_backup.sh whose pid in /run/boa_sql_backup.pid is still alive. A killed run's stale marker is not believed, so it cannot lock the next run out. Exits 0.

Waiting for MySQLD is bounded: the wait polls every 3 seconds and, after about 60 seconds, prints ALERT: MySQLD did not become available within 60 seconds, giving up., removes its /run/mysql_backup_running.pid lock and exits non-zero, leaving the next scheduled run to retry.

The hourly mysql_cleanup.sh carries the same standby marker gate and the same fail-closed replica probe, for the same reason: under ROW binlog a replicated change to a locally truncated row stops the SQL thread outright. On a standby mirror both are the expected steady state — see Failover & recovery scenarios, which covers the same markers in the mirror context and notes that mysql_cluster_backup.sh carries the standby marker gate but deliberately not the replica probe, since it targets the cluster's designated write node.

The proxy, pause and 90%-disk gates are also listed on the Backups overview.

Where dumps land

Host-level nightly dumps go under a single per-run directory named <hostname>-<date>, one subdirectory per database:

/data/disk/arch/sql/<hostname>-<date>/<DB>/         mydumper per-DB split-file output (default)
/data/disk/arch/sql/<hostname>-<date>/<DB>.sql.gz   legacy single-file mysqldump (when /root/.mysql.force.legacy.backup.cnf is set)
/data/disk/arch/sql/<hostname>-<date>/mysql.sql.gz  the mysql system schema (always mysqldump)

_SAVELOCATION is ${_BACKUPDIR}/${_hName}-${_DATE} with _BACKUPDIR=/data/disk/arch/sql and _hName read from /etc/hostname, so every database for one nightly run lands together under the same <hostname>-<date> directory.

The split-file default is much faster for both backup and restore. Single-file mode (mysqldump) is retained only for compatibility with old tooling that expects a .sql file — toggle via /root/.mysql.force.legacy.backup.cnf (see Control files & INI).

Compression is by output mode: mydumper per-DB output is packed to <DB>-<date>.tar.zst with zstd; the legacy single-file path gzips *.sql to *.sql.gz. The mysql system schema is always dumped with mysqldump and gzip'd to mysql.sql.gz, regardless of mode.

mydumper vs. mysqldump

For a typical Drupal DB (~1 GB) on commodity hardware, mydumper is 5–10× faster on backup and 10–20× faster on restore.

The nightly chain

1. List every DB on the Percona instance (one per Ægir-managed site, plus the Hostmaster and Octopus instance DBs). Every DB and table name is identifier-sanitised before it is interpolated into any SQL: anything not matching ^[A-Za-z0-9_]+$ is skipped with a WARN (a security-audit hardening — a tenant-created table with a crafted name can no longer make a TRUNCATE/DROP cross tenants in the root mysql context).
2. For each user DB, before dumping, inline-truncate any giant transient table: if queue, batch, watchdog, or accesslog has an .ibd file that has grown into the gigabyte range (du -h reports a G), it is TRUNCATEd so the dump never carries multi-GB of disposable data. The supported switch for this is _DISABLE_MYSQL_CLEANUP=YES in /root/.barracuda.cnf, with the legacy marker /etc/boa/.disable_mysql_cleanup.cnf honoured for one release beside it — either one leaves the tables untouched. Note the deliberate scope difference: here it gates only these four truncations, while for the hourly mysql_cleanup.sh it kills the entire run. The unused views_data_export_* tables are cleared unconditionally. (Weekly/monthly cache truncation, repair, and InnoDB conversion run here too — see mysql_repair.sh below.)
3. For each user DB, run mydumper into /data/disk/arch/sql/<hostname>-<date>/<DB>/.
4. Dump the mysql system schema separately with mysqldump, to mysql.sql in the same run directory.
5. Compress (zstd for mydumper per-DB output, gzip for the system schema and any legacy single-file dumps).
6. Delete run directories older than the retention threshold.

How mydumper is invoked

Per user database, mysql_backup.sh runs:

mydumper \
  --defaults-file=/root/.my.cnf \
  --database=<DB> --host=localhost --port=3306 \
  --outputdir=/data/disk/arch/sql/<hostname>-<date>/<DB>/ \
  --rows=50000 --build-empty-files --threads=4 \
  --long-query-guard=900 \
  --sync-thread-lock-mode=AUTO \
  --verbose=1

--sync-thread-lock-mode=AUTO is used on Percona 8.x to minimise the FLUSH TABLES WITH READ LOCK (FTWRL) window; on Percona 5.7 BOA falls back to FTWRL (the lock mode is selected by detecting the server version).

In its default mode mydumper refuses to dump a database containing non-transactional tables (for example, a stray MyISAM table), which previously made such a database silently drop out of the nightly archives. Both backup scripts now count non-InnoDB base tables first and append --trx-tables=0 only for the affected databases, so mixed-engine databases are always included, while InnoDB-only databases keep the fastest locking path shown above.

Credentials come from /root/.my.cnf via --defaults-file, so the root password never appears in /proc/<pid>/cmdline. The same property holds for every MySQL command BOA issues — see the install + tuning credential note and the security section.

BOA writes /root/.my.cnf with exactly five groups — [client], [mysql], [mysqldump], [mydumper] and [myloader] — separated by empty lines. mydumper and myloader parse the file with a strict key-file parser, so the separator lines must be genuinely empty, and only groups with real consumers are written.

The system schema is dumped separately

The mysql system schema is never handled by mydumper — it is a mixed storage-engine schema (MyISAM on 5.7, mixed on 8.x). BOA dumps it with mysqldump --routines --events --single-transaction (capturing stored routines and scheduled events mydumper would miss), landing at <run-dir>/mysql.sql, then gzip'd to mysql.sql.gz.

Only mysql is special-cased. The enumeration loop excludes just the three literal names Database, information_schema, and performance_schema, so every other schema SHOW DATABASES returns is treated as a user DB and dumped with mydumper — including the Percona 8.x sys schema, which lands as a normal per-DB archive under <run-dir>/sys/ (packed to sys-<date>.tar.zst).

Expect a sys archive on 8.x hosts when restoring or auditing.

Concurrency lock

mysql_backup.sh writes two PID markers:

• Run-wide marker — /run/boa_sql_backup.pid is created once at the start of the whole pass and removed near the end, marking "a nightly backup run is in progress".
• Per-DB marker — /run/mysql_backup_running.pid is set and cleared around each individual database (and mysql_cleanup.sh sets the same per-DB flag), so other tasks — cleanup, the monitor stack — can detect a backup in progress and stand off.

It also checks mysqld is up (/run/mysqld/mysqld.sock) before each phase.

Retention

Host-level dump retention defaults to 14 days and is an operator knob: _DB_BACKUPS_TTL (days) in /root/.barracuda.cnf, sanitised to digits and falling back to 14 when unset. In basic mode (mysql_backup.sh basic, _THIS_MODE=basic) retention is forced to 3 days. Cleanup deletes any run directory under /data/disk/arch/sql/ older than the threshold:

# /root/.barracuda.cnf — keep host-level dumps for 14 days (default)
_DB_BACKUPS_TTL=14

Backup-tool permissions and restricted VMs

The Ægir/Octopus backup tasks shell out to /usr/bin/mysqldump and /usr/bin/rsync as the unprivileged aegir user, so both binaries must be mode 755. A past regression left them 750 root:root, which broke tenant backups with mysqldump: Permission denied. On every upgrade pass BOA now enforces the mode:

• Normally: chmod 755 on both /usr/bin/mysqldump and /usr/bin/rsync.
• When /root/.restrict_this_vm.cnf exists, the intent is that BOA killalls any running rsync and drops both binaries to 700 (root-only), disabling tenant-facing backups on that VM. Caveat: this 700-lock branch is effectively dead — its only reader (autoupboa, _hostedSys=YES block) deletes the marker immediately before testing for it, so the test never matches and the chmod 755 branch always runs.

See Control files & INI for the /root/.restrict_this_vm.cnf knob, and Troubleshooting if backups fail with Permission denied from mysqldump or rsync.

mysql_repair.sh

Runs on demand (typically when table corruption is detected). It does not loop per database — it runs three single all-databases mysqlcheck passes, each appended to a timestamped log under its work directory:

mysqlcheck -u root -Aa               # analyze all DBs
mysqlcheck -u root -A --auto-repair  # check + auto-repair all DBs
mysqlcheck -u root -Ao               # optimize all DBs

Credentials are the implicit root read of /root/.my.cnf. Manual invocation:

/var/xdrago/mysql_repair.sh

It declines to start while the database is already being worked on: a backup, another maintenance operation, an auto-heal or a restart in progress. While it does run it holds /run/boa_sql_maintenance.pid, which stands the database watchdog down entirely, so a full-server repair cannot be read as a fault and restarted underneath. The marker is released when the script exits, and the watchdog ignores it once stale in any case.

For the scheduled forced maintenance passes — both driven from inside the 01:15 mysql_backup.sh run, not from mysql_repair.sh:

• /root/.my.batch_innodb.cnf — Saturday (_DOW=6). Repairs each database, truncates its cache_* tables, and converts tables to InnoDB.
• /root/.my.optimize.cnf — the Sunday whose day of the month falls from 24 to 30 (_DOW=7, _DOM 24–30), and only in full mode. Repairs each database, truncates its cache_* tables, and optimises it. The window is seven days wide, so every month of 30 or 31 days holds exactly one qualifying Sunday and is never skipped; only a February whose Sundays all fall before the 24th misses the pass — February 2026, for one.

Both are persistent toggles, not one-shots — nothing in mysql_backup.sh removes them, so they fire again in every following window until you delete them. full is the default mode, so only an explicit mysql_backup.sh basic run skips the monthly pass.

Protecting specific cache tables from the truncate. Every cache-truncate pass — the hourly cleanup and the weekly/monthly repairs alike — skips a built-in exception list: cache_bootstrap, cache_discovery, cache_config. If a site keeps data in another cache_* table that must survive (a custom module misusing the cache bin as storage is the classic case), add its exact table name to /root/.my.cache.exceptions.cnf, one per line — the file's contents are appended to the built-in list. These are exact names, not patterns: the skip is a string-equality match per table, so cache_custom_% protects nothing.

Restarting MySQLD after the monthly optimise

/root/.my.restart_after_optimize.cnf is the twin of .my.optimize.cnf. With both files in place, mysql_backup.sh follows the monthly optimise with a full graceful MySQLD restart — bash /var/xdrago/move_sql.sh, the clean stop/start described in Graceful MySQLD control — after every database has been dumped, and before the compress and retention phases.

touch /root/.my.optimize.cnf                # the gate
touch /root/.my.restart_after_optimize.cnf  # the restart on top of it

On its own it does nothing. The restart block repeats every one of .my.optimize.cnf's own conditions (Sunday, day of the month 24–30, full mode) and adds one more of its own: /run/boa_run.pid must be absent, so a restart is never issued while a BOA upgrade run is in progress. BOA never creates or removes this marker.

On a BOA-hosted box these markers do not survive

On a managed host (_hostedSys=YES) BOA erases both optimise markers on its own schedule, so setting them by hand does not stick:

• .my.optimize.cnf is removed on every autoupboa pass — roughly every five minutes, driven from the clear.sh cron — in the same hosted block that deletes /root/.restrict_this_vm.cnf (see above). The monthly optimise therefore never fires there, and .my.restart_after_optimize.cnf, which BOA does not touch, is left permanently inert because its gate is gone.
• .my.batch_innodb.cnf is removed on every _update_agents pass of BOA's own self-update (BOA.sh.txt), alongside the long-retired bare-name variant .batch_innodb.cnf. That block runs while the BOA Skynet agent is active on a hosted Ægir master, so the Saturday repair-and-convert pass does not fire there either.

Neither wipe is a fault to report — it is the hosted fleet's normalisation, and it makes all three of these markers effectively self-hosted-only knobs. On a self-hosted box nothing removes them and they persist until you do.

All three are documented in Control files & INI.

Binary log retention

Binary logging is off by default (_DB_BINARY_LOG=NO in /root/.barracuda.cnf). Turn it on for point-in-time recovery:

_DB_BINARY_LOG=YES

purge_binlogs.sh runs hourly (:01) and, when binary logging is on, issues PURGE BINARY LOGS BEFORE '<cutoff>', keeping the last _BINLOG_KEEP_HOURS hours (default 24).

Restoring

From a mydumper output

# Quiesce the site first (Aegir Disable task, or service nginx stop).
# If the per-DB dir was packed, extract it:
#   cd /data/disk/arch/sql/<hostname>-<date>/ && tar -I zstd -xf <dbname>-<date>.tar.zst
myloader --user=root --database=<dbname> \
  --directory=/data/disk/arch/sql/<hostname>-<date>/<dbname>/ \
  --threads=4 --drop-table=DROP

From a legacy single-file dump

gunzip -c <dbname>.sql.gz | mysql --user=root <dbname>

Manual dump / restore

mydumper --user=root --database=<dbname> --outputdir=/tmp/dump-<dbname> --threads=4 --compress
myloader --user=root --database=<dbname> --directory=/tmp/dump-<dbname> --threads=4 --drop-table=DROP

# Single-file dump (e.g. to import into a non-BOA host):
mysqldump --user=root --single-transaction --quick <dbname> | gzip > <dbname>.sql.gz

Restore-verify

mysql -e "USE <dbname>; SHOW TABLE STATUS;" | head
drush @site-alias updb     # apply DB updates if the codebase changed
drush @site-alias cr       # cache rebuild

Related

• Percona install + tuning — version expectations and mydumper compatibility.
• my.cnf lifecycle + mycnfup — the Percona instance these dumps run against.
• Graceful MySQLD control — move_sql.sh, the clean stop/start the dump/repair paths can sit behind.
• Backups — the off-site suite that archives these local dumps with file + S3 + retention.
• Control files & INI — /root/.mysql.*.cnf toggles, _DB_BACKUPS_TTL, _DB_BINARY_LOG.