# dbdump

> Fast MySQL/MariaDB dump CLI that excludes noisy table DATA (audit logs, sessions, cache) while always preserving complete database STRUCTURE, cutting dump time and size dramatically. Wraps the `mysqldump` binary.

dbdump dumps a MySQL/MariaDB database in two phases: (1) it dumps the schema
(CREATE TABLE, triggers, events) for ALL tables, then (2) it dumps row data only
for the tables you keep. Excluded tables still appear in the output with their
definitions intact. Retained rows can still depend on omitted parent-table data,
so exclusions should be reviewed against the schema. This document is self-contained:
an agent that has never used dbdump can operate it from the information here.

## Requirements

- The `mysqldump` binary must be on `PATH` (dbdump shells out to it for the dump).
  Install via `brew install mysql-client`, `apt-get install mysql-client`, etc.
- dbdump connects to the database itself (Go driver) for inspection, and runs
  `mysqldump` for the actual dump. Nothing else is required.

Verify both commands before configuring a connection:

```bash
dbdump version
mysqldump --version
```

## Install

- Homebrew: `brew install helgesverre/tap/dbdump`
- Go 1.26+: `go install github.com/helgesverre/dbdump/cmd/dbdump@latest`
- Pre-built binaries: https://github.com/helgesverre/dbdump/releases
- From source: `git clone`, then `just install` (installs to `~/.local/bin`, no sudo).

## IMPORTANT for agents / non-interactive use

By default `dbdump dump` opens an interactive terminal UI to pick tables. That UI
requires a TTY on both stdin and stdout; in a non-interactive environment
(scripts, CI, agents) it exits with an error: "interactive mode requires a TTY;
rerun with --auto". Always pass `--auto` in automation. Exact and pattern
exclusions can be added alongside it with `--exclude` / `--exclude-pattern`;
those flags do not disable the interactive picker by themselves.

Errors are written to stderr prefixed with `Error:` and the process exits
non-zero. A cancelled interactive selection exits 0 without dumping.

## Commands

- `dbdump dump [flags]` — Dump the database. Interactive table picker by default;
  add `--auto` for non-interactive smart defaults. Writes a `.sql` (optionally
  compressed) file with mode 0600, written atomically (temp file + rename).
- `dbdump list [flags]` — Print every table with its size and row count, and a
  total count. Read-only; good for deciding what to exclude.
- `dbdump config list` — Show saved connection profiles (passwords are shown only
  as "(saved)", never printed).
- `dbdump config add <name> [connection flags]` — Save the current connection
  flags (host, port, user, password, database, and any `--tls-*` settings) as a
  named profile in `~/.config/dbdump/profiles.yaml` (created 0600).
- `dbdump config remove <name>` — Delete a saved profile.
- `dbdump version` (or `dbdump --version`) — Print version/commit/build info.

## Connection flags (persistent; apply to dump and list)

- `-H, --host` — Database host (default `127.0.0.1`).
- `-P, --port` — Database port (default `3306`).
- `-u, --user` — Database user (REQUIRED).
- `-p, --password` — Password. Prefer an env var (below) so it never appears in
  the process list.
- `-d, --database` — Database name (REQUIRED).
- `--profile <name>` — Load a saved profile. Explicit flags override profile
  values.

Password resolution order (first non-empty wins): `-p` flag, then the selected
profile's password, then `DBDUMP_MYSQL_PWD`, then `MYSQL_PWD`.

Prefer an environment variable so the password is not exposed in shell history
or the process arguments:

```bash
export DBDUMP_MYSQL_PWD='your-password'
```

## Dump flags

- `-o, --output <file>` — Output path. Default `{database}_{timestamp}.sql`
  (plus `.gz`/`.zst` when compressed).
- `--auto` — Non-interactive; apply the smart default exclusions with no prompt.
- `--dry-run` — Print the full plan (each table's size and row count, whether its
  data is dumped or only its structure kept, totals, and the resolved output path
  and compression) and write NOTHING.
- `--exclude <table>` — Exclude a table's DATA by exact name (repeatable).
- `--exclude-pattern <glob>` — Exclude tables whose name matches a glob (`*`, `?`;
  repeatable). A malformed glob is rejected with an error.
- `-c, --config <file>` — Project config YAML (see Configuration).
- `--compress auto|none|gzip|zstd` — Streamed compression. `auto` (default)
  infers from the `-o` filename extension.

## Configuration (exclusions)

Exclusions are merged from these layers, lowest to highest priority: built-in
defaults, global `~/.dbdump.yaml`, project config via `--config`, then CLI flags.
Layers only ADD exclusions; they never remove the built-in defaults. Config files
are decoded strictly — an unknown/misspelled key errors instead of being ignored.

Config YAML shape:

```yaml
exclude:
  exact:
    - my_audit_log
  patterns:
    - "tmp_*"
```

