8.2 運用管理 ​

#!/bin/bash
# Zabbix日常運用自動チェックスクリプト

SCRIPT_DIR="/opt/zabbix/scripts"
LOG_DIR="/var/log/zabbix/operations"
CONFIG_FILE="/etc/zabbix/operations.conf"
REPORT_FILE="$LOG_DIR/daily_report_$(date +%Y%m%d).txt"

# 設定読み込み
source "$CONFIG_FILE"

# ログ出力関数
log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$REPORT_FILE"
}

# Zabbixサーバーステータス確認
check_zabbix_server() {
    log_message "=== Zabbix Server Status Check ==="
    
    # プロセス確認
    if pgrep -f zabbix_server > /dev/null; then
        log_message "✓ Zabbix server process is running"
    else
        log_message "✗ Zabbix server process is not running"
        return 1
    fi
    
    # ポート確認
    if netstat -ln | grep -q ":10051.*LISTEN"; then
        log_message "✓ Zabbix server port 10051 is listening"
    else
        log_message "✗ Zabbix server port 10051 is not listening"
        return 1
    fi
    
    # 設定ファイル確認
    if zabbix_server -t; then
        log_message "✓ Zabbix server configuration is valid"
    else
        log_message "✗ Zabbix server configuration has errors"
        return 1
    fi
    
    return 0
}

# データベース状況確認
check_database() {
    log_message "=== Database Status Check ==="
    
    # 接続確認
    if mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "SELECT 1;" "$DB_NAME" &>/dev/null; then
        log_message "✓ Database connection successful"
    else
        log_message "✗ Database connection failed"
        return 1
    fi
    
    # データベースサイズ確認
    DB_SIZE=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'DB Size MB'
        FROM information_schema.tables 
        WHERE table_schema='$DB_NAME';" -s -N)
    
    log_message "Database size: ${DB_SIZE} MB"
    
    # テーブルサイズ確認（上位5つ）
    log_message "Largest tables:"
    mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT table_name, 
               ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size MB'
        FROM information_schema.TABLES 
        WHERE table_schema = '$DB_NAME'
        ORDER BY (data_length + index_length) DESC 
        LIMIT 5;" | while read line; do
        log_message "  $line"
    done
    
    # 接続数確認
    CONNECTIONS=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "SHOW STATUS LIKE 'Threads_connected';" -s -N | awk '{print $2}')
    MAX_CONNECTIONS=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "SHOW VARIABLES LIKE 'max_connections';" -s -N | awk '{print $2}')
    CONNECTION_USAGE=$(echo "scale=2; $CONNECTIONS * 100 / $MAX_CONNECTIONS" | bc)
    
    log_message "Database connections: $CONNECTIONS/$MAX_CONNECTIONS (${CONNECTION_USAGE}%)"
    
    if (( $(echo "$CONNECTION_USAGE > 80" | bc -l) )); then
        log_message "⚠ High connection usage detected"
    fi
    
    return 0
}

# 監視対象ヘルス確認
check_monitored_hosts() {
    log_message "=== Monitored Hosts Health Check ==="
    
    # エージェント接続確認
    AGENT_COUNT=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT COUNT(*) FROM hosts h
        JOIN interface i ON h.hostid = i.hostid
        WHERE h.status = 0 AND i.available = 1 AND i.type = 1;" -s -N "$DB_NAME")
    
    TOTAL_AGENT_COUNT=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT COUNT(*) FROM hosts h
        JOIN interface i ON h.hostid = i.hostid
        WHERE h.status = 0 AND i.type = 1;" -s -N "$DB_NAME")
    
    AGENT_AVAILABILITY=$(echo "scale=2; $AGENT_COUNT * 100 / $TOTAL_AGENT_COUNT" | bc)
    log_message "Agent availability: $AGENT_COUNT/$TOTAL_AGENT_COUNT (${AGENT_AVAILABILITY}%)"
    
    # 問題ホスト一覧
    log_message "Hosts with problems:"
    mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT h.host, p.name, p.severity, FROM_UNIXTIME(p.clock) as problem_time
        FROM hosts h
        JOIN problem p ON h.hostid = p.objectid
        WHERE p.source = 0 AND p.object = 0
        ORDER BY p.severity DESC, p.clock DESC
        LIMIT 10;" "$DB_NAME" | while read line; do
        log_message "  $line"
    done
    
    return 0
}

# システムリソース確認
check_system_resources() {
    log_message "=== System Resources Check ==="
    
    # CPU使用率
    CPU_USAGE=$(top -bn1 | grep "Cpu(s)" | awk '{print $2}' | sed 's/%us,//')
    log_message "CPU usage: ${CPU_USAGE}%"
    
    if (( $(echo "$CPU_USAGE > 80" | bc -l) )); then
        log_message "⚠ High CPU usage detected"
    fi
    
    # メモリ使用率
    MEMORY_INFO=$(free | grep Mem)
    TOTAL_MEM=$(echo $MEMORY_INFO | awk '{print $2}')
    USED_MEM=$(echo $MEMORY_INFO | awk '{print $3}')
    MEMORY_USAGE=$(echo "scale=2; $USED_MEM * 100 / $TOTAL_MEM" | bc)
    
    log_message "Memory usage: ${MEMORY_USAGE}%"
    
    if (( $(echo "$MEMORY_USAGE > 85" | bc -l) )); then
        log_message "⚠ High memory usage detected"
    fi
    
    # ディスク使用率
    log_message "Disk usage:"
    df -h | grep -E '^/dev/' | while read line; do
        USAGE=$(echo $line | awk '{print $5}' | sed 's/%//')
        MOUNT=$(echo $line | awk '{print $6}')
        log_message "  $MOUNT: ${USAGE}%"
        
        if [ "$USAGE" -gt 90 ]; then
            log_message "  ⚠ High disk usage on $MOUNT"
        fi
    done
    
    return 0
}

