Shell Scripting — Database Centric: Connection Testing & Diagnostics

# ── TCP port reachability ─────────────────────────────────
nc -z -w3 prod-db-01 3306 && echo "MySQL port open" || echo "CLOSED"
nc -z -w3 prod-db-01 5432 && echo "Postgres port open"
nc -z -w3 redis.prod   6379 && echo "Redis port open"

# ── /dev/tcp (no nc required) ────────────────────────────
if timeout 3 bash -c "echo >/dev/tcp/prod-db-01/3306" 2>/dev/null; then
  echo "Port reachable"
else
  echo "Port unreachable or timeout"
fi

# ── Measure TCP connection latency ────────────────────────
time bash -c "echo >/dev/tcp/prod-db-01/3306" 2>&1 | grep real

# ── MySQL application-level probe ─────────────────────────
check_mysql() {
  local host="${1}" user="${2}" pass="${3}" db="${4:-mysql}"
  result=$(MYSQL_PWD="${pass}" mysql -h "${host}" -u "${user}" \
    -D "${db}" -BNse "SELECT 1" 2>&1)
  [[ "${result}" == "1" ]] && echo "MySQL OK" || \
    { echo "MySQL FAIL: ${result}"; return 1; }
}

# ── PostgreSQL probe ──────────────────────────────────────
check_postgres() {
  local host="${1}" user="${2}" pass="${3}" db="${4:-postgres}"
  result=$(PGPASSWORD="${pass}" psql -h "${host}" -U "${user}" -d "${db}" \
    -tAc "SELECT 1" 2>&1)
  [[ "${result}" == "1" ]] && echo "PostgreSQL OK" || \
    { echo "PostgreSQL FAIL: ${result}"; return 1; }
}

# ── Redis probe ───────────────────────────────────────────
check_redis() {
  redis-cli -h "${1}" -p "${2:-6379}" --no-auth-warning -a "${3:-}" \
    PING 2>/dev/null | grep -q PONG \
    && echo "Redis OK" || { echo "Redis FAIL"; return 1; }
}

# ── Generic wait-for-port ─────────────────────────────────
wait_for_port() {
  local host="${1}" port="${2}"
  local timeout="${3:-60}" interval="${4:-2}"
  local elapsed=0

  echo -n "  Waiting for ${host}:${port}"
  while ! nc -z -w1 "${host}" "${port}" 2>/dev/null; do
    (( elapsed >= timeout )) && { echo " TIMEOUT"; return 1; }
    echo -n "."
    sleep "${interval}"
    (( elapsed += interval ))
  done
  echo " UP (${elapsed}s)"
}

# ── Wait for MySQL to accept queries (not just TCP) ───────
wait_for_mysql() {
  local host="${1}" timeout="${2:-120}"
  local elapsed=0

  echo -n "  Waiting for MySQL on ${host}"
  while ! MYSQL_PWD="${DB_PASS}" mysql -h "${host}" \
      -u "${DB_USER}" -e "SELECT 1" &>/dev/null 2>&1; do
    (( elapsed >= timeout )) && { echo " TIMEOUT"; return 1; }
    echo -n "."
    sleep 3; (( elapsed += 3 ))
  done
  echo " READY (${elapsed}s)"
}

# ── Docker entrypoint pattern ─────────────────────────────
wait_for_port "${DB_HOST}" 3306 60
wait_for_mysql "${DB_HOST}" 120
wait_for_port "${REDIS_HOST}" 6379 30
echo "All dependencies ready, starting app..."
exec "$@"

#!/usr/bin/env bash
# pre_deploy_check.sh — Gate deployment on database readiness

set -euo pipefail

PASS=0
FAIL=0

check() {
  local name="${1}"; shift
  printf "  %-30s" "${name}:"
  if "$@" &>/dev/null 2>&1; then
    echo "OK"; (( PASS++ ))
  else
    echo "FAIL"; (( FAIL++ ))
  fi
}

echo "=== Pre-deployment Database Checks ==="
echo "  Time: $(date '+%Y-%m-%d %H:%M:%S')"
echo "  Host: ${HOSTNAME}"
echo

# Network layer
check "MySQL TCP port"  nc -z -w3 "${DB_HOST}" 3306
check "Redis TCP port"  nc -z -w3 "${REDIS_HOST}" 6379

# Authentication
check "MySQL auth" \
  bash -c "MYSQL_PWD='${DB_PASS}' mysql -h '${DB_HOST}' -u '${DB_USER}' -e 'SELECT 1'"
check "Redis auth" \
  bash -c "redis-cli -h '${REDIS_HOST}' -a '${REDIS_PASS}' --no-auth-warning PING | grep -q PONG"

# Schema version
CURRENT_VERSION=$(MYSQL_PWD="${DB_PASS}" mysql -h "${DB_HOST}" \
  -u "${DB_USER}" -D "${DB_NAME}" -BNse \
  "SELECT MAX(version) FROM schema_migrations" 2>/dev/null || echo "000")
check "Schema version >= ${REQUIRED_VERSION}" \
  bash -c "[[ '${CURRENT_VERSION}' >= '${REQUIRED_VERSION:-000}' ]]"

# Sufficient connections available
check "MySQL connections available" bash -c "
  MYSQL_PWD='${DB_PASS}' mysql -h '${DB_HOST}' -u '${DB_USER}' -BNse \
  'SELECT (@@max_connections - COUNT(*)) > 10 FROM information_schema.PROCESSLIST' \
  | grep -q 1"

echo
echo "  Results: ${PASS} passed, ${FAIL} failed"
(( FAIL > 0 )) && { echo "  DEPLOYMENT BLOCKED"; exit 1; }
echo "  All checks passed — proceeding with deployment"