Python监听MySQL日志

前言


在使用后台语言开发中,每天都是在与数据库打交道,而很多时候出问题都出在SQL语句上,而调试起来也不太方便,当然大佬例外哈,我等新手还真没办法在短时间内练习出一些技巧,我是用PHP的,之前在网上也搜索的时候,见别人使用的PHP工具箱,里面有个SQL追踪工具,感觉很不错,能够监听到当前执行的SQL语句,然而那个东西需要自动手动去点击刷新,作为程序猿,要的就是懒,还要手动点,不开心!


既然想懒,那就做成自动的,经过查询资料了解,MySQL内置了log功能,只需要将其开启,然后我们监听其内容变化即可,思路通了,接下来就是开搞了.


动手开搞

这里我选用的是Python来写,毕竟"人生苦短,我用Python"
先上代码,然后再来依次讲解,代码精简一下其实50行不到,我这个完全没优化啥的,主要是实现功能,哪位大佬有时间可以帮忙改改,万分感谢!点击下载

import pymysql
import re
import time
import os
import subprocess

# 数据库信息
host = 'localhost'
user = 'root'
password = '123456'
port = 8889
db = 'JCL'
# 表前缀(如果没有就留空)
prefix = 'xh_'

logpath = os.getcwd() + '/mysql.log'


def connectMySQL(ip, user, pwd, port, db):
    # 开始连接数据库
    connect = pymysql.connect(
        host=ip,
        user=user,
        passwd=pwd,
        port=port,
        db=db,
        charset='utf8'
    )
    # 获取游标
    cur = connect.cursor()
    # 开启mysql标准日志
    cur.execute('set global general_log = on')
    connect.commit()
    currentPath = 'set global log_output = \'file\''
    cur.execute(currentPath)
    connect.commit()
    cur.execute('set global general_log_file=' + '\'' + logpath + '\'')
    connect.commit()
    connect.close()

def monitor():
    command = 'tail -f ' + logpath
    popen = subprocess.Popen(command, stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=True)
    try:
        while True:
            line = popen.stdout.readline().strip()
            encodeStr = bytes.decode(line)
            pattern = re.findall('Query\s*(.*)', encodeStr, re.S)
            if len(pattern) != 0:
                selectStr = pattern[0]
                if selectStr != "COMMIT":
                    joinTime = time.strftime("%H:%M:%S", time.localtime()) + '    '
                    if prefix != "":
                        reg = re.findall(r'\b' + prefix + '\w*', encodeStr, re.S)
                        if len(reg) != 0:
                            table = '操作的表:' + reg[0]
                            joinTime += table
                    print(joinTime + '   ' + selectStr)

    except KeyboardInterrupt:
        os.remove(logpath)


if __name__ == '__main__':
    connectMySQL(host, user, password, port, db)
    time.sleep(2)
    monitor()

讲解

首先是需要导入需要使用到的库
pymysql连接数据库
re正则匹配
time时间
os系统库,我这用来获取路径
subprocess用于调用终端命令

在开始我们定义一下数据库信息和日志保存的路径,我这里是直接使用了脚本当前目录生成一个名为mysql.log的文件

# 数据库信息
host = 'localhost'
user = 'root'
password = '123456'
port = 8889
db = 'JCL'
# 表前缀(如果没有就留空)
prefix = 'xh_'
logpath = os.getcwd() + '/mysql.log'

3.连接数据库,开启数据库的日志,并设置日志输入目录和保存类型

def connectMySQL(ip, user, pwd, port, db):
    # 开始连接数据库
    connect = pymysql.connect(
        host=ip,
        user=user,
        passwd=pwd,
        port=port,
        db=db,
        charset='utf8'
    )
    # 获取游标
    cur = connect.cursor()
    # 开启mysql标准日志
    cur.execute('set global general_log = on')
    connect.commit()
    currentPath = 'set global log_output = \'file\''
    cur.execute(currentPath)
    connect.commit()
    cur.execute('set global general_log_file=' + '\'' + logpath + '\'')
    connect.commit()
    connect.close()

上段代码中有个'set global log_output = \'file\'',MySQL其实还有个输出方式是Table,日志会保存到mysql数据库下的general_log表里面,刚开始我就用的这种方法,想的是待会做数据提取的时候方便,但是使用后发现,好像无法直接清除日志(我没找到简单办法),而且性能没写文件好,所以我舍弃了Table使用了File方式,因为没有直接改动my.cnf,所以在MySQL重启后,这个设置就消失了.如果需要一直保持的话,可以自己去修改my.cnf.
因为这里连接数据库的作用只是用来开启一下设置而已,所以在开启设置后将数据库连接关闭掉

4.开始监听数据,因为linux上有个tail命令就可以直接实时监听某个文件的变化,所以就直接使用subprocess来调用tail命令

def monitor():
    command = 'tail -f ' + logpath
    popen = subprocess.Popen(command, stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=True)
    try:
        while True:
            line = popen.stdout.readline().strip()
            encodeStr = bytes.decode(line)
            pattern = re.findall('Query\s*(.*)', encodeStr, re.S)
            if len(pattern) != 0:
                selectStr = pattern[0]
                if selectStr != "COMMIT":
                    joinTime = time.strftime("%H:%M:%S", time.localtime()) + '    '
                    if prefix != "":
                        reg = re.findall(r'\b' + prefix + '\w*', encodeStr, re.S)
                        if len(reg) != 0:
                            table = '操作的表:' + reg[0]
                            joinTime += table
                    print(joinTime + '   ' + selectStr)

    except KeyboardInterrupt:
        os.remove(logpath)

其中使用了正则表达式筛选了一些非Query的命令,这一段写了一堆的if自己都觉得恶心,后面再改吧!
最终添加了一个try except来监听ctrl+c命令,然后将mysql.log文件删掉!
最后上一张效果图

QQ20180119-194411@2x.png


文中的正则可能不是特别好,希望对正则比较了解的朋友提点建议,文中有不正确的地方希望大家积极指出,共同进步!