Remember MySQL retrieve user data once

Things go through

One day, a sales C of our company's outer area said he could not find the workflow record for the previous August 3. Ask the reason, the original is updated number (our company's workflow is based on enterprise development). After analysis, number and process data does not matter, so the preliminary conclusion: only need to update the number, the results of our company's process system administrator to delete the user first, and then create a new user.

Resolution process

1, the first thought is directly from the scheduled backup data back to the original user ID, the results found that the system only backed up 10 days of records, and the workflow system shows that sales C only August 3 after the process record, more than 40 days from now, from the automatic backup data has been unable to recover.

2, so, only from the database of binary records analysis. Enter the MySQL data storage directory:

　　

3, through the analysis of file modification time, learned that the action of the deletion action in the mysql-bin.000014 file inside the record.

4. Because the log file is binary, the export log is a SQL file:

Mysqlbinlog--no-defaults mysql-bin.000014 > Workflow_operator.sql

5, the log is relatively large, after the export of 132M, compressed packaging files 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 actions of the deleted user:

Last location Delete sales C action in 127766 rows (although the number of log record lines is more, but delete the user's action is less, so good troubleshooting)

　　

7, the user ID found, fortunately, because only deleted the user, did not delete the process data (because the process data is to be archived), so long as the sales of the old process data user_id to the new user_id can be, the flow table is more, through manual labor, to find the table of old ID, And then update it with the UPDATE statement, and finally retrieve all the data:

(last four bit due to privacy, use XXX instead)

UpdateFlow_fr_borrowSet user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';UpdateFlow_fr_costSet user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';UpdateFlow_fr_fixedassetSet user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';UpdateFlow_fr_house_leaseSet user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';UpdateFlow_fr_purchaseSet user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';UpdateFlow_fr_travelSet user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';UpdateFlow_hr_positiveSet user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';UpdateFlow_pr_equip_borrow_saleSet user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';UpdateFlow_pr_equip_returnSet user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';UpdateFlow_sa_tepeSet user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';UpdateFlow_sa_saforeSet user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';UpdateFlow_sa_authorizeSet user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';UpdateFlow_sa_businessSet user_id = 'e76cb8bccaf74f32b94d17f74437xxxx' where user_id = '66adfd032ccf428d9e20e864f729xxxx';UpdateFlow_hr_trialSetsel_user_id= 'e76cb8bccaf74f32b94d17f74437xxxx' wheresel_user_id= '66adfd032ccf428d9e20e864f729xxxx' ;UpdateWf_hist_orderSetCreator= 'e76cb8bccaf74f32b94d17f74437xxxx' whereCreator= '66adfd032ccf428d9e20e864f729xxxx';UpdateWf_hist_taskSetoperator= 'e76cb8bccaf74f32b94d17f74437xxxx' whereoperator= '66adfd032ccf428d9e20e864f729xxxx';UpdateWf_orderSetCreator= 'e76cb8bccaf74f32b94d17f74437xxxx' whereCreator= '66adfd032ccf428d9e20e864f729xxxx';UpdateWf_hist_task_actorSetactor_id= 'e76cb8bccaf74f32b94d17f74437xxxx' whereactor_id= '66adfd032ccf428d9e20e864f729xxxx';

Sales C is very happy, and invited me to play in Guizhou to find him

Remember MySQL retrieve user data once