MySQL : Show How Many INSERT, UPDATE, DELETE In Binary Log File

Each data modification such as INSERT, UPDATE, DELETE and data definition like ALTER, ADD, DROP statement that you perform in your server are record in log files. So each time when you make any these statements, you actually update both your data files and your log file.

If you have a MySQL database setup with master and slave. You know the master writes transaction even to binary log, which is read and executed by the slave, thus making the slave a continuous copy of the master.

So we can say the binary log contains a record of all changes to the databases, both data and structure. It consists of a set of binary log files and index.

In this article I will show you how you can find how many UPDATE, INSERT, and DELETE in binary log file.

Find How Many UPDATE, INSERT And DELETE In Binary Log File

Type the below one line command to get all transaction details:

# mysqlbinlog mysql-bin.005757 | 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 | head -50

You will get some outputs like below”

 126254 insert into user_name_detail
  89384 insert into user_sessions
  86891 delete from user_sessions
  76377 update user_members
  55230 update user_sessions
  28251 delete from user_validating
    185 insert into user_reputation_cache
     89 insert into user_profile_portal
     88 insert into user_pfields_content
     88 insert into user_members
     88 delete from user_pfields_content
     78 insert into user_reputation_totals
     74 update user_task_manager
     60 insert into user_content_cache_posts
     23 insert into user_task_logs
     19 insert into user_topic_views
     14 insert into iuser_core_like_cache
	 ..............................
	 .......................
	 .....................
	 .............

Thanks:)