How to enable per table sql statement logging in mysql

Question

I would like to log all SQL statements that access a specific or a list of specific tables, but not ALL tables.

Is this even possible in MySQL ?

Answer 1

No - the general query log is your only option for logging queries - and this is server wide ... although you could log to a table and then delete the results you dont require

Answer 2

This is possible using Percona Toolkit's pt-query-digest.

If you'd like to monitor all SELECTs, UPDATEs, and JOINs touching table_one, table_two, and table_three on my_database, running something like this on your database server will do the trick:

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 |
pt-query-digest --type tcpdump \
                --run-time 5s \
                --iterations 0 \
                --filter '$event->{fingerprint} =~ m/\b(from|join|into)\s+(`?my_database`?\.)`?(table_one|table_two|table_three)`?\b/' \
                --output slowlog \
                --no-report

This monitors all your incoming database traffic using tcpdump and pipes it into the pt-query-digest tool, which then attempts to filter that down to the queries on those tables. The output will look something like MySQL's slow query log.

You will need to adjust the regular expression in the --filter argument to fit your needs. As is the case with most regular expressions, there are going to be a lot of edge cases. I tried to cover a few of them, but I am by no means an expert when it comes to regular expressions.

This is not a perfect solution, but it has done the trick for me in certain situations where using the general query log is prohibited.