Analyze Database Dumps Automatically With Bash

1. Why a dump reveals more than just "backup succeeded"

Most backup routines only check whether mysqldump completed with exit code zero, then mark the backup as successful. A database dump analysis that goes deeper catches problems that can occur despite a successful exit code: a table that is suddenly empty because a migration went wrong, a dump that is only a fraction of the expected size because the connection dropped during export, or a schema that changed unnoticed.

The reason a database dump analysis operates directly on the dump file, instead of restoring the dump into a test database, is speed and resource efficiency. A full restore test needs its own database instance, time for the import, and disk space for a complete copy. Text based analysis with grep, awk, and regular expressions delivers most of the relevant metrics in a fraction of the time, directly on the dump file as plain text.

This article shows how a database dump analysis in Bash checks size trends, row counts per table, schema changes, and structural integrity, for both uncompressed and gzip compressed dumps, and how the result is automatically sent as a report.

2. Detecting size trends over time without restoring

The simplest form of a database dump analysis is comparing file size across several consecutive backups. A database usually grows continuously, and a sudden drop almost always indicates a problem: a deleted table, a failed export, or an accidental TRUNCATE operation right before the backup run. This check needs no database connection at all, just the file sizes of the last backups in the directory.

For a robust database dump analysis, a simple threshold comparison is enough: if the current dump size is more than twenty percent smaller than the rolling average of the last seven days, that gets flagged as an anomaly. This percentage needs adjusting per environment, because some databases show legitimate size fluctuations too, due to regular maintenance jobs (archiving, log rotation in tables).

#!/usr/bin/env bash
# check-dump-size-trend.sh — flag a suspicious size drop across backups
set -euo pipefail

readonly BACKUP_DIR="/var/backups/mysql"
readonly THRESHOLD_PERCENT=20

# Collect sizes of the last 7 dumps, oldest first
mapfile -t sizes < <(
  find "$BACKUP_DIR" -name "*.sql" -mtime -7 -printf "%T@ %s\n" \
    | sort -n | awk '{print $2}'
)

