前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL 表中非主键列溢出情况监控

MySQL 表中非主键列溢出情况监控

作者头像
保持热爱奔赴山海
修改2020-04-12 22:41:25
2K0
修改2020-04-12 22:41:25
举报
文章被收录于专栏:数据库相关

今天,又掉坑了。 之前踩到过MySQL主键溢出的情况,通过prometheus监控起来了,具体见这篇MySQL主键溢出复盘

这次遇到的坑,更加的隐蔽。 是一个log表里面的一个int signed类型的列写满了。快速的解决方法当然还是只能切新表来救急了,然后搬迁老表的部分历史数据到热表。 

亡羊补牢,处理完故障后,赶紧写脚本把生产的其他表都捋一遍。

下面是我暂时用的一个检测脚本,还不太完善,凑合用

分2个文件(1个sql文件,1个shell脚本)

check.sql 内容如下:

代码语言:javascript
复制
SELECT 
cast( pow(2, case data_type
    when 'tinyint'   then 7
    when 'smallint'  then 15
    when 'mediumint' then 23
    when 'int'       then 31
    when 'bigint'    then 63
    end+(column_type like '% unsigned'))-1 as decimal(30,0))  as max_int,
' - ',
concat ('(', concat('select ','max(',COLUMN_NAME,')',' from ',TABLE_SCHEMA,'.',TABLE_NAME),')') 
from information_schema.COLUMNS where TABLE_SCHEMA NOT IN ('information_schema','sys','test','mysql','performance_schema') and DATA_TYPE IN ('int' ) ;

直接到数据库里面执行,效果类似这样:

check.sh 内容如下:

代码语言:javascript
复制
#!/bin/bash
# 监测int类型的当可用空间少500w的时候,提醒做DDL操作 
# 设置 session级别的 max_execution_time为2秒,防止没有索引的大的拖慢数据库,但是这样可能漏判部分列,需要注意下
# 注意:我这里bigint类型的没有检查,如果需要请修改 check.sql where条件中的DATA_TYPE加上 bigint的检查

source /etc/profile
set -u

mkdir $(date +%F) -pv

# step1 检测
for host in {'192.168.1.100','192.168.1.110','192.168.1.120','192.168.1.130'}; do

mysql -udts -pdts -h${host} -BN < check.sql   2>/dev/null > sql.log
wait

echo "说明: |  当前列允许的最大值  |  巡检用的SQL     " >> $(date +%F)/$host.log

while read line; do
   ret=$(mysql -udts -pdts -h${host} -BNe "set session max_execution_time=2000;select $line" 2>/dev/null)
   echo ${ret}
   if [[ "${ret}" == "NULL" ]]; then
    continue
   fi
   if [ ${ret} -lt 5000000 ] ; then 
      echo "$line 剩余空间 ${ret}, 该表可用水位不足500W,建议做DDL修改为bigint类型" >> $(date +%F)/$host.log
    
   fi
done <  ./sql.log

done

# step2 将检查的内容打包发邮件(这里可能需要根据自己生产的情况改改)
tar czf $(date +%F).tar.gz $(date +%F)
sendemail -s 192.168.1.200  -f post@domain.com -t ergou@domain.com -a $(date +%F).tar.gz -u "$(date +%F) int水位线巡检日志"  -o message-content-type=html -o message-charset=utf8 -m "内容详见附件"

# step3 清理每日生成的以日期命名的目录和tar.gz文件,这里我就不贴命令

再配个每天上午10点的cronjob即可,

最终每天收到邮件里面内容大致类似如下:

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2020/04/10 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档