Root Cause Analysis of Missing Data Using MySQL Binlog Parsing

The client reported that after a nightly batch job, a row inserted into demo.t_dba_info (INSERT INTO t_dba_info(name,age) VALUES('zhenxing',20)) could not be found in the database, even though the batch logs showed a successful insert.

Step 1 – Verify the row in the database : Directly query the table on the primary server and confirm that the row is indeed missing.

Step 2 – Determine the time window and parse binlog : In a test environment, the primary server’s binlog files are inspected. The following shell snippet lists the relevant binlog files and searches for the keyword “zhenxing”.

BINLOG_LIST='mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.000005  mysql-bin.000006  mysql-bin.000007'
for binlog in ${BINLOG_LIST}
do
    echo "====== ${binlog}"
    mysqlbinlog -vv ${binlog} | grep -iEw "zhenxing"
done

The search shows that the INSERT statement was recorded, so the next step is to look for any subsequent DELETE or UPDATE operations on that row.

Step 3 – Filter binlog for DELETE/UPDATE on the table :

BINLOG_LIST='mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.000005  mysql-bin.000006  mysql-bin.000007'
for binlog in ${BINLOG_LIST}
do
    echo "====== ${binlog}"
    mysqlbinlog --no-defaults --base64-output=decode-rows -vv ${binlog} |
    awk '/###/ {if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++} END{for(i in count) print i,"\t",count[i]}' |
    column -t | sort -k2nr | grep -i t_dba_info
 done

The output reveals that only mysql-bin.000006 contains two UPDATE statements on the table; all other binlogs contain only INSERTs. Therefore, the investigation focuses on mysql-bin.000006.

Step 4 – Examine the identified binlog : # mysqlbinlog -vv mysql-bin.000006 | less Scrolling through the file shows UPDATE statements, but none target the “zhenxing” row, leaving the cause still unclear.

Step 5 – Exclude special operations :

Check whether the row was deleted with SET SESSION sql_log_bin=off. Querying the replica confirms the row is absent there as well, ruling out this possibility.

Investigate possible DDL actions (TRUNCATE, CREATE, DROP) that could have removed the data.

BINLOG_LIST='mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.000005  mysql-bin.000006  mysql-bin.000007'
for binlog in ${BINLOG_LIST}
do
    echo "====== ${binlog}"
    mysqlbinlog ${binlog} | egrep -iEw "truncate|create|drop"
done

The scan discovers two TRUNCATE statements on t_dba_info in mysql-bin.000004, surrounding the INSERT of the missing row:

#211211 14:52:47 truncate table t_dba_info
#211211 14:53:00 insert into t_dba_info(name,age) values('zhenxing',20)
#211211 14:53:18 truncate table t_dba_info

Thus, the row was removed by a subsequent TRUNCATE operation caused by a batch‑process mistake.

Step 6 – Fault summary : The case demonstrates how extensive binlog information can be parsed to pinpoint data‑loss root causes, and highlights the importance of proper binlog configuration (e.g., max_binlog_size=250M, binlog_rows_query_log_events=1) for detailed auditing.

Additional notes: without audit logging, binlog does not capture the IP or user that performed the TRUNCATE, limiting forensic detail.

Binlog parsing tips :

Prefer parsing on a replica to avoid impacting the primary.

First narrow down relevant binlog files, then analyze the specific file in detail.

When parsing DDL, omit the -v flag to speed up processing.

If binlog_rows_query_log_events is enabled, use -vv to see the exact SQL statements.