如何查看实时 MySQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/568564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I view live MySQL queries?
提问by barfoon
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?
例如，我想设置某种侦听器，然后请求一个网页并查看引擎执行的所有查询，或者只是查看在生产服务器上运行的所有查询。我怎样才能做到这一点？
采纳答案by Chad Birch
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 INSERTon top of every single query.
请注意，这可能会大大减慢服务器上的所有内容，并INSERT在每个查询之上添加额外内容。
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 -fon the logfile would do the trick.
编辑：另一种选择是General Query Log，但将其写入平面文件将消除显示灵活性的很多可能性，尤其是实时显示。如果您只是想要一种简单、易于实施的方式来查看正在发生的事情，那么启用 GQL 然后使用tail -f在日志文件上运行就可以解决问题。
回答by artfulrobot
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
做你的查询（在任何数据库上）。Grep 或以其他方式检查/var/run/mysqld/mysqld.log
Then don't forget to
然后别忘了
mysql> SET GLOBAL general_log = 'OFF';
or the performance will plummet and your disk will fill!
否则性能会下降，你的磁盘会填满！
回答by halfpastfour.am
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.
这将每秒在屏幕上打印当前查询。
-uThe mysql user you want to execute the command as-pPrompt for your password (so you don't have to save it in a file or have the command appear in your command history)iThe interval in seconds.- Use the
--verboseflag to show the full process list, displaying the entire query for each process. (Thanks, nmat)
-u要执行命令的 mysql 用户-p提示您输入密码（这样您就不必将其保存在文件中或让命令出现在您的命令历史记录中）i以秒为单位的间隔。- 使用
--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 .02seconds to run and is ran between intervals, you won't see it.
有一个可能的缺点：如果快速查询在您设置的时间间隔之间运行，则可能不会显示它们。IE：我的间隔设置为一秒，如果有一个查询需要.02几秒钟才能运行并且在间隔之间运行，您将看不到它。
Use this option preferably when you quickly want to check on running queries without having to set up a listener or anything else.
当您希望快速检查正在运行的查询而无需设置侦听器或其他任何内容时，最好使用此选项。
回答by python1981
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
回答by kenorb
strace
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 outputby piping(just add |between these two one-liners) above into the following command:
由于有很多转义字符，您可以通过管道（只需在这两个单行之间添加）将strace 的输出格式化为以下命令：|
grep --line-buffered -o '".\+[^"]"' | grep --line-buffered -o '[^"]*[^"]' | while read -r line; do printf "%b" $line; done | tr "\r\n" "56" | tr -d "[:cntrl:]" | tr "56" "\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
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
了解更多：DTracing MySQL 入门
Gibbs MySQL Spyglass
吉布斯 MySQL 望远镜
See this answer.
看到这个答案。
Logs
日志
Here are the steps useful for development proposes.
以下是对开发建议有用的步骤。
Add these lines into your ~/.my.cnfor global my.cnf:
将这些行添加到您的~/.my.cnf或全局的my.cnf：
[mysqld]
general_log=1
general_log_file=/tmp/mysqld.log
Paths: /var/log/mysqld.logor /usr/local/var/log/mysqld.logmay also work depending on your file permissions.
路径：/var/log/mysqld.log或者/usr/local/var/log/mysqld.log也可以根据您的文件权限工作。
then restart your MySQL/MariaDB by (prefix with sudoif necessary):
然后通过以下方式重新启动您的 MySQL/MariaDB（sudo如有必要，请使用前缀）：
killall -HUP mysqld
Then check your logs:
然后检查您的日志：
tail -f /tmp/mysqld.log
After finish, change general_logto 0(so you can use it in future), then remove the file and restart SQL server again: killall -HUP mysqld.
完成后，更改general_log为0（以便您将来使用它），然后删除该文件并再次重新启动SQL服务器：killall -HUP mysqld。
回答by Michael Krauklis
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。我希望这可能对某人有所帮助。
回答by Wil
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/
回答by recurse
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;
回答by gb2d
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.
我一直在寻求做同样的事情，并从各种帖子中拼凑出一个解决方案，并创建了一个小型控制台应用程序来输出写入日志文件的实时查询文本。这对我来说很重要，因为我将 Entity Framework 与 MySQL 一起使用，并且我需要能够检查生成的 SQL。
Steps to create the log file (some duplication of other posts, all here for simplicity):
创建日志文件的步骤（其他帖子的一些重复，为了简单起见，都在这里）：
Edit the file located at:
C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.iniAdd "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.logCreate / 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); } } } }
编辑位于以下位置的文件：
C:\Program Files (x86)\MySQL\MySQL Server 5.5\my.ini在文件底部添加“log=development.log”。（注意保存这个文件需要我以管理员身份运行我的文本编辑器）。
使用MySql工作台打开命令行，输入密码。
运行以下命令以打开记录所有查询运行的常规日志记录：
SET GLOBAL general_log = 'ON'; To turn off: SET GLOBAL general_log = 'OFF';这将导致正在运行的查询写入以下位置的文本文件。
C:\ProgramData\MySQL\MySQL Server 5.5\data\development.log创建/运行将实时输出日志信息的控制台应用程序：
来源：
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); } } } }
