แจก Script เช็คสุขภาพ Database (MySQL/PostgreSQL) ด้วย Bash

#!/bin/bash

# --- Configuration ---
DB_TYPE="mysql" # หรือ "postgresql"
DB_USER="your_db_user"
DB_PASS="your_db_password"
DB_HOST="localhost"

ALERT_CPU_THRESHOLD=80 # เปอร์เซ็นต์
ALERT_MEM_THRESHOLD=80 # เปอร์เซ็นต์
ALERT_DISK_THRESHOLD=85 # เปอร์เซ็นต์
ALERT_CONN_THRESHOLD=100 # จำนวน Connections

# --- Script Logic ---
log_message() {
  echo "$(date '+%Y-%m-%d %H:%M:%S') - $1"
}

check_mysql() {
  # Check Service Status
  if ! systemctl is-active --quiet mysql;
  then
    log_message "[ERROR] MySQL service is not running."
    return 1
  fi

  # Check Resource Usage (basic)
  CPU_USAGE=$(top -bn1 | grep "%Cpu(s)" | awk '{print $2}' | cut -d'.' -f1)
  MEM_USAGE=$(free | grep Mem | awk '{print $3/$2 * 100.0}')
  MEM_USAGE_INT=$(echo "$MEM_USAGE" | cut -d'.' -f1)
  DISK_USAGE=$(df -h / | grep -v Filesystem | awk '{ print $5 " " $4 }')
  CONN_COUNT=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "SHOW GLOBAL STATUS LIKE 'Threads_connected';" | awk '/Threads_connected/{print $2}')

  log_message "[INFO] MySQL Status: CPU=${CPU_USAGE}%, MEM=${MEM_USAGE_INT}%, Disk=${DISK_USAGE}, Connections=${CONN_COUNT}"

  # Alerts
  if [ "$CPU_USAGE" -gt "$ALERT_CPU_THRESHOLD" ]; then log_message "[ALERT] High CPU Usage: ${CPU_USAGE}%"; fi
  if [ "$MEM_USAGE_INT" -gt "$ALERT_MEM_THRESHOLD" ]; then log_message "[ALERT] High Memory Usage: ${MEM_USAGE_INT}%"; fi
  if [[ "$DISK_USAGE" == *"%"* ]] && [ "$(echo $DISK_USAGE | awk '{print $1}' | sed 's/%//')" -gt "$ALERT_DISK_THRESHOLD" ]; then log_message "[ALERT] Low Disk Space: ${DISK_USAGE}"; fi
  if [ "$CONN_COUNT" -gt "$ALERT_CONN_THRESHOLD" ]; then log_message "[ALERT] High Connection Count: ${CONN_COUNT}"; fi
}

check_postgresql() {
  # Check Service Status
  if ! systemctl is-active --quiet postgresql;
  then
    log_message "[ERROR] PostgreSQL service is not running."
    return 1
  fi

  # Check Resource Usage (basic)
  CPU_USAGE=$(top -bn1 | grep "%Cpu(s)" | awk '{print $2}' | cut -d'.' -f1)
  MEM_USAGE=$(free | grep Mem | awk '{print $3/$2 * 100.0}')
  MEM_USAGE_INT=$(echo "$MEM_USAGE" | cut -d'.' -f1)
  # Assuming PostgreSQL data directory is on root partition for simplicity, adjust if needed
  DISK_USAGE=$(df -h / | grep -v Filesystem | awk '{ print $5 " " $4 }')
  CONN_COUNT=$(sudo -u postgres psql -h $DB_HOST -U postgres -c "SELECT count(*) FROM pg_stat_activity;" | awk '/^[[:space:]]*[0-9]+/{print $1}')

  log_message "[INFO] PostgreSQL Status: CPU=${CPU_USAGE}%, MEM=${MEM_USAGE_INT}%, Disk=${DISK_USAGE}, Connections=${CONN_COUNT}"

  # Alerts
  if [ "$CPU_USAGE" -gt "$ALERT_CPU_THRESHOLD" ]; then log_message "[ALERT] High CPU Usage: ${CPU_USAGE}%"; fi
  if [ "$MEM_USAGE_INT" -gt "$ALERT_MEM_THRESHOLD" ]; then log_message "[ALERT] High Memory Usage: ${MEM_USAGE_INT}%"; fi
  if [[ "$DISK_USAGE" == *"%"* ]] && [ "$(echo $DISK_USAGE | awk '{print $1}' | sed 's/%//')" -gt "$ALERT_DISK_THRESHOLD" ]; then log_message "[ALERT] Low Disk Space: ${DISK_USAGE}"; fi
  if [ "$CONN_COUNT" -gt "$ALERT_CONN_THRESHOLD" ]; then log_message "[ALERT] High Connection Count: ${CONN_COUNT}"; fi
}

if [ "$DB_TYPE" == "mysql" ]; then
  check_mysql
elif [ "$DB_TYPE" == "postgresql" ]; then
  check_postgresql
else
  log_message "[ERROR] Unsupported DB_TYPE: $DB_TYPE"
fi