How to enable sql table logging in mysql

Question

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

Is this possible in MySQL?

Answer 1

Not. a general query log is your only option for query logging - and this is the server server ... although you can log in to the table and then delete the results that you do not need.

Answer 2

This is possible with the Percona Toolkit pt-query-digest .

If you want to track all SELECT s, UPDATE s and JOIN by touching table_one , table_two and table_three on my_database , do something like this in 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 of your incoming database traffic with tcpdump and passes it to the pt-query-digest tool, which then tries to filter this query before the queries in these tables. The result will look like a MySQL slow query log.

You will need to customize the regex in the --filter argument to suit your needs. As with most regular expressions, there will be many edge cases. I tried to cover some of them, but I'm by no means an expert when it comes to regular expressions.

This is not an ideal solution, but in some situations it does the trick when using a common query log is prohibited.