Recovering Deleted MySQL User Data from Binary Logs

Background

Sales employee C could not find any workflow records before 2023‑08‑03. The workflow system built on Enterprise WeChat had deleted the original user account after the employee changed his WeChat ID. Deleting the user removed the entry from the user table but left the workflow rows unchanged.

Investigation

Only ten days of scheduled backups were available, insufficient to restore the missing 40‑day period. The team therefore inspected the MySQL data directory and identified the binary log file mysql-bin.000014 that recorded the DELETE operation.

Recovery Procedure

Export the binary log to a readable SQL file:

mysqlbinlog --no-defaults mysql-bin.000014 > workflow_operator.sql

Compress the 132 MB dump for transfer:

tar -czvf workflow_operator.tar.gz workflow_operator.sql

Search the exported file for the DELETE statement. The deletion of user C appears at line 127 766.

Record the old user_id value that was removed.

Identify every workflow‑related table that stores the user_id (or equivalent actor columns) and replace the old ID with the new one using UPDATE statements.

SQL Updates

Example statements (the actual IDs are truncated for privacy):

update flow_fr_borrow set user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';
update flow_fr_cost set user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';
update flow_fr_fixedasset set user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';
... (additional UPDATE statements for each affected workflow table) ...
update wf_hist_task_actor set actor_Id = 'e76cb8bccaf74f32b94d17f74437xxxx' where actor_Id = '66adfd032ccf428d9e20e864f729xxxx';

Result

After executing the updates, all historical workflow records that belonged to the original user became visible under the new account. The employee confirmed that the data was fully recovered.

Original article: http://www.cnblogs.com/tantec/p/mysql_get_back_user_data.html