Monitor MariaDB Galera cluster with Prometheus and Grafana for high availability insights

#!/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'

# Default values
MARIADB_IPS="${MARIADB_IPS:-}"
EXPORTER_PASSWORD="${EXPORTER_PASSWORD:-$(openssl rand -base64 32)}"

# Usage function
usage() {
    echo "Usage: $0 [OPTIONS]"
    echo "Options:"
    echo "  -i, --ips          Comma-separated MariaDB cluster IPs (e.g., 10.0.1.10,10.0.1.11,10.0.1.12)"
    echo "  -p, --password     MySQL exporter password (auto-generated if not provided)"
    echo "  -h, --help         Show this help message"
    exit 1
}

# Parse arguments
while [[ $# -gt 0 ]]; do
    case $1 in
        -i|--ips) MARIADB_IPS="$2"; shift 2 ;;
        -p|--password) EXPORTER_PASSWORD="$2"; shift 2 ;;
        -h|--help) usage ;;
        *) echo -e "${RED}Unknown option: $1${NC}"; usage ;;
    esac
done

if [[ -z "$MARIADB_IPS" ]]; then
    echo -e "${RED}Error: MariaDB cluster IPs are required${NC}"
    usage
fi

# Cleanup function
cleanup() {
    echo -e "${RED}Installation failed. Cleaning up...${NC}"
    systemctl stop prometheus 2>/dev/null || true
    systemctl stop mysqld_exporter 2>/dev/null || true
    systemctl disable prometheus 2>/dev/null || true
    systemctl disable mysqld_exporter 2>/dev/null || true
    rm -rf /opt/prometheus /etc/prometheus /var/lib/prometheus
    rm -f /usr/local/bin/prometheus /usr/local/bin/promtool /usr/local/bin/mysqld_exporter
    userdel prometheus 2>/dev/null || true
    userdel mysqld_exporter 2>/dev/null || true
}

trap cleanup ERR

# Check if running as root
if [[ $EUID -ne 0 ]]; then
    echo -e "${RED}This script must be run as root${NC}"
    exit 1
fi

# Detect distribution
if [ -f /etc/os-release ]; then
    . /etc/os-release
    case "$ID" in
        ubuntu|debian) 
            PKG_MGR="apt"
            PKG_INSTALL="apt install -y"
            PKG_UPDATE="apt update && apt upgrade -y"
            ;;
        almalinux|rocky|centos|rhel|ol|fedora)
            PKG_MGR="dnf"
            PKG_INSTALL="dnf install -y"
            PKG_UPDATE="dnf update -y"
            ;;
        amzn)
            PKG_MGR="yum"
            PKG_INSTALL="yum install -y"
            PKG_UPDATE="yum update -y"
            ;;
        *)
            echo -e "${RED}Unsupported distribution: $ID${NC}"
            exit 1
            ;;
    esac
else
    echo -e "${RED}Cannot detect distribution${NC}"
    exit 1
fi

echo -e "${GREEN}Installing MariaDB Galera cluster monitoring with Prometheus and Grafana${NC}"

# Step 1: Update system packages
echo -e "${YELLOW}[1/10] Updating system packages...${NC}"
$PKG_UPDATE
$PKG_INSTALL wget curl tar openssl

# Step 2: Install Prometheus
echo -e "${YELLOW}[2/10] Installing Prometheus server...${NC}"
cd /tmp
wget -q https://github.com/prometheus/prometheus/releases/download/v2.48.0/prometheus-2.48.0.linux-amd64.tar.gz
tar xzf prometheus-2.48.0.linux-amd64.tar.gz
mv prometheus-2.48.0.linux-amd64 /opt/prometheus
useradd --no-create-home --shell /bin/false prometheus
chown -R prometheus:prometheus /opt/prometheus

# Step 3: Create Prometheus directories
echo -e "${YELLOW}[3/10] Creating Prometheus directories and configuration...${NC}"
mkdir -p /etc/prometheus /var/lib/prometheus
chown prometheus:prometheus /etc/prometheus /var/lib/prometheus
ln -sf /opt/prometheus/prometheus /usr/local/bin/
ln -sf /opt/prometheus/promtool /usr/local/bin/

# Step 4: Configure Prometheus
echo -e "${YELLOW}[4/10] Configuring Prometheus for MariaDB monitoring...${NC}"
IFS=',' read -ra IP_ARRAY <<< "$MARIADB_IPS"
MARIADB_TARGETS=""
NODE_TARGETS=""
for ip in "${IP_ARRAY[@]}"; do
    MARIADB_TARGETS="${MARIADB_TARGETS}          - '${ip}:9104'\n"
    NODE_TARGETS="${NODE_TARGETS}          - '${ip}:9100'\n"
done

cat > /etc/prometheus/prometheus.yml << EOF
global:
  scrape_interval: 15s
  evaluation_interval: 15s

rule_files:
  - "galera_alerts.yml"

scrape_configs:
  - job_name: 'prometheus'
    static_configs:
      - targets: ['localhost:9090']

  - job_name: 'mariadb-galera'
    static_configs:
      - targets: 
$(echo -e "$MARIADB_TARGETS")
    scrape_interval: 5s
    metrics_path: /metrics

  - job_name: 'node-exporter'
    static_configs:
      - targets:
