Is there any query/way to show the last queries executed on ALL servers?
10 Answers
860
votes
For those blessed with MySQL >= 5.1.12, you can control this option globally at runtime:
- Execute
SET GLOBAL log_output = 'TABLE'; - Execute
SET GLOBAL general_log = 'ON'; - Take a look at the table
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';
I prefer this method to editing .cnf files because:
- 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)
For more information, see MySQL 5.1 Reference Manual - Server System Variables - general_log
44
votes
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.
Edit your MySQL config, e.g. /etc/mysql/my.cnf - look for, or add, a line like this
[mysqld]
log = /var/log/mysql/mysql.log
Restart mysql to pick up that change, now you can
tail -f /var/log/mysql/mysql.log
Hey presto, you can watch the queries as they come in.
19
votes
You can do the flowing thing for monitoring mysql query logs.
Open mysql configuration file my.cnf
sudo nano /etc/mysql/my.cnf
Search following lines under a [mysqld] heading and uncomment these lines to enable log
general_log_file = /var/log/mysql/mysql.log
general_log = 1
Restart your mysql server for reflect changes
sudo service mysql start
Monitor mysql server log with following command in terminal
tail -f /var/log/mysql/mysql.log
13
votes
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
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
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
5
votes
4
votes
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
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
3
votes
If you don't feel like changing your MySQL configuration you could use an SQL profiler like "Neor Profile SQL" http://www.profilesql.com .
3
votes
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
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)
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.
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.
