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: 0.21.3-2 (_MYQUICK_VRN_TWO, newer codenames) and 0.19.3-3 (_MYQUICK_VRN_ONE, older codenames), pinned in BARRACUDA.sh.txt.

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. mysql_backup.sh itself also stands down (exits 0) if a mysql_cluster_backup.sh is already running, so the two never overlap.

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 Aegir-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. This is gated by the /etc/boa/.disable_mysql_cleanup.cnf kill-switch — create that file to leave the tables untouched. 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). 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.

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. A 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". A 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 Aegir/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

For the weekly forced repair-and-convert, /root/.my.batch_innodb.cnf (run on Saturday inside the 01:15 mysql_backup.sh pass) repairs, truncates cache_*, and converts tables to InnoDB; /root/.my.optimize.cnf (last Sunday of month) repairs and optimizes. Both 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 --overwrite-tables

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 --overwrite-tables

# 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.