How to Show Last Queries Executed in MySQL

MySQL is a popular database system used by millions of websites and organizations. It is often used to execute large number of queries on a daily basis. As a software developer or database administrator, you may need to view last queries executed in MySQL. In this article, we will learn how to show last queries executed in MySQL.

How to Show Last Queries Executed in MySQL

There are several ways to do this. We will look at a couple of simple ones to log MySQL queries. They work for MySQL >=5.1.12. MySQL allows you to log queries to either a file, or a MySQL table. We will look at both these approaches.

1. Log Queries to MySQL Table

In this case, log into MySQL and run the following query to set the logging output to a MySQL table.

SET GLOBAL log_output = 'TABLE';

Then run the following query to turn on query logging.

SET GLOBAL general_log = 'ON';

After you run the above queries, you will be able to view last executed queries in mysql.general_log table.

select * from mysql.general_log

OR

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

If you are unable to run the above queries, you may need to contact your database administrator to modify your user privileges.

2. Log Queries to File

Log into MySQL and run the following query to log queries to a file instead of a table.

SET GLOBAL log_output = "FILE";

Next, run the following query to specify the desired path to your log file.

SET GLOBAL general_log_file = "/path/to/your/logfile.log";

Lastly, turn on query logging with the following SQL query.

SET GLOBAL general_log = 'ON';

3. Turn on Logging Permanently

Please note, in both the above methods, if you restart your MySQL server, it will be reset to default settings, that is, logging may be turned off, and you may need to re-run the above queries.

If you want to turn on logging permanently, open my.cnf file in a text editor.

$ vi /etc/mysql/my.cnf

Look for [mysqld] and add the following lines as shown. It basically mentions the path to log file against log variable. You can change the path to log file as per your requirement.

[mysqld]
log = /var/log/mysql/mysql.log

Save and close the file. Restart MySQL server to apply changes.

From now on, you will be able to view latest queries executed by opening the above mentioned log file.

$ tail -f /var/log/mysql/mysql.log

In this article, we have learnt several ways to show last queries executed in MySQL.

How to Show Last Queries Executed in MySQL