如何在我的Linux服务器上跟踪MySQL查询?

例如,我想要设置某种侦听器,然后请求一个web页面并查看引擎执行的所有查询,或者只是查看在生产服务器上运行的所有查询。我该怎么做呢?


当前回答

我处于一种特殊的情况,我没有打开日志的权限,如果日志被打开,我也没有查看日志的权限。我不能添加触发器,但我确实有权限调用show processlist。所以,我尽了最大的努力,想出了这个:

创建一个名为"showsqlprocesslist"的bash脚本:

#!/bin/bash

while [ 1 -le 1 ]
do
         mysql --port=**** --protocol=tcp --password=**** --user=**** --host=**** -e "show processlist\G" | grep Info | grep -v processlist | grep -v "Info: NULL";
done

执行脚本:

./showsqlprocesslist > showsqlprocesslist.out &

尾部输出:

tail -f showsqlprocesslist.out

宾果得了。即使它没有节流,它只占用2-4%的CPU在我运行它的盒子上。我希望这能帮助到一些人。

其他回答

我一直想做同样的事情,并从各种帖子中拼凑出了一个解决方案,再加上创建了一个小的控制台应用程序来输出实时查询文本,因为它被写入日志文件。这在我的情况下是重要的,因为我使用实体框架与MySQL,我需要能够检查生成的SQL。

创建日志文件的步骤(一些其他帖子的复制,这里都是为了简单起见):

Edit the file located at: C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.ini Add "log=development.log" to the bottom of the file. (Note saving this file required me to run my text editor as an admin). Use MySql workbench to open a command line, enter the password. Run the following to turn on general logging which will record all queries ran: SET GLOBAL general_log = 'ON'; To turn off: SET GLOBAL general_log = 'OFF'; This will cause running queries to be written to a text file at the following location. C:\ProgramData\MySQL\MySQL Server 5.5\data\development.log Create / Run a console app that will output the log information in real time: Source available to download here Source: using System; using System.Configuration; using System.IO; using System.Threading; namespace LiveLogs.ConsoleApp { class Program { static void Main(string[] args) { // Console sizing can cause exceptions if you are using a // small monitor. Change as required. Console.SetWindowSize(152, 58); Console.BufferHeight = 1500; string filePath = ConfigurationManager.AppSettings["MonitoredTextFilePath"]; Console.Title = string.Format("Live Logs {0}", filePath); var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite); // Move to the end of the stream so we do not read in existing // log text, only watch for new text. fileStream.Position = fileStream.Length; StreamReader streamReader; // Commented lines are for duplicating the log output as it's written to // allow verification via a diff that the contents are the same and all // is being output. // var fsWrite = new FileStream(@"C:\DuplicateFile.txt", FileMode.Create); // var sw = new StreamWriter(fsWrite); int rowNum = 0; while (true) { streamReader = new StreamReader(fileStream); string line; string rowStr; while (streamReader.Peek() != -1) { rowNum++; line = streamReader.ReadLine(); rowStr = rowNum.ToString(); string output = String.Format("{0} {1}:\t{2}", rowStr.PadLeft(6, '0'), DateTime.Now.ToLongTimeString(), line); Console.WriteLine(output); // sw.WriteLine(output); } // sw.Flush(); Thread.Sleep(500); } } } }

你可以很容易地将每个查询记录到日志文件中:

mysql> SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+

mysql> SET GLOBAL general_log = 'ON';

执行查询(在任何db上)。查看/var/run/mysqld/mysqld.log

那就别忘了

mysql> SET GLOBAL general_log = 'OFF';

否则,性能将急剧下降,磁盘将被填满!

运行这个方便的SQL查询来查看正在运行的MySQL查询。它可以在您喜欢的任何环境中运行,无需任何代码更改或开销。它可能需要一些MySQL权限配置,但对我来说,它不需要任何特殊设置就可以运行。

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep';

唯一的问题是,你经常会错过执行非常快的查询,所以它对运行时间较长的查询或MySQL服务器有正在备份的查询时最有用——以我的经验,这正是我想要查看“活动”查询的时候。

您还可以添加条件,使其更特定于任何SQL查询。

显示所有运行5秒或更长时间的查询:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' AND TIME >= 5;

显示所有正在运行的更新:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep' AND INFO LIKE '%UPDATE %';

详情见:http://dev.mysql.com/doc/refman/5.1/en/processlist-table.html

我处于一种特殊的情况,我没有打开日志的权限,如果日志被打开,我也没有查看日志的权限。我不能添加触发器,但我确实有权限调用show processlist。所以,我尽了最大的努力,想出了这个:

创建一个名为"showsqlprocesslist"的bash脚本:

#!/bin/bash

while [ 1 -le 1 ]
do
         mysql --port=**** --protocol=tcp --password=**** --user=**** --host=**** -e "show processlist\G" | grep Info | grep -v processlist | grep -v "Info: NULL";
done

执行脚本:

./showsqlprocesslist > showsqlprocesslist.out &

尾部输出:

tail -f showsqlprocesslist.out

宾果得了。即使它没有节流,它只占用2-4%的CPU在我运行它的盒子上。我希望这能帮助到一些人。

除了前面描述如何启用一般日志记录的答案之外,在将任何SQL写入日志之前,我必须在我的香草MySql 5.6安装中修改一个额外的变量:

SET GLOBAL log_output = 'FILE';

默认设置为“NONE”。