python编写脚本应用实例

时间:2022-08-16 19:13:40

这里主要记录工作中应用python编写脚本的实例。由于shell脚本操作数据库(增、删、改、查)并不是十分直观方便,故这里采用python监控mysql状态,然后将状态保存到数据库中,供前台页面进行调用显示。

1、监控mysql主从复制状态

1)创建保存主从复制状态的表

CREATE TABLE `slave_status` (
`id`
int(11) NOT NULL AUTO_INCREMENT,
`slave_host`
char(15) NOT NULL, #从库主机ip
`master_host`
char(15) NOT NULL, #主库主机ip
`value`
int(11) NOT NULL, #返回值,2表示正常,0表示不正常
`created_date`
datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE
=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

2)python脚本

# coding:utf8
import mysql.connector
import time

#############################################################
#
从库主机信息
#
############################################################
hosts = ['192.168.245.131','192.168.245.132']
dbuser
= 'root'
password
= 'root'
port
= 3306
#############################################################
#
保存数据的主机信息
#
############################################################
save_host = '192.168.245.129'
save_dbuser
= 'root'
save_password
= 'root'
save_port
= 3306

query
= "show slave status"

for host in hosts:
try:
conn_slave
= mysql.connector.connect(host=host,port=port,user=dbuser,passwd=password,charset='utf8')
conn_save
= mysql.connector.connect(host=save_host,port=save_port,user=save_dbuser,passwd=save_password,charset='utf8')
cur_slave
= conn_slave.cursor()
cur_save
= conn_save.cursor()
cur_slave.execute(query)
results
= cur_slave.fetchall()

master_host
= results[0][1]
if results[0][10]=='Yes' and results[0][11] == 'Yes':
insert_sql
= "insert into a.slave_status(slave_host,master_host,value,created_date) values('%s','%s',2,NOW());" % (host,master_host)
else:
insert_sql
= "insert into a.slave_status(slave_host,master_host,value,created_date) values('%s','%s',0,NOW());" % (host,master_host)
cur_save.execute(insert_sql)
conn_save.commit()
except Exception as e:
raise str(e)
finally:
cur_save.close()
cur_slave.close()
conn_save.close()
conn_slave.close()