Shell Scripting — Database Centric: MySQL from the Shell

# ── NEVER do this — password visible in process list ───────
mysql -u root -p"mypassword" mydb  # BAD: visible in ps aux

# ── Option 1: ~/.my.cnf (best for personal scripts) ───────
cat ~/.my.cnf
# [client]
# host = localhost
# user = app_user
# password = s3cr3t
# database = myapp
chmod 600 ~/.my.cnf
mysql                          # reads credentials from ~/.my.cnf
mysql --defaults-file=~/.my.cnf mydb

# ── Option 2: MYSQL_PWD env var (acceptable in scripts) ───
MYSQL_PWD="${DB_PASS}" mysql -u "${DB_USER}" -h "${DB_HOST}" "${DB_NAME}"

# ── Option 3: Dedicated defaults file per environment ──────
mysql --defaults-file=/etc/myapp/mysql.conf mydb

# mysql.conf:
# [client]
# user=app_user
# password=s3cr3t
# host=prod-db-01
# port=3306

# ── Wrapper function ───────────────────────────────────────
db() {
  mysql --defaults-file=/etc/myapp/mysql.conf \
        --silent --skip-column-names \
        --batch "${DB_NAME}" "$@"
}
db -e "SELECT COUNT(*) FROM users"
db < migration.sql

# ── Key flags for scripting ───────────────────────────────
# -e 'SQL'       execute SQL string
# -B / --batch   tab-separated output, no table borders
# -N / --skip-column-names  omit header row
# -s / --silent  suppress informational messages
# -E / --vertical  one column per line (useful for wide rows)

# ── Fetch a single value ──────────────────────────────────
COUNT=$(mysql -BNs -e "SELECT COUNT(*) FROM users" mydb)
echo "Users: ${COUNT}"

# ── Fetch a row into variables ─────────────────────────────
read -r name email plan <<< $(mysql -BNs \
  -e "SELECT name,email,plan FROM users WHERE id=42" mydb)
echo "${name} (${email}) — ${plan}"

# ── Process each row with a while loop ───────────────────
mysql -BNs \
  -e "SELECT id,name,email FROM users WHERE active=1" mydb | \
while IFS=$'\t' read -r id name email; do
  echo "Processing user ${id}: ${name} <${email}>"
  send_welcome_email "${email}"
done

# ── Pipe SQL from heredoc ─────────────────────────────────
mysql -B mydb <<SQL
SELECT
  u.name,
  COUNT(o.id) AS order_count,
  SUM(o.total)   AS revenue
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY u.id
ORDER BY revenue DESC
LIMIT 10;
SQL

# ── Parse output with AWK ────────────────────────────────
mysql -BNs -e "SELECT name,cpu_pct FROM server_metrics" mydb | \
  awk -F'\t' '$2 > 80 { printf "ALERT: %-20s CPU=%s%%\n", $1, $2 }'

#!/usr/bin/env bash
# mysql_health.sh — Comprehensive MySQL health check

DB_CONF="/etc/myapp/mysql.conf"
ALERT_EMAIL="dba@example.com"
ERRORS=0

db() { mysql --defaults-file="${DB_CONF}" -BNs -e "$1"; }

# ── 1. Connectivity check ─────────────────────────────────
if ! db "SELECT 1" &>/dev/null; then
  echo "CRITICAL: Cannot connect to MySQL" | mail -s "DB Down" "${ALERT_EMAIL}"
  exit 1
fi

# ── 2. Replication lag ────────────────────────────────────
LAG=$(db "SHOW SLAVE STATUS\G" 2>/dev/null | \
  grep "Seconds_Behind_Master" | awk '{print $2}')
[[ -n "${LAG}" ]] && (( LAG > 30 )) && {
  echo "WARN: Replication lag ${LAG}s"; (( ERRORS++ ))
}

# ── 3. Connection count ───────────────────────────────────
CONNS=$(db "SELECT COUNT(*) FROM information_schema.PROCESSLIST")
MAX_CONNS=$(db "SELECT @@max_connections")
PCT=$(( CONNS * 100 / MAX_CONNS ))
(( PCT > 80 )) && { echo "WARN: Connections ${CONNS}/${MAX_CONNS} (${PCT}%)"; (( ERRORS++ )); }

# ── 4. Long-running queries ───────────────────────────────
db "SELECT id,user,time,info FROM information_schema.PROCESSLIST
    WHERE command != 'Sleep' AND time > 60" | \
  while IFS=$'\t' read -r id user time query; do
    echo "WARN: Long query (${time}s) user=${user}: ${query:0:80}"
    (( ERRORS++ ))
  done

# ── 5. Disk usage per database ─────────────────────────────
db "SELECT table_schema,
  ROUND(SUM(data_length+index_length)/1073741824,2) AS gb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY gb DESC" | \
  awk -F'\t' '$2 > 10 { printf "INFO: DB %-20s %.1f GB\n", $1, $2 }'

(( ERRORS > 0 )) && echo "Health check: ${ERRORS} warnings" || \
  echo "Health check: OK"