Detailed explanation of examples of MySQL retrieving user data

What happened

One day, a sales C from an outside area of ​​our company said that he could not find the workflow records before August 3rd. When asked about the reason, it turned out that WeChat had been updated (our company's workflow is developed based on Enterprise WeChat). After analysis, we found that WeChat ID has nothing to do with process data, so we came to the preliminary conclusion: originally we only needed to update WeChat ID, but as a result, our company's process system administrator deleted the user first and then created a new user.

Solution process

1. The first thing that comes to mind is to retrieve the original user ID directly from the scheduled backup data. It turns out that the system only backed up records for ten days. The workflow system shows that Sales C only has process records after August 3rd. It has been more than 40 days since then, and it cannot be restored from the automatic backup data.

2. Therefore, it can only be analyzed from the binary records of the database. Enter the directory where MySQL data is stored:

　

3. By analyzing the file modification time, we learned that the deletion operation is in the mysql-bin.000014 file. Record.

4. Because the log file is binary, the log is exported as a sql file:

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

5. The log record is relatively large, 132M after exporting. If the file is compressed and downloaded to the local, only 15.2M

tar -czvf workflow_operator.tar.gz workflow_operator.sql

6. Use the text tool locally to find all the operations of deleting users:

Finally, the action of deleting Sales C is located at line 127766 (although the number of log records is relatively large, However, there are relatively few actions to delete users, so it is easy to troubleshoot)

　

7. The user ID was found. Fortunately, because only the user was deleted, the process data was not deleted (because Process data needs to be archived), so you only need to replace the old process data user_id of Sales C with the new user_id. There are many process tables. Through manual labor, you need to find the table with the old ID, and then use the update statement together. Update, finally retrieved all the data:

(The last four digits are replaced by XXX due to privacy concerns)

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';
update flow_fr_house_lease set user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';
update flow_fr_purchase set user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';
update flow_fr_travel set user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';
update flow_hr_positive set user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';
update flow_pr_equip_borrow_sale set user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';
update flow_pr_equip_return set user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';
update flow_sa_tepe set user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';
update flow_sa_safore set user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';
update flow_sa_authorize set user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';
update flow_sa_business set user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';
update flow_hr_trial set sel_user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where sel_user_id = '66adfd032ccf428d9e20e864f729xxxx' ;
update wf_hist_order set creator = 'e76cb8bccaf74f32b94d17f74437xxxx' where creator = '66adfd032ccf428d9e20e864f729xxxx';
update wf_hist_task set operator = 'e76cb8bccaf74f32b94d17f74437xxxx' where operator = '66adfd032ccf428d9e20e864f729xxxx';
update wf_order set creator = 'e76cb8bccaf74f32b94d17f74437xxxx' where creator = '66adfd032ccf428d9e20e864f729xxxx';
update wf_hist_task_actor set actor_Id = 'e76cb8bccaf74f32b94d17f74437xxxx' where actor_Id = '66adfd032ccf428d9e20e864f729xxxx';

Sales C expressed that he was very happy and invited me to go If you want to play in Guizhou, go to him

The above is the detailed content of Detailed explanation of examples of MySQL retrieving user data. For more information, please follow other related articles on the PHP Chinese website!