Shell ScriptingEnvironmentsDatabase CentricAdvancedMay 2026

Shell Scripting — Database Centric: Multi-Database Environment Management

Manage dev, staging, and production databases consistently — environment detection, config loading, production refresh to staging with PII masking, feature flag overrides, and schema synchronisation.

Managing dev, staging, and production databases consistently — keeping schemas in sync, propagating seed data, refreshing staging from production snapshots — is an ongoing challenge. Shell scripts that codify environment management turn ad-hoc manual processes into repeatable, audited operations.

1 Environment-aware database configuration

BASH

#!/usr/bin/env bash
# db_env.sh — Multi-environment database management library

# ── Environment detection ─────────────────────────────────
detect_env() {
  case "${HOSTNAME}" in
    *prod*)    echo "production" ;;
    *stg*|*staging*) echo "staging" ;;
    *dev*)     echo "development" ;;
    *)         echo "${APP_ENV:-development}" ;;
  esac
}

ENV=$(detect_env)

# ── Load environment-specific config ──────────────────────
case "${ENV}" in
  production)
    DB_CONF="/etc/myapp/prod-mysql.conf"
    DB_NAME="myapp"
    DB_HOST="prod-db-01"
    READONLY=true    # protect production
    ;;
  staging)
    DB_CONF="/etc/myapp/staging-mysql.conf"
    DB_NAME="myapp_staging"
    DB_HOST="stg-db-01"
    READONLY=false
    ;;
  development)
    DB_CONF="${HOME}/.myapp/dev-mysql.conf"
    DB_NAME="myapp_dev"
    DB_HOST="localhost"
    READONLY=false
    ;;
esac

db() {
  [[ "${READONLY}" == "true" ]] && [[ "$1" =~ ^(INSERT|UPDATE|DELETE|DROP|ALTER|TRUNCATE) ]] && {
    echo "ERROR: Write blocked in ${ENV}" >&2; return 1
  }
  mysql --defaults-file="${DB_CONF}" -D "${DB_NAME}" -BNs -e "$1"
}

echo "Connected to: ${ENV} (${DB_HOST}/${DB_NAME})"

2 Refresh staging from production

BASH

#!/usr/bin/env bash
# refresh_staging.sh — Refresh staging DB from production snapshot

set -euo pipefail

PROD_CONF="/etc/myapp/prod-mysql.conf"
STAGE_CONF="/etc/myapp/staging-mysql.conf"
BACKUP="/tmp/prod_snapshot_$$.sql.gz"
STAGING_DB="myapp_staging"

echo "=== Staging Refresh: $(date '+%Y-%m-%d %H:%M') ==="

# Step 1: Dump production (with data masking)
echo "  Dumping production..."
mysqldump --defaults-file="${PROD_CONF}"   --single-transaction --no-tablespaces myapp |   # Mask PII before loading into staging
  sed -E "s/'[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+'/CONCAT('user',@row:=@row+1,'@example.com')/g" |   gzip > "${BACKUP}"
echo "  Dump: $(du -sh "${BACKUP}" | cut -f1)"

# Step 2: Drop and recreate staging DB
echo "  Recreating staging database..."
mysql --defaults-file="${STAGE_CONF}" << SQL
DROP DATABASE IF EXISTS ${STAGING_DB};
CREATE DATABASE ${STAGING_DB} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SQL

# Step 3: Load into staging
echo "  Loading snapshot..."
zcat "${BACKUP}" | mysql --defaults-file="${STAGE_CONF}" "${STAGING_DB}"

# Step 4: Apply staging-specific overrides
echo "  Applying staging overrides..."
mysql --defaults-file="${STAGE_CONF}" "${STAGING_DB}" << SQL
-- Override production credentials for staging environment
UPDATE config SET value='smtp.mailtrap.io' WHERE key='smtp_host';
UPDATE config SET value='test_api_key' WHERE key='payment_api_key';
-- Disable email sending in staging
UPDATE feature_flags SET enabled=0 WHERE name='send_real_emails';
SQL

echo "  Staging refresh complete"
rm -f "${BACKUP}"

✔ ✔ Environment manageme — Always detect the environment from hostname or APP_ENV and apply appropriate guards. Block write operations in production via wrapper functions. When refreshing staging from production, mask PII (emails, phone numbers, addresses) before the data crosses environment boundaries. Apply staging-specific overrides (sandbox payment keys, disabled email sending) as a mandatory post-refresh step. Document the refresh procedure and test it monthly.