How to show the last queries executed on MySQL

Question

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

User · Answer

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 gt  show variables like  general log    mysql gt  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

User · Answer

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

User · Answer

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

User · Answer

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 cnf file 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

User · Answer

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

User · Answer

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  gt    tmp statements sql   enabling    mysqld  log    var log mysql mysql log   or general log will have an effect on performance of mysql

User · Answer

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

User · Answer

SELECT   FROM  mysql general log  WHERE command type   Query  LIMIT total

User · Answer

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   lt  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

User · Answer

You can look at the following in linux   cd  root  ls -al   vi  mysql history   It may help

[mysql] How to show the last queries executed on MySQL?

Examples related to mysql

Examples related to logging