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