Configure MySQL backup monitoring with Prometheus alerts and Grafana dashboards

#!/usr/bin/env bash
set -euo pipefail

# Production MySQL Backup Monitoring Setup Script
# Configures Prometheus monitoring, Grafana dashboards, and automated MySQL backups

# Color definitions
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

# Default values
MYSQL_ROOT_PASSWORD=""
BACKUP_PASSWORD="SecureBackupPassword123!"

# Usage function
usage() {
    echo "Usage: $0 [--mysql-root-password PASSWORD] [--backup-password PASSWORD]"
    echo "  --mysql-root-password: MySQL root password (will prompt if not provided)"
    echo "  --backup-password: Password for backup user (default: SecureBackupPassword123!)"
    exit 1
}

# Parse arguments
while [[ $# -gt 0 ]]; do
    case $1 in
        --mysql-root-password)
            MYSQL_ROOT_PASSWORD="$2"
            shift 2
            ;;
        --backup-password)
            BACKUP_PASSWORD="$2"
            shift 2
            ;;
        --help|-h)
            usage
            ;;
        *)
            echo -e "${RED}Error: Unknown argument $1${NC}"
            usage
            ;;
    esac
done

# Cleanup function for rollback
cleanup() {
    echo -e "${YELLOW}Cleaning up on error...${NC}"
    systemctl stop node_exporter 2>/dev/null || true
    userdel backup_user 2>/dev/null || true
    rm -f /etc/systemd/system/node_exporter.service
    rm -rf /opt/backup-monitoring /var/backups/mysql
}

trap cleanup ERR

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

# Auto-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"
            MYSQL_CLIENT="mysql-client-core-8.0"
            ;;
        almalinux|rocky|centos|rhel|ol)
            PKG_MGR="dnf"
            PKG_INSTALL="dnf install -y"
            PKG_UPDATE="dnf update -y"
            MYSQL_CLIENT="mysql"
            ;;
        fedora)
            PKG_MGR="dnf"
            PKG_INSTALL="dnf install -y"
            PKG_UPDATE="dnf update -y"
            MYSQL_CLIENT="mysql"
            ;;
        amzn)
            PKG_MGR="yum"
            PKG_INSTALL="yum install -y"
            PKG_UPDATE="yum update -y"
            MYSQL_CLIENT="mysql"
            ;;
        *)
            echo -e "${RED}Error: Unsupported distribution: $ID${NC}"
            exit 1
            ;;
    esac
else
    echo -e "${RED}Error: Cannot detect distribution${NC}"
    exit 1
fi

echo -e "${GREEN}[1/8] Updating system packages...${NC}"
$PKG_UPDATE

echo -e "${GREEN}[2/8] Installing MySQL backup tools...${NC}"
$PKG_INSTALL $MYSQL_CLIENT curl gnupg2 wget

# Install Percona XtraBackup
if [[ "$PKG_MGR" == "apt" ]]; then
    CODENAME=$(lsb_release -sc)
    wget -q https://repo.percona.com/apt/percona-release_latest.${CODENAME}_all.deb
    dpkg -i percona-release_latest.${CODENAME}_all.deb
    apt update
    $PKG_INSTALL percona-xtrabackup-80
    rm -f percona-release_latest.${CODENAME}_all.deb
else
    $PKG_INSTALL https://repo.percona.com/yum/percona-release-latest.noarch.rpm
    percona-release enable-only tools release
    $PKG_INSTALL percona-xtrabackup-80
fi

echo -e "${GREEN}[3/8] Installing Node Exporter...${NC}"
NODE_EXPORTER_VERSION="1.7.0"
wget -q https://github.com/prometheus/node_exporter/releases/download/v${NODE_EXPORTER_VERSION}/node_exporter-${NODE_EXPORTER_VERSION}.linux-amd64.tar.gz
tar xzf node_exporter-${NODE_EXPORTER_VERSION}.linux-amd64.tar.gz
cp node_exporter-${NODE_EXPORTER_VERSION}.linux-amd64/node_exporter /usr/local/bin/
rm -rf node_exporter-${NODE_EXPORTER_VERSION}.linux-amd64*

# Create node_exporter user
useradd --no-create-home --shell /bin/false node_exporter
mkdir -p /var/lib/node_exporter/textfile_collector
chown -R node_exporter:node_exporter /var/lib/node_exporter
chmod 755 /var/lib/node_exporter
chmod 755 /var/lib/node_exporter/textfile_collector

# Create systemd service
cat > /etc/systemd/system/node_exporter.service << 'EOF'
[Unit]
Description=Node Exporter
Wants=network-online.target
After=network-online.target

[Service]
User=node_exporter
Group=node_exporter
Type=simple
ExecStart=/usr/local/bin/node_exporter --collector.textfile.directory=/var/lib/node_exporter/textfile_collector

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl enable node_exporter
systemctl start node_exporter

echo -e "${GREEN}[4/8] Creating backup directory structure...${NC}"
mkdir -p /var/backups/mysql/{daily,weekly,monthly}
mkdir -p /opt/backup-monitoring
mkdir -p /var/log
touch /var/log/mysql-backup.log

# Create mysql user if it doesn't exist
if ! id mysql >/dev/null 2>&1; then
    useradd --system --home /var/lib/mysql --shell /bin/false mysql
fi

chown -R mysql:mysql /var/backups/mysql
chmod 750 /var/backups/mysql
chmod 750 /var/backups/mysql/{daily,weekly,monthly}
chmod 644 /var/log/mysql-backup.log

