Comments on: Identifying Useful Info from MySQL Row-Based Binary Logs

By: ishant mahajan

ishant mahajan — Tue, 16 Jun 2020 09:50:50 +0000

Is there any way to have the timestamp till micro or milliseconds

By: venkat

venkat — Wed, 24 Apr 2019 10:20:39 +0000

Hi Alok,
Mine is mixed replication in 5.7.24. Can you share a script that gives me the statistics about which table has more operations going on it inserts , updates and deletes . Would be really thankful

Venkat

By: Jitendra

Jitendra — Fri, 04 Jan 2019 11:17:05 +0000

Hi , I have one doubt , I could see the Table_map in mysql binary log has some random behavior, 1st of all it is not matching with the table/-is defined in INFORMATION_SCHEMA.INNODB_SYS_TABLES .

Secondly when I restart my database the Table_map value in mysql binary log changes to a different value,

I could not correlate it. how and on what basis this table_map value is changing.

Any help on this is highly appreciable.

By: Mannoj

Mannoj — Mon, 08 Jan 2018 07:56:37 +0000

Guys this is useful, but anyone using this script for binlog year dated 2015 it will show timestamp, if not it won’t. Percona team request you to change this in line number 8 -> #15 . Remove this entry for it to work for any Year in binlog. Or change it as per carelessadmin.

Also people looking for this time level count.

1. line number 8 -> #15 (Remove entry #15)
2. sh summarize_binlogs.sh | grep -i Table | cut -d ‘:’ -f’2,3,4′ | sort | uniq -c

By: ani

ani — Wed, 31 May 2017 11:30:39 +0000

In reply to ani. I have also tried to create triggers on the replica but it is not triggered by binary log events I don't know why

By: ani

ani — Wed, 31 May 2017 09:01:28 +0000

Is there a way to find information regarding which column actually changed in the table! I want to find the columns that are frequently updated

By: Anurag Kale

Anurag Kale — Sat, 04 Mar 2017 11:51:48 +0000

I there any similar way of tracing the DDL changes made during a time range in MySQL?

By: carelessadmin

carelessadmin — Mon, 24 Oct 2016 14:34:35 +0000

There is an error in “{if(match($0, /#15.*Table_map:.*mapped to number/))”
better use {if(match($0, /^#.*Table_map:.*mapped to number/))

By: Alok Pathak

Alok Pathak — Mon, 27 Jun 2016 12:49:04 +0000

Hi Sanju,

When using row based binary logging, actual changes will be logged instead of query which made the change. If you’re using MySQL 5.6, you can enable “binlog_rows_query_log_events” variable to log the actual query (for informational purpose only) along with the row data. You can read more about this in the link : http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_rows_query_log_events.

Regards,
Alok

By: Sanju

Sanju — Fri, 06 May 2016 11:02:17 +0000

In reply to Sanju. I mean extract SQL statements of single table.

By: Sanju

Sanju — Fri, 06 May 2016 11:00:09 +0000

Alok,

Suppose, I have a binlog in ROW format. I want to extract SQL statements for particular query. How can I do that?

Thanks
Sanju.

By: Shantanu Oak

Shantanu Oak — Mon, 09 Feb 2015 04:52:24 +0000

If you are not using row based binary then something like this would work. mysqlbinlog ${1} | grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | cut -c1-100 | tr '[A-Z]' '[a-z]' | sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | sort | uniq -c | sort -nr

By: Aftab Khan

Aftab Khan — Thu, 22 Jan 2015 16:10:50 +0000

Nice post! I think the same information (workload) can be easily seen using performance schema:

mysql> select table_schema, table_name, rows_fetched,rows_inserted,rows_updated,
rows_deleted from x$schema_table_statistics where table_schema=”test”\G
*************************** 1. row ***************************
table_schema: test
table_name: tbl_partition
rows_fetched: 846933340
rows_inserted: 588251
rows_updated: 488251
rows_deleted: 100000
1 row in set (0.03 sec)