Record MySQL user data retrieval and mysql user Retrieval

Record MySQL user data retrieval and mysql user Retrieval

Incident Handling

One day, a sales clerk in our company's external region said he could not find his workflow record before January 1, August 3. The reason is that the number is updated (our company's workflow is developed based on the enterprise ). After analysis, the number has nothing to do with the process data. Therefore, the preliminary conclusion is: we only need to update the number. As a result, the process system administrator of our company deletes the user first, A new user is created.

 

Solution Process

1. The first thought was to retrieve the original user ID from the scheduled backup data. The result showed that the system only backed up the records for ten days, the workflow system shows that sales C only has a process record after January 1, August 3, more than 40 days ago, and cannot be recovered from the automatically backed up data.

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

　　

 

3, through the analysis file modification time, know the action of the delete operation records in the mysql-bin.000014 file.

4. Because the log file is binary, the exported log is an SQL file:

Mysqlbinlog -- no-defaults mysql-bin.000014> workflow_operator. SQL

5. The log record is large. 132 MB after export, compress the package file and download it to the local device. The log record is only 15.2 MB.

Tar-czvf workflow_operator.tar.gz workflow_operator. SQL

6. Use a local text tool to search for all user deletion operations:

Finally, the action to delete sales C is located in 127766 rows (although the number of log records is large, there are fewer actions to delete users, so it is good to troubleshoot)

　　

7. the user ID is found. Fortunately, because only the user is deleted, the process data is not deleted (because the process data is archived ), therefore, 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 physical labor, you can find a table with the old ID, and then use the update statement to update it together, finally, all the data is retrieved:

(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 is very happy, And if you invite me to Guizhou to visit him