# Zabbix内部メトリクス確認
check_zabbix_internals() {
    log_message "=== Zabbix Internal Metrics ==="
    
    # キュー状況確認
    QUEUE_INFO=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT 
            SUM(CASE WHEN i.type = 0 THEN 1 ELSE 0 END) as 'Zabbix agent items',
            SUM(CASE WHEN i.type = 7 THEN 1 ELSE 0 END) as 'Zabbix agent active items',
            SUM(CASE WHEN i.type = 4 THEN 1 ELSE 0 END) as 'SNMP items',
            COUNT(*) as 'Total items'
        FROM items i
        JOIN hosts h ON i.hostid = h.hostid
        WHERE i.status = 0 AND h.status = 0;" -s -N "$DB_NAME")
    
    log_message "Item counts: $QUEUE_INFO"
    
    # 値更新状況
    LATEST_DATA=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT 
            COUNT(*) as items_updated_last_hour
        FROM items i
        JOIN hosts h ON i.hostid = h.hostid
        WHERE i.status = 0 AND h.status = 0 
        AND i.lastvalue_ts > UNIX_TIMESTAMP() - 3600;" -s -N "$DB_NAME")
    
    log_message "Items updated in last hour: $LATEST_DATA"
    
    # トリガー状況
    TRIGGER_INFO=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT 
            COUNT(CASE WHEN value = 1 THEN 1 END) as 'Problem triggers',
            COUNT(CASE WHEN value = 0 THEN 1 END) as 'OK triggers',
            COUNT(*) as 'Total enabled triggers'
        FROM triggers t
        JOIN hosts h ON t.hostid = h.hostid
        WHERE t.status = 0 AND h.status = 0;" -s -N "$DB_NAME")
    
    log_message "Trigger status: $TRIGGER_INFO"
    
    return 0
}

# アラート統計
generate_alert_statistics() {
    log_message "=== Alert Statistics (Last 24h) ==="
    
    # 深刻度別問題数
    mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT 
            CASE severity
                WHEN 0 THEN 'Not classified'
                WHEN 1 THEN 'Information'
                WHEN 2 THEN 'Warning'
                WHEN 3 THEN 'Average'
                WHEN 4 THEN 'High'
                WHEN 5 THEN 'Disaster'
            END as 'Severity',
            COUNT(*) as 'Count'
        FROM problem
        WHERE clock > UNIX_TIMESTAMP() - 86400
        GROUP BY severity
        ORDER BY severity DESC;" "$DB_NAME" | while read line; do
        log_message "  $line"
    done
    
    # 上位問題ホスト
    log_message "Top 5 hosts with most problems (Last 24h):"
    mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT h.host, COUNT(*) as problem_count
        FROM problem p
        JOIN hosts h ON p.objectid = h.hostid
        WHERE p.clock > UNIX_TIMESTAMP() - 86400
        AND p.source = 0 AND p.object = 0
        GROUP BY h.hostid, h.host
        ORDER BY problem_count DESC
        LIMIT 5;" "$DB_NAME" | while read line; do
        log_message "  $line"
    done
}

# 総合評価
generate_summary() {
    log_message "=== Daily Operation Summary ==="
    
    # 全体的な健康度スコア計算
    local score=100
    local issues=0
    
    # システムリソースチェック
    CPU_USAGE=$(top -bn1 | grep "Cpu(s)" | awk '{print $2}' | sed 's/%us,//')
    if (( $(echo "$CPU_USAGE > 80" | bc -l) )); then
        score=$((score - 20))
        issues=$((issues + 1))
    fi
    
    # メモリ使用率チェック
    MEMORY_INFO=$(free | grep Mem)
    TOTAL_MEM=$(echo $MEMORY_INFO | awk '{print $2}')
    USED_MEM=$(echo $MEMORY_INFO | awk '{print $3}')
    MEMORY_USAGE=$(echo "scale=2; $USED_MEM * 100 / $TOTAL_MEM" | bc)
    
    if (( $(echo "$MEMORY_USAGE > 85" | bc -l) )); then
        score=$((score - 15))
        issues=$((issues + 1))
    fi
    
    # 問題数チェック
    PROBLEM_COUNT=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT COUNT(*) FROM problem 
        WHERE source = 0 AND object = 0;" -s -N "$DB_NAME")
    
    if [ "$PROBLEM_COUNT" -gt 10 ]; then
        score=$((score - 20))
        issues=$((issues + 1))
    elif [ "$PROBLEM_COUNT" -gt 5 ]; then
        score=$((score - 10))
        issues=$((issues + 1))
    fi
    
    # 健康度判定
    if [ "$score" -ge 90 ]; then
        status="EXCELLENT"
        emoji="🟢"
    elif [ "$score" -ge 70 ]; then
        status="GOOD"
        emoji="🟡"
    elif [ "$score" -ge 50 ]; then
        status="WARNING"
        emoji="🟠"
    else
        status="CRITICAL"
        emoji="🔴"
    fi
    
    log_message "$emoji Overall Health Score: $score/100 ($status)"
    log_message "Issues detected: $issues"
    
    # 推奨アクション
    if [ "$issues" -gt 0 ]; then
        log_message "Recommended actions:"
        if (( $(echo "$CPU_USAGE > 80" | bc -l) )); then
            log_message "  - Investigate high CPU usage"
        fi
        if (( $(echo "$MEMORY_USAGE > 85" | bc -l) )); then
            log_message "  - Investigate high memory usage"
        fi
        if [ "$PROBLEM_COUNT" -gt 5 ]; then
            log_message "  - Review and resolve active problems"
        fi
    fi
}

