如何显示在 MySQL 上执行的最后查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/650238/
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 to show the last queries executed on MySQL?
提问by FerranB
Is there any query/way to show the last queries executed on ALLservers?
是否有任何查询/方法可以显示在所有服务器上执行的最后查询？
回答by FlipMcF
For those blessed with MySQL >= 5.1.12, you can control this option globally at runtime:
对于那些拥有 MySQL >= 5.1.12 的用户，您可以在运行时全局控制此选项：
- Execute
SET GLOBAL log_output = 'TABLE'; - Execute
SET GLOBAL general_log = 'ON'; - Take a look at the table
mysql.general_log
- 执行
SET GLOBAL log_output = 'TABLE'; - 执行
SET GLOBAL general_log = 'ON'; - 看看表
mysql.general_log
If you prefer to output to a file instead of a table:
如果您更喜欢输出到文件而不是表格：
SET GLOBAL log_output = "FILE";the default.SET GLOBAL general_log_file = "/path/to/your/logfile.log";SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = "FILE";默认.SET GLOBAL general_log_file = "/path/to/your/logfile.log";SET GLOBAL general_log = 'ON';
I prefer this method to editing .cnf files because:
我更喜欢这种方法来编辑 .cnf 文件，因为：
- you're not editing the
my.cnffile and potentially permanently turning on logging - you're not fishing around the filesystem looking for the query log - or even worse, distracted by the need for the perfect destination.
/var/log /var/data/log/opt /home/mysql_savior/var - You don't have to restart the server and interrupt any current connections to it.
- restarting the server leaves you where you started (log is by default still off)
- 您没有编辑
my.cnf文件并可能永久打开日志记录 - 你不是在文件系统中寻找查询日志——或者更糟糕的是，因为需要完美的目的地而分心。
/var/log /var/data/log/opt /home/mysql_savior/var - 您不必重新启动服务器并中断与它的任何当前连接。
- 重新启动服务器会让你从你开始的地方（默认情况下日志仍然关闭）
For more information, see MySQL 5.1 Reference Manual - Server System Variables - general_log
有关更多信息，请参阅 MySQL 5.1 参考手册 - 服务器系统变量 - general_log
回答by Paul Dixon
You can enable a general query logfor that sort of diagnostic. Generally you don't log all SELECT queries on a production server though, it's a performance killer.
您可以为此类诊断启用常规查询日志。通常，您不会在生产服务器上记录所有 SELECT 查询，这是一个性能杀手。
Edit your MySQL config, e.g. /etc/mysql/my.cnf - look for, or add, a line like this
编辑您的 MySQL 配置，例如 /etc/mysql/my.cnf - 查找或添加这样的行
[mysqld]
log = /var/log/mysql/mysql.log
Restart mysql to pick up that change, now you can
重新启动 mysql 以获取该更改，现在您可以
tail -f /var/log/mysql/mysql.log
Hey presto, you can watch the queries as they come in.
嘿，presto，您可以在查询进来时观看它们。
回答by Rokibul Hasan
You can do the flowing thing for monitoring mysql query logs.
你可以做流动的事情来监控 mysql 查询日志。
Open mysql configuration file my.cnf
打开mysql配置文件my.cnf
sudo nano /etc/mysql/my.cnf
Search following lines under a [mysqld]heading and uncomment these lines to enable log
在[mysqld]标题下搜索以下行并取消注释这些行以启用日志
general_log_file = /var/log/mysql/mysql.log
general_log = 1
Restart your mysql server for reflect changes
重新启动您的 mysql 服务器以反映更改
sudo service mysql start
Monitor mysql server log with following command in terminal
在终端中使用以下命令监控 mysql 服务器日志
tail -f /var/log/mysql/mysql.log
回答by zloctb
SELECT * FROM mysql.general_log WHERE command_type ='Query' LIMIT total;
回答by minhas23
1) If general mysql logging is enabled then we can check the queries in the log file or table based what we have mentioned in the config. Check what is enabled with the following command
1) 如果启用了一般的 mysql 日志记录，那么我们可以根据我们在配置中提到的内容检查日志文件或表中的查询。检查使用以下命令启用的内容
mysql> show variables like 'general_log%';
mysql> show variables like 'log_output%';
If we need query history in table then
如果我们需要表中的查询历史，那么
Execute SET GLOBAL log_output = 'TABLE';
Execute SET GLOBAL general_log = 'ON';
Take a look at the table mysql.general_log
看看表mysql.general_log
If you prefer to output to a file:
如果您更喜欢输出到文件：
SET GLOBAL log_output = "FILE"; which is set by default.
SET GLOBAL general_log_file = "/path/to/your/logfile.log";
SET GLOBAL general_log = 'ON';
2) We can also check the queries in the .mysql_history file cat ~/.mysql_history
2）我们也可以查看.mysql_history文件中的查询 cat ~/.mysql_history
回答by Avinash Singh
If mysql binlogis enabled you can check the commands ran by user by executing following command in linux console by browsing to mysql binlog directory
如果启用了mysql binlog，您可以通过浏览到 mysql binlog 目录在 linux 控制台中执行以下命令来检查用户运行的命令
mysqlbinlog binlog.000001 > /tmp/statements.sql
enabling
使能
[mysqld]
log = /var/log/mysql/mysql.log
or general log will have an effect on performance of mysql
或者一般日志会影响mysql的性能
回答by Bitclaw
If you don't feel like changing your MySQL configuration you could use an SQL profiler like "Neor Profile SQL" http://www.profilesql.com.
如果您不想更改 MySQL 配置，您可以使用 SQL 分析器，如“Neor Profile SQL” http://www.profilesql.com。
回答by Dev Aggarwal
After reading Paul's answer, I went on digging for more information on https://dev.mysql.com/doc/refman/5.7/en/query-log.html
阅读保罗的回答后，我继续挖掘有关https://dev.mysql.com/doc/refman/5.7/en/query-log.html 的更多信息
I found a really useful code by a person. Here's the summary of the context.
我找到了一个非常有用的代码。这是上下文的摘要。
(Note: The following code is not mine)
（注：以下代码不是我的）
This script is an example to keep the table clean which will help you to reduce your table size. As after a day, there will be about 180k queries of log. ( in a file, it would be 30MB per day)
此脚本是保持表格清洁的示例，可帮助您减小表格大小。一天后，将有大约 180k 的日志查询。（在一个文件中，每天 30MB）
You need to add an additional column (event_unix) and then you can use this script to keep the log clean... it will update the timestamp into a Unix-timestamp, delete the logs older than 1 day and then update the event_time into Timestamp from event_unix... sounds a bit confusing, but it's working great.
您需要添加一个额外的列（event_unix），然后您可以使用此脚本来保持日志干净......它会将时间戳更新为 Unix 时间戳，删除超过 1 天的日志，然后将 event_time 更新为时间戳来自 event_unix ... 听起来有点令人困惑，但效果很好。
Commands for the new column:
新列的命令：
SET GLOBAL general_log = 'OFF';
RENAME TABLE general_log TO general_log_temp;
ALTER TABLE `general_log_temp`
ADD COLUMN `event_unix` int(10) NOT NULL AFTER `event_time`;
RENAME TABLE general_log_temp TO general_log;
SET GLOBAL general_log = 'ON';
Cleanup script:
清理脚本：
SET GLOBAL general_log = 'OFF';
RENAME TABLE general_log TO general_log_temp;
UPDATE general_log_temp SET event_unix = UNIX_TIMESTAMP(event_time);
DELETE FROM `general_log_temp` WHERE `event_unix` < UNIX_TIMESTAMP(NOW()) - 86400;
UPDATE general_log_temp SET event_time = FROM_UNIXTIME(event_unix);
RENAME TABLE general_log_temp TO general_log;
SET GLOBAL general_log = 'ON';
Credit goes to Sebastian Kaiser (Original writer of the code).
归功于 Sebastian Kaiser（代码的原始作者）。
Hope someone will find it useful as I did.
希望有人会像我一样觉得它有用。
回答by Avinash Singh
You can look at the following in linux
你可以在linux中查看以下内容
cd /root
ls -al
vi .mysql_historyIt may help
vi .mysql_history它可能有帮助