Built-in default exact excludes: activity_log, audits, sessions, cache,
cache_locks, failed_jobs, telescope_entries, telescope_entries_tags,
telescope_monitoring, pulse_entries, pulse_aggregates.
Built-in default pattern excludes: `telescope_*`, `pulse_*`, `*_cache`.

## SSH tunnel

- `--ssh-host <host>` — Bastion host; enabling this makes dbdump open and tear
  down a local tunnel for the run.
- `--ssh-port` (default 22), `--ssh-user` (defaults to the DB user), `--ssh-key`,
  `--ssh-local-port` (default: auto-pick a free port).
- With a tunnel, `-H/-P` describe the DB endpoint as seen FROM the SSH server.

## TLS/SSL

Applies to both the inspection connection and the `mysqldump` subprocess.

- `--tls-mode disabled|preferred|require|verify-ca|verify-identity` — mirrors
  MySQL's ssl-mode. `require` = encrypt without verification; `verify-ca` =
  verify the cert chain; `verify-identity` = verify chain and hostname.
- `--tls-ca <pem>` — CA cert to verify the server (implies verify-ca if no mode).
- `--tls-cert <pem>` / `--tls-key <pem>` — client cert + key for mutual TLS
  (must be given together).
- `--tls-skip-verify` — encrypt but skip verification (dev/self-signed only;
  conflicts with verify-* modes).
- `--tls-server-name <name>` — override the verified hostname; use behind an SSH
  tunnel where the host becomes `127.0.0.1`. By itself, this enables
  `verify-identity` so the requested hostname is actually checked.
- With no `--tls-*` flag, the connection is unencrypted (unchanged default).
- An explicit `disabled` mode stays plaintext even if a saved profile contains
  certificate paths. Custom TLS options cannot be combined with `preferred`;
  choose `require`, `verify-ca`, or `verify-identity` instead.

## Restore a dump

- Plain: `mysql -u root -p mydb < dump.sql`
- gzip: `gzip -dc dump.sql.gz | mysql -u root -p mydb`
- zstd: `zstd -dc dump.sql.zst | mysql -u root -p mydb`

## Examples

```bash
# 1. Set credentials without putting them in process arguments
export DBDUMP_MYSQL_PWD=secret

# 2. Confirm connectivity and inspect table sizes (read-only)
dbdump list -H localhost -u root -d mydb

# 3. Preview the exact non-interactive plan; this writes nothing
dbdump dump -H localhost -u root -d mydb --auto --dry-run

# 4. Run the reviewed plan
dbdump dump -H localhost -u root -d mydb --auto

# Custom exclusions + gzip
dbdump dump -H localhost -u root -d mydb --auto \
  --exclude audits --exclude-pattern "temp_*" --compress gzip -o backup.sql.gz

# Schema only (exclude all data)
dbdump dump -H localhost -u root -d mydb --auto --exclude-pattern "*"

# Through an SSH bastion
dbdump dump -H 127.0.0.1 -P 3306 -u root -d mydb --auto \
  --ssh-host bastion.example.com --ssh-user deploy --ssh-key ~/.ssh/id_ed25519

# Over TLS, verifying the server against a CA
dbdump dump -H db.example.com -u root -d mydb --auto \
  --tls-mode verify-identity --tls-ca /etc/ssl/certs/ca.pem

# Save and reuse a connection profile
dbdump config add prod -H db.example.com -u readonly -d myapp
dbdump dump --profile prod --auto
```

## Agent safety checklist

1. Confirm the target host and database name before running a dump.
2. Use `list`, then `--auto --dry-run`, before the real command.
3. Remember that excludes remove row data only; schema is always preserved.
4. Use an explicit `-o` path in automation and check the command's exit status.
5. Treat saved profiles and dump files as sensitive even though they are mode 0600.

## Troubleshooting

- `interactive mode requires a TTY`: add `--auto`, including to dry runs.
- `mysqldump is required`: install a MySQL-compatible client and verify it is on `PATH`.
- Authentication failures: check `DBDUMP_MYSQL_PWD`, the selected profile, user, host, and port.
- TLS failures: start with the intended mode; use `--tls-server-name` behind an SSH tunnel when certificate identity differs from `127.0.0.1`.
- Before retrying a failed command, read the `Error:` message on stderr. Failed dumps do not replace the destination file.

## Docs

- [README](https://github.com/helgesverre/dbdump/blob/main/README.md): overview, install, command reference.
- [User Guide](https://github.com/helgesverre/dbdump/blob/main/USER-GUIDE.md): full configuration, examples, troubleshooting.
- [Changelog](https://github.com/helgesverre/dbdump/blob/main/CHANGELOG.md): version history.
- [Source](https://github.com/helgesverre/dbdump): GitHub repository.