Configure Netdata MySQL monitoring and database performance alerts

#!/usr/bin/env bash

set -euo pipefail

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

# Script variables
SCRIPT_NAME="$(basename "$0")"
MYSQL_USER="${1:-netdata}"
ADMIN_EMAIL="${2:-admin@example.com}"
NETWORK_RANGE="${3:-127.0.0.1}"

# Function definitions
log_info() { echo -e "${GREEN}[INFO]${NC} $1"; }
log_warn() { echo -e "${YELLOW}[WARN]${NC} $1"; }
log_error() { echo -e "${RED}[ERROR]${NC} $1"; }

usage() {
    cat << EOF
Usage: $SCRIPT_NAME [mysql_user] [admin_email] [network_range]

Arguments:
  mysql_user     MySQL monitoring user (default: netdata)
  admin_email    Email for alerts (default: admin@example.com)
  network_range  Network/IP for dashboard access (default: 127.0.0.1)

Examples:
  $SCRIPT_NAME
  $SCRIPT_NAME netdata alerts@company.com 192.168.1.0/24
  $SCRIPT_NAME monitoring admin@domain.com 10.0.0.0/8
EOF
    exit 1
}

cleanup() {
    log_error "Installation failed. Check logs above for details."
    exit 1
}

trap cleanup ERR

check_prerequisites() {
    if [[ $EUID -ne 0 ]]; then
        log_error "This script must be run as root or with sudo"
        exit 1
    fi

    if ! command -v curl >/dev/null 2>&1; then
        log_error "curl is required but not installed"
        exit 1
    fi
}

detect_distro() {
    if [[ ! -f /etc/os-release ]]; then
        log_error "Cannot detect distribution. /etc/os-release not found"
        exit 1
    fi

    . /etc/os-release
    case "$ID" in
        ubuntu|debian)
            PKG_MGR="apt"
            PKG_UPDATE="apt update"
            PKG_INSTALL="apt install -y"
            PKG_UPGRADE="apt upgrade -y"
            FIREWALL_CMD="ufw"
            ;;
        almalinux|rocky|centos|rhel|ol|fedora)
            PKG_MGR="dnf"
            PKG_UPDATE="dnf check-update || true"
            PKG_INSTALL="dnf install -y"
            PKG_UPGRADE="dnf update -y"
            FIREWALL_CMD="firewall-cmd"
            ;;
        amzn)
            PKG_MGR="yum"
            PKG_UPDATE="yum check-update || true"
            PKG_INSTALL="yum install -y"
            PKG_UPGRADE="yum update -y"
            FIREWALL_CMD="firewall-cmd"
            ;;
        *)
            log_error "Unsupported distribution: $ID"
            exit 1
            ;;
    esac

    log_info "Detected distribution: $PRETTY_NAME"
    log_info "Package manager: $PKG_MGR"
}

update_system() {
    log_info "[1/8] Updating system packages..."
    $PKG_UPDATE
    $PKG_UPGRADE
}

