The pre-1.1.1 pipeline ran each stage as a separate file step:
mysqldump → file.sql
xz → file.sql.xz (read whole file into memory)
Crypt::encryptString(file_get_contents(file.sql.xz))
The third step blew up with "Allowed memory size exhausted" on
mid-three-digit-MB compressed dumps because Laravel's Crypt envelope
reads the whole input, base64-encodes (+33%), and JSON-wraps it for
the MAC. Peak memory was ~3.5× the file size.
New pipeline is one shell pipe:
bash -c 'set -o pipefail; mysqldump | xz -3 | openssl enc \
-aes-256-cbc -pbkdf2 -iter 600000 -salt -pass env:WK_KEY > out'
Zero PHP-side allocation for the payload — the encryption runs in the
openssl process, not the PHP heap. The output file format is the
standard `openssl enc -salt` format (starts with "Salted__"), so it's
also restorable with vanilla openssl on any host:
openssl enc -d -aes-256-cbc -pbkdf2 -iter 600000 -pass env:K \
-in backup.sql.xz.enc | xz -d | mysql ...
Encryption is still APP_KEY-derived: the base64-stripped APP_KEY is
fed to openssl as the passphrase, PBKDF2 (600k iters) stretches it
into the AES key. A backup is still only restorable by a deployment
that knows the same APP_KEY.
Other changes:
- xz default level dropped from 9 to 3 (tunable via --xz-level or
config('workkit.backup.xz_level')). For SQL dumps -9 buys a few %
size at multiples of the time cost; -3 is the sweet spot.
- Restore detects and rejects pre-1.1.1 Crypt-format backups with a
clear error (those need a one-off Crypt::decryptString, which would
hit the same memory wall, so we don't auto-fall-back).
- "bad decrypt" / "bad magic" stderr now translates to "APP_KEY
mismatch" so operators don't have to recognise openssl errors.
Verified end-to-end against the dev DB (981 blogs / 659 users / 209
roles): backup in 1.2s, openssl-format output, restore into throwaway
DB matches every spot-checked row count.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Three new artisan commands modelled on blax-intranet's database
backup/restore pattern, generalised so any project pulling in
laravel-workkit gets them for free:
- workkit:db:backup
mysqldump → xz -9 → Crypt::encryptString → storage/backups/db_<conn>_<ts>.sql.xz.enc
Password is passed via MYSQL_PWD so it doesn't appear in `ps`.
- workkit:db:restore
Picks newest backup (or --file=…), inverts the pipeline, and pipes
the .sql into the mysql CLI. Detects .enc / .xz suffixes so plain
dumps and partially-encoded files work too. Confirms before
overwriting unless --force is set; emits a clear "APP_KEY
mismatch" message when Crypt::decryptString fails.
- workkit:db:prune-backups
Drops backups older than --days (defaults to 30 / config).
--dry-run shows what would go.
Encryption uses Laravel's Crypt facade, so the AES-256 key is
derived from APP_KEY — same key the rest of the app uses for cookies
and encrypted columns. A backup is restorable only by a deployment
that knows the host's APP_KEY.
Shipped config/workkit.php (mergeConfigFrom + publishes) for
overriding the backup directory and retention window without
hard-coding env-specific values in the package.
Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>
Fabian Wagner