Mysql Misoperation Quick Restore Data

Mysql Misoperation Quick Restore Data

background

In the daily work process, ECM has a large number of scenarios of manual direct operation of the database to modify data, which is difficult to achieve absolute certainty. When there is no backup and a large amount of data is updated or deleted by misoperation, it needs to be restored quickly to avoid affecting normal business use.

Recovery programme

1. Recovery from Data Backup

Recovery steps:

  • Create backup rds instances using backup data on Ali Cloud (typically once a day)
  • Get the tablexx data from the backup rds instance.
  • Temporary tables imported into the production repository are restored by table association.

Disadvantages:

  • Long recovery time: At present, it takes more than 4 hours to build a backup instance from Ali Cloud for 1T+. If it is in the peak period of business (e.g. monthly closure), it will seriously affect business.
  • Only the data before the backup time can be obtained. If the operation time is very long from the backup time point, the data generated in the process can only be restored by human flesh (painful experience: if there are multiple sources of data in the table, it needs to be restored by reading logs from different sources).
  • The newly built backup instance is charged on time.

2. Fast recovery of binlog 2sql

github path: binlog2sql

Recovery steps (take uat as an example)

1. Executing sql:

UPDATE `f_income_total` SET `relate_no` = '123456789' WHERE `receipt_no` = 'REC2017010800000002';

