How to show the last queries executed on MySQL?

Question

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

Lictor

Answer 1

For those blessed with MySQL >= 5.1.12, you can control this option globally at runtime:

1. Execute SET GLOBAL log_output = 'TABLE';
2. Execute SET GLOBAL general_log = 'ON';
3. Take a look at the table mysql.general_log

If you prefer to output to a file instead of a table:

1. SET GLOBAL log_output = "FILE"; the default.
2. SET GLOBAL general_log_file = "/path/to/your/logfile.log";
3. SET GLOBAL general_log = 'ON';

I prefer this method to editing .cnf files because:

1. you're not editing the my.cnf file and potentially permanently turning on logging
2. 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
3. You don't have to restart the server and interrupt any current connections to it.
4. 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

Anthropoid

Great UI design. Anyway, MySQL log tables are actually using CSV engine so you can do all that FlipMcF said in the answer about enabling logging into general_log table, and have tail -f of general_log like this: tail -f /var/lib/mysql/mysql/general_log.CSV – Indophenol

damn it, the mysql doc for this does not even shot the table parameter. thanks a lot. – Fogbound

@Vangel, see here. It's there. But yes, it was frustrating to not see it for version 5.5, even though it is shown for version 5.1. – Act

Is there a way to list all the variables that are set? – Barkley

@Jeach: #1494222 – Anthropoid

Can we do this database basis? Each database on MySQL have its own query log table? This would make me life much easier! – Amputate

remember to clear out your general log table when you are finished: "truncate table mysql.general_log" – Chicken

@FlipMcF, Btw, what are some use cases whereby we need to do this? – Moonfaced

I got the result in this way, but parameters are present by question mark, e.g., select foo from bar where x=?. How can I get the complete query? – Westwardly

To deactivate the log SET GLOBAL general_log = 0; and can safely truncate table TRUNCATE table mysql.general_log – Kareykari

Beware that this only shows you all the queries run, not how long they each took, there's a different mechanism for that :| – Ausgleich

@rogerdpack: what's the different mechanism? – Salema

@SamGoldberg I can't remember off the top of my head, maybe the "slow query" option or something like that :) – Ausgleich

@okwap use long_query_time = 0 and slow_query_log = 1 : serverfault.com/a/814716 – Weitzel

This answer is NINE YEARS OLD. Don't we have MySQL 8 now? – Anthropoid

This answer is also valid for MariaDB. – Hanhana

How do you see the current value of global log_output? – Displayed

what if you don't have super privileges? – Chungchungking

@Anthropoid Can you please also pass the details, like is there any how we can clear the query log file? Is there any way, so that we can check the latest query log easily by deleting old ones – Tedie

removing EXECUTE prefix works for me – Tanka

Answer 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.

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.

Argus

general_log_file and general_log in my my.cnf – Outfielder

This is working if you want know restart mysql askubuntu.com/questions/82374/how-do-i-start-stop-mysql-server – Galle

Newer versions of Mac OS X (at least on Mac OS X) require the general_log_file and general_log options instead of just "log =". Otherwise, you get an error like this: ERROR /usr/local/mysql/bin/mysqld: ambiguous option '--log=/tmp/mysql_queries.log' (log-bin, log_slave_updates) – Dithionite

@JaySheth, I got that error on Mariadb 10.2, so I'm not sure it's Mac OS only related. – Rompers

I am not able to restart my mysql server if I add this line [mysqld] log = /var/log/mysql/mysql.log in ` /etc/mysql/my.cnf` – Macaroni

Answer 3

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

Mitch

Answer 4

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

Jibber

Answer 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

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

Buxtehude

Answer 6

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

Peddada

Answer 7

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

Idiographic

possible, but painful. more useful if you are looking to see what happened in the past. – Chicken

Answer 8

If you don't feel like changing your MySQL configuration you could use an SQL profiler like "Neor Profile SQL" http://www.profilesql.com .

Unravel

Answer 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

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.

Nostrum

Interesting. You'd miss queries during the cleanup unless you locked all the db's first. There are other issues that may arise here. If we're at this point of 'continuous' logging, I'd use file logging or the bin log and an off-the-shelf log rotator. – Anthropoid

Answer 10

You can look at the following in linux

cd /root
ls -al
vi .mysql_history   

It may help

Idiographic

This looks like it would only work for the official command-line mysql client, and only for queries executed by the root user – Dongola

Question says 'all the server' which makes this answer incorrect. This would show all queries executed by a single client. – Anthropoid

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 genral log will have an effect on performance of mysql – Idiographic

Some entries in this are suppressed by default anyway, strings containing "password" for example. – Clupeid

cat /home/shravan/.mysql_history – Vhf