MySQL巡检脚本

# 最佳实践配置方式
1. 创建专用检查账号：
   CREATE USER 'inspector'@'localhost' IDENTIFIED BY 'password';
   GRANT SELECT, PROCESS ON *.* TO 'inspector'@'localhost';


2. 使用配置文件认证：
   echo -e "[client]\nuser=inspector\npassword=password" > ~/.my.cnf
   chmod 600 ~/.my.cnf

# 可轻松扩展的检查项：
check_plugins() {
    exec_sql "SHOW PLUGINS;" "插件状态"
}


check_scheduler() {
    exec_sql "SHOW EVENTS;" "定时任务检查"
}

#!/bin/bash
# MySQL数据库专业巡检脚本
# 作者：Chkov
# 版本：4.2 | 支持MySQL 5.7/8.0+
# 最后更新：2025-03-27


# 配置区 ================================================================
MYSQL_USER="admin"                         # 需具有PROCESS,SELECT权限
MYSQL_PASS="password"                      # 建议使用~/.my.cnf配置
MYSQL_HOST="localhost"                     
MYSQL_PORT="3306"
REPORT_FILE="MySQL_Inspection_$(date +%Y%m%d).md"
CHECK_DATE=$(date "+%Y-%m-%d %H:%M:%S")
LOG_WARNINGS="mysql_warnings_$(date +%Y%m%d).log"


# 样式定义 ==============================================================
MD_HR="---"
MD_BOLD="\033[1m"
MD_RESET="\033[0m"
RED='\033[31m'
GREEN='\033[32m'
YELLOW='\033[33m'


# 初始化报告 ===========================================================
init_report() {
    cat > "$REPORT_FILE" << EOF
# MySQL巡检报告


**主机名**: $(hostname)  
**数据库版本**: $(mysql -V | awk '{print $3}')  
**巡检时间**: $CHECK_DATE  


${MD_HR}
EOF
}


# 执行SQL函数 ==========================================================
exec_sql() {
    local sql="$1"
    local title="$2"
    local remark="$3"


    echo -e "\n### $title" >> "$REPORT_FILE"
    [[ -n "$remark" ]] && echo -e "*${remark}*" >> "$REPORT_FILE"


    echo '```sql' >> "$REPORT_FILE"
    mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$MYSQL_PASS" -e "$sql" 2>>"$LOG_WARNINGS" | sed '/^\s*$/d' >> "$REPORT_FILE"
    echo '```' >> "$REPORT_FILE"
}


# 基础检查 =============================================================
check_basic() {
    echo -e "\n## 1. 基础检查" >> "$REPORT_FILE"


    # 版本信息
    exec_sql "SELECT VERSION() AS version, @@version_comment AS flavor;" "1.1 版本信息"


    # 运行状态
    exec_sql "SHOW GLOBAL STATUS LIKE 'Uptime';" "1.2 运行时间"


    # 关键参数
    exec_sql "
        SHOW VARIABLES WHERE 
        Variable_name IN (
            'max_connections','innodb_buffer_pool_size',
            'slow_query_log','log_bin','sync_binlog',
            'innodb_flush_log_at_trx_commit'
        );
    " "1.3 关键参数检查" "重点关注缓冲池和事务配置"
}


# 存储检查 =============================================================
check_storage() {
    echo -e "\n## 2. 存储检查" >> "$REPORT_FILE"


    # 数据库大小
    exec_sql "
        SELECT table_schema AS database_name,
        ROUND(SUM(data_length+index_length)/1024/1024,2) AS total_mb,
        ROUND(SUM(data_length)/1024/1024,2) AS data_mb,
        ROUND(SUM(index_length)/1024/1024,2) AS index_mb
        FROM information_schema.tables
        GROUP BY table_schema
        ORDER BY total_mb DESC;
    " "2.1 数据库容量分布"


    # 表空间碎片
    exec_sql "
        SELECT table_schema, table_name,
        ROUND(data_free/1024/1024,2) AS free_mb,
        ROUND((data_length+index_length)/1024/1024,2) AS used_mb,
        ROUND(data_free/(data_length+index_length+data_free)*100,2) AS frag_pct
        FROM information_schema.tables
        WHERE data_free > 100*1024*1024  -- 大于100MB碎片
        ORDER BY data_free DESC
        LIMIT 10;
    " "2.2 表空间碎片" "大于10%建议optimize table"
}


# 性能检查 =============================================================
check_performance() {
    echo -e "\n## 3. 性能检查" >> "$REPORT_FILE"


    # 连接数
    exec_sql "
        SHOW GLOBAL STATUS LIKE 'Threads_connected';
        SHOW VARIABLES LIKE 'max_connections';
        SELECT SUBSTRING_INDEX(host,':',1) AS client,
               COUNT(*) AS connections,
               GROUP_CONCAT(DISTINCT user) AS users
        FROM information_schema.processlist
        GROUP BY client
        ORDER BY connections DESC
        LIMIT 5;
    " "3.1 连接数状态" "警戒线：used/max > 80%"


    # 慢查询
    exec_sql "
        SHOW GLOBAL STATUS LIKE 'Slow_queries';
        SHOW VARIABLES LIKE 'long_query_time';
    " "3.2 慢查询统计"


    # 缓冲池命中率
    exec_sql "
        SELECT (1-SUM(innodb_buffer_pool_reads)/SUM(innodb_buffer_pool_read_requests)) * 100 AS hit_rate
        FROM information_schema.INNODB_BUFFER_POOL_STATS;
    " "3.3 InnoDB缓冲池命中率" "警戒线：< 95%"


    # 当前活动会话
    exec_sql "
        SELECT id, user, host, db, command, time, state, SUBSTRING(info,1,100) AS query
        FROM information_schema.processlist
        WHERE command NOT IN ('Sleep','Daemon')
        ORDER BY time DESC
        LIMIT 10;
    " "3.4 活动会话TOP10" "关注Time>300秒的会话"
}


