Searching MySQL Bin Log for a Query

Question

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.....

Braeunig

Actually, UPDATE isn't a query. SELECT is a query. – Juniper

Answer 1

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

Illomened

Instead of a file mysql_bin.log, MySQL 8.0 on Ubuntu 22.04 has individually-numbered log files of the form binlog.000555. There can be twenty or thirty of them. Is there a way to search all of them at once using the mysqlbinlog command, or do we have to run this individually for all twenty or thirty files? – Atlas

@BoreaDeitz In data directory: ls binlog.0* | xargs mysqlbinlog >> ~/results.txt – Illomened

Answer 2

mysqlbinlog ${1} |grep -i  'update\|insert\|delete\|replace\|alter' | tr ‘[A-Z]’ ‘[a-z]’|sed -e '/*/d' | sort | uniq -c | sort -nr

Arpent

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 – Maytime

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. – Marquetry

Answer 3

Maybe MySQL general query log can help you.

Ahvenanmaa

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. – Braeunig

Answer 4

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

Lewislewisite