mysql 性能监控脚本

于 2025-01-15 16:49:37 发布
阅读量1.4k 收藏 33
点赞数 24
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_36251204/article/details/145163638
版权

主要改进点：

1. 新增数据库总容量统计

  • 按数据库显示总大小
  • 增加大数据库警告（超过10GB）

4. 优化了输出格式

5. 将表大小统计和数据库总容量统计分开显示

现在运行脚本时，你会看到：

  • 新增的数据库总容量统计部分
  • 每个数据库的总大小
  • 按大小排序
  • 大数据库警告

2. 原有的表大小统计部分

  • 每个表的大小
  • 按大小排序
  • 大表警告

这些改进可以帮助你：

  • 全面了解数据库存储使用情况
  • 识别需要优化的数据库

3. 规划存储扩容

  • 制定数据归档策略
  • 优化数据库架构设计

注意：如果需要更详细的存储分析，可以考虑增加以下功能：

  • 按天/周/月的增长趋势
  • 索引与数据大小的比例
  • 表碎片率分析
  • 表行数统计等 
    #!/bin/bash

# 添加MySQL连接配置
MYSQL_USER="root"
MYSQL_PASSWORD="mycsbit1!"  # 请替换为实际的MySQL root密码
MYSQL_HOST="localhost"

# 检查MySQL连接
if ! mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SELECT 1" >/dev/null 2>&1; then
    echo "错误：无法连接到MySQL服务器"
    echo "请检查："
    echo "1. MySQL服务是否正在运行"
    echo "2. 用户名和密码是否正确"
    echo "3. 主机地址是否正确"
    exit 1
fi

# MySQL性能分析脚本 - 深度优化版

# 获取MySQL性能指标
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SHOW GLOBAL STATUS" > /tmp/mysql_status.txt
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SHOW GLOBAL VARIABLES" > /tmp/mysql_variables.txt

# 解析关键指标
connections=$(grep -w "Connections" /tmp/mysql_status.txt | awk '{print $2}')
slow_queries=$(grep -w "Slow_queries" /tmp/mysql_status.txt | awk '{print $2}')
questions=$(grep -w "Questions" /tmp/mysql_status.txt | awk '{print $2}')
qcache_hits=$(grep -w "Qcache_hits" /tmp/mysql_status.txt | awk '{print $2}')
qcache_inserts=$(grep -w "Qcache_inserts" /tmp/mysql_status.txt | awk '{print $2}')
threads_connected=$(grep -w "Threads_connected" /tmp/mysql_status.txt | awk '{print $2}')
max_used_connections=$(grep -w "Max_used_connections" /tmp/mysql_status.txt | awk '{print $2}')
innodb_buffer_pool_reads=$(grep -w "Innodb_buffer_pool_reads" /tmp/mysql_status.txt | awk '{print $2}')
innodb_buffer_pool_read_requests=$(grep -w "Innodb_buffer_pool_read_requests" /tmp/mysql_status.txt | awk '{print $2}')
table_locks_waited=$(grep -w "Table_locks_waited" /tmp/mysql_status.txt | awk '{print $2}')
created_tmp_disk_tables=$(grep -w "Created_tmp_disk_tables" /tmp/mysql_status.txt | awk '{print $2}')
created_tmp_tables=$(grep -w "Created_tmp_tables" /tmp/mysql_status.txt | awk '{print $2}')

# 获取配置参数
max_connections=$(grep -w "max_connections" /tmp/mysql_variables.txt | awk '{print $2}')
query_cache_size=$(grep -w "query_cache_size" /tmp/mysql_variables.txt | awk '{print $2}')
innodb_buffer_pool_size=$(grep -w "innodb_buffer_pool_size" /tmp/mysql_variables.txt | awk '{print $2}')

# 计算关键比率
if [ $qcache_hits -gt 0 ]; then
    qcache_hit_rate=$(echo "scale=2; $qcache_hits / ($qcache_hits + $qcache_inserts) * 100" | bc)
else
    qcache_hit_rate=0
fi

if [ $innodb_buffer_pool_read_requests -gt 0 ]; then
    innodb_buffer_pool_hit_rate=$(echo "scale=2; (1 - $innodb_buffer_pool_reads / $innodb_buffer_pool_read_requests) * 100" | bc)
else
    innodb_buffer_pool_hit_rate=0
fi

if [ $created_tmp_tables -gt 0 ]; then
    tmp_disk_tables_rate=$(echo "scale=2; $created_tmp_disk_tables / $created_tmp_tables * 100" | bc)
else
    tmp_disk_tables_rate=0
fi

# 获取错误统计信息
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SHOW GLOBAL STATUS LIKE 'Connection_errors%'" > /tmp/mysql_errors.txt
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SHOW GLOBAL STATUS LIKE 'Aborted%'" >> /tmp/mysql_errors.txt