$(echo -e "$NODE_TARGETS")
EOF

chown prometheus:prometheus /etc/prometheus/prometheus.yml

# Step 5: Create Galera alert rules
echo -e "${YELLOW}[5/10] Creating Galera alert rules...${NC}"
cat > /etc/prometheus/galera_alerts.yml << 'EOF'
groups:
  - name: galera_alerts
    rules:
      - alert: GaleraNodeDown
        expr: up{job="mariadb-galera"} == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "Galera node is down"
          description: "Galera node {{ $labels.instance }} has been down for more than 1 minute."

      - alert: GaleraClusterSplit
        expr: mysql_global_status_wsrep_cluster_size < 3
        for: 30s
        labels:
          severity: critical
        annotations:
          summary: "Galera cluster split detected"
          description: "Galera cluster size is {{ $value }}, indicating a potential split-brain scenario."
EOF

chown prometheus:prometheus /etc/prometheus/galera_alerts.yml

# Step 6: Install MariaDB exporter
echo -e "${YELLOW}[6/10] Installing MariaDB exporter...${NC}"
wget -q https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
tar xzf mysqld_exporter-0.15.0.linux-amd64.tar.gz
mv mysqld_exporter-0.15.0.linux-amd64/mysqld_exporter /usr/local/bin/
useradd --no-create-home --shell /bin/false mysqld_exporter
chmod 755 /usr/local/bin/mysqld_exporter

# Step 7: Configure MariaDB exporter credentials
echo -e "${YELLOW}[7/10] Configuring MariaDB exporter credentials...${NC}"
mkdir -p /etc/mysql
cat > /etc/mysql/.mysqld_exporter.cnf << EOF
[client]
user=exporter
password=$EXPORTER_PASSWORD
host=localhost
port=3306
EOF

chown mysqld_exporter:mysqld_exporter /etc/mysql/.mysqld_exporter.cnf
chmod 600 /etc/mysql/.mysqld_exporter.cnf

# Step 8: Create systemd services
echo -e "${YELLOW}[8/10] Creating systemd services...${NC}"

# Prometheus service
cat > /etc/systemd/system/prometheus.service << 'EOF'
[Unit]
Description=Prometheus Server
Wants=network-online.target
After=network-online.target

[Service]
User=prometheus
Group=prometheus
Type=simple
Restart=always
ExecStart=/usr/local/bin/prometheus \
  --config.file=/etc/prometheus/prometheus.yml \
  --storage.tsdb.path=/var/lib/prometheus \
  --web.console.templates=/opt/prometheus/consoles \
  --web.console.libraries=/opt/prometheus/console_libraries \
  --web.listen-address=0.0.0.0:9090

[Install]
WantedBy=multi-user.target
EOF

# MariaDB exporter service
cat > /etc/systemd/system/mysqld_exporter.service << 'EOF'
[Unit]
Description=MariaDB Exporter for Prometheus
Wants=network-online.target
After=network-online.target

[Service]
User=mysqld_exporter
Group=mysqld_exporter
Type=simple
Restart=always
ExecStart=/usr/local/bin/mysqld_exporter \
  --config.my-cnf=/etc/mysql/.mysqld_exporter.cnf \
  --collect.global_status \
  --collect.global_variables \
  --collect.slave_status \
  --collect.info_schema.processlist \
  --collect.info_schema.tables

[Install]
WantedBy=multi-user.target
EOF

# Step 9: Configure firewall
echo -e "${YELLOW}[9/10] Configuring firewall...${NC}"
if command -v firewall-cmd &> /dev/null; then
    firewall-cmd --permanent --add-port=9090/tcp --add-port=9104/tcp
    firewall-cmd --reload
elif command -v ufw &> /dev/null; then
    ufw allow 9090/tcp
    ufw allow 9104/tcp
fi

# Step 10: Start services
echo -e "${YELLOW}[10/10] Starting services...${NC}"
systemctl daemon-reload
systemctl enable prometheus mysqld_exporter
systemctl start prometheus mysqld_exporter

# Verification
echo -e "${YELLOW}Verifying installation...${NC}"
sleep 5

if systemctl is-active --quiet prometheus; then
    echo -e "${GREEN}✓ Prometheus is running${NC}"
else
    echo -e "${RED}✗ Prometheus failed to start${NC}"
    exit 1
fi

if systemctl is-active --quiet mysqld_exporter; then
    echo -e "${GREEN}✓ MariaDB exporter is running${NC}"
else
    echo -e "${RED}✗ MariaDB exporter failed to start${NC}"
    exit 1
fi

echo -e "${GREEN}Installation completed successfully!${NC}"
echo -e "${YELLOW}Next steps:${NC}"
echo "1. Create the monitoring user on each MariaDB node:"
echo "   mysql -u root -p"
echo "   CREATE USER 'exporter'@'localhost' IDENTIFIED BY '$EXPORTER_PASSWORD';"
echo "   GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';"
echo "   GRANT SELECT ON performance_schema.* TO 'exporter'@'localhost';"
echo "   FLUSH PRIVILEGES;"
echo ""
echo "2. Install node_exporter on MariaDB nodes for system metrics"
echo "3. Access Prometheus at: http://$(hostname -I | awk '{print $1}'):9090"
echo "4. Configure Grafana to use Prometheus as datasource"