#!/usr/bin/env bash set -euo pipefail usage() { cat <<'EOF' Использование: mysql_health.sh [--host HOST] [--port PORT] [--socket PATH] [--user USER] [--defaults-file FILE] [--top N] Читает состояние MySQL или MariaDB. Пароль в аргументах не принимается. EOF } host="" port="" socket="" user="" defaults_file="" top=10 while [[ $# -gt 0 ]]; do case "$1" in --host) [[ $# -ge 2 ]] || exit 2; host="$2"; shift 2 ;; --port) [[ $# -ge 2 ]] || exit 2; port="$2"; shift 2 ;; --socket) [[ $# -ge 2 ]] || exit 2; socket="$2"; shift 2 ;; --user) [[ $# -ge 2 ]] || exit 2; user="$2"; shift 2 ;; --defaults-file) [[ $# -ge 2 ]] || exit 2; defaults_file="$2"; shift 2 ;; --top) [[ $# -ge 2 ]] || exit 2; top="$2"; shift 2 ;; -h|--help) usage; exit 0 ;; *) echo "Неизвестный аргумент: $1" >&2; usage >&2; exit 2 ;; esac done [[ "$top" =~ ^[0-9]+$ ]] || { echo "--top должен быть числом" >&2; exit 2; } command -v mysql >/dev/null 2>&1 || { echo "mysql не найден. Установите mariadb-client или mysql-client." >&2; exit 2; } command -v mysqladmin >/dev/null 2>&1 || { echo "mysqladmin не найден." >&2; exit 2; } conn=() [[ -n "$defaults_file" ]] && conn+=("--defaults-file=$defaults_file") [[ -n "$host" ]] && conn+=("--host=$host") [[ -n "$port" ]] && conn+=("--port=$port") [[ -n "$socket" ]] && conn+=("--socket=$socket") [[ -n "$user" ]] && conn+=("--user=$user") if ! mysqladmin "${conn[@]}" ping >/dev/null 2>&1; then echo "Сервер не отвечает на ping." >&2 exit 1 fi echo "== Статус ==" mysqladmin "${conn[@]}" status 2>/dev/null || true status_all="$(mysql "${conn[@]}" --batch --skip-column-names -e 'SHOW GLOBAL STATUS' 2>/dev/null || true)" if [[ -z "$status_all" ]]; then echo "Не удалось прочитать SHOW GLOBAL STATUS. Проверьте права пользователя." >&2 exit 1 fi declare -A metrics=() while IFS=$'\t' read -r name value; do [[ -n "$name" ]] && metrics["$name"]="$value" done <<< "$status_all" echo echo "== Метрики ==" echo "Uptime: ${metrics[Uptime]:-unknown} c" echo "Threads connected: ${metrics[Threads_connected]:-unknown}" echo "Threads running: ${metrics[Threads_running]:-unknown}" echo "Queries: ${metrics[Queries]:-unknown}" echo "Slow queries: ${metrics[Slow_queries]:-unknown}" echo "Aborted connects: ${metrics[Aborted_connects]:-unknown}" echo "Opened tables: ${metrics[Opened_tables]:-unknown}" uptime="${metrics[Uptime]:-0}" queries="${metrics[Queries]:-0}" if [[ "$uptime" =~ ^[0-9]+$ && "$queries" =~ ^[0-9]+$ ]] && ((uptime > 0)); then echo "Средний QPS: $((queries / uptime))" fi reads="${metrics[Innodb_buffer_pool_reads]:-0}" requests="${metrics[Innodb_buffer_pool_read_requests]:-0}" if [[ "$reads" =~ ^[0-9]+$ && "$requests" =~ ^[0-9]+$ ]] && ((requests > 0)); then hit="$(awk -v r="$reads" -v q="$requests" 'BEGIN {printf "%.2f", (1 - r / q) * 100}')" echo "Buffer pool hit: ${hit}%" fi echo echo "== Репликация ==" replica="$(mysql "${conn[@]}" -e 'SHOW REPLICA STATUS\G' 2>/dev/null || true)" if [[ -z "$replica" ]]; then replica="$(mysql "${conn[@]}" -e 'SHOW SLAVE STATUS\G' 2>/dev/null || true)" fi if [[ -z "$replica" ]]; then echo "Репликация не настроена или нет доступа." else get_repl() { awk -F': ' -v key="$1" '$1 ~ key {gsub(/^[ \t]+/, "", $2); print $2; exit}' <<< "$replica" } io="$(get_repl 'Replica_IO_Running')" [[ -n "$io" ]] || io="$(get_repl 'Slave_IO_Running')" sql="$(get_repl 'Replica_SQL_Running')" [[ -n "$sql" ]] || sql="$(get_repl 'Slave_SQL_Running')" lag="$(get_repl 'Seconds_Behind_Source')" [[ -n "$lag" ]] || lag="$(get_repl 'Seconds_Behind_Master')" echo "IO: ${io:-unknown}" echo "SQL: ${sql:-unknown}" echo "Lag: ${lag:-unknown}" fi echo echo "== Размеры баз ==" mysql "${conn[@]}" --table -e "SELECT table_schema AS db, ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS mb FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC LIMIT $top;" 2>/dev/null || echo "Нет доступа к information_schema." echo if [[ -n "${replica:-}" ]]; then if [[ "${io:-}" != "Yes" || "${sql:-}" != "Yes" ]]; then echo "Репликация не в норме." exit 1 fi fi echo "Сервер отвечает, критичных ошибок не найдено."