mysql数据库误操作怎么回退 mysql 误操作

Mysql误操作快速还原数据

背景

在日常的工作过程中，ECM存在大量的人工直接操作数据库来修改数据的场景，很难做到万无一失。当没有备份，并且误操作更新/删除了大量的数据的时候，需要快速恢复，以免影响正常的业务使用。

恢复方案

1、从数据备份中恢复

恢复步骤：

• 使用阿里云上的备份数据创建备份的rds实例（一般来讲是一天一次）
• 从备份的rds实例中获取tablexx的数据。
• 导入到生产库的临时表，并通过表关联恢复。

缺点：

• 恢复时间长：目前生产库1T+，从阿里云上构建备份实例需要4个小时以上，如果是在业务高峰期（例如：月结），会严重影响业务。
• 只能获取到备份时间前的数据，如果操作的时间距离备份时间点间隔很长，中间产生的数据只能通过人肉恢复（惨痛经历：如果该表的数据有多个来源，需要去根据不同来源阅读日志来恢复）
• 新构建的备份实例是按时收费的。

2、binlog2sql快速恢复

github路径：binlog2sql

恢复步骤（以uat为例）

1、执行的sql:

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

执行完sql后，发现更新错了relate_no这个值，需要恢复到原来的值。（没有备份，不知道原来什么值）

2、利用binlog2sql获取回滚sql

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

其中具体的参数请参照github上的解析

3、得到的回滚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`='京东POP店-美的小家电官方旗舰店-零售户', `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`='[e-mail]', `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`='京东POP店-美的小家电官方旗舰店-零售户' 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`='[e-mail]' 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`='淘宝商城', `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`='[e-mail]', `remark`='test冰箱事业部', `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`='电汇（电商）' 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`='淘宝商城' 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`='[e-mail]' AND `remark`='test冰箱事业部' 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`='电汇（电商）' LIMIT 1; #start 45603867 end 45604536 time 2019-08-06 13:56:09

4、人工确认需要回滚的sql

以上面的例子为例，只update了一个REC，但是得到了两条回滚语句。通过分析，其实是业务也在进行，更新了某个REC，这时候，第二条sql是不应该回滚的，需要人肉识别出来。

5、在生产环境执行回滚的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`='京东POP店-美的小家电官方旗舰店-零售户', `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`='[e-mail]', `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`='京东POP店-美的小家电官方旗舰店-零售户' 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`='[e-mail]' 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

需要注意的点：

• 生产/uat的数据库没有外网权限，需要登录可以连到db的服务器上去执行binlog2sql.(目前uat-manage下/mnt/binlog2sql/binlog2sql-master/binlog2sql，已经安装配置好，可以直接使用)
• 阿里云rds上有时候显示的日志备份不是最新的，有时候需要等一会才会显示到日志备份下（我操作的时候还没显示出来mysql-bin.002478）