Mysql监控慢日志，防止恶意用户注入

为什么80%的码农都做不了架构师？>>>   

#!/usr/bin/python
import re
userlist=['abc@192.168.1.1','cba@192.168.1.1']
table=['table1','table2']
SEEK_FILE = '/tmp/seek.txt'
MYSQL_LOG='/root/var/log/mysql/mysql_log.txt_2013-07-03_00'
try:                                                    # check seek file
    f_seek = open(SEEK_FILE, 'r')
    n = long(f_seek.read())
except IOError:
    f_seek = open(SEEK_FILE, 'w')
    f_seek.write('0')
    n = 0
finally:
    f_seek.close()
f = open(MYSQL_LOG,'r')
f.seek(n)
logcheck=open('/tmp/logcheck.txt','a')                  # open logfie which record the illogical operation
while True:
    seek_1=f.tell()							# return seek from tell()
    line=f.readline()
    if "backup_@localhost" in line:
    	key=re.findall(r'\d{8}',line)[0]
        while True:
            line=f.readline()
            if re.findall(r'\d{8}',line)==[]:
                continue
            else:
                key1=re.findall(r'\d{8}',line)[0]
            if key==key1 and "Quit" not in line:
                continue
            elif "Connect" in line:
                user=re.findall(r'\S+@\S+',line)[0]
                if user not in userlist:
                    print '%s connect database' %user
                    logcheck.write(line.strip()+'\n')
            elif "show create" in line:
                print 'illogical operation %s' %line.strip()
                logcheck.write(line.strip()+'\n')
            elif "show grants" in line:
                print 'illogical operation %s' %line.strip()
                logcheck.write(line.strip()+'\n')
            elif "desc table" in line:
                print 'illogical operation %s' %line.strip()
                logcheck.write(line.strip()+'\n')
            elif "use mysql" in line:
                print 'illogical operation %s' %line.strip()
                logcheck.write(line.strip()+'\n')
            elif "select *" in line:
                sql_str=re.search(r'from(.*)where',line)
                tablename=sql_str.group().split()[1]
                keyword=sql_str.group().split()[-1]
                if tablename not in table or keyword is None:
                    print 'illogical operation %s' %line.strip()
                    logcheck.write(line.strip()+'\n')
            elif key==key1  and "Quit" in line:
                break
    if "select *" in line:
        sql_str=re.search(r'from(.*)where',line)
        tablename=sql_str.group().split()[1]
        keyword=sql_str.group().split()[-1]
        if tablename not in table or keyword is None:
            print 'illogical operation %s' %line.strip()
            logcheck.write(line.strip()+'\n')
    if "show create" in line:
        print 'illogical operation %s' %line.strip()
        logcheck.write(line.strip()+'\n')
    if "show grants" in line:
        print 'illogical operation %s' %line.strip()
        logcheck.write(line.strip()+'\n')
    if "desc table" in line:
        print 'illogical operation %s' %line.strip()
        logcheck.write(line.strip()+'\n')
    if "use mysql" in line:
        print 'illogical operation %s' %line.strip()
        logcheck.write(line.strip()+'\n')
    if "Connect" in line:
        user=re.findall(r'\S+@\S+',line)[0]
        if user not in userlist:
            print '%s connect database' %user
            logcheck.write(line.strip()+'\n')
    seek_2=f.tell()                                 # end readline,return seek from tell()
    if seek_1==seek_2:
        f_seek=open(SEEK_FILE,'w')
        f_seek.write(str(seek_2))
        f_seek.close()
        logcheck.close()
        break

转载于:https://my.oschina.net/jastme/blog/278538