How to filter output of "SHOW BINLOG EVENTS"

Question

Without talking about master/slave replication, I just want to customize which entries I see when querying the MySQL Binary-Logs with SHOW BINLOG EVENTS. Already tried to find the same information in information_schema and performance_schema so that I can SELECT on a proper table but I was unable to find it.

The available filters from the docs do not seem to allow this directly.

What I'd like is something like:

-- /!\ invalid syntax  /!\ --
SHOW BINLOG EVENTS WHERE Event_type = 'Query' AND Info LIKE 'UPDATE%'

-- /!\ invalid syntax  /!\ --
SHOW BINLOG EVENTS IN (SELECT Log_name FROM (SHOW BINARY LOGS))

Is there a way to achieve this?

---

My current workaround with the mysqlbinlog utility (runs in MySQL Container):

cd /var/lib/mysql && while read p; do mysqlbinlog -d example_db -s /var/lib/mysql/$p | grep UPDATE; done <./mysql-bin.index; cd - > /dev/null

Scat

Answer 1

Unfortunately, MySQL does not support filtering 'SHOW BINLOG EVENTS', directly with a 'WHERE' clause. To achieve this, you need to use the 'mysqlbinlog' utility and filter the output.

Here’s a script:

cd /var/lib/mysql
for log_file in $(mysql -u your_user -pyour_password -e "SHOW BINARY LOGS;" | awk '{print $1}' | grep -v "Log_name"); do
  mysqlbinlog $log_file | grep -E "Query\s+.*UPDATE"
done
cd - > /dev/null

Prussiate