# 解析错误统计
connection_errors_max=$(grep -w "Connection_errors_max_connections" /tmp/mysql_errors.txt | awk '{print $2}')
connection_errors_internal=$(grep -w "Connection_errors_internal" /tmp/mysql_errors.txt | awk '{print $2}')
connection_errors_peer=$(grep -w "Connection_errors_peer_address" /tmp/mysql_errors.txt | awk '{print $2}')
aborted_connects=$(grep -w "Aborted_connects" /tmp/mysql_errors.txt | awk '{print $2}')
aborted_clients=$(grep -w "Aborted_clients" /tmp/mysql_errors.txt | awk '{print $2}')

# 获取InnoDB相关状态
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SHOW ENGINE INNODB STATUS\G" > /tmp/innodb_status.txt
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SHOW GLOBAL STATUS LIKE 'Innodb%'" > /tmp/innodb_stats.txt

# 解析InnoDB关键指标
innodb_buffer_pool_reads=$(grep -w "Innodb_buffer_pool_reads" /tmp/innodb_stats.txt | awk '{print $2}')
innodb_buffer_pool_read_requests=$(grep -w "Innodb_buffer_pool_read_requests" /tmp/innodb_stats.txt | awk '{print $2}')
innodb_log_waits=$(grep -w "Innodb_log_waits" /tmp/innodb_stats.txt | awk '{print $2}')
innodb_row_lock_time_avg=$(grep -w "Innodb_row_lock_time_avg" /tmp/innodb_stats.txt | awk '{print $2}')
innodb_row_lock_waits=$(grep -w "Innodb_row_lock_waits" /tmp/innodb_stats.txt | awk '{print $2}')
innodb_data_reads=$(grep -w "Innodb_data_reads" /tmp/innodb_stats.txt | awk '{print $2}')
innodb_data_writes=$(grep -w "Innodb_data_writes" /tmp/innodb_stats.txt | awk '{print $2}')

# 计算InnoDB缓冲池命中率
if [ $innodb_buffer_pool_read_requests -gt 0 ]; then
    innodb_buffer_pool_hit_rate=$(echo "scale=2; (1 - $innodb_buffer_pool_reads / $innodb_buffer_pool_read_requests) * 100" | bc)
else
    innodb_buffer_pool_hit_rate=0
fi

# 获取用户会话信息
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "SELECT USER, COUNT(*) as connections, SUM(COMMAND != 'Sleep') as active_connections FROM information_schema.PROCESSLIST GROUP BY USER" > /tmp/user_sessions.txt

# 解析用户会话信息
echo -e "\n9. 用户会话统计:"
while read -r user connections active; do
    # 跳过表头
    if [[ $user == "USER" ]]; then
        continue
    fi
    echo "   用户: $user"
    echo "     总连接数: $connections"
    echo "     活跃连接数: $active"
    echo "     空闲连接数: $((connections - active))"
    
    # 添加用户连接数警告
    if [ $connections -gt 50 ]; then
        echo "     * 警告：该用户连接数超过50，建议检查是否有连接泄漏"
    fi
    if [ $((connections - active)) -gt 30 ]; then
        echo "     * 警告：该用户空闲连接数过多，建议优化连接池配置"
    fi
done < /tmp/user_sessions.txt

# 获取数据库表大小信息
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "
SELECT 
    table_schema AS '数据库',
    table_name AS '表名',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS '大小(MB)'
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;
" > /tmp/table_sizes.txt

# 获取数据库总容量
mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -h$MYSQL_HOST -e "
SELECT 
    table_schema AS '数据库',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '总大小(MB)'
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;
" > /tmp/db_total_sizes.txt

# 解析并显示数据库总容量
echo -e "\n10. 数据库容量统计:"
echo "   数据库名 | 总大小(MB)"
echo "   ----------------------"
while read -r db total_size; do
    # 跳过表头
    if [[ $db == "数据库" ]]; then
        continue
    fi
    printf "   %-20s | %10s\n" "$db" "$total_size"
    
    # 添加大数据库警告
    if [ $(echo "$total_size > 10240" | bc) -eq 1 ]; then
        echo "     * 警告：数据库 ${db} 总大小超过10GB，建议："
        echo "       - 检查数据归档策略"
        echo "       - 考虑分库分表"
        echo "       - 优化存储结构"
    fi
done < /tmp/db_total_sizes.txt

# 解析并显示表大小信息
echo -e "\n11. 数据库表大小统计:"
echo "   数据库名 | 表名 | 大小(MB)"
echo "   ----------------------------"
while read -r db table size; do
    # 跳过表头
    if [[ $db == "数据库" ]]; then
        continue
    fi
    printf "   %-10s | %-20s | %8s\n" "$db" "$table" "$size"
    
    # 添加大表警告
    if [ $(echo "$size > 1024" | bc) -eq 1 ]; then
        echo "     * 警告：${db}.${table} 表大小超过1GB，建议："
        echo "       - 检查是否需要分区"
        echo "       - 考虑归档历史数据"
        echo "       - 优化索引设计"
    fi
