Automatic slow query and Analysis Based on Python and automatic mail sending
Consists of two program files. The first program implements slow query analysis and file upload.
#! /Usr/local/python27/bin/python2.7from fabric. api import * from fabric. context_managers import * from fabric. contrib. console import confirmimport sysimport osimport timeimport datetimesdir = '/alidata/server/mysql/data/'ddir ='/root/'env. user = 'root' env. roledefs = {'master _ db': ['2017. 168.1.2 '], 'slave _ db': [' 192. 168.1.3 '], 'quliao _ db': ['2017. 168.1.4 ']} env. passwords = {'[email protected]: 22': '20140901 ',' [Email protected]: 22': '000000', '[email protected]: 22': '000000'} # Slow query analysis function, this function calls a local tool on the server to analyze slow queries, and then clears the slow Query files. Def my_slow (ori_log, save_log, result_log): run ('cp' + sdir + ori_log + ''+ save_log) run ('/usr/local/bin/mysqlsla-lt slow' + ddir + save_log +'-sort c_sum-top 10> '+'/root/'+ result_log) run ('echo '+ ''+'> '+ sdir + ori_log) # download the slow Query file analyzed by the server and save it to the local directory. def my_get (fname ): with LCD ('/alidata/slow_log'): dname = time. strftime ('% Y % m % d', time. localtime (time. time () with cd ('/root/'): get (fname, dname + '/') # defines the file deletion function and accepts two Parameters Used to delete the copied slow Query file and the slow Query file after analysis. Def remove (f1, f2): with cd ('/root'): run ('rm-F' + f1 + ''+ f2) # create a folder named by the current date on the client machine to save the analyzed slow Query file @ runs_oncedef mk_dir (): with LCD ('/alidata/slow_log '): dname = time. strftime ('% Y % m % d', time. localtime (time. time () local ('mkdir' + dname) @ roles ('slave _ db') def sdb (): print ("run slave db pro ") run ("ls/alidata/server/mysql/data") my_slow ('slow. log', 'mysql-r-'+ time. strftime ('% Y % m % d', time. localtime (time. time () + '. log', 'mysql-read-'+ time. strftime ('% Y % m % d', time. localtime (time. time () + '. log ') my_get ('mysql-read-' + time. strftime ('% Y % m % d', time. localtime (time. time () + '. log') remove ('mysql-r-'+ time. strftime ('% Y % m % d', time. localtime (time. time () + '. log', 'mysql-read-'+ time. strftime ('% Y % m % d', time. localtime (time. time () + '. log ') @ roles ('master _ db') def mdb (): print ('run master db Pro') run ("ls/alidata/server/mysql/data ") my_slow ('ay131008162509536ef1z-slow. log', 'mysql-w-'+ time. strftime ('% Y % m % d', time. localtime (time. time () + '. log', 'mysql-write-'+ time. strftime ('% Y % m % d', time. localtime (time. time () + '. log ') my_get ('mysql-write-' + time. strftime ('% Y % m % d', time. localtime (time. time () + '. log') remove ('mysql-w-'+ time. strftime ('% Y % m % d', time. localtime (time. time () + '. log', 'mysql-write-'+ time. strftime ('% Y % m % d', time. localtime (time. time () + '. log ') @ roles ('quliao _ db') def ldb (): print ('run quliao db Pro') run ("ls/alidata/server/mysql/data ") my_slow ('slow. log', 'mysql-q-'+ time. strftime ('% Y % m % d', time. localtime (time. time () + '. log', 'mysql-quliao-'+ time. strftime ('% Y % m % d', time. localtime (time. time () + '. log ') my_get ('mysql-quliao-' + time. strftime ('% Y % m % d', time. localtime (time. time () + '. log') remove ('mysql-q-'+ time. strftime ('% Y % m % d', time. localtime (time. time () + '. log', 'mysql-quliao-'+ time. strftime ('% Y % m % d', time. localtime (time. time () + '. log') def deploy (): mk_dir () execute (sdb) execute (mdb) execute (ldb)
# Finally, the client's local email sending program is called to send the slow query log that has just been captured to the Administrator as an email attachment. Local ('/root/tuchao/sendma. py') the second program sends the analyzed logs as email attachments to the Administrator.
#! /Usr/local/python27/bin/python2.7 # coding = utf8import smtplibimport timeimport datetimefrom email. mime. text import MIMETextfrom email import encodersfrom email. header import Headerfrom email. utils import parseaddr, formataddrfrom email. MIMEMultipart import MIMEMultipartfrom email. MIMEBase import MIMEBasefrom_addr = '[email protected] 'password = '000000' smtp _ server = '2017. 234.116.2 'to _ addr = [' [email Protected] ',' [email protected] '] slow_log_path ='/alidata/slow_log/'+ time. strftime ('% Y % m % d', time. localtime (time. time () + '/' def _ format_addr (s): name, addr = parseaddr (s) return formataddr (Header (name, 'utf-8 '). encode (), addr. encode ('utf-8') if isinstance (addr, unicode) else addr) # combine multiple objects to construct a MIMEMultipart object. Msg = MIMEMultipart () msg ['from'] = _ format_addr (U' From O & M Technology XX <% s> '% from_addr) msg ['to'] = _ format_addr (u'administrator <% s> '% to_addr) msg ['subobject'] = Header (u'mysql slow query log ', 'utf-8 '). encode () msg. attach (MIMEText ('
Constructing a mail object is a mescript object. Constructing a MIMEText object means that a text mail object constructs a MIMEImage object, which means that an image as an attachment should combine multiple objects, you can use the MIMEMultipart object MIMEBase to represent any object.