mysql 使用 general 开启SQL跟踪功能

查看当前状态

mysql> SHOW VARIABLES LIKE '%general%';

启用

临时启用

SET GLOBAL general_log=on;
SET GLOBAL general_log_file='/tmp/general.log';

永久启用

通过修改配置文件来启用,需要重启mysql服务

[mysqld]
general_log=ON
general_log_file=/tmp/general.log

再次查看状态是否已开启，和日志存储目录

mysql> SHOW VARIABLES LIKE '%general%';

SQL日志记录后，可能会有很多SQL同时写入，但是我们可能想要的只有查询语句，可以通过以下代码进行筛选

# general_log 监控SQL日志，解析其中的select语句

def extract_select_where_statements(log):
    statements = log.split('\n')
    select_where_statements = []
    for statement in statements:
        if 'Query' in statement:
            sql = statement.split('Query')[1].strip()
            if sql.lower().startswith('select') and 'where' in sql.lower():
                select_where_statements.append(sql)
    return select_where_statements


def extract_select_statements(input_file, output_file):
    # 从文件中读取日志数据
    with open(input_file, 'r', encoding='utf-8') as file:
        log_data = file.read()

    # 正则表达式匹配SELECT语句
    select_where_statements = extract_select_where_statements(log_data)
    # 查找所有匹配的SELECT语句
    # select_statements = select_pattern.findall(log_data)

    # 将匹配的SELECT语句输出到文件，并在每一行结束时加上分号
    with open(output_file, 'w', encoding='utf-8') as file:
        for statement in select_where_statements:
            file.write(statement.strip() + ';\n')


def main():
    while True:
        user_input = input("Press Enter to convert log data or 'q' to quit: ")
        if user_input.lower() == 'q':
            print("Exiting the program.")
            break
        else:
            extract_select_statements(input_file, output_file)
            print(f"SELECT statements have been extracted and saved to {output_file}.")


if __name__ == "__main__":
    # 输入日志文件路径
    input_file = './log_data.txt'
    # 输出文件路径
    output_file = './select_statements.txt'
    main()