CODEWITHSUNDEEP
mysqllogging
Benjamin
Benjamin

Reputation: 628

How to enable per table sql statement logging in mysql

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 ?

Upvotes: 6

Views: 4929

Answers (3)

Jongab
Jongab

Reputation: 51

You can do it with a trigger:

delimiter |
CREATE TRIGGER trigger_name AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
     insert into tmp.my_log values(........);
END;|

The original values are stored in OLD.column_name and the updated values are stored in NEW.column_name. You can log whichever fields you are interested in to a different table.

Upvotes: 1

Tristan
Tristan

Reputation: 2727

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.

Upvotes: 4

Manse
Manse

Reputation: 38147

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

Upvotes: 6

Related Questions