MySQL自动化运维脚本编写_定时备份、数据清理与监控实战

#!/bin/bash
# 备份配置
DB_USER="your_db_user"
DB_PASS="your_db_password"
BACKUP_DIR="/data/mysql_backup"
DATE=$(date +%Y%m%d%H%M%S)
LOG_FILE="/var/log/mysql_backup.log"
RETENTION_DAYS=7 # 保留7天备份

# 创建备份目录
mkdir -p ${BACKUP_DIR}

echo "--- MySQL备份开始于 $(date) ---" >> ${LOG_FILE}

# 执行备份
# --single-transaction 保证InnoDB表数据一致性
# --master-data=2 记录binlog位置
mysqldump -u${DB_USER} -p${DB_PASS} --all-databases --single-transaction --master-data=2 | gzip > ${BACKUP_DIR}/all_databases_${DATE}.sql.gz
BACKUP_STATUS=$? # 获取上一个命令的退出状态

if [ ${BACKUP_STATUS} -eq 0 ]; then
    echo "备份成功: ${BACKUP_DIR}/all_databases_${DATE}.sql.gz" >> ${LOG_FILE}
else
    echo "备份失败！错误码: ${BACKUP_STATUS}" >> ${LOG_FILE}
    # 可以在这里添加邮件通知逻辑
fi

# 清理旧备份
find ${BACKUP_DIR} -type f -name "*.sql.gz" -mtime +${RETENTION_DAYS} -exec rm {} \;
echo "已清理${RETENTION_DAYS}天前的旧备份。" >> ${LOG_FILE}

echo "--- MySQL备份结束于 $(date) ---" >> ${LOG_FILE}

0 2 * * * /usr/local/bin/mysql_backup.sh > /dev/null 2>&1

#!/bin/bash
DB_USER="your_db_user"
DB_PASS="your_db_password"
DB_NAME="your_database_name"
LOG_FILE="/var/log/mysql_data_cleanup.log"

echo "--- MySQL数据清理开始于 $(date) ---" >> ${LOG_FILE}

# 清理3个月前的订单日志
# 注意：对于大表，分批删除是更安全的做法，避免长时间锁表
# 这里只是一个简单的例子，生产环境请务必考虑分批和索引
SQL_QUERY="DELETE FROM order_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000;" # 每次删除1万条

# 循环执行，直到没有数据可删
while true; do
    mysql -u${DB_USER} -p${DB_PASS} ${DB_NAME} -e "${SQL_QUERY}"
    ROWS_AFFECTED=$(mysql -u${DB_USER} -p${DB_PASS} ${DB_NAME} -e "SELECT ROW_COUNT();" | tail -n 1)
    echo "$(date): 清理了 ${ROWS_AFFECTED} 行旧数据。" >> ${LOG_FILE}
    if [ ${ROWS_AFFECTED} -eq 0 ]; then
        break
    fi
    sleep 1 # 稍微等待，避免CPU飙高
done

echo "--- MySQL数据清理结束于 $(date) ---" >> ${LOG_FILE}

#!/bin/bash
DB_USER="your_db_user"
DB_PASS="your_db_password"
LOG_FILE="/var/log/mysql_monitor.log"
ALERT_THRESHOLD_DISK=90 # 磁盘使用率告警阈值 %
ALERT_THRESHOLD_CONNECTIONS=80 # 连接数告警阈值 %

echo "--- MySQL监控开始于 $(date) ---" >> ${LOG_FILE}

# 1. 检查MySQL服务状态
mysqladmin -u${DB_USER} -p${DB_PASS} ping > /dev/null 2>&1
if [ $? -ne 0 ]; then
    echo "$(date): ERROR: MySQL服务未运行或无法连接！" >> ${LOG_FILE}
    # 发送告警
fi

# 2. 检查磁盘空间
DISK_USAGE=$(df -h /data | awk 'NR==2 {print $5}' | sed 's/%//g')
if [ ${DISK_USAGE} -ge ${ALERT_THRESHOLD_DISK} ]; then
    echo "$(date): WARNING: 数据库磁盘使用率达到 ${DISK_USAGE}%，超过阈值 ${ALERT_THRESHOLD_DISK}%！" >> ${LOG_FILE}
    # 发送告警
fi

# 3. 检查连接数
MAX_CONNECTIONS=$(mysql -u${DB_USER} -p${DB_PASS} -e "SHOW VARIABLES LIKE 'max_connections';" | awk 'NR==2 {print $2}')
CURRENT_CONNECTIONS=$(mysql -u${DB_USER} -p${DB_PASS} -e "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2 {print $2}')

if [ -n "$MAX_CONNECTIONS" ] && [ -n "$CURRENT_CONNECTIONS" ] && [ "$MAX_CONNECTIONS" -gt 0 ]; then
    USAGE_PERCENT=$((CURRENT_CONNECTIONS * 100 / MAX_CONNECTIONS))
    if [ ${USAGE_PERCENT} -ge ${ALERT_THRESHOLD_CONNECTIONS} ]; then
        echo "$(date): WARNING: 当前连接数 ${CURRENT_CONNECTIONS}，已达最大连接数 ${MAX_CONNECTIONS} 的 ${USAGE_PERCENT}%！" >> ${LOG_FILE}
        # 发送告警
    fi
fi

# 4. 检查慢查询日志（示例：只检查文件大小，实际应解析内容）
# 假设慢查询日志路径为 /var/log/mysql/mysql-slow.log
SLOW_LOG_SIZE=$(du -sh /var/log/mysql/mysql-slow.log 2>/dev/null | awk '{print $1}')
if [ -n "$SLOW_LOG_SIZE" ]; then
    echo "$(date): 慢查询日志大小: ${SLOW_LOG_SIZE}" >> ${LOG_FILE}
    # 可以进一步解析日志内容，查找新增的慢查询
fi

echo "--- MySQL监控结束于 $(date) ---" >> ${LOG_FILE}

*/5 * * * * /usr/local/bin/mysql_monitor.sh > /dev/null 2>&1