How to show the last queries executed on MySQL?

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

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

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


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.

Tags:

Mysql

Logging