update、delete沒有帶where條件，誤操作，如何恢復呢？
我現在有一張學生表，我要把小於60更新成不及格。
1 mysql> select * from student; 2 3 +----+------+-------+-------+ 4 5 | id | name | class | score | 6 7 +----+------+-------+-------+ 8 9 | 1 | a | 1 | 56 | 10 11 | 2 | b | 1 | 61 | 12 13 | 3 | c | 2 | 78 | 14 15 | 4 | d | 2 | 45 | 16 17 | 5 | e | 3 | 76 | 18 19 | 6 | f | 3 | 89 | 20 21 | 7 | g | 4 | 43 | 22 23 | 8 | h | 4 | 90 | 24 25 +----+------+-------+-------+ 26 27 8 rows in set (0.02 sec)
結果，忘帶where條件了，
1 mysql> update student set score='failure'; 2 3 Query OK, 8 rows affected (0.11 sec) 4 5 Rows matched: 8 Changed: 8 Warnings: 0 6 7 8 9 mysql> select * from student; 10 11 +----+------+-------+---------+ 12 13 | id | name | class | score | 14 15 +----+------+-------+---------+ 16 17 | 1 | a | 1 | failure | 18 19 | 2 | b | 1 | failure | 20 21 | 3 | c | 2 | failure | 22 23 | 4 | d | 2 | failure | 24 25 | 5 | e | 3 | failure | 26 27 | 6 | f | 3 | failure | 28 29 | 7 | g | 4 | failure | 30 31 | 8 | h | 4 | failure | 32 33 +----+------+-------+---------+ 34 35 8 rows in set (0.01 sec)
把整張表的記錄都給更新成不及格了。
傳統的方法是：利用最近的全量備份+增量binlog備份，恢復到誤操作之前的狀態，那麼隨著表的記錄增大，binlog的增多，恢復起來很費時費力。
現在通過一個簡單的方法，可以恢復到誤操作之前的狀態。
我的binlog日志設置為binlog_format = ROW，
首先，創建一個普通權限的賬號（切記不能是SUPER權限），例如：
1 GRANT ALL PRIVILEGES ON yourDB.* TO 'admin_read_only'@'%' IDENTIFIED BY '123456'; 2 3 flush privileges;
把read_only打開，設置數據庫只讀，
1 mysql> set global read_only = 1; 2 3 Query OK, 0 rows affected (0.01 sec)
把剛才創建的admin_read_only賬號給運維，讓運維把前端程序（PHP/JSP/.NET等）的用戶名改下，然後重啟前端程序（PHP/JSP/.NET等），這樣再連接進來的用戶對數據庫的訪問只能讀不能寫，保證恢復的一致性。
通過binlog先找到那條語句
1 [root@M1 data]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS 2 3 mysql-bin.000001 | grep -B 15 'failure'| more 4 5 /*!*/; 6 7 # at 192 8 9 #121124 23:55:15 server id 25 end_log_pos 249 CRC32 0x83a12fbc Table_map: `test`.`student` mapped to number 76 10 11 # at 249 12 13 #121124 23:55:15 server id 25 end_log_pos 549 CRC32 0xcf7d2635 Update_rows: table id 76 flags: STMT_END_F 14 15 ### UPDATE test.student 16 17 ### WHERE 18 19 ### @11=1 /* INT meta=0 nullable=0 is_null=0 */ 20 21 ### @2='a' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 22 23 ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ 24 25 ### @4='56' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 26 27 ### SET 28 29 ### @11=1 /* INT meta=0 nullable=0 is_null=0 */ 30 31 ### @2='a' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 32 33 ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ 34 35 ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 36 37 ### UPDATE test.student 38 39 ### WHERE 40 41 ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ 42 43 ### @2='b' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 44 45 ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ 46 47 ### @4='61' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 48 49 ### SET 50 51 ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ 52 53 ### @2='b' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 54 55 ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ 56 57 ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 58 59 --More--
然後把那條binlog給導出來
1 [root@M1 data]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS 2 3 mysql-bin.000001 | sed -n '/# at 249/,/COMMIT/p' > /opt/1.txt 4 5 [root@M1 data]# 6 7 [root@M1 data]# more /opt/1.txt 8 9 # at 249 10 11 #121124 23:55:15 server id 25 end_log_pos 549 CRC32 0xcf7d2635 Update_rows: table id 76 flags: STMT_END_F 12 13 ### UPDATE test.student 14 15 ### WHERE 16 17 ### @11=1 /* INT meta=0 nullable=0 is_null=0 */ 18 19 ### @2='a' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 20 21 ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ 22 23 ### @4='56' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 24 25 ### SET 26 27 ### @11=1 /* INT meta=0 nullable=0 is_null=0 */ 28 29 ### @2='a' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 30 31 ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ 32 33 ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 34 35 ### UPDATE test.student 36 37 ### WHERE 38 39 ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ 40 41 ### @2='b' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 42 43 ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ 44 45 ### @4='61' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 46 47 ### SET 48 49 ### @1=2 /* INT meta=0 nullable=0 is_null=0 */ 50 51 ### @2='b' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 52 53 ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ 54 55 ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 56 57 ### UPDATE test.student 58 59 ### WHERE 60 61 ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ 62 63 ### @2='c' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 64 65 ### @3=2 /* INT meta=0 nullable=1 is_null=0 */ 66 67 ### @4='78' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 68 69 ### SET 70 71 ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ 72 73 ### @2='c' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 74 75 ### @3=2 /* INT meta=0 nullable=1 is_null=0 */ 76 77 ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 78 79 ### UPDATE test.student 80 81 ### WHERE 82 83 ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ 84 85 ### @2='d' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 86 87 ### @3=2 /* INT meta=0 nullable=1 is_null=0 */ 88 89 ### @4='45' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 90 91 ### SET 92 93 ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ 94 95 ### @2='d' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 96 97 ### @3=2 /* INT meta=0 nullable=1 is_null=0 */ 98 99 ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 100 101 ### UPDATE test.student 102 103 ### WHERE 104 105 ### @1=5 /* INT meta=0 nullable=0 is_null=0 */ 106 107 ### @2='e' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 108 109 ### @33=3 /* INT meta=0 nullable=1 is_null=0 */ 110 111 ### @4='76' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 112 113 ### SET 114 115 ### @1=5 /* INT meta=0 nullable=0 is_null=0 */ 116 117 ### @2='e' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 118 119 ### @33=3 /* INT meta=0 nullable=1 is_null=0 */ 120 121 ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 122 123 ### UPDATE test.student 124 125 ### WHERE 126 127 ### @1=6 /* INT meta=0 nullable=0 is_null=0 */ 128 129 ### @2='f' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 130 131 ### @33=3 /* INT meta=0 nullable=1 is_null=0 */ 132 133 ### @4='89' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 134 135 ### SET 136 137 ### @1=6 /* INT meta=0 nullable=0 is_null=0 */ 138 139 ### @2='f' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 140 141 ### @33=3 /* INT meta=0 nullable=1 is_null=0 */ 142 143 ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 144 145 ### UPDATE test.student 146 147 ### WHERE 148 149 ### @1=7 /* INT meta=0 nullable=0 is_null=0 */ 150 151 ### @2='g' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 152 153 ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ 154 155 ### @4='43' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 156 157 ### SET 158 159 ### @1=7 /* INT meta=0 nullable=0 is_null=0 */ 160 161 ### @2='g' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 162 163 ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ 164 165 ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 166 167 ### UPDATE test.student 168 169 ### WHERE 170 171 ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ 172 173 ### @2='h' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 174 175 ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ 176 177 ### @4='90' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 178 179 ### SET 180 181 ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ 182 183 ### @2='h' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 184 185 ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ 186 187 ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */ 188 189 # at 549 190 191 #121124 23:55:15 server id 25 end_log_pos 580 CRC32 0x378c91b0 Xid = 531 192 193 COMMIT/*!*/; 194 195 [root@M1 data]#
其中，這些是誤操作之前的數據
1 ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ 2 3 ### @2='h' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 4 5 ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ 6 7 ### @4='90' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
這些是誤操作之後的數據
1 ### @1=8 /* INT meta=0 nullable=0 is_null=0 */ 2 3 ### @2='h' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */ 4 5 ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ 6 7 ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
這裡，@1/@2/@3/@4對應的表字段是id,name,class,score
現在，就要進行最後一步的恢復操作了，只需把這些binlog轉成成SQL語句，然後將其導入進去。
1 [root@M1 opt]# sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 1.txt
2
3 | sed -r '/WHERE/{:a;N;/@4/!ba;s/### @2.*//g}'
4
5 | sed 's/### //g;s/\/\*.*/,/g'
6
7 | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g'
8
9 | sed '/^$/d' > ./recover.sql
10
11 [root@M1 opt]#
12
13 [root@M1 opt]# cat recover.sql
14
15 UPDATE test.student
16
17 SET
18
19 @11=1 ,
20
21 @2='a' ,
22
23 @3=1 ,
24
25 @4='56' ,
26
27 WHERE
28
29 @11=1 ;
30
31 UPDATE test.student
32
33 SET
34
35 @1=2 ,
36
37 @2='b' ,
38
39 @3=1 ,
40
41 @4='61' ,
42
43 WHERE
44
45 @1=2 ;
46
47 UPDATE test.student
48
49 SET
50
51 @1=3 ,
52
53 @2='c' ,
54
55 @3=2 ,
56
57 @4='78' ,
58
59 WHERE
60
61 @1=3 ;
62
63 UPDATE test.student
64
65 SET
66
67 @1=4 ,
68
69 @2='d' ,
70
71 @3=2 ,
72
73 @4='45' ,
74
75 WHERE
76
77 @1=4 ;
78
79 UPDATE test.student
80
81 SET
82
83 @1=5 ,
84
85 @2='e' ,
86
87 @33=3 ,
88
89 @4='76' ,
90
91 WHERE
92
93 @1=5 ;
94
95 UPDATE test.student
96
97 SET
98
99 @1=6 ,
100
101 @2='f' ,
102
103 @33=3 ,
104
105 @4='89' ,
106
107 WHERE
108
109 @1=6 ;
110
111 UPDATE test.student
112
113 SET
114
115 @1=7 ,
116
117 @2='g' ,
118
119 @3=4 ,
120
121 @4='43' ,
122
123 WHERE
124
125 @1=7 ;
126
127 UPDATE test.student
128
129 SET
130
131 @1=8 ,
132
133 @2='h' ,
134
135 @3=4 ,
136
137 @4='90' ,
138
139 WHERE
140
141 @1=8 ;
142
143 [root@M1 opt]#
再把@1/@2/@3/@4對應的表字段是id,name,class,score，替換掉
1 [root@M1 opt]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/class/g;s/@4/score/g' recover.sql 2 3 [root@M1 opt]# sed -i -r 's/(score=.*),/\1/g' recover.sql 4 [root@M1 opt]# 5 [root@M1 opt]# cat recover.sql 6 UPDATE test.student 7 SET 8 id=1 , 9 name='a' , 10 class=1 , 11 score='56' 12 WHERE 13 id=1 ; 14 UPDATE test.student 15 SET 16 id=2 , 17 name='b' , 18 class=1 , 19 score='61' 20 WHERE 21 id=2 ; 22 UPDATE test.student 23 SET 24 id=3 , 25 name='c' , 26 class=2 , 27 score='78' 28 WHERE 29 id=3 ; 30 UPDATE test.student 31 SET 32 id=4 , 33 name='d' , 34 class=2 , 35 score='45' 36 WHERE 37 id=4 ; 38 UPDATE test.student 39 SET 40 id=5 , 41 name='e' , 42 class=3 , 43 score='76' 44 WHERE 45 id=5 ; 46 UPDATE test.student 47 SET 48 id=6 , 49 name='f' , 50 class=3 , 51 score='89' 52 WHERE 53 id=6 ; 54 UPDATE test.student 55 SET 56 id=7 , 57 name='g' , 58 class=4 , 59 score='43' 60 WHERE 61 id=7 ; 62 UPDATE test.student 63 SET 64 id=8 , 65 name='h' , 66 class=4 , 67 score='90' 68 WHERE 69 id=8 ; 70 [root@M1 opt]#
OK。最激動人心的一幕到來了，我們進行恢復：
1 mysql> select * from student; 2 3 +----+------+-------+---------+ 4 5 | id | name | class | score | 6 7 +----+------+-------+---------+ 8 9 | 1 | a | 1 | failure | 10 11 | 2 | b | 1 | failure | 12 13 | 3 | c | 2 | failure | 14 15 | 4 | d | 2 | failure | 16 17 | 5 | e | 3 | failure | 18 19 | 6 | f | 3 | failure | 20 21 | 7 | g | 4 | failure | 22 23 | 8 | h | 4 | failure | 24 25 +----+------+-------+---------+ 26 27 8 rows in set (0.02 sec) 28 29 30 31 mysql> source /opt/recover.sql 32 33 Query OK, 1 row affected (0.11 sec) 34 35 Rows matched: 1 Changed: 1 Warnings: 0 36 37 38 39 Query OK, 1 row affected (0.95 sec) 40 41 Rows matched: 1 Changed: 1 Warnings: 0 42 43 44 45 Query OK, 1 row affected (0.16 sec) 46 47 Rows matched: 1 Changed: 1 Warnings: 0 48 49 50 51 Query OK, 1 row affected (0.03 sec) 52 53 Rows matched: 1 Changed: 1 Warnings: 0 54 55 56 57 Query OK, 1 row affected (0.80 sec) 58 59 Rows matched: 1 Changed: 1 Warnings: 0 60 61 62 63 Query OK, 1 row affected (0.08 sec) 64 65 Rows matched: 1 Changed: 1 Warnings: 0 66 67 68 69 Query OK, 1 row affected (0.09 sec) 70 71 Rows matched: 1 Changed: 1 Warnings: 0 72 73 74 75 Query OK, 1 row affected (0.07 sec) 76 77 Rows matched: 1 Changed: 1 Warnings: 0 78 79 80 81 mysql> select * from student; 82 83 +----+------+-------+-------+ 84 85 | id | name | class | score | 86 87 +----+------+-------+-------+ 88 89 | 1 | a | 1 | 56 | 90 91 | 2 | b | 1 | 61 | 92 93 | 3 | c | 2 | 78 | 94 95 | 4 | d | 2 | 45 | 96 97 | 5 | e | 3 | 76 | 98 99 | 6 | f | 3 | 89 | 100 101 | 7 | g | 4 | 43 | 102 103 | 8 | h | 4 | 90 | 104 105 +----+------+-------+-------+ 106 107 8 rows in set (0.02 sec)
1 mysql>
出處http://hcymysql.blog.51cto.com/5223301/1070148
