背景:
由于我们的环境使用otter,将mysql数据同步到oracle ,也有databus,将oracle数据同步到mysql ,所以需要写监控数据延迟和比对数据一致性,先写数据一致性比对脚本。这个是初版,后续可以加上不一致的数据用相应颜色显示等等。
引用工具:
cx-Oracle 8.1.0
PyMySQL 1.0.2
requests 2.25.1
我们使用的python3.9 所以装cx-oracle时候一定要安装相应的版本。
cx_Oracle-8.1.0-cp39-cp39-manylinux1_x86_64.whl。我们也安装的oracle客户端 下载instantclient-basic-linux.x64-11.2.0.4.0.zip
instantclient-sqlplus-linux.x64-11.2.0.4.0.zip 解压后配制tnsnames.ora就好
smtplib是python自带的 但如果使用smtplib.SMTP_SSL 则需要安装open-ssl 然后再重新编译python。
引用代码:
import pymysql
import cx_Oracle
import pdb
import smtplib
from email.mime.text import MIMEText
from email.header import Header
读取表名称跳过#注释行 读取表的最大id和行数:
def readTableName(filename,ouser,opword,odb,muser,mpword,hostname,mdb):
with open(filename,'r') as f:
for fcon in f.readlines():
fc = fcon.strip().strip('\n')
# escape startswith # line
if fc.startswith('#'):
continue
else:
#execute sql
try:
#pdb.set_trace()
info = 'table_name:{:30} '.format(fc)
writeFile(info)
resstr='maxid:{:10} count:{:10} '
resm = executeSqlStatistic(hostname,muser,mpword,mdb,fc)
#print(resm)
myres = resstr.format(str(resm[0]),str(resm[1]))
writeFile(myres)
reso = executeStatistic(ouser,opword,odb,fc)
#print(reso)
orares = resstr.format(str(reso[0]),str(reso[1]))
writeFile(orares)
writeFile('\n')
except Exception as e:
print(e)
连接oracle代码:
#execute from oracle
def executeStatistic(user,pword,instance,table_name):
conn = cx_Oracle.connect(user,pword,instance)
cursor = conn.cursor()
selsql = 'select max(id),count(1) from kcrm.{}'.format(table_name)
cursor.execute(selsql)
res =[]
try:
res = cursor.fetchone()
except cx_Oracle.Error as err:
print(err)
finally:
cursor.close()
conn.close()
#print(res)
return res
连接mysql代码:
#execute from mysql
def executeSqlStatistic(hostname,uname,pword,db_name,table_name):
conn = pymysql.connect(host=hostname, port=3306, user=uname, password=pword)
cursor = conn.cursor()
changedb = 'use `{}`'.format(db_name)
cursor.execute( changedb )
selsql = 'select max(id),count(1) from {}'.format(table_name)
cursor.execute(selsql)
res=[]
try:
res = cursor.fetchone()
except pymysql.Error as err:
print(err)
finally:
cursor.close()
conn.close()
return res
写入文件代码:
#write statistic to file
def writeFile(info,filename='/root/statistic_log'):
with open(filename,'a+') as f:
f.write(info)
发邮件代码:
#read filename send mail
def sendMail(msg):
to_addr=['list@mail..','list@mail..','list@mail..']
mimetext = MIMEText(msg,'plain','utf-8')
mimetext['Subject'] = Header('Check consistency sync data','utf-8').encode()
server = smtplib.SMTP_SSL('mail.xxx.net',465)
#server.ehlo()
#server.starttls()
server.login('user','pwd')
server.sendmail('usermail',to_addr,mimetext.as_string())
server.quit()
主函数:
if __name__ == '__main__':
with open('/root/statistic_log','w') as f:
f.write('')
hostname = ''
mdb = 'dbname'
muser = 'user'
mpword = 'pwd'
#readTableName(filename,ouser,opword,odb,muser,mpword,hostname,mdb):
odb = 'ip:port/orcl'
ouser = 'user'
opword = 'pwd'
readTableName('/root/check_table.txt',ouser,opword,odb,muser,mpword,hostname,mdb)