# 复制检查 =============================================================
check_replication() {
    echo -e "\n## 4. 复制检查" >> "$REPORT_FILE"


    # 主从状态
    exec_sql "SHOW SLAVE STATUS\G" "4.1 复制状态" | head -30 >> "$REPORT_FILE"


    # 主库二进制日志
    exec_sql "SHOW MASTER STATUS;" "4.2 二进制日志状态"


    # 复制延迟
    exec_sql "
        SHOW SLAVE STATUS\G
        SELECT TIMESTAMPDIFF(SECOND, LAST_QUEUED_TIME, NOW()) AS rep_lag_sec;
    " "4.3 复制延迟" "警戒线：> 60秒"
}


# 安全检查 =============================================================
check_security() {
    echo -e "\n## 5. 安全检查" >> "$REPORT_FILE"


    # 用户权限
    exec_sql "
        SELECT user, host, 
        IF(password='','NO','YES') AS has_password,
        IF(plugin='mysql_native_password','native',plugin) AS auth_method,
        account_locked, password_expired
        FROM mysql.user
        WHERE user NOT IN ('mysql.sys','mysql.session');
    " "5.1 用户账户状态" "关注空密码和过期账户"


    # 密码策略
    exec_sql "
        SHOW VARIABLES LIKE 'validate_password%';
    " "5.2 密码复杂度策略"


    # SSL连接
    exec_sql "
        SHOW VARIABLES LIKE 'have_ssl';
        SELECT user, host, ssl_type FROM mysql.user 
        WHERE ssl_type NOT IN ('','ANY');
    " "5.3 SSL连接检查"
}


# 备份检查 =============================================================
check_backup() {
    echo -e "\n## 6. 备份检查" >> "$REPORT_FILE"


    # 备份空间
    echo -e "\n### 6.1 备份目录空间" >> "$REPORT_FILE"
    echo '```bash' >> "$REPORT_FILE"
    df -h backup 2>/dev/null | awk '{print $2,$3,$5}' >> "$REPORT_FILE"
    echo '```' >> "$REPORT_FILE"


    # 备份日志
    echo -e "\n### 6.2 最近备份记录" >> "$REPORT_FILE"
    echo '检查要点：' >> "$REPORT_FILE"
    echo '- 确认备份文件存在且大小正常' >> "$REPORT_FILE"
    echo '- 验证最后一次备份时间(<24小时)' >> "$REPORT_FILE"
    echo '- 定期执行恢复测试' >> "$REPORT_FILE"
}


# 生成建议 =============================================================
gen_recommendations() {
    echo -e "\n## 7. 优化建议" >> "$REPORT_FILE"


    # 自动生成建议函数
    generate_suggestions() {
        local metrics="$1"
        local threshold="$2"
        local suggestion="$3"


        mysql -h"$MYSQL_HOST" -P"$MYSQL_PORT" -u"$MYSQL_USER" -p"$MYSQL_PASS" -Nse "SELECT $metrics" | \
        awk -v thresh="$threshold" -v sugg="$suggestion" '$1 > thresh {print "1. [ ] " sugg " (当前值: "$1")"}'
    }


    echo -e "\n### 7.1 紧急事项" >> "$REPORT_FILE"
    generate_suggestions "(SELECT (Threads_connected/max_connections)*100 FROM performance_schema.global_status JOIN performance_schema.global_variables WHERE variable_name='max_connections')" 80 "连接数超过80%需扩容"
    generate_suggestions "(SELECT ROUND(frag_pct,0) FROM (SELECT (data_free/(data_length+index_length+data_free)*100) AS frag_pct FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema') ORDER BY frag_pct DESC LIMIT 1) t)" 20 "存在严重表碎片(>20%)需整理"


    echo -e "\n### 7.2 长期优化" >> "$REPORT_FILE"
    echo "1. [ ] 考虑升级到MySQL 8.0最新版本" >> "$REPORT_FILE"
    echo "2. [ ] 实施监控告警(推荐Prometheus+mysqld_exporter)" >> "$REPORT_FILE"
    echo "3. [ ] 建立定期维护窗口执行OPTIMIZE和ANALYZE" >> "$REPORT_FILE"


    echo -e "\n${MD_HR}\n" >> "$REPORT_FILE"
    echo "**检查用时**: ${SECONDS}秒" >> "$REPORT_FILE"
    [[ -s "$LOG_WARNINGS" ]] && echo -e "\n${YELLOW}警告：检查过程中发现错误，详见 $LOG_WARNINGS${MD_RESET}"
}


# 主程序 ===============================================================
main() {
    SECONDS=0
    init_report
    check_basic
    check_storage
    check_performance 
    check_replication
    check_security
    check_backup
    gen_recommendations


    echo -e "\n${MD_BOLD}巡检报告已生成: ${REPORT_FILE}${MD_RESET}"
    echo "使用命令查看: less -R $REPORT_FILE"
}


main