# 通知送信
send_notification() {
    local status="$1"
    local score="$2"
    
    # Slack通知
    if [ -n "$SLACK_WEBHOOK_URL" ]; then
        curl -X POST -H 'Content-type: application/json' \
            --data "{\"text\":\"📊 Zabbix Daily Health Report\\nScore: $score/100 ($status)\\nDetailed report: $REPORT_FILE\"}" \
            "$SLACK_WEBHOOK_URL"
    fi
    
    # メール通知（重要な問題がある場合のみ）
    if [ "$score" -lt 70 ] && [ -n "$ADMIN_EMAIL" ]; then
        mail -s "Zabbix Health Alert: $status ($score/100)" "$ADMIN_EMAIL" < "$REPORT_FILE"
    fi
}

# メイン実行
main() {
    # ログディレクトリ作成
    mkdir -p "$LOG_DIR"
    
    log_message "Starting Zabbix daily operations check..."
    
    # 各チェック実行
    check_zabbix_server
    check_database
    check_monitored_hosts
    check_system_resources
    check_zabbix_internals
    generate_alert_statistics
    
    # 総合評価
    generate_summary
    
    # 通知送信
    CPU_USAGE=$(top -bn1 | grep "Cpu(s)" | awk '{print $2}' | sed 's/%us,//')
    MEMORY_INFO=$(free | grep Mem)
    TOTAL_MEM=$(echo $MEMORY_INFO | awk '{print $2}')
    USED_MEM=$(echo $MEMORY_INFO | awk '{print $3}')
    MEMORY_USAGE=$(echo "scale=2; $USED_MEM * 100 / $TOTAL_MEM" | bc)
    PROBLEM_COUNT=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT COUNT(*) FROM problem 
        WHERE source = 0 AND object = 0;" -s -N "$DB_NAME")
    
    # スコア再計算（通知用）
    score=100
    if (( $(echo "$CPU_USAGE > 80" | bc -l) )); then score=$((score - 20)); fi
    if (( $(echo "$MEMORY_USAGE > 85" | bc -l) )); then score=$((score - 15)); fi
    if [ "$PROBLEM_COUNT" -gt 10 ]; then score=$((score - 20)); fi
    
    if [ "$score" -ge 90 ]; then
        status="EXCELLENT"
    elif [ "$score" -ge 70 ]; then
        status="GOOD"
    elif [ "$score" -ge 50 ]; then
        status="WARNING"
    else
        status="CRITICAL"
    fi
    
    send_notification "$status" "$score"
    
    log_message "Daily operations check completed"
}

# スクリプト実行
main "$@"

#!/bin/bash
# Zabbix包括的バックアップシステム

BACKUP_CONFIG="/etc/zabbix/backup.conf"
LOG_FILE="/var/log/zabbix/backup.log"
NOTIFICATION_CONFIG="/etc/zabbix/notifications.conf"

# 設定読み込み
source "$BACKUP_CONFIG"
source "$NOTIFICATION_CONFIG"

# ログ関数
log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# 通知関数
send_notification() {
    local status="$1"
    local message="$2"
    local severity="$3"
    
    # Slack通知
    if [ -n "$SLACK_WEBHOOK_URL" ]; then
        local emoji
        case "$severity" in
            "success") emoji="✅" ;;
            "warning") emoji="⚠️" ;;
            "error") emoji="❌" ;;
            *) emoji="ℹ️" ;;
        esac
        
        curl -X POST -H 'Content-type: application/json' \
            --data "{\"text\":\"$emoji Zabbix Backup Report\\nStatus: $status\\n$message\"}" \
            "$SLACK_WEBHOOK_URL"
    fi
    
    # メール通知（エラー時のみ）
    if [ "$severity" = "error" ] && [ -n "$ADMIN_EMAIL" ]; then
        echo "$message" | mail -s "Zabbix Backup Error: $status" "$ADMIN_EMAIL"
    fi
}

# ディスク容量確認
check_disk_space() {
    local backup_dir="$1"
    local required_space="$2"  # GB単位
    
    local available_space=$(df "$backup_dir" | awk 'NR==2 {print int($4/1024/1024)}')
    
    if [ "$available_space" -lt "$required_space" ]; then
        log_message "ERROR: Insufficient disk space. Required: ${required_space}GB, Available: ${available_space}GB"
        return 1
    fi
    
    log_message "Disk space check passed: ${available_space}GB available"
    return 0
}

