MySQL如何记录线程连接(我需要用户和IP) [英] MySQL how to log thread connections (I need user and ip)

问题描述

我有时会需要解析mysql binlog，以找出哪个查询更改了数据库中的特定值.

From time to time I need to parse my mysql binlog to find out which query changed a specific value in my database.

查询条目如下:

# at 335156
#101208  5:55:57 server id 1  end_log_pos 335265    
         Query  thread_id=1772  exec_time=0 error_code=0
SET TIMESTAMP=1291784157/*!*/;
UPDATE table SET value = 0 WHERE id = 185555

现在，如果我想出了具体的查询，我有thread_id，但是从该条目中我在哪里可以找到对应的用户/ip?

Now If I figured out the specific query I have the thread_id but where do I find the corrosponding user / ip from this entry?

推荐答案

首先，您必须启用一般查询日志

First you have to enable the genereral query log http://dev.mysql.com/doc/refman/5.5/en/query-log.html

然后，您可以找到所有连接以及它们在其中运行的查询.谨提醒您，此日志会很大，请谨慎使用.

Then you can find all connects and what queries they ran there. I word of warning, this log gets big so use with care.

这是我现在以root用户身份连接到本地服务器的示例.

This is an example of my connect as root to my local server just now.

101208 10:55:18      5667 Connect  root@localhost on 
                     5667 Query select @@version_comment limit 1
101208 10:55:26      5667 Query show databases

这篇关于MySQL如何记录线程连接(我需要用户和IP)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！