#! /usr/bin/env python
# -*- coding: utf-8 -*-
import os
import time
from datetime import datetime
# 数据库连接信息
host = 'localhost'
port = 3306
user = 'root'
passwd = '123456'
db = 'my_database'
# 警告阈值
cpu_warn = 80 # CPU利用率
memory_warn = 50 # 内存利用率
disk_warn = 80 # 磁盘利用率
# 查询语句
cpu_sql = 'SHOW GLOBAL STATUS WHERE Variable_name = "Cpu_usage";'
memory_sql = 'SHOW GLOBAL STATUS WHERE Variable_name = "Memory_usage";'
disk_sql = 'SHOW GLOBAL STATUS WHERE Variable_name = "Disk_free";'
def get_db_info(sql):
# 执行语句并获取结果
result = os.popen(f'mysqldump -h {host} -P {port} -u {user} -p{passwd} -e "{sql}"')
# 处理并返回
return result.read().split('tn')[-1].strip('%')
def check_db():
print(f'Starting check database {db} at {datetime.now()}...')
# 获取CPU、内存、磁盘信息
cpu_usage = get_db_info(cpu_sql)
memory_usage = get_db_info(memory_sql)
disk_free = get_db_info(disk_sql)
# 判断是否超过警告阈值
cpu_alert = cpu_usage > cpu_warn
memory_alert = memory_usage > memory_warn
disk_alert = disk_free < disk_warn
# 发送预警信息
if cpu_alert or memory_alert or disk_alert:
warning_msg = f"""
Database {db} status:
CPU Usage: {cpu_usage}%, {'Alert!' if cpu_alert else 'Normal'}
Memory Usage: {memory_usage}%, {'Alert!' if memory_alert else 'Normal'}
Disk Free: {disk_free}%, {'Alert!' if disk_alert else 'Normal'}
"""
print(warning_msg)
# 发送邮件或其他方式发送预警信息
else:
print(f'Database {db} status: Normal!')
if __name__ == '__main__':
while True:
check_db() # 执行巡检
time.sleep(60*5) # 每5分钟执行一次
可以通过/etc/system/systemd下注册py巡检任务，以daemon服务的方式启动
