在Ubuntu上使用mysqlsniffer跟踪MySQL查询

You’ve got a production database server, and you can’t enable query logging… so how do you see the queries being executed against the database?

您已经有一个生产数据库服务器，并且无法启用查询日志记录…那么您如何看待针对数据库执行的查询？

The answer: use a modified network sniffer to parse out the MySQL packets and decode them. You’ll have to do a little compiling, but it’ll be worth it. Note that this will not usually work for local connections, although you are welcome to try.

答案：使用修改后的网络嗅探器解析出MySQL数据包并对其进行解码。 您必须进行一些编译，但这是值得的。 请注意，尽管欢迎您尝试，但这通常不适用于本地连接。

First, you need to install libpcap-dev, which is the development library that allows an application to sniff network packets.

首先，您需要安装libpcap-dev，这是允许应用程序嗅探网络数据包的开发库。

sudo apt-get install libpcap-dev

须藤apt-get install libpcap-dev

Now let’s make a directory, download the source code and compile it

现在让我们建立一个目录，下载源代码并进行编译

mkdir mysqlsniffer

mkdir mysqlsniffer

cd mysqlsniffer

cd mysqlsniffer

wget hackmysql.com/code/mysqlsniffer.tgz

wget hackmysql.com/code/mysqlsniffer.tgz

tar xvfz mysqlsniffer.tgz

tar xvfz mysqlsniffer.tgz

gcc -O2 -lpcap -o mysqlsniffer mysqlsniffer.c packet_handlers.c misc.c

gcc -O2 -lpcap -o mysqlsniffer mysqlsniffer.c packet_handlers.c misc.c

At this point, we have a shiny new executable named mysqlsniffer in our source directory. You can copy it wherever you like (somewhere in the path would be useful)

此时，我们在源目录中有了一个名为mysqlsniffer的闪亮新可执行文件。 您可以将其复制到任意位置(路径中的某个位置会很有用)

To run mysqlsniffer, you need to specify the network interface that MySQL is listening on. For me, it’s eth0.

要运行mysqlsniffer，您需要指定MySQL正在监听的网络接口。 对我来说是eth0。

sudo /path/to/mysqlsniffer eth0

须藤/ path / to / mysqlsniffer eth0

Loads of stuff starts flying by… let’s filter it out a little more so we can just get the queries and not all the excess data.

大量的东西开始飞奔……让我们对其进行过滤，以便我们只获取查询，而不是所有多余的数据。

$ sudo /path/to/mysqlsniffer –no-mysql-hdrs eth0 | grep COM_QUERY

$ sudo / path / to / mysqlsniffer –no-mysql-hdrs eth0 | grep COM_QUERY

192.168.73.1.2622 > server: COM_QUERY: SELECT @@sql_mode192.168.73.1.2622 > server: COM_QUERY: SET SESSION sql_mode=”192.168.73.1.2622 > server: COM_QUERY: SET NAMES utf8192.168.73.1.1636 > server: COM_QUERY: SELECT @@SQL_MODE192.168.73.1.1636 > server: COM_QUERY: SHOW FULL COLUMNS FROM `db2842_howto`.`wp_users`

192.168.73.1.2622>服务器：COM_QUERY：选择@@ sql_mode192.168.73.1.2622>服务器：COM_QUERY：SET SESSION sql_mode =“” 192.168.73.1.2622>服务器：COM_QUERY：设置名称utf8192.168.73.1.1636>服务器：COM_QUERY：选择@@ SQL_MODE192.168.73.1.1636>服务器：COM_QUERY：从db2842_howto中显示完整的列。wp_users

Ah, now there we are… all sorts of query information, without having to restart MySQL.

嗯，现在我们有了……各种各样的查询信息，而不必重新启动MySQL。

Here are the full options for the command:

这是命令的完整选项：

Usage: mysqlsniffer [OPTIONS] INTERFACE

用法：mysqlsniffer [OPTIONS]接口

OPTIONS:–port N Listen for MySQL on port number N (default 3306)–verbose Show extra packet information–tcp-ctrl Show TCP control packets (SYN, FIN, RST, ACK)–net-hdrs Show major IP and TCP header values–no-mysql-hdrs Do not show MySQL header (packet ID and length)–state Show state–v40 MySQL server is version 4.0–dump Dump all packets in hex–help Print this

选项：–port N在端口号N上侦听MySQL(默认值为3306)–详细显示额外的数据包信息–tcp-ctrl显示TCP控制数据包(SYN，FIN，RST，ACK)–net-hdrs显示主要的IP和TCP标头值–no-mysql-hdrs不显示MySQL标头(数据包ID和长度)–state显示状态–v40 MySQL服务器版本为4.0 –dump转储所有数据包(十六进制)–help打印此

Original source code and more information at:http://hackmysql.com/mysqlsniffer

原始源代码和更多信息，请访问： http : //hackmysql.com/mysqlsniffer

If you are running on a development server, it would be easier to just turn on query logging.

如果您在开发服务器上运行，那么仅打开查询日志记录会更容易。

翻译自: https://www.howtogeek.com/howto/ubuntu/track-mysql-queries-with-mysqlsniffer-on-ubuntu/