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