How to show MySQL query history?

In this article, we will learn a method to show the history of MySQL executed commands. We will learn how to show the history of commands executed through the table mysql.general_log.

Show MySQL history command using mysql.general_log table

MySQL has a table where the queries that run on the database server are stored. By default this function is inactive. We can activate it through the following commands:

SET GLOBAL log_output = 'TABLE'; SET GLOBAL general_log = 'ON';

Script 1. MySQL query history log in table activation

Now we can check the mysql.general_log table content with the following command:

SELECT * FROM mysql.general_log LIMIT 10

Script 2. Command to retrieve mysql.general_log table content

Result:

Table 1. First 10 rows of the mysql.general_log table content

We have limited the query to only the first ten rows to show an example of the extensive content of the mysql.general_log table.

As we can see the table has six columns. Event_time shows the date and time when the command was executed. User_host indicates the username and host that executed the command. Thread_id and server_id, show the respective ids. The command_type column shows the type of command executed (Query, Quit, Connect …). Finally, the argument column shows the text of the executed command.

If there is a Clients table in our database, and we want to locate the queries that have been made on that table, we can use the following command:

SELECT * FROM mysql.general_log WHERE argument LIKE '%clients%'

Script 3. Consult queries made on Clients table

Result:

Table 2. Query made on Clients table

The size of the table mysql.general_log can increase rapidly since the table is fed with the commands executed on the server. We can clean the content of the table, or we can also deactivate the function so the commands are no longer stored in the log.

We can execute the following commands to clean up the table:

SET GLOBAL general_log=OFF; TRUNCATE table mysql.general_log; SET GLOBAL general_log=ON;

Script 4. Clean mysql.general_log content

We can execute the following command to deactivate the functionality, and the executed commands are no longer saved:

SET GLOBAL general_log=OFF;

Script 5. Deactivate mysql.general_log functionality