如何显示在MySQL上执行的最后查询?

Is there any query/way to show the last queries executed on ALL servers?

是否有任何查询/方法显示在所有服务器上执行的最后查询?

10 个解决方案

#1

640

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';
执行SET GLOBAL log_output = 'TABLE';
Execute SET GLOBAL general_log = 'ON';
执行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 instead of a table:

如果您喜欢输出文件而不是表格:

SET GLOBAL log_output = "FILE"; the default.
设置全局log_output = "FILE";默认值。
SET GLOBAL general_log_file = "/path/to/your/logfile.log";
设置全局general_log_file = "/path/to/your/log .log";
SET GLOBAL general_log = 'ON';
设置全局general_log = 'ON';

I prefer this method to editing .cnf files because:

我更喜欢这个方法来编辑。cnf文件，因为:

you're not editing the my.cnf file and potentially permanently turning on logging
您没有编辑my.cnf文件，并可能永久地打开日志记录
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
您并不是在文件系统中寻找查询日志——或者更糟糕的是，由于需要完美的目的地而分心。/var/log /var/data/log / opt /home/mysql_savior / var
restarting the server leaves you where you started (log is by default still off)
重新启动服务器将使您回到开始的位置(默认情况下日志仍然是关闭的)
You don't have to restart the server and interrupt any current connections to it.
您不必重新启动服务器并中断与它的任何当前连接。

For more information, see MySQL 5.1 Reference Manual - Server System Variables - general_log

有关更多信息，请参见MySQL 5.1参考手册-服务器系统变量- general_log

#2

You can enable a general query log for 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.

嘿，很快，你就能看到查询了。

#3

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

#4

SELECT * FROM  mysql.general_log  WHERE command_type ='Query' LIMIT total;

#5

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

我们还可以检查.mysql_history文件cat ~/.mysql_history中的查询。

#6

If mysql binlog is 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的性能

#7

Maybe you could find that out by looking at the query log.

也许你可以通过查看查询日志找到答案。

#8

You can look at the following in linux

您可以在linux中查看以下内容

cd /root

ls -al

vi .mysql_history It may help

mysql_history可能会有所帮助。

#9

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

在阅读了Paul的答案之后，我继续挖掘https://dev.mysql.com/doc/refman/5.7/en/querylog.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).

功劳归于塞巴斯蒂安·凯瑟(代码的原作者)。

Hope someone will find it useful as I did.

希望有人会发现它像我一样有用。

#10

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配置，可以使用“Neor Profile SQL”http://www.profilesql.com之类的SQL分析器。

#1

640