7

Is it possible to query the mysql bin log for a particular query? For example, suppose I want to know if anyone in the last hour did a specific query (like 'Update tableX where userName = 'bob'"). I just want to see if a particular query has been run recently.....

David
  • 407
  • 3
  • 8
  • 13

4 Answers4

9

Use mysqlbinlog - nix or mysqlbinlog.exe - windows

$bash>mysqlbinlog mysql_bin.log > mysql_bin.txt

After conversion You can search DML in mysql_bin.txt

baklarz2048
  • 10,699
  • 2
  • 31
  • 37
5
mysqlbinlog ${1} |grep -i  'update\|insert\|delete\|replace\|alter' | tr ‘[A-Z]’ ‘[a-z]’|sed -e '/*/d' | sort | uniq -c | sort -nr
Jeeva
  • 89
  • 1
  • 3
  • 3
    Consider providing an explanation to the original post. While the information may be a bit vague, your answer is a bit unclear. Please explain to the OP why this will help – nomistic Sep 19 '15 at 13:26
  • this command will return only queries that alter a value. further grepping could limit the results to a table name and even further to that same table with specific keywords. it may take a while to run, but would limit the results without disk usage. – TheSatinKnight May 10 '19 at 00:20
0

Maybe MySQL general query log can help you.

Gadonski
  • 3,150
  • 2
  • 25
  • 31
  • Actually I already read that and only saw options for things like search by start and end time. I was hoping to avoid writing the entire multi-gigabyte bin log to disk and then having to grep it. – David Oct 08 '10 at 12:50
0

The upvoted answer did't help me. The reason is that mysqlbinlog uses BINLOG statement which is readable by server and it can't be used to filter queries.

But fortunately there's the -v option which reconstructs the query and allows to make searches. This option applies both to MySQL 5.7 and 8.

Use this command to check if mytable was updated:

mysqldump -v mysql-bin.log | grep UPDATE | grep mytable
Denis
  • 302
  • 4
  • 7