MySQL数据库性能监控与优化建议脚本

#!/bin/bash # ========================================== # MySQL数据库性能监控与优化建议脚本 # 用途：监控MySQL性能，分析慢查询，给出优化建议 # 使用方法：bash mysql_perf_monitor.sh # ========================================== # MySQL连接配置（可通过环境变量或参数修改） MYSQL_HOST="${MYSQL_HOST:-localhost}" MYSQL_PORT="${MYSQL_PORT:-3306}" MYSQL_USER="${MYSQL_USER:-root}" MYSQL_PASS="${MYSQL_PASS:-}" echo "========== MySQL性能监控报告 ==========" echo "监控时间: $(date '+%Y-%m-%d %H:%M:%S')" echo "目标主机: $MYSQL_HOST:$MYSQL_PORT" echo "" # 检查mysql客户端 if ! command -v mysql &> /dev/null; then echo "❌ 错误: mysql客户端未安装" exit 1 fi # 构建mysql命令 MYSQL_CMD="mysql -h$MYSQL_HOST -P$MYSQL_PORT -u$MYSQL_USER" if [ -n "$MYSQL_PASS" ]; then MYSQL_CMD="$MYSQL_CMD -p$MYSQL_PASS" fi MYSQL_CMD="$MYSQL_CMD -N -e" # 测试连接 if ! $MYSQL_CMD "SELECT 1" &>/dev/null; then echo "❌ 错误: 无法连接MySQL，请检查连接配置" exit 1 fi echo "✅ MySQL连接成功" echo "" # 1. 基本信息 echo "【1. 数据库基本信息】" $MYSQL_CMD "SELECT CONCAT(' 版本: ', VERSION());" $MYSQL_CMD "SELECT CONCAT(' 运行时间: ', ROUND(VARIABLE_VALUE/3600, 1), '小时') FROM performance_schema.global_status WHERE VARIABLE_NAME='Uptime';" $MYSQL_CMD "SELECT CONCAT(' 当前连接数: ', VARIABLE_VALUE) FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_connected';" $MYSQL_CMD "SELECT CONCAT(' 最大连接数: ', @@max_connections);" echo "" # 2. 连接数分析 echo "【2. 连接数分析】" CURRENT_CONN=$($MYSQL_CMD "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Threads_connected';") MAX_CONN=$($MYSQL_CMD "SELECT @@max_connections;") CONN_RATIO=$(echo "scale=1; $CURRENT_CONN * 100 / $MAX_CONN" | bc) echo " 当前连接数: $CURRENT_CONN / $MAX_CONN (${CONN_RATIO}%)" if (( $(echo "$CONN_RATIO > 80" | bc -l) )); then echo " ⚠️ 连接数使用率超过80%，建议:" echo " - 检查是否有连接泄漏" echo " - 考虑增大max_connections" echo " - 优化应用连接池配置" else echo " ✅ 连接数使用正常" fi # 连接超时 ABORTED_CLIENTS=$($MYSQL_CMD "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Aborted_clients';") ABORTED_CONNECTS=$($MYSQL_CMD "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Aborted_connects';") echo " 异常断开客户端: $ABORTED_CLIENTS" echo " 异常连接数: $ABORTED_CONNECTS" echo "" # 3. 查询缓存分析 echo "【3. 查询缓存分析】" Qcache_HITS=$($MYSQL_CMD "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Qcache_hits';" 2>/dev/null || echo "0") Qcache_INSERTS=$($MYSQL_CMD "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Qcache_inserts';" 2>/dev/null || echo "0") Qcache_NOT_CACHED=$($MYSQL_CMD "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Qcache_not_cached';" 2>/dev/null || echo "0") if [ "$Qcache_HITS" != "0" ] || [ "$Qcache_INSERTS" != "0" ]; then TOTAL_QUERIES=$((Qcache_HITS + Qcache_INSERTS + Qcache_NOT_CACHED)) if [ "$TOTAL_QUERIES" -gt 0 ]; then HIT_RATIO=$(echo "scale=1; $Qcache_HITS * 100 / $TOTAL_QUERIES" | bc) echo " 查询缓存命中率: ${HIT_RATIO}%" echo " 缓存命中: $Qcache_HITS, 插入: $Qcache_INSERTS, 未缓存: $Qcache_NOT_CACHED" fi else echo " 查询缓存未启用或MySQL 8.0+已移除查询缓存" fi echo "" # 4. InnoDB缓冲池分析 echo "【4. InnoDB缓冲池分析】" BP_READ_REQUESTS=$($MYSQL_CMD "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests';") BP_READS=$($MYSQL_CMD "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads';") BP_SIZE=$($MYSQL_CMD "SELECT @@innodb_buffer_pool_size;") if [ "$BP_READ_REQUESTS" -gt 0 ]; then BP_HIT_RATIO=$(echo "scale=2; (1 - $BP_READS / $BP_READ_REQUESTS) * 100" | bc) echo " 缓冲池命中率: ${BP_HIT_RATIO}%" echo " 逻辑读: $BP_READ_REQUESTS, 物理读: $BP_READS" echo " 缓冲池大小: $(echo "scale=1; $BP_SIZE / 1024 / 1024" | bc) MB" if (( $(echo "$BP_HIT_RATIO < 95" | bc -l) )); then echo " ⚠️ 缓冲池命中率低于95%，建议:" echo " - 增大innodb_buffer_pool_size (建议为物理内存的50-70%)" echo " - 优化查询，减少全表扫描" else echo " ✅ 缓冲池命中率良好" fi fi echo "" # 5. 慢查询分析 echo "【5. 慢查询分析】" SLOW_QUERIES=$($MYSQL_CMD "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Slow_queries';") SLOW_LOG=$($MYSQL_CMD "SELECT @@slow_query_log;") LONG_QUERY_TIME=$($MYSQL_CMD "SELECT @@long_query_time;") echo " 慢查询总数: $SLOW_QUERIES" echo " 慢查询日志: $SLOW_LOG (1=开启, 0=关闭)" echo " 慢查询阈值: ${LONG_QUERY_TIME}秒" if [ "$SLOW_LOG" = "0" ]; then echo " ⚠️ 慢查询日志未开启，建议开启:" echo " SET GLOBAL slow_query_log = 1;" echo " SET GLOBAL long_query_time = 1;" fi # Top 5慢查询（如果有performance_schema） echo "" echo " Top 5慢查询（按执行次数）:" $MYSQL_CMD "SELECT CONCAT(' 执行', COUNT_STAR, '次, 平均', ROUND(AVG_TIMER_WAIT/1000000000, 2), 's: ', LEFT(SQL_TEXT, 80)) FROM performance_schema.events_statements_summary_by_digest WHERE SQL_TEXT IS NOT NULL AND SQL_TEXT NOT LIKE 'SHOW%' AND SQL_TEXT NOT LIKE 'SET%' ORDER BY SUM_TIMER_WAIT DESC LIMIT 5;" 2>/dev/null || echo " (需要performance_schema支持)" echo "" # 6. 表锁分析 echo "【6. 锁等待分析】" TABLE_LOCKS_WAITED=$($MYSQL_CMD "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Table_locks_waited';") TABLE_LOCKS_IMMEDIATE=$($MYSQL_CMD "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Table_locks_immediate';") TOTAL_LOCKS=$((TABLE_LOCKS_WAITED + TABLE_LOCKS_IMMEDIATE)) if [ "$TOTAL_LOCKS" -gt 0 ]; then LOCK_WAIT_RATIO=$(echo "scale=2; $TABLE_LOCKS_WAITED * 100 / $TOTAL_LOCKS" | bc) echo " 表锁等待率: ${LOCK_WAIT_RATIO}%" echo " 立即获取锁: $TABLE_LOCKS_IMMEDIATE, 等待锁: $TABLE_LOCKS_WAITED" if (( $(echo "$LOCK_WAIT_RATIO > 1" | bc -l) )); then echo " ⚠️ 锁等待率较高，建议:" echo " - 优化查询，减少锁持有时间" echo " - 考虑使用InnoDB替代MyISAM" echo " - 检查是否有长事务" else echo " ✅ 锁等待正常" fi fi echo "" # 7. 优化建议汇总 echo "【7. 优化建议汇总】" echo " 1. 配置优化:" echo " - innodb_buffer_pool_size: 建议为物理内存的50-70%" echo " - max_connections: 根据应用需求调整，避免过高或过低" echo " - slow_query_log: 建议开启，long_query_time设为1秒" echo " 2. 查询优化:" echo " - 定期分析慢查询日志，优化Top SQL" echo " - 确保WHERE条件字段有索引" echo " - 避免SELECT *，只查询需要的字段" echo " 3. 表结构优化:" echo " - 定期执行ANALYZE TABLE更新统计信息" echo " - 大表考虑分区" echo " - 检查是否有冗余索引" echo " 4. 监控建议:" echo " - 定期监控连接数、缓冲池命中率、慢查询数" echo " - 设置告警阈值，及时发现性能问题" echo "" echo "========== 监控完成 =========="

[{"name": "MYSQL_HOST", "label": "MySQL\u4e3b\u673a", "default": "localhost"}, {"name": "MYSQL_PORT", "label": "MySQL\u7aef\u53e3", "default": "3306"}, {"name": "MYSQL_USER", "label": "\u7528\u6237\u540d", "default": "root"}, {"name": "MYSQL_PASS", "label": "\u5bc6\u7801", "default": ""}]