if (( ${#sizes[@]} < 2 )); then
  echo "[INFO] Not enough history to compare, skipping trend check"
  exit 0
fi

current_size="${sizes[-1]}"
sum=0
for (( i = 0; i < ${#sizes[@]} - 1; i++ )); do
  sum=$(( sum + sizes[i] ))
done
average=$(( sum / (${#sizes[@]} - 1) ))

drop_percent=$(awk -v cur="$current_size" -v avg="$average" 'BEGIN { printf "%.1f", (1 - cur/avg) * 100 }')

if awk -v d="$drop_percent" -v t="$THRESHOLD_PERCENT" 'BEGIN { exit !(d > t) }'; then
  echo "[WARN] Dump size dropped by ${drop_percent}% vs. 7-day average" >&2
  echo "[WARN] Current: $current_size bytes, average: $average bytes" >&2
  exit 1
fi

echo "[OK] Dump size within expected range (${drop_percent}% vs. average)"

This single metric is already enough for a first database dump analysis to catch the most severe backup disasters early. For a more differentiated diagnosis of which specific table is affected, the table by table analysis in the next section is needed.

3. Counting row counts per table directly from the dump

A mysqldump in default format writes an INSERT INTO block for every table, often with multiple value tuples per line via --extended-insert. For an accurate database dump analysis per table, simply counting INSERT INTO lines is not enough, because a single line can contain hundreds of records. Instead, you count the number of value groups, recognizable by opening parentheses after a comma or right after VALUES.

A more reliable alternative that avoids fragile parenthesis counting is exporting with --skip-extended-insert, causing mysqldump to write exactly one record per line. For a database dump analysis primarily meant for diagnostics, setting this option on the backup itself makes sense, because it significantly simplifies subsequent analysis, at the cost of a somewhat larger uncompressed dump file.

#!/usr/bin/env bash
# count-rows-per-table.sh — approximate row counts from a mysqldump file
set -euo pipefail

readonly DUMP_FILE="${1:?Usage: count-rows-per-table.sh <dump.sql>}"

# Works reliably when the dump was created with --skip-extended-insert
awk '
/^INSERT INTO/ {
  match($0, /INSERT INTO `?([a-zA-Z0-9_]+)`?/, arr)
  table = arr[1]
  count[table]++
}
END {
  for (t in count) printf "%-30s %d rows\n", t, count[t]
}' "$DUMP_FILE" | sort

For dumps using --extended-insert, which occur more often in practice because they are considerably smaller, you instead count the number of completed value groups per INSERT INTO line with a regular expression that matches ),( as the separator between records. This count is an approximation, but for a database dump analysis meant to show trends over time rather than exact numbers, this precision is sufficient.

4. Detecting schema changes between two dumps

A mysqldump by default also contains the CREATE TABLE statements for every table. That makes a database dump analysis a practical tool for detecting schema drift: unintended changes to the database structure that happened without a documented migration, for example a column added manually straight to the production database that never shows up anywhere in version control.

The technique is simple: all CREATE TABLE blocks are extracted from each dump and brought into a canonical, sorted form, then two versions are compared with diff. For a meaningful database dump analysis, it matters to filter out variable components like AUTO_INCREMENT start values beforehand, because those change with every insert and would otherwise falsely show up as a schema change on every comparison.

#!/usr/bin/env bash
# schema-diff.sh — compare CREATE TABLE statements between two dumps
set -euo pipefail

readonly OLD_DUMP="${1:?Usage: schema-diff.sh <old.sql> <new.sql>}"
readonly NEW_DUMP="${2:?Usage: schema-diff.sh <old.sql> <new.sql>}"

extract_schema() {
  local dump_file="$1"
  # Extract CREATE TABLE blocks, strip AUTO_INCREMENT values (they always change)
  awk '/^CREATE TABLE/,/^\) ENGINE/' "$dump_file" \
    | sed -E 's/AUTO_INCREMENT=[0-9]+//g'
}

old_schema="$(mktemp)"
new_schema="$(mktemp)"
extract_schema "$OLD_DUMP" > "$old_schema"
extract_schema "$NEW_DUMP" > "$new_schema"

if diff -u "$old_schema" "$new_schema" > /tmp/schema.diff; then
  echo "[OK] No schema changes detected"
else
  echo "[WARN] Schema changes detected between dumps:" >&2
  cat /tmp/schema.diff >&2
fi

rm -f "$old_schema" "$new_schema"

This kind of database dump analysis does not replace formal migration management, but is a valuable safety net for catching schema drift between the actual state of the production database and what is documented in migration files early, before it turns into a bigger problem at the next planned migration.

5. Checking the structural integrity of the dump

An incomplete or aborted mysqldump can still produce a valid, if truncated, text file. Without explicit checking, this is often only noticed at the actual restore attempt, in the worst case exactly when the backup is needed. A structural database dump analysis therefore checks whether the dump begins and ends with the expected markers that mysqldump always writes on a complete run.

The most reliable marker for a complete dump is the comment line -- Dump completed on, which mysqldump writes as the last line, provided the export was not aborted. If this line is missing, the dump is very likely incomplete, even if mysqldump itself terminated with exit code zero, for example because the connection was interrupted exactly between the last record and this closing line.

#!/usr/bin/env bash
# verify-dump-completeness.sh — check structural markers of a mysqldump file
set -euo pipefail

readonly DUMP_FILE="${1:?Usage: verify-dump-completeness.sh <dump.sql>}"

errors=0

# A complete mysqldump always ends with this comment line
if ! tail -n 5 "$DUMP_FILE" | grep -q "^-- Dump completed on"; then
  echo "[ERROR] Missing 'Dump completed on' marker — dump may be truncated" >&2
  errors=$(( errors + 1 ))
fi

# Every dump should contain at least one CREATE TABLE statement
if ! grep -q "^CREATE TABLE" "$DUMP_FILE"; then
  echo "[ERROR] No CREATE TABLE statements found — dump looks empty" >&2
  errors=$(( errors + 1 ))
fi

# Check for unbalanced quotes, a common sign of a corrupted or truncated file
quote_count="$(grep -o "'" "$DUMP_FILE" | wc -l)"
if (( quote_count % 2 != 0 )); then
  echo "[WARN] Odd number of single quotes ($quote_count) — possible corruption" >&2
  errors=$(( errors + 1 ))
fi

if (( errors > 0 )); then
  echo "[FAIL] $errors integrity issue(s) found in $DUMP_FILE" >&2
  exit 1
fi

echo "[OK] Dump structurally complete: $DUMP_FILE"

These three checks together catch the most common failure patterns: a dump cut off during transfer, an accidentally empty export, and a file corrupted by encoding problems. For a production ready database dump analysis, this check belongs right after the backup run, not only at the next restore attempt.

6. Detecting anomalies: sudden data loss or explosion

Besides file level size trends, a database dump analysis that tracks individual tables over time pays off. A table that normally grows by a few percent per day but suddenly drops to zero rows suggests an accidental DELETE or TRUNCATE operation. Conversely, a table that grows tenfold overnight often points to a faulty import job or an infinite loop in the application duplicating records.

The technique is an extension of the row counting from section three: instead of just showing the current state, the row count of every table gets logged in a time series file, so every database dump analysis can compare against the value from 24 hours ago. A threshold of plus or minus fifty percent without a known reason is a good starting point for most applications.

#!/usr/bin/env bash
set -euo pipefail

readonly HISTORY_FILE="/var/backups/table-row-history.tsv"
readonly TODAY="$(date +%Y-%m-%d)"

# Append today's counts, then compare against yesterday's for each table
while read -r table count; do
  echo -e "${TODAY}\t${table}\t${count}" >> "$HISTORY_FILE"

  yesterday_count="$(awk -F'\t' -v t="$table" -v d="$(date -d yesterday +%Y-%m-%d)" \
    '$1 == d && $2 == t { print $3 }' "$HISTORY_FILE")"

  [[ -z "$yesterday_count" ]] && continue

  change_percent=$(awk -v c="$count" -v y="$yesterday_count" \
    'BEGIN { if (y == 0) { print 0 } else { printf "%.1f", ((c - y) / y) * 100 } }')

  if awk -v p="$change_percent" 'BEGIN { exit !(p < -50 || p > 100) }'; then
    echo "[ANOMALY] Table '$table': ${change_percent}% change since yesterday ($yesterday_count -> $count)" >&2
  fi
done < <(./count-rows-per-table.sh latest-dump.sql)

7. Analyzing compressed dumps without unpacking them

In practice, dumps are almost always compressed directly with gzip to save disk space, either through a pipe during the backup or afterward. A database dump analysis that first unpacks the entire dump wastes disk space and time, especially for multi gigabyte databases. The solution: zgrep, zcat, and zless are direct equivalents of grep, cat, and less that work transparently on gzip compressed files, without ever producing a temporary unpacked copy.

All analysis scripts shown in this article can be adapted to compressed dumps with minimal changes, by replacing cat with zcat and grep with zgrep. For a database dump analysis that runs routinely after every backup, this is the difference between an analysis that runs in seconds and one that first needs minutes to unpack a multi gigabyte dump.

#!/usr/bin/env bash
set -euo pipefail

readonly DUMP_FILE="${1:?Usage: analyze-compressed.sh <dump.sql.gz>}"

# Check completeness marker directly on the compressed file
if zcat "$DUMP_FILE" | tail -n 5 | grep -q "^-- Dump completed on"; then
  echo "[OK] Compressed dump appears complete"
else
  echo "[ERROR] Compressed dump missing completeness marker" >&2
  exit 1
fi

# Count rows per table without ever writing an uncompressed copy to disk
zcat "$DUMP_FILE" | awk '
/^INSERT INTO/ {
  match($0, /INSERT INTO `?([a-zA-Z0-9_]+)`?/, arr)
  count[arr[1]]++
}
END {
  for (t in count) printf "%-30s %d rows\n", t, count[t]
}' | sort

8. Summarizing and sending results as a report

Each individual check from the previous sections delivers valuable signals on its own, but the real operational benefit comes only once a database dump analysis summarizes all results in a single report sent automatically after every backup. Following the heredoc and inline CSS pattern described in an earlier article of this series, such a report can be built directly from Bash with no additional dependencies.

What matters for a usable report is distinguishing between warnings and hard failures: a five percent size drop is an observation, a missing completion marker is an error that needs immediate attention. A database dump analysis that treats every finding the same way leads either to ignored critical errors or to alert fatigue from too many trivial warnings.

9. Dump analysis compared to a test restore

A text based database dump analysis does not replace every use case of a real restore test, but covers a large share of the relevant checks with significantly less effort.

The pragmatic solution is a combination: a fast text based database dump analysis runs after every single backup, a full test restore with a subsequent consistency check runs less often, say weekly, as a deeper safeguard against problems that a pure text analysis cannot capture.

10. Summary

A thorough database dump analysis goes well beyond simply checking the exit code of mysqldump. The size trend across several backups catches sudden drops indicating deleted tables or aborted exports. Row counts per table, counted directly from the dump, show which specific table is affected. Schema diffs between consecutive dumps uncover unintended structural changes that bypassed the official migration management.

Structural integrity checking based on the completion marker catches truncated dumps that are incomplete despite an exit code of zero. All these checks also work on gzip compressed dumps with zgrep and zcat, without ever producing a temporary unpacked copy. A database dump analysis does not replace a full restore test, but as a fast, resource efficient addition after every single backup, it is an essential building block of reliable backup strategies.

11. FAQ: Analyzing Database Dumps Automatically