After executing sql, it is found that the value of related_no has been updated incorrectly and needs to be restored to the original value. (No backup, I don't know what the original value is)

2. Obtaining rollback SQL by binlog 2sql

python2.7 binlog2sql.py -h rm-bp1ls75a0z7f7pe96.mysql.rds.aliyuncs.com -P 3306 -u uat_ec_manage -p 'o30F83hUejz8bOe0' -d ins_632_uat -t f_income_total --start-file='mysql-bin.002478' --start-datetime='2019-08-06 13:50:00' --stop-datetime='2019-08-06 14:00:00' --sql-type UPDATE -B

Refer to the parsing on github for specific parameters

3. Resulting rollback sql

UPDATE `ins_632_uat`.`f_income_total` SET `downstream_system`='ERP', `erp_date`=NULL, `source_type`=0, `exchange_rate_date`=NULL, `relate_no`=NULL, `currency`='CNY', `shop_id`=715, `cims_confirm_time`=NULL, `gtms_code`=NULL, `customer_name`='JD.COM POP store-The Official Flagship Shop of Small Household Appliances in America-Retailers', `receiveables_trx_id`=0, `receipt_date`='2016-11-08 14:33:34', `supplier_code`=NULL, `ca_code`=NULL, `callback_name`=NULL, `biz_type`=1, `create_time`='2017-01-08 15:19:17', `receipt_no`='REC2017010800000002', `bank_account_other`='', `status`=4, `customer_code`='C0024081', `update_time`='2017-12-27 20:17:42', `verify_amount`=707.00, `supplier_addr_id`=NULL, `invoice_type`=NULL, `gl_date`='2016-12-31 08:00:00', `exchange_rate_type`=NULL, `supplier_name`=NULL, `error_msg`=NULL, `bank_account`='yangpy2@midea.com.cn', `remark`=NULL, `exchange_rate`=NULL, `cims_code`=NULL, `other_relate_no`=NULL, `org_id`=1401, `customer_addr_id`=NULL, `amount`=707.00, `calling_type`=1, `batch_no`=1483859957716, `ca_budget_category`=NULL, `category_id`=NULL, `receipt_method_id`=17025, `refund_type`=NULL WHERE `downstream_system`='ERP' AND `erp_date` IS NULL AND `source_type`=0 AND `exchange_rate_date` IS NULL AND `relate_no`='123456789' AND `currency`='CNY' AND `shop_id`=715 AND `cims_confirm_time` IS NULL AND `gtms_code` IS NULL AND `customer_name`='JD.COM POP store-The Official Flagship Shop of Small Household Appliances in America-Retailers' AND `receiveables_trx_id`=0 AND `receipt_date`='2016-11-08 14:33:34' AND `supplier_code` IS NULL AND `ca_code` IS NULL AND `callback_name` IS NULL AND `biz_type`=1 AND `create_time`='2017-01-08 15:19:17' AND `receipt_no`='REC2017010800000002' AND `bank_account_other`='' AND `status`=4 AND `customer_code`='C0024081' AND `update_time`='2019-08-06 13:56:26' AND `verify_amount`=707.00 AND `supplier_addr_id` IS NULL AND `invoice_type` IS NULL AND `gl_date`='2016-12-31 08:00:00' AND `exchange_rate_type` IS NULL AND `supplier_name` IS NULL AND `error_msg` IS NULL AND `bank_account`='yangpy2@midea.com.cn' AND `remark` IS NULL AND `exchange_rate` IS NULL AND `cims_code` IS NULL AND `other_relate_no` IS NULL AND `org_id`=1401 AND `customer_addr_id` IS NULL AND `amount`=707.00 AND `calling_type`=1 AND `batch_no`=1483859957716 AND `ca_budget_category` IS NULL AND `category_id` IS NULL AND `receipt_method_id`=17025 AND `refund_type` IS NULL LIMIT 1; #start 45616828 end 45617523 time 2019-08-06 13:56:26UPDATE `ins_632_uat`.`f_income_total` SET `downstream_system`='CIMS', `erp_date`=NULL, `source_type`=4, `exchange_rate_date`=NULL, `relate_no`='62978904688480111_0', `currency`='CNY', `shop_id`=NULL, `cims_confirm_time`=NULL, `gtms_code`=NULL, `customer_name`='Tmall', `receiveables_trx_id`=NULL, `receipt_date`='2019-08-03 08:00:00', `supplier_code`=NULL, `ca_code`=NULL, `callback_name`=NULL, `biz_type`=1, `create_time`='2019-08-06 13:54:55', `receipt_no`='REC2019080600000002', `bank_account_other`=NULL, `status`=0, `customer_code`='C0010850', `update_time`='2019-08-06 13:54:55', `verify_amount`=NULL, `supplier_addr_id`=NULL, `invoice_type`=NULL, `gl_date`='2019-07-31 08:00:00', `exchange_rate_type`=NULL, `supplier_name`=NULL, `error_msg`=NULL, `bank_account`='mideabxzyd@midea.com', `remark`='test Refrigerator Division', `exchange_rate`=NULL, `cims_code`=NULL, `other_relate_no`=NULL, `org_id`=701, `customer_addr_id`=NULL, `amount`=1000.01, `calling_type`=1, `batch_no`=NULL, `ca_budget_category`=NULL, `category_id`=NULL, `receipt_method_id`=17025, `refund_type`='Telegraphic transfer (e-commerce)' WHERE `downstream_system`='CIMS' AND `erp_date` IS NULL AND `source_type`=4 AND `exchange_rate_date` IS NULL AND `relate_no`='62978904688480111_0' AND `currency`='CNY' AND `shop_id` IS NULL AND `cims_confirm_time` IS NULL AND `gtms_code` IS NULL AND `customer_name`='Tmall' AND `receiveables_trx_id` IS NULL AND `receipt_date`='2019-08-03 08:00:00' AND `supplier_code` IS NULL AND `ca_code` IS NULL AND `callback_name` IS NULL AND `biz_type`=1 AND `create_time`='2019-08-06 13:54:55' AND `receipt_no`='REC2019080600000002' AND `bank_account_other` IS NULL AND `status`=1 AND `customer_code`='C0010850' AND `update_time`='2019-08-06 13:56:09' AND `verify_amount` IS NULL AND `supplier_addr_id` IS NULL AND `invoice_type` IS NULL AND `gl_date`='2019-07-31 08:00:00' AND `exchange_rate_type` IS NULL AND `supplier_name` IS NULL AND `error_msg`='' AND `bank_account`='mideabxzyd@midea.com' AND `remark`='test Refrigerator Division' AND `exchange_rate` IS NULL AND `cims_code` IS NULL AND `other_relate_no` IS NULL AND `org_id`=701 AND `customer_addr_id` IS NULL AND `amount`=1000.01 AND `calling_type`=1 AND `batch_no` IS NULL AND `ca_budget_category` IS NULL AND `category_id` IS NULL AND `receipt_method_id`=17025 AND `refund_type`='Telegraphic transfer (e-commerce)' LIMIT 1; #start 45603867 end 45604536 time 2019-08-06 13:56:09

4. Manual confirmation of sql that needs to be rolled back

In the example above, only one REC is update d, but two rollback statements are obtained. Through analysis, in fact, the business is also in progress, updating a certain REC, at this time, the second sql should not be rolled back, need to be identified by human flesh.

5. Execute rollback sql in production environment

UPDATE `ins_632_uat`.`f_income_total` SET `downstream_system`='ERP', `erp_date`=NULL, `source_type`=0, `exchange_rate_date`=NULL, `relate_no`=NULL, `currency`='CNY', `shop_id`=715, `cims_confirm_time`=NULL, `gtms_code`=NULL, `customer_name`='JD.COM POP store-The Official Flagship Shop of Small Household Appliances in America-Retailers', `receiveables_trx_id`=0, `receipt_date`='2016-11-08 14:33:34', `supplier_code`=NULL, `ca_code`=NULL, `callback_name`=NULL, `biz_type`=1, `create_time`='2017-01-08 15:19:17', `receipt_no`='REC2017010800000002', `bank_account_other`='', `status`=4, `customer_code`='C0024081', `update_time`='2017-12-27 20:17:42', `verify_amount`=707.00, `supplier_addr_id`=NULL, `invoice_type`=NULL, `gl_date`='2016-12-31 08:00:00', `exchange_rate_type`=NULL, `supplier_name`=NULL, `error_msg`=NULL, `bank_account`='yangpy2@midea.com.cn', `remark`=NULL, `exchange_rate`=NULL, `cims_code`=NULL, `other_relate_no`=NULL, `org_id`=1401, `customer_addr_id`=NULL, `amount`=707.00, `calling_type`=1, `batch_no`=1483859957716, `ca_budget_category`=NULL, `category_id`=NULL, `receipt_method_id`=17025, `refund_type`=NULL WHERE `downstream_system`='ERP' AND `erp_date` IS NULL AND `source_type`=0 AND `exchange_rate_date` IS NULL AND `relate_no`='123456789' AND `currency`='CNY' AND `shop_id`=715 AND `cims_confirm_time` IS NULL AND `gtms_code` IS NULL AND `customer_name`='JD.COM POP store-The Official Flagship Shop of Small Household Appliances in America-Retailers' AND `receiveables_trx_id`=0 AND `receipt_date`='2016-11-08 14:33:34' AND `supplier_code` IS NULL AND `ca_code` IS NULL AND `callback_name` IS NULL AND `biz_type`=1 AND `create_time`='2017-01-08 15:19:17' AND `receipt_no`='REC2017010800000002' AND `bank_account_other`='' AND `status`=4 AND `customer_code`='C0024081' AND `update_time`='2019-08-06 13:56:26' AND `verify_amount`=707.00 AND `supplier_addr_id` IS NULL AND `invoice_type` IS NULL AND `gl_date`='2016-12-31 08:00:00' AND `exchange_rate_type` IS NULL AND `supplier_name` IS NULL AND `error_msg` IS NULL AND `bank_account`='yangpy2@midea.com.cn' AND `remark` IS NULL AND `exchange_rate` IS NULL AND `cims_code` IS NULL AND `other_relate_no` IS NULL AND `org_id`=1401 AND `customer_addr_id` IS NULL AND `amount`=707.00 AND `calling_type`=1 AND `batch_no`=1483859957716 AND `ca_budget_category` IS NULL AND `category_id` IS NULL AND `receipt_method_id`=17025 AND `refund_type` IS NULL LIMIT 1; #start 45616828 end 45617523 time 2019-08-06 13:56:26

Points to be noted are as follows:

  • The production/uat database has no external network privileges and needs to be logged on to the db server to execute binlog2sql. (Currently under uat-manage ment/mnt/binlog2sql/binlog2sql-master/binlog2sql has been installed and configured and can be used directly)
  • Sometimes the log backups displayed on Aliyun rds are not up-to-date. Sometimes it takes a while before they are displayed under the log backups (mysql-bin.002478 was not shown when I operated).

​

 

Keywords: SQL MySQL Database github

Added by Qaid on Wed, 07 Aug 2019 04:55:11 +0300

Popular Keywords

©2024 Programming VIP