done < /tmp/table_sizes.txt

# 输出性能报告
echo "===== MySQL深度性能分析报告 ====="
echo "1. 连接信息:"
echo "   当前连接数: $threads_connected"
echo "   最大连接数: $max_connections"
echo "   历史最大连接数: $max_used_connections"
echo "   连接使用率: $(echo "scale=2; $max_used_connections / $max_connections * 100" | bc)%"

echo -e "\n2. 缓存信息:"
echo "   查询缓存命中率: ${qcache_hit_rate}%"
echo "   InnoDB缓冲池命中率: ${innodb_buffer_pool_hit_rate}%"
echo "   查询缓存大小: $(echo "scale=2; $query_cache_size / 1024 / 1024" | bc) MB"
echo "   InnoDB缓冲池大小: $(echo "scale=2; $innodb_buffer_pool_size / 1024 / 1024 / 1024" | bc) GB"

echo -e "\n3. 查询性能:"
echo "   慢查询数量: $slow_queries"
echo "   总查询数量: $questions"
echo "   表锁等待次数: $table_locks_waited"
echo "   磁盘临时表比例: ${tmp_disk_tables_rate}%"

echo -e "\n4. 优化建议:"
# 连接数建议
if [ $(echo "$max_used_connections / $max_connections > 0.8" | bc) -eq 1 ]; then
    echo "   * 警告：连接数使用率超过80%，建议增加max_connections"
fi

# 缓存建议
if [ $(echo "$innodb_buffer_pool_hit_rate < 95" | bc) -eq 1 ]; then
    echo "   * 警告：InnoDB缓冲池命中率低于95%，建议增加innodb_buffer_pool_size"
fi

if [ $(echo "$qcache_hit_rate < 20" | bc) -eq 1 ]; then
    echo "   * 警告：查询缓存命中率低于20%，考虑禁用查询缓存（query_cache_type=0）"
fi

# 临时表建议
if [ $(echo "$tmp_disk_tables_rate > 10" | bc) -eq 1 ]; then
    echo "   * 警告：磁盘临时表比例过高，建议增加tmp_table_size和max_heap_table_size"
fi

# 在性能报告中添加InnoDB分析部分
echo -e "\n7. InnoDB存储引擎分析:"
echo "   缓冲池命中率: ${innodb_buffer_pool_hit_rate}%"
echo "   日志等待次数: $innodb_log_waits"
echo "   平均行锁等待时间: ${innodb_row_lock_time_avg}ms"
echo "   行锁等待次数: $innodb_row_lock_waits"
echo "   数据读取次数: $innodb_data_reads"
echo "   数据写入次数: $innodb_data_writes"

# 添加InnoDB相关优化建议
echo -e "\n8. InnoDB优化建议:"
if [ $(echo "$innodb_buffer_pool_hit_rate < 95" | bc) -eq 1 ]; then
    echo "   * 警告：InnoDB缓冲池命中率低于95%，建议："
    echo "     - 增加innodb_buffer_pool_size"
    echo "     - 优化查询以减少全表扫描"
fi

if [ $innodb_log_waits -gt 0 ]; then
    echo "   * 警告：存在日志等待，建议："
    echo "     - 增加innodb_log_file_size"
    echo "     - 增加innodb_log_buffer_size"
fi

if [ $innodb_row_lock_waits -gt 0 ]; then
    echo "   * 警告：存在行锁等待，建议："
    echo "     - 优化事务设计"
    echo "     - 检查索引使用情况"
    echo "     - 考虑使用更细粒度的锁"
fi

echo "================================"

# 清理临时文件
rm -f /tmp/mysql_status.txt
rm -f /tmp/mysql_variables.txt