# 設定ファイルバックアップ
backup_configuration() {
    log_message "Starting configuration backup..."
    
    local config_backup_dir="$BACKUP_DIR/config/$(date +%Y%m%d_%H%M%S)"
    mkdir -p "$config_backup_dir"
    
    # Zabbix設定
    if [ -d /etc/zabbix ]; then
        tar -czf "$config_backup_dir/zabbix_config.tar.gz" /etc/zabbix/
        log_message "Zabbix configuration backed up"
    fi
    
    # Apache/Nginx設定
    if [ -d /etc/apache2 ]; then
        tar -czf "$config_backup_dir/apache_config.tar.gz" /etc/apache2/
        log_message "Apache configuration backed up"
    fi
    
    if [ -d /etc/nginx ]; then
        tar -czf "$config_backup_dir/nginx_config.tar.gz" /etc/nginx/
        log_message "Nginx configuration backed up"
    fi
    
    # SSL証明書
    if [ -d /etc/ssl ]; then
        tar -czf "$config_backup_dir/ssl_certs.tar.gz" /etc/ssl/
        log_message "SSL certificates backed up"
    fi
    
    # カスタムスクリプト
    if [ -d /opt/zabbix ]; then
        tar -czf "$config_backup_dir/custom_scripts.tar.gz" /opt/zabbix/
        log_message "Custom scripts backed up"
    fi
    
    # メタデータファイル作成
    cat > "$config_backup_dir/backup_metadata.txt" << EOF
Backup Type: Configuration
Backup Date: $(date)
Hostname: $(hostname)
Zabbix Version: $(zabbix_server --version | head -1)
Database Type: $DB_TYPE
Backup Size: $(du -sh "$config_backup_dir" | cut -f1)
EOF
    
    log_message "Configuration backup completed: $config_backup_dir"
    return 0
}

# データベース完全バックアップ
backup_database_full() {
    log_message "Starting full database backup..."
    
    local db_backup_dir="$BACKUP_DIR/database/full/$(date +%Y%m%d_%H%M%S)"
    mkdir -p "$db_backup_dir"
    
    # 容量確認
    local db_size=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) 
        FROM information_schema.tables 
        WHERE table_schema='$DB_NAME';" -s -N)
    
    local required_space=$(echo "$db_size * 1.5" | bc | cut -d. -f1)
    required_space=${required_space:-5}  # デフォルト5GB
    
    if ! check_disk_space "$BACKUP_DIR" "$required_space"; then
        return 1
    fi
    
    # MySQL完全バックアップ
    if [ "$DB_TYPE" = "mysql" ]; then
        log_message "Creating MySQL full backup..."
        
        mysqldump \
            --user="$DB_USER" \
            --password="$DB_PASSWORD" \
            --host="$DB_HOST" \
            --port="$DB_PORT" \
            --single-transaction \
            --routines \
            --triggers \
            --events \
            --hex-blob \
            --complete-insert \
            --add-drop-table \
            --add-locks \
            --create-options \
            --disable-keys \
            --extended-insert \
            --quick \
            --lock-tables=false \
            "$DB_NAME" | gzip > "$db_backup_dir/zabbix_full_backup.sql.gz"
        
        if [ ${PIPESTATUS[0]} -eq 0 ]; then
            log_message "MySQL full backup completed successfully"
        else
            log_message "ERROR: MySQL full backup failed"
            return 1
        fi
    fi
    
    # PostgreSQL完全バックアップ
    if [ "$DB_TYPE" = "postgresql" ]; then
        log_message "Creating PostgreSQL full backup..."
        
        PGPASSWORD="$DB_PASSWORD" pg_dump \
            --host="$DB_HOST" \
            --port="$DB_PORT" \
            --username="$DB_USER" \
            --dbname="$DB_NAME" \
            --no-password \
            --format=custom \
            --compress=9 \
            --verbose \
            --file="$db_backup_dir/zabbix_full_backup.dump"
        
        if [ $? -eq 0 ]; then
            log_message "PostgreSQL full backup completed successfully"
        else
            log_message "ERROR: PostgreSQL full backup failed"
            return 1
        fi
    fi
    
    # バックアップ検証
    if [ -f "$db_backup_dir/zabbix_full_backup.sql.gz" ]; then
        backup_size=$(du -sh "$db_backup_dir/zabbix_full_backup.sql.gz" | cut -f1)
    elif [ -f "$db_backup_dir/zabbix_full_backup.dump" ]; then
        backup_size=$(du -sh "$db_backup_dir/zabbix_full_backup.dump" | cut -f1)
    fi
    
    # メタデータ作成
    cat > "$db_backup_dir/backup_metadata.txt" << EOF
Backup Type: Database Full
Backup Date: $(date)
Database Type: $DB_TYPE
Database Name: $DB_NAME
Database Size: ${db_size}GB
Backup Size: $backup_size
Hostname: $(hostname)
MySQL Version: $(mysql --version)
Backup Method: mysqldump/pg_dump
Compression: gzip/custom
EOF
    
    log_message "Full database backup completed: $db_backup_dir"
    return 0
}

