Shell Scripting — Database Centric: Database Table Diffing

# ── Compare schemas between environments ──────────────────
# Using mysqldiff (part of MySQL Utilities)
mysqldiff   --server1=user:pass@prod-db-01   --server2=user:pass@staging-db-01   prod_myapp:staging_myapp

# ── Manual schema comparison via information_schema ────────
PROD_CONF="/etc/myapp/prod-mysql.conf"
STAGE_CONF="/etc/myapp/staging-mysql.conf"

# Get column definitions for a table in each env
diff <(
  mysql --defaults-file="${PROD_CONF}" -BNs information_schema     -e "SELECT COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_DEFAULT
        FROM COLUMNS WHERE TABLE_SCHEMA='myapp' AND TABLE_NAME='users'
        ORDER BY ORDINAL_POSITION"
) <(
  mysql --defaults-file="${STAGE_CONF}" -BNs information_schema     -e "SELECT COLUMN_NAME,COLUMN_TYPE,IS_NULLABLE,COLUMN_DEFAULT
        FROM COLUMNS WHERE TABLE_SCHEMA='myapp' AND TABLE_NAME='users'
        ORDER BY ORDINAL_POSITION"
) && echo "Schemas identical" || echo "Schema differences found"

# ── List tables present in prod but not staging ────────────
comm -23   <(mysql --defaults-file="${PROD_CONF}" -BNs -e "SHOW TABLES IN myapp" | sort)   <(mysql --defaults-file="${STAGE_CONF}" -BNs -e "SHOW TABLES IN myapp" | sort)

# ── Compare row counts across environments ────────────────
echo "Table count comparison:"
mysql --defaults-file="${PROD_CONF}" -BNs information_schema   -e "SELECT TABLE_NAME, TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA='myapp' ORDER BY TABLE_NAME" | while IFS=$'\t' read -r tbl rows; do
  stage_rows=$(mysql --defaults-file="${STAGE_CONF}" -BNs information_schema     -e "SELECT TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA='myapp' AND TABLE_NAME='${tbl}'")
  diff=$(( rows - stage_rows ))
  printf "  %-25s prod=%7d staging=%7d diff=%+d\n" "${tbl}" "${rows}" "${stage_rows}" "${diff}"
done

# ── Checksum entire table contents ────────────────────────
# MySQL CHECKSUM TABLE for fast comparison
for tbl in users orders products; do
  PROD_CK=$(mysql --defaults-file="${PROD_CONF}" -BNs myapp     -e "CHECKSUM TABLE ${tbl}" | awk '{print $2}')
  STAGE_CK=$(mysql --defaults-file="${STAGE_CONF}" -BNs myapp     -e "CHECKSUM TABLE ${tbl}" | awk '{print $2}')
  if [[ "${PROD_CK}" == "${STAGE_CK}" ]]; then
    echo "  ${tbl}: IDENTICAL"
  else
    echo "  ${tbl}: DIFFERS (prod=${PROD_CK} stage=${STAGE_CK})"
  fi
done

# ── Find rows in prod not in staging ──────────────────────
# Export both, sort, diff
mysql --defaults-file="${PROD_CONF}" -BNs myapp   -e "SELECT id,email FROM users ORDER BY id" | sort > /tmp/prod_users.txt
mysql --defaults-file="${STAGE_CONF}" -BNs myapp   -e "SELECT id,email FROM users ORDER BY id" | sort > /tmp/stage_users.txt

echo "Rows in prod but not staging:"
comm -23 /tmp/prod_users.txt /tmp/stage_users.txt | head -20
echo "Rows in staging but not prod:"
comm -13 /tmp/prod_users.txt /tmp/stage_users.txt | head -20