MySQL常用的性能监控基础脚本
DfsnVue的博客
09-26 257
通过监控数据库连接数、查询缓存命中率、查询慢日志、数据库锁定情况和表空间使用情况，可以全面了解数据库的性能状况，并及时采取措施进行优化。在本文中，我们将介绍一些常用的MySQL性能监控脚本，这些脚本可以帮助数据库管理员快速识别和解决性能问题。通过查看锁定信息，可以了解当前数据库的锁定情况，并且可以根据需要对锁定进行优化。通过定期执行这个脚本，可以了解表的大小情况，并及时采取措施来优化表的空间占用。如果查询慢日志开启，可以通过查看慢日志文件来分析慢查询的原因，并进行性能优化。
MySQL性能监控与调优工具详解
最新发布
LYFYSZ123的博客
01-13 1166
MySQL性能监控与调优是确保数据库系统高效稳定运行的关键步骤。通过使用合适的监控工具，实时了解数据库的健康状态，结合性能调优工具和最佳实践，能够有效提升数据库的性能，满足业务需求的增长。记住，性能监控与调优是一个持续的过程，需要不断地监测、分析和优化。希望通过本篇文章，你能够全面了解MySQL的性能监控与调优工具，并在实际项目中有效应用这些方法，构建一个高效、稳定的数据库系统。如果在实施过程中遇到任何问题，欢迎查阅官方文档或参与社区讨论，获取更多支持和帮助。在下一篇博客中，我们将探讨“
mysql keepalived 监控脚本
10-30
mysql keepalived 监控脚本
mysql监控脚本
03-01
mysql监控脚本，mysql 宕机自启服务。mysql监控脚本，自启mysql监控脚本，自启
监控mysql服务脚本
小胡子
09-22 253
#!/bin/bash PORT=`netstat -lnt|grep 3306|awk -F '[ :]+' ' [print $5] if [ $PORT -eq 3306] ; then echo " db is running" else /data/3306/mysql restart fi #!/bin/bash PORT=`netstat -lnt|grep 3306|wc -l...
MySQL监控脚本
weixin_34197488的博客
03-01 153
  zabbix监控mysql时自定key用到的脚本   #!/usr/bin/env python #-*- coding: UTF-8 -*- from __future__ import print_function from mysql import connector import logging,argparse,sys """ 作者:蒋乐兴 时间:2017年2月下旬 目标:用...
Mysql 监控脚本
weixin_34292402的博客
04-22 360
cat mysql_status_output.py #coding=utf-8import jaydebeapiimport sysimport pymysqlimport osfrom prometheus_client import Gauge,start_http_serverimport time #v_host=os.popen('echo $HOSTNAME')#hostname=...
mysql 监控读写脚本_mysql 监控脚本一
weixin_33842051的博客
02-18 258
mysql监控# monitor master-slave 监控主从同步#### 192.168.57.108/192.168.101.93hostip="192.168.11.93"user="root"pass="Uasdf*dn"STATUS=$(mysql -h$hostip -u$user -p$pass mysql -e "show slave status\G" | gr...
mysql状态监控脚本
08-10
linux下检测mysql进程状态脚本，做Mysql主备模式，可以监测mysql数据库允许状态。
mysql从服务器状态监控脚本
10-06
mysql从服务器状态监控脚本,源自官方网
MySQL性能监控脚本的实用工具与源码解析
根据提供的文件信息，我们将深入探讨MySQL性能监控脚本的相关知识点。首先，让我们从标题入手，标题为"mysql 性能监控脚本"，这暗示了接下来的内容将主要关注于如何通过编写或使用脚本来监控MySQL数据库服务器的性能...
mysql 田_MySQL监控脚本
weixin_39901558的博客
01-28 159
前段时间，主机的MySQL服务无缘无故会自动关闭，以前从没出过这种情况，于是看了下mysql的错误日志，如下所示：以为是内存分配不够，便调大了内存，过了几天后，依然出现mysql服务自动关闭的情况，无奈之下，开始写了脚本监控mysql。mysql的监控方式有多种，如，通过查看端口号和进程是否存在来检测mysql服务是否正常，或者通过端口和主机连接mysql，但当CPU负载很高时，会出现端口在服务可...
监控mysql服务脚本_shell脚本：监控MySQL服务是否正常
weixin_33100265的博客
01-19 200
监控MySQL服务是否正常，通常的思路为：检查3306端口是否启动，ps查看mysqld进程是否启动，命令行登录mysql执行语句返回结果，php或jsp程序检测(需要开发人员开发程序)等等；方法1：监听3306端口#!/bin/bash#writtenbymofansheng@2015-10-15port=`netstat-nlt|grep3306|wc-l`if[$port-n...
mysql 监控脚本
qq_35809876的博客
04-01 360
本文本章转载地址：https://www.cnblogs.com/magicaltravel/p/6182384.html#!/bin/bash#定义变量v_username='mysql'v_datetime=`date +"%Y-%m-%d %H:%M:%S"`v_hostname=`hostname`v_ip=`/sbin/ifconfig bond2|grep 'inet addr'|aw...
python发送qq邮件_Python3实现发送QQ邮件功能（文本）
weixin_39950812的博客
11-28 134
本文为大家分享了Python3实现发送QQ邮件功能：文本，供大家参考，具体内容如下注意：使用前需要到qq中设置开启POP3 和IMAP服务和设置第三方授权码然后在下面打x那里填入相关信息即可import smtplibfrom email.mime.text import MIMETextfrom email.utils import formataddrmy_sender='xxxx@qq.co...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值