python监控mysql数据改变_python3小脚本-监控服务器性能并插入mysql数据库

操作系统：

centos版本

7.4

防火墙

关闭

selinux

关闭

python版本

3.6

mysql版本

5.7

#操作系统性能脚本

[root@localhost sql]# cat cpu_mac.py

#!/usr/bin/env python3

#coding:utf-8

import subprocess

def monitor(frist_invoke=1):

shell_command = 'sar 1 1| grep "^Average:"'

status,result = subprocess.getstatusoutput(shell_command)

if status != 0:

value_dic = {'status': status}

else:

value_dic = {}

#print('---res:',result)

user,nice,system,iowait,steal,idle = result.split()[2:]

#拿回监控数据后保存为字典

value_dic= {

'user': user,

'nice': nice,

'system': system,

'idle': idle,

'status': status

}

return value_dic

if __name__ == '__main__':

print(monitor())

#获取服务器性能后使用python3插入数据库脚本

[root@localhost sql]# cat /root/sql/class_sql.py

import pymysql

import cpu_mac

from datetime import datetime

db = pymysql.connect("192.168.10.100", "root", "root", "dx_monitor", charset="utf8")

#获取当前时间

NOW = datetime.now()

#获取当前时间，并格式化

TIME_DATE = NOW.strftime("%Y-%m-%d %H:%M:%S")

#获取当前时间，并格式化为年月

MONITOR_DATE = NOW.strftime("%Y_%m")

class monitor:

def __init__(self, db, MONITOR_DATE, TIME_DATE):

self.db = db

self.MONITOR_DATE = MONITOR_DATE

self.TIME_DATE = TIME_DATE

def create_table(self):

#获取当前数据库游标

cursor = self.db.cursor()

#查看当前数据表是否存在

date_sql = "DROP TABLE IF EXISTS res_monitor_{0}".format(self.MONITOR_DATE)

try:

#创建数据表

sql = """

CREATE TABLE  res_monitor_self.MONITOR_DATE (

id int UNSIGNED AUTO_INCREMENT,

user varchar(20) NOT NULL,

nice varchar(20) NOT NULL,

system varchar (20) NOT NULL,

idle  varchar(20) NOT NULL,

status varchar(20) NOT NULL,

date DATETIME,

PRIMARY KEY( id )

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

""".format(self.MONITOR_DATE)

#判断数据表

data = cursor.execute(date_sql)

#执行创建表

cursor.execute(sql)

self.db.close()

#print("Database version : %s" % data)

#print("表创建成功")

except :

print('表错误')

data = cursor.execute(date_sql)

cursor.execute(sql)

self.db.close()

print("Database version : %s" % data)

print("表创建成功")

cursor.close()

self.db.close()

def insert_table(self):

de = cpu_mac.monitor()

#插入数据到数据库表空间

sql = "INSERT INTO res_monitor_{0} (user, nice, system, idle, status, date) VALUES (%s, %s, %s, %s, %s, %s)".format(self.MONITOR_DATE)

cursor = db.cursor()

T = (( de['user']), (de['nice']), (de['system']), (de['idle']), (de['status']), (self.TIME_DATE))

try:

#执行表

cursor.execute(sql, T)

self.db.commit()

print('监控数据插入ok')

#     print('ok 123')

#     # sql = "INSERT INTO res_monitor_{0} (user, nice, system, idle, status, date) VALUES ({1}, {2}, {3}, {4}, {5})".format(MONITOR_DATE, de['user'], de['nice'], de['system'], de['idle'], de['status'])

#

#     print(sql)

#

#     # cursor.execute(sql, T)

#     print('监控数据插入ok')

except:

self.db.rollback()

print('监控数据插入错误')

cursor.close()

self.db.close()

if __name__ == '__main__':

db_sql = monitor(db,MONITOR_DATE,TIME_DATE)

if TIME_DATE_MONTH == '01':

db_sql.create_table()

db_sql.insert_table()

#写入定时任务

[root@localhost sql]# crontab -l

* * * * * /usr/bin/python3 /root/sql/class_sql.py

#连接数据库

[root@localhost ~]# mysql -uroot -proot -h192.168.10.100

#创建的库

#创建的表

#执行的最终结果