# データベース増分バックアップ
backup_database_incremental() {
    log_message "Starting incremental database backup..."
    
    local inc_backup_dir="$BACKUP_DIR/database/incremental/$(date +%Y%m%d_%H%M%S)"
    mkdir -p "$inc_backup_dir"
    
    if [ "$DB_TYPE" = "mysql" ]; then
        # バイナリログバックアップ
        local last_log_file="$BACKUP_DIR/database/incremental/.last_log_position"
        local start_position=""
        
        if [ -f "$last_log_file" ]; then
            start_position=$(cat "$last_log_file")
        else
            # 初回実行時は現在位置から開始
            start_position=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "SHOW MASTER STATUS\G" | grep Position | awk '{print $2}')
        fi
        
        # 現在のログファイルと位置取得
        local current_log=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "SHOW MASTER STATUS\G" | grep File | awk '{print $2}')
        local current_position=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "SHOW MASTER STATUS\G" | grep Position | awk '{print $2}')
        
        # バイナリログの複製
        if [ -n "$current_log" ] && [ -n "$current_position" ]; then
            cp "/var/lib/mysql/$current_log" "$inc_backup_dir/"
            
            # 位置情報保存
            echo "$current_position" > "$last_log_file"
            
            log_message "Incremental backup completed: $current_log (position: $current_position)"
        else
            log_message "WARNING: Could not determine binary log position"
            return 1
        fi
    fi
    
    return 0
}

# 監視データアーカイブ
archive_monitoring_data() {
    log_message "Starting monitoring data archive..."
    
    local archive_dir="$BACKUP_DIR/archive/$(date +%Y%m%d)"
    mkdir -p "$archive_dir"
    
    # 古いデータの特定（3ヶ月前）
    local archive_date=$(date -d "3 months ago" +%s)
    
    # 履歴データアーカイブ
    mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
        SELECT 'history', COUNT(*) FROM history WHERE clock < $archive_date
        UNION ALL
        SELECT 'history_uint', COUNT(*) FROM history_uint WHERE clock < $archive_date
        UNION ALL
        SELECT 'history_str', COUNT(*) FROM history_str WHERE clock < $archive_date
        UNION ALL
        SELECT 'history_text', COUNT(*) FROM history_text WHERE clock < $archive_date
        UNION ALL
        SELECT 'history_log', COUNT(*) FROM history_log WHERE clock < $archive_date;" "$DB_NAME" > "$archive_dir/archive_counts.txt"
    
    # アーカイブデータをCSVエクスポート
    for table in history history_uint history_str history_text history_log; do
        log_message "Archiving table: $table"
        
        mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
            SELECT * FROM $table WHERE clock < $archive_date
            INTO OUTFILE '$archive_dir/${table}_archive.csv'
            FIELDS TERMINATED BY ','
            ENCLOSED BY '\"'
            LINES TERMINATED BY '\n';" "$DB_NAME"
        
        if [ $? -eq 0 ]; then
            # アーカイブ成功後、古いデータ削除
            mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "
                DELETE FROM $table WHERE clock < $archive_date;" "$DB_NAME"
            
            log_message "Archived and cleaned table: $table"
        else
            log_message "ERROR: Failed to archive table: $table"
        fi
    done
    
    # アーカイブファイル圧縮
    cd "$archive_dir"
    tar -czf "monitoring_data_archive_$(date +%Y%m%d).tar.gz" *.csv
    rm -f *.csv
    
    log_message "Monitoring data archive completed: $archive_dir"
    return 0
}

# バックアップ検証
verify_backup() {
    local backup_file="$1"
    local backup_type="$2"
    
    log_message "Verifying backup: $backup_file"
    
    case "$backup_type" in
        "mysql_dump")
            # MySQL dumpファイルの検証
            if zcat "$backup_file" | head -100 | grep -q "MySQL dump"; then
                log_message "✓ MySQL dump verification passed"
                return 0
            else
                log_message "✗ MySQL dump verification failed"
                return 1
            fi
            ;;
        "postgresql_dump")
            # PostgreSQL dumpファイルの検証
            if pg_restore --list "$backup_file" > /dev/null 2>&1; then
                log_message "✓ PostgreSQL dump verification passed"
                return 0
            else
                log_message "✗ PostgreSQL dump verification failed"
                return 1
            fi
            ;;
        "config_tar")
            # 設定ファイルTARの検証
            if tar -tzf "$backup_file" > /dev/null 2>&1; then
                log_message "✓ Configuration archive verification passed"
                return 0
            else
                log_message "✗ Configuration archive verification failed"
                return 1
            fi
            ;;
    esac
    
    return 1
}

# 古いバックアップクリーンアップ
cleanup_old_backups() {
    log_message "Starting backup cleanup..."
    
    # 設定バックアップクリーンアップ（30日保持）
    find "$BACKUP_DIR/config" -type d -mtime +30 -exec rm -rf {} \; 2>/dev/null
    
    # 完全データベースバックアップクリーンアップ（12週間保持）
    find "$BACKUP_DIR/database/full" -type d -mtime +84 -exec rm -rf {} \; 2>/dev/null
    
    # 増分バックアップクリーンアップ（7日保持）
    find "$BACKUP_DIR/database/incremental" -type d -mtime +7 -exec rm -rf {} \; 2>/dev/null
    
    # アーカイブクリーンアップ（6ヶ月保持）
    find "$BACKUP_DIR/archive" -type f -mtime +180 -delete 2>/dev/null
    
    log_message "Backup cleanup completed"
}

