How to show the last queries executed on MySQL?

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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

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

Answer 5

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

Answer 6

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