Sometimes binlog can save your life

Sometimes binlog can save your life

Everyone is familiar with mysql binlog!

binlog is a binary log that records all database table structure changes (such as CREATE, ALTER TABLE...) and table data modifications (INSERT, UPDATE, DELETE...).

Binlog does not record operations such as SELECT and SHOW, because these operations do not modify the data itself, but you can view all statements executed by MySQL by querying the general log.

Binary logs include two types of files: binary log index files (file name suffix .index) are used to record all binary files, and binary log files (file name suffix .00000*) record all DDL and DML of the database (except for data query Statement) statement event.

Today I will tell my friends about the two problems I encountered, but because of the existence of binlog, the bug was successfully located.

No1. Suddenly one day, a customer reported that a user had modified the points for no reason. When receiving bug feedback, it must be based on the logic of the code. After careful inspection, no logic problems with the code were found. Then we thought of binlog. The log locates the bug problem.

No2. A user's wallet has more money (you can drag the programmer responsible for this business out to sacrifice). After checking, there is still no code logic problem. binlog can help you again!

Next, I will explain to you how we locate bugs:

(1) First log in to mysql (default root)

mysql -uroot -p password

Execute after

show variables like'log_%'; confirm whether bin_log is turned on, if not (judging whether to turn on according to your own business)

Execute after

show master logs;

View all binlog logs

Then we selectively intercept the execution sql records we want according to the time interval, and finally execute:

/www/server/mysql/bin/mysqlbinlog -vv --start-datetime="2020-03-17 15:45:00" --stop-datetime="2020-03-17 19:00:00" - database=xx/www/server/data/mysql-bin.000005 >/root/xx.sql;

We write the binlog log into a xx.sql file so that we can view it easily

After that, we can view the corresponding SQL statement by opening the sql file, such as the first question, the point problem, then we can search for the update statement according to the user's id to confirm that the wrong points are added by executing those sql. This way we can quickly locate the problem!

Reference: Sometimes binlog can save your life-Cloud + Community-Tencent Cloud