# クラウドバックアップ同期
sync_to_cloud() {
    local local_backup_dir="$1"
    
    if [ -n "$CLOUD_SYNC_ENABLED" ] && [ "$CLOUD_SYNC_ENABLED" = "true" ]; then
        log_message "Starting cloud backup sync..."
        
        case "$CLOUD_PROVIDER" in
            "aws")
                aws s3 sync "$local_backup_dir" "s3://$S3_BUCKET/zabbix-backups/" \
                    --delete --storage-class STANDARD_IA
                ;;
            "gcp")
                gsutil -m rsync -r -d "$local_backup_dir" "gs://$GCS_BUCKET/zabbix-backups/"
                ;;
            "azure")
                azcopy sync "$local_backup_dir" "https://$AZURE_ACCOUNT.blob.core.windows.net/$AZURE_CONTAINER/zabbix-backups/"
                ;;
        esac
        
        if [ $? -eq 0 ]; then
            log_message "Cloud backup sync completed successfully"
        else
            log_message "ERROR: Cloud backup sync failed"
            return 1
        fi
    fi
    
    return 0
}

# バックアップレポート生成
generate_backup_report() {
    local report_file="$BACKUP_DIR/reports/backup_report_$(date +%Y%m%d).txt"
    mkdir -p "$BACKUP_DIR/reports"
    
    cat > "$report_file" << EOF
# Zabbix Backup Report

**Date**: $(date)
**Hostname**: $(hostname)

## Backup Summary

### Configuration Backup
- Location: $BACKUP_DIR/config/
- Latest: $(ls -1t "$BACKUP_DIR/config/" | head -1)
- Size: $(du -sh "$BACKUP_DIR/config/"$(ls -1t "$BACKUP_DIR/config/" | head -1) | cut -f1)

### Database Backup
- Full Backup Location: $BACKUP_DIR/database/full/
- Latest Full: $(ls -1t "$BACKUP_DIR/database/full/" | head -1)
- Incremental Location: $BACKUP_DIR/database/incremental/
- Total Database Backups: $(find "$BACKUP_DIR/database" -name "*.sql.gz" -o -name "*.dump" | wc -l)

### Storage Usage
- Total Backup Size: $(du -sh "$BACKUP_DIR" | cut -f1)
- Available Space: $(df -h "$BACKUP_DIR" | awk 'NR==2 {print $4}')

### Backup Status
- Configuration: ✓ Success
- Database Full: ✓ Success
- Database Incremental: ✓ Success
- Cloud Sync: $([ "$CLOUD_SYNC_ENABLED" = "true" ] && echo "✓ Enabled" || echo "- Disabled")

## Recent Backup Files
$(find "$BACKUP_DIR" -type f -mtime -1 -name "*backup*" | head -10)

## Recommendations
- Monitor disk space usage
- Test restore procedures monthly
- Verify cloud sync functionality
- Review backup retention policies

EOF

    log_message "Backup report generated: $report_file"
}

# メイン実行関数
main() {
    local backup_type="${1:-full}"
    
    log_message "Starting Zabbix backup process (type: $backup_type)..."
    
    # ディレクトリ作成
    mkdir -p "$BACKUP_DIR"/{config,database/{full,incremental},archive,reports}
    
    case "$backup_type" in
        "config")
            backup_configuration
            ;;
        "database-full")
            backup_database_full
            ;;
        "database-incremental")
            backup_database_incremental
            ;;
        "archive")
            archive_monitoring_data
            ;;
        "full")
            backup_configuration
            backup_database_full
            archive_monitoring_data
            sync_to_cloud "$BACKUP_DIR"
            ;;
        "maintenance")
            cleanup_old_backups
            generate_backup_report
            ;;
    esac
    
    if [ $? -eq 0 ]; then
        log_message "Backup process completed successfully"
        send_notification "SUCCESS" "Backup completed successfully" "success"
    else
        log_message "ERROR: Backup process failed"
        send_notification "FAILED" "Backup process encountered errors" "error"
        exit 1
    fi
    
    # 日次レポート生成（フルバックアップ時のみ）
    if [ "$backup_type" = "full" ]; then
        generate_backup_report
    fi
}

# スクリプト実行
main "$@"

#!/bin/bash
# Zabbix自動アップグレードシステム

UPGRADE_CONFIG="/etc/zabbix/upgrade.conf"
LOG_FILE="/var/log/zabbix/upgrade.log"
BACKUP_DIR="/backup/zabbix/upgrade"
TEST_CONFIG="/etc/zabbix/upgrade_tests.conf"

# 設定読み込み
source "$UPGRADE_CONFIG"

# ログ関数
log_message() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# 現在バージョン確認
get_current_version() {
    local current_version=$(zabbix_server --version | head -1 | awk '{print $3}')
    echo "$current_version"
}

# 新バージョン確認
check_new_version() {
    local current_version="$1"
    local latest_version
    
    # Zabbix公式リポジトリから最新バージョン確認
    latest_version=$(curl -s "https://repo.zabbix.com/zabbix/" | \
        grep -oP 'href="[0-9]+\.[0-9]+/"' | \
        grep -oP '[0-9]+\.[0-9]+' | \
        sort -V | tail -1)
    
    log_message "Current version: $current_version"
    log_message "Latest version: $latest_version"
    
    if [ "$current_version" != "$latest_version" ]; then
        echo "$latest_version"
        return 0
    else
        log_message "Already running latest version"
        return 1
    fi
}