install_netdata() {
    log_info "[2/8] Installing Netdata monitoring agent..."
    bash <(curl -Ss https://my-netdata.io/kickstart.sh) --stable-channel --disable-telemetry
}

configure_mysql_user() {
    log_info "[3/8] Configuring MySQL monitoring user..."
    
    if ! command -v mysql >/dev/null 2>&1; then
        log_warn "MySQL client not found. Please create the monitoring user manually:"
        cat << EOF
CREATE USER '$MYSQL_USER'@'localhost';
GRANT USAGE, REPLICATION CLIENT, PROCESS ON *.* TO '$MYSQL_USER'@'localhost';
FLUSH PRIVILEGES;
EOF
        return
    fi

    # Try to create MySQL user automatically
    if mysql -e "SELECT 1" >/dev/null 2>&1; then
        mysql << EOF
CREATE USER IF NOT EXISTS '$MYSQL_USER'@'localhost';
GRANT USAGE, REPLICATION CLIENT, PROCESS ON *.* TO '$MYSQL_USER'@'localhost';
FLUSH PRIVILEGES;
EOF
        log_info "MySQL user '$MYSQL_USER' created successfully"
    else
        log_warn "Cannot connect to MySQL. Please create the monitoring user manually."
    fi
}

configure_netdata_mysql() {
    log_info "[4/8] Configuring Netdata MySQL monitoring..."
    
    NETDATA_CONFIG_DIR="/etc/netdata"
    MYSQL_CONF_DIR="$NETDATA_CONFIG_DIR/python.d"
    
    mkdir -p "$MYSQL_CONF_DIR"
    
    cat > "$MYSQL_CONF_DIR/mysql.conf" << EOF
localhost:
  name: 'local'
  host: 'localhost'
  port: 3306
  user: '$MYSQL_USER'
  update_every: 10
  priority: 90100
EOF

    chown netdata:netdata "$MYSQL_CONF_DIR/mysql.conf"
    chmod 644 "$MYSQL_CONF_DIR/mysql.conf"
}

configure_alerts() {
    log_info "[5/8] Configuring MySQL performance alerts..."
    
    HEALTH_CONFIG_DIR="/etc/netdata/health.d"
    mkdir -p "$HEALTH_CONFIG_DIR"
    
    cat > "$HEALTH_CONFIG_DIR/mysql_custom.conf" << 'EOF'
# MySQL Connection Pool Usage
alarm: mysql_connections_usage
    on: mysql_localhost.connections
  calc: $active * 100 / $limit
  warn: $this > 70
  crit: $this > 85
 every: 10s
 units: %
  info: MySQL connection pool usage percentage
    to: sysadmin

# MySQL Slow Queries Alert
alarm: mysql_slow_queries_rate
    on: mysql_localhost.slow_queries
  calc: $slow_queries
  warn: $this > 5
  crit: $this > 20
 every: 60s
 units: queries/s
  info: MySQL slow queries per second
    to: dba

# MySQL Query Cache Hit Rate
alarm: mysql_qcache_hit_rate
    on: mysql_localhost.qcache_ops
  calc: $hits * 100 / ($hits + $inserts + $not_cached)
  warn: $this < 80
  crit: $this < 60
 every: 60s
 units: %
  info: MySQL query cache hit rate percentage
    to: dba

# MySQL InnoDB Buffer Pool Usage
alarm: mysql_innodb_buffer_pool_usage
    on: mysql_localhost.innodb_buffer_pool_pages
  calc: $used * 100 / $total
  warn: $this > 85
  crit: $this > 95
 every: 30s
 units: %
  info: MySQL InnoDB buffer pool usage percentage
    to: sysadmin

# MySQL Replication Lag
alarm: mysql_slave_lag
    on: mysql_localhost.slave_lag
  calc: $seconds
  warn: $this > 30
  crit: $this > 120
 every: 30s
 units: seconds
  info: MySQL slave replication lag in seconds
    to: dba
EOF

    chown netdata:netdata "$HEALTH_CONFIG_DIR/mysql_custom.conf"
    chmod 644 "$HEALTH_CONFIG_DIR/mysql_custom.conf"
}

configure_notifications() {
    log_info "[6/8] Configuring email notifications..."
    
    HEALTH_ALARM_NOTIFY="/etc/netdata/health_alarm_notify.conf"
    
    # Backup original config if it exists
    if [[ -f "$HEALTH_ALARM_NOTIFY" ]]; then
        cp "$HEALTH_ALARM_NOTIFY" "$HEALTH_ALARM_NOTIFY.backup"
    fi
    
    cat > "$HEALTH_ALARM_NOTIFY" << EOF
SEND_EMAIL="YES"
DEFAULT_RECIPIENT_EMAIL="$ADMIN_EMAIL"
EMAIL_SENDER="netdata@\$(hostname -f)"
EMAIL_PLAINTEXT_ONLY="NO"

# Role-based recipients
role_recipients_sysadmin="$ADMIN_EMAIL"
role_recipients_dba="$ADMIN_EMAIL"

# Notification frequency
DEFAULT_RECIPIENT_CUSTOM_FREQUENCIES="critical:10m warning:1h"
EOF

    chown netdata:netdata "$HEALTH_ALARM_NOTIFY"
    chmod 644 "$HEALTH_ALARM_NOTIFY"
}

configure_firewall() {
    log_info "[7/8] Configuring firewall for Netdata access..."
    
    if [[ "$FIREWALL_CMD" == "ufw" ]]; then
        if command -v ufw >/dev/null 2>&1; then
            if [[ "$NETWORK_RANGE" == *"/"* ]] || [[ "$NETWORK_RANGE" == *"."*".0" ]]; then
                ufw allow from "$NETWORK_RANGE" to any port 19999
            else
                ufw allow from "$NETWORK_RANGE" to any port 19999
            fi
            ufw --force enable >/dev/null 2>&1 || true
        fi
    elif [[ "$FIREWALL_CMD" == "firewall-cmd" ]]; then
        if command -v firewall-cmd >/dev/null 2>&1; then
            if systemctl is-active firewalld >/dev/null 2>&1; then
                firewall-cmd --permanent --add-rich-rule="rule family=\"ipv4\" source address=\"$NETWORK_RANGE\" port protocol=\"tcp\" port=\"19999\" accept"
                firewall-cmd --reload
            fi
        fi
    fi
}

start_services() {
    log_info "[8/8] Starting and enabling Netdata service..."
    
    systemctl restart netdata
    systemctl enable netdata
    
    # Wait for service to start
    sleep 5
    
    if systemctl is-active netdata >/dev/null 2>&1; then
        log_info "Netdata service is running successfully"
    else
        log_error "Netdata service failed to start"
        exit 1
    fi
}

verify_installation() {
    log_info "Verifying installation..."
    
    # Check if Netdata is listening on port 19999
    if netstat -tlnp 2>/dev/null | grep -q ":19999" || ss -tlnp 2>/dev/null | grep -q ":19999"; then
        log_info "✓ Netdata web interface is accessible on port 19999"
    else
        log_warn "⚠ Netdata may not be listening on port 19999"
    fi
    
    # Check configuration files
    if [[ -f "/etc/netdata/python.d/mysql.conf" ]]; then
        log_info "✓ MySQL monitoring configuration is in place"
    fi
    
    if [[ -f "/etc/netdata/health.d/mysql_custom.conf" ]]; then
        log_info "✓ Custom MySQL alerts configured"
    fi
    
    log_info "Installation completed successfully!"
    log_info "Access Netdata dashboard at: http://$(hostname -I | awk '{print $1}'):19999"
    log_info "MySQL monitoring will appear automatically once MySQL is detected"
}

main() {
    if [[ "${1:-}" == "-h" ]] || [[ "${1:-}" == "--help" ]]; then
        usage
    fi
    
    log_info "Starting Netdata MySQL monitoring setup..."
    log_info "MySQL user: $MYSQL_USER"
    log_info "Admin email: $ADMIN_EMAIL" 
    log_info "Network access: $NETWORK_RANGE"
    
    check_prerequisites
    detect_distro
    update_system
    install_netdata
    configure_mysql_user
    configure_netdata_mysql
    configure_alerts
    configure_notifications
    configure_firewall
    start_services
    verify_installation
    
    log_info "Setup completed! Monitor your MySQL database performance at http://$(hostname -I | awk '{print $1}'):19999"
}

main "$@"