如何查看实时MySQL查询?

How can I trace MySQL queries on my Linux server as they happen?

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

For example I'd love to set up some sort of listener, then request a web page and view all of the queries the engine executed, or just view all of the queries being run on a production server. How can I do this?

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

11 个解决方案

#1

You can run the MySQL command SHOW FULL PROCESSLIST; to see what queries are being processed at any given time, but that probably won't achieve what you're hoping for.

可以运行MySQL命令SHOW FULL PROCESSLIST;查看在任何给定时间正在处理哪些查询，但这可能无法实现您所希望的。

The best method to get a history without having to modify every application using the server is probably through triggers. You could set up triggers so that every query run results in the query being inserted into some sort of history table, and then create a separate page to access this information.

不需要使用服务器修改每个应用程序，获得历史记录的最佳方法可能是通过触发器。您可以设置触发器，以便将查询中的每个查询运行结果插入到某种历史表中，然后创建一个单独的页面来访问这些信息。

Do be aware that this will probably considerably slow down everything on the server though, with adding an extra INSERT on top of every single query.

请注意，这可能会大大降低服务器上的所有内容，在每个查询的顶部添加一个额外的插入。

---

Edit: another alternative is the General Query Log, but having it written to a flat file would remove a lot of possibilities for flexibility of displaying, especially in real-time. If you just want a simple, easy-to-implement way to see what's going on though, enabling the GQL and then using running tail -f on the logfile would do the trick.

编辑:另一种选择是通用查询日志，但是将其写入平面文件将会消除显示灵活性的许多可能性，尤其是实时显示。如果您只是想要一种简单、易于实现的方式来了解发生了什么，那么启用GQL，然后在logfile上使用运行tail -f就可以了。

#2

You can log every query to a log file really easily:

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

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';

Do your queries (on any db). Grep or otherwise examine /var/run/mysqld/mysqld.log

执行查询(对任何db)。或者检查/var/ run/sqmyld/mysql. log

Then don't forget to

然后别忘了

mysql> SET GLOBAL general_log = 'OFF';

or the performance will plummet and your disk will fill!

否则性能将直线下降，你的磁盘将填满!

#3

Even though an answer has already been accepted, I would like to present what might even be the simplest option:

虽然已经接受了一个答案，但我想提出一个甚至可能是最简单的选择:

$ mysqladmin -u bob -p -i 1 processlist

This will print the current queries on your screen every second.

这将每秒钟在屏幕上打印当前查询。