# 互換性確認
check_compatibility() {
    local target_version="$1"
    local current_version="$2"
    
    log_message "Checking compatibility for upgrade to $target_version..."
    
    # メジャーバージョン変更確認
    local current_major=$(echo "$current_version" | cut -d. -f1)
    local target_major=$(echo "$target_version" | cut -d. -f1)
    
    if [ "$current_major" != "$target_major" ]; then
        log_message "WARNING: Major version upgrade detected ($current_major -> $target_major)"
        return 2  # 要注意
    fi
    
    # データベーススキーマ変更確認
    local schema_changes=$(curl -s "https://www.zabbix.com/documentation/current/en/manual/installation/upgrade_notes_$target_major" | \
        grep -i "database" | wc -l)
    
    if [ "$schema_changes" -gt 0 ]; then
        log_message "WARNING: Database schema changes detected"
        return 1  # 注意必要
    fi
    
    log_message "Compatibility check passed"
    return 0
}

# アップグレード前バックアップ
create_upgrade_backup() {
    log_message "Creating upgrade backup..."
    
    local backup_timestamp=$(date +%Y%m%d_%H%M%S)
    local upgrade_backup_dir="$BACKUP_DIR/upgrade_$backup_timestamp"
    
    mkdir -p "$upgrade_backup_dir"
    
    # 設定ファイルバックアップ
    tar -czf "$upgrade_backup_dir/config_backup.tar.gz" /etc/zabbix/
    
    # データベースバックアップ
    mysqldump --single-transaction --routines --triggers \
        -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" | \
        gzip > "$upgrade_backup_dir/database_backup.sql.gz"
    
    # アプリケーションファイルバックアップ
    tar -czf "$upgrade_backup_dir/application_backup.tar.gz" /usr/share/zabbix/
    
    # バックアップ検証
    if [ -f "$upgrade_backup_dir/database_backup.sql.gz" ] && \
       [ -f "$upgrade_backup_dir/config_backup.tar.gz" ]; then
        log_message "Upgrade backup created successfully: $upgrade_backup_dir"
        echo "$upgrade_backup_dir"
        return 0
    else
        log_message "ERROR: Upgrade backup creation failed"
        return 1
    fi
}

# パッケージアップグレード実行
perform_package_upgrade() {
    local target_version="$1"
    
    log_message "Starting package upgrade to version $target_version..."
    
    # パッケージリポジトリ更新
    apt update
    
    # 利用可能バージョン確認
    local available_version=$(apt list --upgradable 2>/dev/null | grep zabbix-server | awk '{print $2}')
    log_message "Available package version: $available_version"
    
    # Zabbixサーバー停止
    log_message "Stopping Zabbix server..."
    systemctl stop zabbix-server
    systemctl stop zabbix-agent
    
    # パッケージアップグレード
    log_message "Upgrading Zabbix packages..."
    apt upgrade -y zabbix-server-mysql zabbix-frontend-php zabbix-apache-conf zabbix-agent
    
    if [ $? -eq 0 ]; then
        log_message "Package upgrade completed successfully"
        return 0
    else
        log_message "ERROR: Package upgrade failed"
        return 1
    fi
}

# データベースアップグレード
upgrade_database() {
    log_message "Starting database upgrade..."
    
    # データベースアップグレードスクリプト実行
    local upgrade_script="/usr/share/doc/zabbix-server-mysql/database.sql"
    
    if [ -f "$upgrade_script" ]; then
        mysql -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" < "$upgrade_script"
        
        if [ $? -eq 0 ]; then
            log_message "Database upgrade completed successfully"
            return 0
        else
            log_message "ERROR: Database upgrade failed"
            return 1
        fi
    else
        log_message "No database upgrade script found - skipping"
        return 0
    fi
}

# アップグレード後テスト
post_upgrade_test() {
    log_message "Starting post-upgrade tests..."
    
    # サービス起動
    systemctl start zabbix-server
    systemctl start zabbix-agent
    
    # 起動確認
    sleep 10
    
    if ! systemctl is-active zabbix-server >/dev/null; then
        log_message "ERROR: Zabbix server failed to start"
        return 1
    fi
    
    # データベース接続確認
    if ! mysql -u "$DB_USER" -p"$DB_PASSWORD" -e "SELECT 1;" "$DB_NAME" >/dev/null 2>&1; then
        log_message "ERROR: Database connection failed"
        return 1
    fi
    
    # API接続確認
    local api_response=$(curl -s -X POST \
        -H "Content-Type: application/json" \
        -d '{"jsonrpc":"2.0","method":"apiinfo.version","params":{},"id":1}' \
        "http://localhost/zabbix/api_jsonrpc.php")
    
    if echo "$api_response" | grep -q "result"; then
        local new_version=$(echo "$api_response" | jq -r '.result')
        log_message "API test passed - version: $new_version"
    else
        log_message "WARNING: API test failed"
        return 1
    fi
    
    # 基本機能テスト
    source "$TEST_CONFIG"
    run_functional_tests
    
    if [ $? -eq 0 ]; then
        log_message "Post-upgrade tests completed successfully"
        return 0
    else
        log_message "ERROR: Post-upgrade tests failed"
        return 1
    fi
}

