How to log queries to a selected table in MySql

Question

I want to log queries to just one table.

I have found these

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';

However this logs all queries.

There are questions on SO, but they are for total logging.

How to show the last queries executed on MySQL?

Log all queries in mysql

• Rick James: grep the "general log" for the table's name.
• Rohit Gupta: @RickJames, Yes, but I don't want to log all the queries. That is madness. Was the downvote yours?
• Rick James: Not my downvote; I think the general log is the best available tool.

Answer 1

You cannot use the general log or slow log to be dedicated to a single table.

Here is a suggestion: Setup a Query Digest

It is possible to setup a Query Digest Without using the Slow Log. How ???

You can install pt-query-digest and have it examine the MySQL Processlist

You can run pt-query-digest every hour and then you can read the query digest header for every query and look for the table that you wish to profile for SELECTs, INSERTs, UPDATEs, and DELETEs.

Here are some past posts on this using pt-query-digest

• Dec 28, 2011 : Is there a tool like Microsoft's "SQL Server Profiler" for MySQL?
• Nov 24, 2011 : MySQL general query log performance effects

I have suggested this many times over the years

Answer 2

If you want to log all activities for the given table (select/insert/update/delete) you have no choice but log everything.
If you want to log only modifications made to the table (insert/update/delete) you can enable a replication master binlog and restrict it to the desired table:

 replicate-do-table=db_name.tbl_name

Sure, you have to use mysqlbinlog utility to decode binlog.

Further reading: https://dev.mysql.com/doc/refman/8.4/en/replication-options-replica.html#option_mysqld_replicate-do-table