echo -e "${GREEN}[5/8] Creating MySQL backup user...${NC}"
if [[ -z "$MYSQL_ROOT_PASSWORD" ]]; then
    echo -e "${YELLOW}Please enter MySQL root password:${NC}"
    read -s MYSQL_ROOT_PASSWORD
fi

mysql -u root -p"$MYSQL_ROOT_PASSWORD" << EOF
CREATE USER IF NOT EXISTS 'backup_user'@'localhost' IDENTIFIED BY '$BACKUP_PASSWORD';
GRANT SELECT, LOCK TABLES, SHOW VIEW, PROCESS, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
GRANT RELOAD ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
EOF

echo -e "${GREEN}[6/8] Creating backup monitoring script...${NC}"
cat > /opt/backup-monitoring/mysql_backup.sh << EOF
#!/bin/bash
set -euo pipefail

METRICS_FILE="/var/lib/node_exporter/textfile_collector/mysql_backup.prom"
BACKUP_DIR="/var/backups/mysql/daily"
LOG_FILE="/var/log/mysql-backup.log"
DB_USER="backup_user"
DB_PASS="$BACKUP_PASSWORD"
DATE=\$(date +%Y%m%d_%H%M%S)
BACKUP_NAME="mysql_backup_\${DATE}.sql.gz"

# Initialize metrics
{
    echo "# HELP mysql_backup_duration_seconds Time taken for MySQL backup in seconds"
    echo "# TYPE mysql_backup_duration_seconds gauge"
    echo "# HELP mysql_backup_size_bytes Size of MySQL backup file in bytes"
    echo "# TYPE mysql_backup_size_bytes gauge"
    echo "# HELP mysql_backup_success Backup success status (1=success, 0=failure)"
    echo "# TYPE mysql_backup_success gauge"
    echo "# HELP mysql_backup_timestamp_seconds Timestamp of last backup"
    echo "# TYPE mysql_backup_timestamp_seconds gauge"
} > \$METRICS_FILE

START_TIME=\$(date +%s)
echo "\$(date): Starting MySQL backup" >> \$LOG_FILE

if mysqldump -u \$DB_USER -p\$DB_PASS --single-transaction --routines --triggers --all-databases | gzip > "\${BACKUP_DIR}/\${BACKUP_NAME}"; then
    END_TIME=\$(date +%s)
    DURATION=\$((END_TIME - START_TIME))
    BACKUP_SIZE=\$(stat -c%s "\${BACKUP_DIR}/\${BACKUP_NAME}")
    
    echo "\$(date): Backup completed successfully. Size: \${BACKUP_SIZE} bytes, Duration: \${DURATION}s" >> \$LOG_FILE
    
    {
        echo "mysql_backup_duration_seconds \$DURATION"
        echo "mysql_backup_size_bytes \$BACKUP_SIZE"
        echo "mysql_backup_success 1"
        echo "mysql_backup_timestamp_seconds \$END_TIME"
    } >> \$METRICS_FILE
    
    find \$BACKUP_DIR -name "mysql_backup_*.sql.gz" -mtime +7 -delete
    exit 0
else
    END_TIME=\$(date +%s)
    DURATION=\$((END_TIME - START_TIME))
    
    echo "\$(date): Backup failed after \${DURATION}s" >> \$LOG_FILE
    
    {
        echo "mysql_backup_duration_seconds \$DURATION"
        echo "mysql_backup_size_bytes 0"
        echo "mysql_backup_success 0"
        echo "mysql_backup_timestamp_seconds \$END_TIME"
    } >> \$METRICS_FILE
    
    exit 1
fi
EOF

chmod 750 /opt/backup-monitoring/mysql_backup.sh
chown mysql:mysql /opt/backup-monitoring/mysql_backup.sh

echo -e "${GREEN}[7/8] Setting up cron job...${NC}"
echo "0 2 * * * mysql /opt/backup-monitoring/mysql_backup.sh" > /etc/cron.d/mysql-backup
chmod 644 /etc/cron.d/mysql-backup

echo -e "${GREEN}[8/8] Verifying installation...${NC}"

# Test backup script
sudo -u mysql /opt/backup-monitoring/mysql_backup.sh

# Verify services
if systemctl is-active --quiet node_exporter; then
    echo -e "${GREEN}✓ Node Exporter is running${NC}"
else
    echo -e "${RED}✗ Node Exporter failed to start${NC}"
    exit 1
fi

# Verify metrics file
if [[ -f /var/lib/node_exporter/textfile_collector/mysql_backup.prom ]]; then
    echo -e "${GREEN}✓ Backup metrics file created${NC}"
else
    echo -e "${RED}✗ Backup metrics file not found${NC}"
    exit 1
fi

# Verify backup file
if ls /var/backups/mysql/daily/mysql_backup_*.sql.gz 1> /dev/null 2>&1; then
    echo -e "${GREEN}✓ MySQL backup completed successfully${NC}"
else
    echo -e "${RED}✗ MySQL backup failed${NC}"
    exit 1
fi

echo -e "${GREEN}Installation completed successfully!${NC}"
echo -e "${YELLOW}Next steps:${NC}"
echo "1. Configure Prometheus to scrape Node Exporter on port 9100"
echo "2. Add alerting rules for mysql_backup_success metric"
echo "3. Import Grafana dashboard for backup monitoring"
echo "4. Backups are scheduled daily at 2 AM via cron"