关闭

mysql delete错误使用where条件的操作恢复(模拟oracle闪回)

605人阅读 评论(0) 收藏 举报
分类:


先准备数据:

CREATE TABLE `qq1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `class_id` int NOT NULL DEFAULT 0,
  `first_name` varchar(20) NOT NULL DEFAULT '',
  `last_name` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(product)root@localhost [test]> select * from qq1;
+----+----------+------------+-----------+
| id | class_id | first_name | last_name |
+----+----------+------------+-----------+
|  1 |      101 | lo         | 1gang     |
|  2 |      101 | zhou       | 2gang     |
|  3 |      102 | wone       | 3gang     |
|  4 |      101 | huo        | 4gang     |
|  5 |      102 | son        | 5gang     |
|  6 |      102 | dong       | 6gang     |
|  7 |      103 | qo         | 7gang     |
|  8 |      103 | dao        | 8gang     |
+----+----------+------------+-----------+
8 rows in set (0.00 sec)

现在要删除id大于5的记录,但不小心,将条件写成大于4。如下:
(product)root@localhost [test]> delete from qq1 where id>4;
Query OK, 4 rows affected (0.00 sec)

(product)root@localhost [test]> select * from qq1;
+----+----------+------------+-----------+
| id | class_id | first_name | last_name |
+----+----------+------------+-----------+
|  1 |      101 | lo         | 1gang     |
|  2 |      101 | zhou       | 2gang     |
|  3 |      102 | wone       | 3gang     |
|  4 |      101 | huo        | 4gang     |


通过binlog先找到那条语句:
-bash-4.1$ mysqlbinlog --no-defaults -v -v --base64-output=decode-rows mysql-bin.000143|grep -A 15 '### DELETE FROM `test`.`qq1`'|more
### DELETE FROM `test`.`qq1`
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2=102 /* INT meta=0 nullable=0 is_null=0 */
###   @3='son' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @4='5gang' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### DELETE FROM `test`.`qq1`
### WHERE
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2=102 /* INT meta=0 nullable=0 is_null=0 */
###   @3='dong' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @4='6gang' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### DELETE FROM `test`.`qq1`
### WHERE
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2=103 /* INT meta=0 nullable=0 is_null=0 */
###   @3='qo' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @4='7gang' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### DELETE FROM `test`.`qq1`
### WHERE
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2=103 /* INT meta=0 nullable=0 is_null=0 */
###   @3='dao' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @4='8gang' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
# at 15124169
#160828  8:43:47 server id 65303306  end_log_pos 15124200 CRC32 0xf8c4dd77      Xid = 26350786
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

现在要分析binlog,把误操作的delete语句保存到文本中:

mysqlbinlog --no-defaults -v -v --base64-output=decode-rows mysql-bin.000143|sed -n '/DELETE FROM `test`.`qq1`/,/COMMIT/p' >/data/mysql/mysql3306/logs/2.sql
cat 2.sql
### DELETE FROM `test`.`qq1`
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2=102 /* INT meta=0 nullable=0 is_null=0 */
###   @3='son' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @4='5gang' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### DELETE FROM `test`.`qq1`
### WHERE
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2=102 /* INT meta=0 nullable=0 is_null=0 */
###   @3='dong' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @4='6gang' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### DELETE FROM `test`.`qq1`
### WHERE
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2=103 /* INT meta=0 nullable=0 is_null=0 */
###   @3='qo' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @4='7gang' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### DELETE FROM `test`.`qq1`
### WHERE
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2=103 /* INT meta=0 nullable=0 is_null=0 */
###   @3='dao' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @4='8gang' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
# at 15124169
#160828  8:43:47 server id 65303306  end_log_pos 15124200 CRC32 0xf8c4dd77      Xid = 26350786
COMMIT/*!*/;
接下来转换成标准的sql语句:
cat 2.sql|sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.*),/\1;/g' | sed 's/@[1-9]=//g'>delete.sql

上面命令分段说明:
sed -n '/###/p'  ---打印###开头的行;
sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;'  ---先删除每行前的"### ",再将每行后面/*到*\部分删除,将DELETE FROM用INSERT INTO替换,将WHERE
用select 替换;
sed -r 's/(@4.*),/\1;/g'  ---将@4那行后面的,号用;分替换
sed 's/@[1-9]=//g' ---将每行类似@1=删除

-bash-4.1$ cat delete.sql
INSERT INTO `test`.`qq1`
SELECT
  5 ,
  102 ,
  'son' ,
  '5gang' ;
INSERT INTO `test`.`qq1`
SELECT
  6 ,
  102 ,
  'dong' ,
  '6gang' ;
INSERT INTO `test`.`qq1`
SELECT
  7 ,
  103 ,
  'qo' ,
  '7gang' ;
INSERT INTO `test`.`qq1`
SELECT
  8 ,
  103 ,
  'dao' ,
  '8gang' ;

(product)root@localhost [test]> select * from qq1;
+----+----------+------------+-----------+
| id | class_id | first_name | last_name |
+----+----------+------------+-----------+
|  1 |      101 | lo         | 1gang     |
|  2 |      101 | zhou       | 2gang     |
|  3 |      102 | wone       | 3gang     |
|  4 |      101 | huo        | 4gang     |

(product)root@localhost [test]> source delete.sql
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

(product)root@localhost [test]> select * from qq1;
+----+----------+------------+-----------+
| id | class_id | first_name | last_name |
+----+----------+------------+-----------+
|  1 |      101 | lo         | 1gang     |
|  2 |      101 | zhou       | 2gang     |
|  3 |      102 | wone       | 3gang     |
|  4 |      101 | huo        | 4gang     |
|  5 |      102 | son        | 5gang     |
|  6 |      102 | dong       | 6gang     |
|  7 |      103 | qo         | 7gang     |
|  8 |      103 | dao        | 8gang     |
+----+----------+------------+-----------+
8 rows in set (0.00 sec)

被删除的数据已回来了。

0
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

ORACLE数据库误操作执行了DELETE,该如何恢复数据?

作为一个程序员,数据库操作是必须的,但是如果操作失误,一般都会造成比较严重的后果。 今天一不小心,干了一件揪心的事情,将正在使用的组织机构误操作执行了delete,顿时大脑有点缺氧,感觉蒙圈了(相信...
  • qq_35052774
  • qq_35052774
  • 2016-08-11 17:23
  • 7123

MySQL误操作后如何快速恢复数据

基本上每个跟数据库打交道的程序员(当然也可能是你同事)都会碰一个问题,MySQL误操作后如何快速回滚?比如,delete一张表,忘加限制条件,整张表都没了。假如这还是线上环境核心业务数据,那这事就闹大...
  • GarfieldEr007
  • GarfieldEr007
  • 2016-12-16 23:01
  • 3381

mysql 误操作通过日志数据恢复

今天一不小心把公司的服务器上的数据库给删了,至于怎么会把服务器上的数据库给删了就不说了,说多了都是泪啊。删了以后我赶紧找备份(PS:还好之前有备份这下不怕啦,哈哈!)。赶紧用备份的sql恢复下,回复后...
  • yhjsspz
  • yhjsspz
  • 2013-12-16 22:44
  • 7973

mysql update忘加where条件的操作恢复(模拟oracle闪回)

先准备数据: CREATE TABLE `student` (   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,   `name` varchar...
  • zengxuewen2045
  • zengxuewen2045
  • 2016-08-29 06:46
  • 926

mysql恢复之delete 忘加where条件误删除恢复(binglog格式必须是ROW)

(一)恢复的前提条件就是开启了二进制日志和格式为行格式,二个条件缺一不行! mysql> show variables like "%log_bin%"; +---------------------...
  • anzhen0429
  • anzhen0429
  • 2017-07-23 12:34
  • 149

Oracle9i使用闪回操作

  • 2008-05-01 20:13
  • 4KB
  • 下载

Oracle数据库10g中使用闪回数据库操作

Oracle数据库10g中使用闪回数据库操作 使用FLASHBACK DATABASE可以恢复数据库到先前的某个时间点或者某个SCN号。 在Oracle10g以前的版本中,要实现数据库恢复到过去某...
  • seagal890
  • seagal890
  • 2012-01-31 21:04
  • 483

关于如何避免操作(update、delete)mysql数据库时忘记添加where而导致全表修改或删除情况

在实际开发中,有时在对数据库进行更新或删除时,因忘记添加了where条件而造成了对整表的更新或删除。OMG,从网上找了许多的如何恢复误操作的方法,都是通过查看日志转换成sql语句再进行恢复。(好麻烦啊...
  • weialemon
  • weialemon
  • 2017-11-15 13:54
  • 66

关于如何避免操作(update、delete)mysql数据库时忘记添加where而导致全表修改或删除情况

在实际开发中,有时在对数据库进行更新或删除时,因忘记添加了where条件而造成了对整表的更新或删除。OMG,从网上找了许多的如何恢复误操作的方法,都是通过查看日志转换成sql语句再进行恢复。(好麻烦啊...
  • liboyang71
  • liboyang71
  • 2017-06-13 17:32
  • 694

MySQL恢复之update忘加where条件误操作后数据恢复(提前条件binlog为row行格式)

在数据库日常维护中,开发人员是最让人头痛的,很多时候都会由于SQL语句写的有问题导致服务器出问题,导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件,导致全表更新,这是作为运维或者D...
  • anzhen0429
  • anzhen0429
  • 2017-07-23 16:37
  • 466
    个人资料
    • 访问:206112次
    • 积分:4004
    • 等级:
    • 排名:第9057名
    • 原创:198篇
    • 转载:2篇
    • 译文:1篇
    • 评论:27条
    最新评论