• -u The mysql user you want to execute the command as
• -u你想要执行命令的mysql用户。
• -p Prompt for your password (so you don't have to save it in a file or have the command appear in your command history)
• -p提示输入密码(这样你就不必把它保存在文件中，或者命令出现在你的命令历史中)
• i The interval in seconds.
• 以秒为单位。
• Use the --verbose flag to show the full process list, displaying the entire query for each process. (Thanks, nmat)
• 使用-verbose标志显示完整的流程列表，显示每个流程的整个查询。(谢谢,nmat)

There is a possible downside: fast queries might not show up if they run between the interval that you set up. IE: My interval is set at one second and if there is a query that takes .02 seconds to run and is ran between intervals, you won't see it.

有一个可能的缺点:如果快速查询在您设置的间隔之间运行，那么它们可能不会出现。IE:我的间隔设置为1秒，如果有一个查询需要运行0.02秒，并且在间隔之间运行，您将看不到它。

Use this option preferably when you quickly want to check on running queries without having to set up a listener or anything else.

如果您想快速检查正在运行的查询，而不需要设置侦听器或其他任何东西，最好使用此选项。

#4

Run this convenient SQL query to see running MySQL queries. It can be run from any environment you like, whenever you like, without any code changes or overheads. It may require some MySQL permissions configuration, but for me it just runs without any special setup.

运行这个方便的SQL查询以查看正在运行的MySQL查询。它可以在任何您喜欢的环境中运行，任何时候都可以，不需要任何代码更改或开销。它可能需要一些MySQL权限配置，但对我来说，它只是在没有任何特殊设置的情况下运行。

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

The only catch is that you often miss queries which execute very quickly, so it is most useful for longer-running queries or when the MySQL server has queries which are backing up - in my experience this is exactly the time when I want to view "live" queries.

唯一的问题是，您经常会错过执行速度非常快的查询，因此对于运行时间较长的查询，或者当MySQL服务器有正在备份的查询时，这是最有用的。

You can also add conditions to make it more specific just any SQL query.

您还可以添加条件，使其更具体，只是任何SQL查询。

e.g. Shows all queries running for 5 seconds or more:

例:显示所有查询运行5秒或以上:

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

e.g. Show all running UPDATEs:

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

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

For full details see: http://dev.mysql.com/doc/refman/5.1/en/processlist-table.html

有关详细信息，请参见:http://dev.mysql.com/doc/refman/5.1/en/processlist-table.html。

#5

I'm in a particular situation where I do not have permissions to turn logging on, and wouldn't have permissions to see the logs if they were turned on. I could not add a trigger, but I did have permissions to call show processlist. So, I gave it a best effort and came up with this:

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

Create a bash script called "showsqlprocesslist":

创建一个名为“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

Execute the script:

执行脚本:

./showsqlprocesslist > showsqlprocesslist.out &

Tail the output:

尾巴的输出:

tail -f showsqlprocesslist.out

Bingo bango. Even though it's not throttled, it only took up 2-4% CPU on the boxes I ran it on. I hope maybe this helps someone.

宾果得了。即使它没有被控制，它在我运行它的盒子上只占用2-4%的CPU。我希望这能帮助某人。

#6

This is the easiest setup on a Linux Ubuntu machine I have come across. Crazy to see all the queries live.

这是我遇到的Linux Ubuntu机器上最简单的设置。看到所有查询都在运行，真是太疯狂了。

Find and open your MySQL configuration file, usually /etc/mysql/my.cnf on Ubuntu. Look for the section that says “Logging and Replication”

找到并打开你的MySQL配置文件，通常是Ubuntu上的/etc/mysql/my.cnf。查找“日志和复制”部分

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.

log = /var/log/mysql/mysql.log

Just uncomment the “log” variable to turn on logging. Restart MySQL with this command:

只需取消“log”变量的注释，以打开日志记录。使用以下命令重新启动MySQL:

sudo /etc/init.d/mysql restart

Now we’re ready to start monitoring the queries as they come in. Open up a new terminal and run this command to scroll the log file, adjusting the path if necessary.

现在，我们已经准备好开始监视查询了。打开一个新的终端并运行这个命令来滚动日志文件，如果需要的话调整路径。

tail -f /var/log/mysql/mysql.log

Now run your application. You’ll see the database queries start flying by in your terminal window. (make sure you have scrolling and history enabled on the terminal)

现在运行您的应用程序。您将在终端窗口中看到数据库查询开始快速通过。(确保在终端上启用了滚动和历史记录)

FROM http://www.howtogeek.com/howto/database/monitor-all-sql-queries-in-mysql/

从http://www.howtogeek.com/howto/database/monitor-all-sql-queries-in-mysql/

#7

From a command line you could run:

从命令行可以运行:

watch --interval=[your-interval-in-seconds] "mysqladmin -u root -p[your-root-pw] processlist | grep [your-db-name]"

Replace the values [x] with your values.

用您的值替换值[x]。

Or even better:

或者更好的是:

 mysqladmin -u root -p -i 1 processlist;

#8

Check out mtop.

看看mtop。支持

#9

strace

The quickest way to see live MySQL/MariaDB queries is to use debugger. On Linux you can use strace, for example:

查看实时MySQL/MariaDB查询的最快方式是使用调试器。在Linux上，您可以使用strace，例如:

sudo strace -e trace=read,write -s 2000 -fp $(pgrep -nf mysql) 2>&1

Since there are lot of escaped characters, you may format strace's output by piping (just add | between these two one-liners) above into the following command:

由于有很多转义字符，您可以将上面的管道(只需在这两个一行程序之间添加|)设置为以下命令:

grep --line-buffered -o '".\+[^"]"' | grep --line-buffered -o '[^"]*[^"]' | while read -r line; do printf "%b" $line; done | tr "\r\n" "\275\276" | tr -d "[:cntrl:]" | tr "\275\276" "\r\n"

So you should see fairly clean SQL queries with no-time, without touching configuration files.

因此，您应该可以看到相当干净的SQL查询，并且不需要修改配置文件。

Obviously this won't replace the standard way of enabling logs, which is described below (which involves reloading the SQL server).

显然，这不会取代启用日志的标准方式，如下所述(其中包括重新加载SQL服务器)。

dtrace

Use MySQL probes to view the live MySQL queries without touching the server. Example script:

使用MySQL探测来查看实时的MySQL查询，而不需要访问服务器。示例脚本:

#!/usr/sbin/dtrace -q
pid$target::*mysql_parse*:entry /* This probe is fired when the execution enters mysql_parse */
{
     printf("Query: %s\n", copyinstr(arg1));
}

Save above script to a file (like watch.d), and run:

将上面的脚本保存到一个文件中(如watch.d)，并运行:

pfexec dtrace -s watch.d -p $(pgrep -x mysqld)

Learn more: Getting started with DTracing MySQL

了解更多信息:开始使用dtrace MySQL

Rust

AgilData launched recently the Gibbs MySQL Scalability Advisor (a free self-service tool) which allows users to capture a live stream of queries to be uploaded to Gibbs. Spyglass (which is Open Source) will watch interactions between your MySQL Servers and client applications. No reconfiguration or restart of the MySQL database server is needed (either client or app).

AgilData最近推出了Gibbs MySQL伸缩性Advisor(一种免费的自助服务工具)，它允许用户捕获一个实时的查询流，并将其上传到Gibbs。Spyglass(开源的)将监视MySQL服务器和客户端应用程序之间的交互。不需要重新配置或重新启动MySQL数据库服务器(客户端或应用程序)。

GitHub: AgilData/gibbs-mysql-spyglass

GitHub:AgilData / gibbs-mysql-spyglass

Learn more: Packet Capturing MySQL with Rust

了解更多:包用铁锈捕获MySQL

Install command: curl -s https://raw.githubusercontent.com/AgilData/gibbs-mysql-spyglass/master/install.sh | bash

安装命令:curl -s https://raw.githubusercontent.com/AgilData/gibbs-mysql-spyglass/master/install.sh | bash。

Logs

Here are the steps useful for development proposes.

以下是对开发建议有用的步骤。

Add these lines into your ~/.my.cnf or global my.cnf:

将这些行添加到您的~/ my.cnf或global my.cnf中:

[mysqld]
general_log=1
general_log_file=/tmp/mysqld.log

_{Paths: /var/log/mysqld.log or /usr/local/var/log/mysqld.log may also work depending on your file permissions.}

道路:/var/log/mysqld.日志或/usr/local/var/log/mysqld.日志也可以根据您的文件权限进行工作。

then restart your MySQL/MariaDB by (prefix with sudo if necessary):

然后重新启动MySQL/MariaDB(如有必要，前缀为sudo):

killall -HUP mysqld

Then check your logs:

然后检查你的日志:

tail -f /tmp/mysqld.log

After finish, change general_log to 0 (so you can use it in future), then remove the file and restart SQL server again: killall -HUP mysqld.

完成后，将general_log更改为0(以便以后使用)，然后删除文件并重新启动SQL server: killall -HUP mysqld。

#10

I've been looking to do the same, and have cobbled together a solution from various posts, plus created a small console app to output the live query text as it's written to the log file. This was important in my case as I'm using Entity Framework with MySQL and I need to be able to inspect the generated SQL.

我一直在寻找同样的方法，并从不同的帖子中拼凑出一个解决方案，另外我还创建了一个小型控制台应用程序，在将实时查询文本写入日志文件时输出该文本。这在我的例子中很重要，因为我正在使用MySQL的实体框架，我需要能够检查生成的SQL。

Steps to create the log file (some duplication of other posts, all here for simplicity):

创建日志文件的步骤(一些其他文章的重复，这里都是为了简单起见):

1. 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).

   添加“日志=发展。记录到文件的底部。(注意，保存这个文件需要我以管理员的身份运行我的文本编辑器)。

2. Use MySql workbench to open a command line, enter the password.

   使用MySql workbench打开命令行，输入密码。

   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
   

3. 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);
           }
       }
     }
   }
   

#11

In addition to previous answers describing how to enable general logging, I had to modify one additional variable in my vanilla MySql 5.6 installation before any SQL was written to the log:

除了之前描述如何启用常规日志记录的回答之外，我还需要在我的MySql 5.6安装中修改一个额外的变量，然后才能将任何SQL写入日志:

SET GLOBAL log_output = 'FILE';

The default setting was 'NONE'.

默认设置是“NONE”。