# 機能テスト実行
run_functional_tests() {
    log_message "Running functional tests..."
    
    # ホスト数確認
    local host_count=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e \
        "SELECT COUNT(*) FROM hosts WHERE status=0;" -s -N "$DB_NAME")
    
    if [ "$host_count" -gt 0 ]; then
        log_message "✓ Host count check passed: $host_count hosts"
    else
        log_message "✗ Host count check failed"
        return 1
    fi
    
    # アイテム数確認
    local item_count=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e \
        "SELECT COUNT(*) FROM items WHERE status=0;" -s -N "$DB_NAME")
    
    if [ "$item_count" -gt 0 ]; then
        log_message "✓ Item count check passed: $item_count items"
    else
        log_message "✗ Item count check failed"
        return 1
    fi
    
    # 最新データ確認
    local recent_data=$(mysql -u "$DB_USER" -p"$DB_PASSWORD" -e \
        "SELECT COUNT(*) FROM history WHERE clock > UNIX_TIMESTAMP() - 3600;" -s -N "$DB_NAME")
    
    if [ "$recent_data" -gt 0 ]; then
        log_message "✓ Recent data check passed: $recent_data entries"
    else
        log_message "⚠ Recent data check warning: $recent_data entries"
    fi
    
    # Web interface確認
    local web_response=$(curl -s -o /dev/null -w "%{http_code}" "http://localhost/zabbix/")
    
    if [ "$web_response" = "200" ]; then
        log_message "✓ Web interface check passed"
    else
        log_message "✗ Web interface check failed: HTTP $web_response"
        return 1
    fi
    
    return 0
}

# ロールバック実行
perform_rollback() {
    local backup_dir="$1"
    
    log_message "Starting rollback process..."
    
    if [ ! -d "$backup_dir" ]; then
        log_message "ERROR: Backup directory not found: $backup_dir"
        return 1
    fi
    
    # サービス停止
    systemctl stop zabbix-server
    systemctl stop zabbix-agent
    
    # 設定ファイル復元
    tar -xzf "$backup_dir/config_backup.tar.gz" -C /
    
    # データベース復元
    mysql -u "$DB_USER" -p"$DB_PASSWORD" "$DB_NAME" < \
        <(zcat "$backup_dir/database_backup.sql.gz")
    
    # アプリケーションファイル復元
    tar -xzf "$backup_dir/application_backup.tar.gz" -C /
    
    # サービス再起動
    systemctl start zabbix-server
    systemctl start zabbix-agent
    
    log_message "Rollback completed"
    return 0
}

# 通知送信
send_upgrade_notification() {
    local status="$1"
    local version="$2"
    local message="$3"
    
    # Slack通知
    if [ -n "$SLACK_WEBHOOK_URL" ]; then
        local emoji
        case "$status" in
            "success") emoji="✅" ;;
            "warning") emoji="⚠️" ;;
            "error") emoji="❌" ;;
            *) emoji="ℹ️" ;;
        esac
        
        curl -X POST -H 'Content-type: application/json' \
            --data "{\"text\":\"$emoji Zabbix Upgrade Report\\nVersion: $version\\nStatus: $status\\n$message\"}" \
            "$SLACK_WEBHOOK_URL"
    fi
    
    # メール通知
    if [ -n "$ADMIN_EMAIL" ]; then
        echo "$message" | mail -s "Zabbix Upgrade $status: $version" "$ADMIN_EMAIL"
    fi
}

# メイン処理
main() {
    local mode="${1:-check}"
    
    case "$mode" in
        "check")
            # アップグレード可能性確認
            local current_version=$(get_current_version)
            local new_version
            
            if new_version=$(check_new_version "$current_version"); then
                log_message "New version available: $new_version"
                
                check_compatibility "$new_version" "$current_version"
                local compat_result=$?
                
                case "$compat_result" in
                    0) log_message "✓ Upgrade recommended" ;;
                    1) log_message "⚠ Upgrade possible with caution" ;;
                    2) log_message "⚠ Major upgrade - manual review required" ;;
                esac
            fi
            ;;
            
        "upgrade")
            # 自動アップグレード実行
            local current_version=$(get_current_version)
            local target_version="$2"
            
            if [ -z "$target_version" ]; then
                target_version=$(check_new_version "$current_version")
            fi
            
            if [ -n "$target_version" ]; then
                log_message "Starting upgrade from $current_version to $target_version"
                
                # バックアップ作成
                local backup_dir
                if backup_dir=$(create_upgrade_backup); then
                    
                    # アップグレード実行
                    if perform_package_upgrade "$target_version" && \
                       upgrade_database && \
                       post_upgrade_test; then
                        
                        log_message "Upgrade completed successfully"
                        send_upgrade_notification "success" "$target_version" "Upgrade completed successfully"
                    else
                        log_message "Upgrade failed - performing rollback"
                        perform_rollback "$backup_dir"
                        send_upgrade_notification "error" "$target_version" "Upgrade failed - rollback performed"
                        exit 1
                    fi
                else
                    log_message "Pre-upgrade backup failed - aborting"
                    send_upgrade_notification "error" "$target_version" "Pre-upgrade backup failed"
                    exit 1
                fi
            else
                log_message "No upgrade available"
            fi
            ;;
            
        "rollback")
            # 手動ロールバック
            local backup_dir="$2"
            
            if [ -z "$backup_dir" ]; then
                backup_dir=$(find "$BACKUP_DIR" -name "upgrade_*" -type d | sort | tail -1)
            fi
            
            if [ -n "$backup_dir" ]; then
                perform_rollback "$backup_dir"
                send_upgrade_notification "warning" "rollback" "Manual rollback performed"
            else
                log_message "ERROR: No backup directory specified"
                exit 1
            fi
            ;;
            
        "test")
            # アップグレード後テストのみ実行
            post_upgrade_test
            ;;
    esac
}

# スクリプト実行
main "$@"