数据库性能监控脚本

#!/bin/bash
# db_performance_monitor.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

# 定义变量
DB_USER="fgedu"
DB_PASS="Fgedu@123"
DB_NAME="fgedudb"
LOG_FILE="/opengauss/logs/db_performance_monitor.log"
ALERT_FILE="/opengauss/logs/db_alert.log"

# 日志函数from DB视频:www.itpux.com
log() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" >> $LOG_FILE
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1"
}

# 告警函数
alert() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] ALERT: $1" >> $ALERT_FILE
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] ALERT: $1"
    # 这里可以添加邮件、短信等告警通知
}

# 检查CPU使用率
check_cpu() {
    log "检查CPU使用率..."
    CPU_USAGE=$(top -bn1 | grep "Cpu(s)" | awk '{print $2 + $4}')
    log "CPU使用率：$CPU_USAGE%"
    if (( $(echo "$CPU_USAGE > 80" | bc -l) )); then
        alert "CPU使用率过高：$CPU_USAGE%"
    fi
}

# 检查内存使用率
check_memory() {
    log "检查内存使用率..."
    MEM_TOTAL=$(free -m | grep Mem | awk '{print $2}')
    MEM_USED=$(free -m | grep Mem | awk '{print $3}')
    MEM_USAGE=$(echo "scale=2; $MEM_USED / $MEM_TOTAL * 100" | bc)
    log "内存使用率：$MEM_USAGE%"
    if (( $(echo "$MEM_USAGE > 80" | bc -l) )); then
        alert "内存使用率过高：$MEM_USAGE%"
    fi
}

# 检查磁盘使用率
check_disk() {
    log "检查磁盘使用率..."
    DISK_USAGE=$(df -h | grep '/opengauss' | awk '{print $5}' | sed 's/%//')
    log "磁盘使用率：$DISK_USAGE%"
    if (( $DISK_USAGE > 80 )); then
        alert "磁盘使用率过高：$DISK_USAGE%"
    fi
}

# 检查慢查询
check_slow_queries() {
    log "检查慢查询..."
    SLOW_QUERIES=$(gsql -U $DB_USER -d $DB_NAME -t -c "
    SELECT 
        pid,
        usename,
        datname,
        now() - query_start AS duration,
        query
    FROM 
        pg_stat_activity
    WHERE 
        state = 'active' 
        AND now() - query_start > interval '5 seconds'
    ORDER BY 
        duration DESC;
    ")
    if [ -n "$SLOW_QUERIES" ]; then
        log "发现慢查询："
        log "$SLOW_QUERIES"
        alert "发现慢查询，请检查"
    else
        log "未发现慢查询"
    fi
}

# 主流程
log "=== 数据库性能监控开始 ==="

check_cpu
check_memory
check_disk
check_slow_queries

log "=== 数据库性能监控完成 ==="

#!/bin/bash
# db_backup.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

# 定义变量
DB_USER="fgedu"
DB_PASS="Fgedu@123"
DB_NAME="fgedudb"
BACKUP_DIR="/opengauss/backup"
DATE=$(date +'%Y%m%d_%H%M%S')
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_backup_$DATE.sql"
LOG_FILE="/opengauss/logs/db_backup.log"

# 日志函数
log() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" >> $LOG_FILE
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1"
}

# 检查备份目录
check_backup_dir() {
    if [ ! -d $BACKUP_DIR ]; then
        log "创建备份目录：$BACKUP_DIR"
        mkdir -p $BACKUP_DIR
    fi
}

# 执行备份
execute_backup() {
    log "开始备份数据库：$DB_NAME"
    gsql -U $DB_USER -d $DB_NAME -c "VACUUM ANALYZE;"
    gs_dump -U $DB_USER -d $DB_NAME -f $BACKUP_FILE -F p
    if [ $? -eq 0 ]; then
        log "备份成功，备份文件：$BACKUP_FILE"
        # 压缩备份文件
        gzip $BACKUP_FILE
        log "备份文件已压缩：$BACKUP_FILE.gz"
        # 删除7天前的备份文件
        find $BACKUP_DIR -name "${DB_NAME}_backup_*.sql.gz" -mtime +7 -delete
        log "已删除7天前的备份文件"
    else
        log "备份失败"
    fi
}

# 主流程
log "=== 数据库备份开始 ==="

check_backup_dir
execute_backup

log "=== 数据库备份完成 ==="

# 启用Shell脚本调试模式
bash -x script.sh

# 检查脚本语法
bash -n script.sh

# 逐行执行脚本
bash -v script.sh

# 使用日志调试
echo "Debug: variable=$variable" >> debug.log

# 使用set命令启用调试模式
set -x  # 启用调试模式
# 脚本代码
set +x  # 禁用调试模式

# 检查命令执行结果
command && echo "Success" || echo "Failure"

# 使用trap命令捕获信号
trap "echo 'Script interrupted'; exit 1" INT TERM