delete、update忘加where条件误操作苏醒进程演示,deletewhere

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,

先是,创建叁个习感到常权限的账号(切记不能够是SUPECRUISER权限),比方:

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 [[email protected] 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 [[email protected] 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 [[email protected] data]#  
  6 
  7 [[email protected] 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 [[email protected] 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 [[email protected] opt]# sed '/WHERE/{:a;N;/SET/!ba;s/([^n]*)n(.*)n(.*)/3n2n1/}' 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 [[email protected] opt]#  
 12 
 13 [[email protected] 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 [[email protected] opt]# 

再把@1/@2/@3/@4对应的表字段是id,name,class,score,替换掉

 1 [[email protected] opt]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/class/g;s/@4/score/g' recover.sql 
 2 
 3 [[email protected] opt]# sed -i -r 's/(score=.*),/1/g' recover.sql  
 4 [[email protected] opt]# 
 5 [[email protected] 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 [[email protected] 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>  

出处

update、delete未有带where条件,误操作,怎么样复苏呢?
笔者后天有一张学生表,小编要把…

使用MySQL闪回工夫恢复生机误删除误修改的多少

 

 

作者相信广大人都蒙受过忘带where条件大概where条件漏写了三个和写错了的境况,结果实践了delete/update后把整张表的数据都给改了。守旧的消除方式是:利用近些日子的全量备份+增量binlog备份,复苏到误操作以前的场合,可是此办法有叁个缺陷,那正是随着表的笔录增大,binlog的充实,复苏起来会很费时费劲。

 

现行反革命有叁个简约的法门,能够苏醒到误操作在此之前的情形。听上去那办法就好像采取的是Oracle的闪回效能,但MySQL这段日子(包蕴新型的V5.7版本与玛丽亚DB10.1拨出版本)还不辜负有那样的意义,不过,大家全然能够模拟出这风流罗曼蒂克效率来。使用该方式前,记得要将binlog日志设置为binlog_format
= ROW,假如是STATEMENT,这些措施是没用的。切记!!

 

在教学闪回过来前,大家首先要询问一下闪回的概念。闪回就象是Windows里的回笼站成效。MySQL实现闪回,是因此模拟的措施(解析binlog)来还原数据。二零一一年,原天猫商城彭立勋针对MySQL5.5本子,对rows格式的binlog能够开展逆向操作。delete反向生成insert,update生成反向的update。2014年,原费用宝楼方鑫针对MySQL5.6版本也如出后生可畏辙完成了此成效。

 

但当下还从未指向MariaDB
10的版本,由于binlog格式与MySQL区别,不能够利用此工具恢复生机数据,所以笔者也是透过深入分析binlog,用sed命令举行逆向操作,针对delete反向生成replace
into,update反向生成replace into,适用于MySQL/MariaDB/Percona版本。

 

我借鉴了Percona官方博客的思绪完结。

 

注!DROP/TRUNCATE操作无法接收闪回,须要全量备份+binlog增量备份复苏。

 

下边小编通过三个示范给我们演示一下。

 

u 误删除闪回步骤

幸存一张test表,总共有10条记下,查询结果如图(1卡塔尔(قطر‎所示。

 

图(1) test表数据

 

上边在实践delete删除操作的时候,忘加where条件,导致全表记录被删除,如图(2卡塔尔所示。

 

图(2卡塔尔国 test表的笔录全表被删除

 

 

光复进程

1、找到误删除的binlog保存下去          

# mysqlbinlog –base64-output=decode-rows -v -v
–start-datetime=”2016-05-04

13:15:00″ mysql-bin.000001 | grep -B 50 ‘### DELETE FROM
`test`.`test`’ | more

 

由那时间定位到全表删除的语句,如图(3卡塔尔国所示。

 

图(3) delete删除

 

这儿,请小心看小编用玉白灰框标出的@1,@2,@3,@4,这里对应原表字段分别是id,k,c,pad,后边的=等号则对应的原表被删除的记录。

 

下一步,只需把那大器晚成段binlog保存下去,以待前边的还原,实施命令如下。

# mysqlbinlog –base64-output=decode-rows -v -v mysql-bin.000001 | sed
-n

‘/end_log_pos 1049/,/COMMIT/p’ | tail -n +2 > /root/recover.binlog

 

查阅recover.binlog,我们获取了之类内容:

# cat /root/recover.binlog

### DELETE FROM `test`.`test`

### WHERE

###   @1=1 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3=” /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4=’qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=2 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3=” /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4=’qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=3 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3=” /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4=’qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=4 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3=” /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4=’qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=5 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3=” /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4=’qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=6 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3=” /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4=’qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=7 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3=” /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4=’qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=8 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3=” /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4=’qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=9 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3=” /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4=’qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ /*

STRING(180) meta=65204 nullable=0 is_null=0 */

### DELETE FROM `test`.`test`

### WHERE

###   @1=10 /* INT meta=0 nullable=0 is_null=0 */

###   @2=0 /* INT meta=0 nullable=0 is_null=0 */

###   @3=” /* STRING(360) meta=61032 nullable=0 is_null=0 */

###   @4=’qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ /*

STRING(180) meta=65204 nullable=0 is_null=0 */

# at 1049

#160504 13:16:08 server id 17128  end_log_pos 1076      Xid = 3225

COMMIT/*!*/;

 

2、逆向将delete转为replace into可执行SQL语句

# cat recover.binlog | sed -n ‘/###/p’ | sed ‘s/###
//g;s//*.*/,/g;s/DELETE

FROM/nREPLACE INTO/g;s/WHERE/SELECT/g’ | sed -r ‘s/(@4.*),/1;/g’ |
sed

‘s/@[1-9].*=//g’ > /root/recover.sql

 

注:@4代表字段的末梢一位,假使您的表有十个字段,要将其改为@10

 

查看recover.sql,大家得到了如下内容:

# cat /root/recover.sql

REPLACE INTO `test`.`test`

SELECT

  1 ,

  0 ,

  ” ,

  ‘qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ ;

 

REPLACE INTO `test`.`test`

SELECT

  2 ,

  0 ,

  ” ,

  ‘qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ ;

 

REPLACE INTO `test`.`test`

SELECT

  3 ,

  0 ,

  ” ,

  ‘qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ ;

 

REPLACE INTO `test`.`test`

SELECT

  4 ,

  0 ,

  ” ,

  ‘qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ ;

 

REPLACE INTO `test`.`test`

SELECT

  5 ,

  0 ,

  ” ,

  ‘qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ ;

 

REPLACE INTO `test`.`test`

SELECT

  6 ,

  0 ,

  ” ,

  ‘qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ ;

 

REPLACE INTO `test`.`test`

SELECT

  7 ,

  0 ,

  ” ,

  ‘qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ ;

 

REPLACE INTO `test`.`test`

SELECT

  8 ,

  0 ,

  ” ,

  ‘qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ ;

 

REPLACE INTO `test`.`test`

SELECT

  9 ,

  0 ,

  ” ,

  ‘qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ ;

 

REPLACE INTO `test`.`test`

SELECT

  10 ,

  0 ,

  ” ,

  ‘qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt’ ;

 

 

3、恢复

最冲动的风度翩翩幕到来了,以往就要进行最后一步的回复操作了,把这么些binlog调换到SQL语句,然后将其导入进去,实施命令如下:

# source /root/recover.sql

劳苦功高告成!你也快入手试试啊!友情提示:千万不要在生育条件下测量试验哦。

 

u 误改进update闪回步骤

1、同理第一步

 

2、逆向将delete转为replace into可执行SQL语句

 

# cat recover.binlog | sed -n ‘/###/p’ | sed ‘s/###
//g;s//*.*/,/g;’ | sed -n ‘/UPDATE

`test`.`test`/,/SET/p’| sed ‘s/UPDATE/REPLACE

INTO/g;s/WHERE/SELECT/g;s/SET/n/g’ |sed -r ‘s/(@4.*),/1;/g’ | sed

‘s/@[1-9].*=//g’ > /root/recover.sql

 

3、同理第三步

        MySQL的复制效能是依靠MySQL的分布、高品质的底子。

       
web服务器发生多量的突增采访,只须要充实几台web服务器,保障代码一向就能够。而MySQL复制就从未那么粗略,由于事务性就一定要难复制。

        MySQL复制功效提供分担读负载。使用复制功能,对数据库服务器进行水平扩大,为数据库服务器扩充贰个或多少个备库,这个备库就足以分地摊老板数据库的读负载。同一时间,备份工作也为高可用、横祸恢复、备份提供越多的选取.

       
MySQL的复制是依执照主人库上二进制日志,将主库上的日志复制到从库上做到的,所以mysql的复制是异步的。所以同期点上主库的数量和备库上会存在不直接的情况,况且不能够作保主库和备库的延迟

可解决难点

   
1、利用MySQL功用能够兑现分裂服务器上的数据分布,也足以完结差异机房数据布满。利用二进制日志增量进行的,无需太多带宽,极度是依赖语句的复制时,所急需的带宽就越来越少了。但依照行的复制,进行多量的转移时会对带宽还来自然的下压力,但在同朝气蓬勃IDC机房复制,压力可忽视。但在垮IDC情状下复制就需哟嗬思虑。大量多少复制最棒分批来拓宽。

    2、完毕分裂服务器上的数据分布

   
3、达成读取的载重均衡,供给其余构件合营到位。利用DNS轮询的措施把程序的读连接到差异的备份数据库,恐怕可利用LVS、haproxy这样的情势也得以。MySQL是非共享结构,同样的数据分布在人工新生儿窒息服务器上。

    4、增添数量安全性,利用备库的备份来压缩主库负载、

    5、数据库在线晋级

MySQL二进制日志

    MySQL服务层日志

        和动用存款和储蓄引擎非亲非故,是劳务器层记录的

        二进制日志:记录了具有对MySQL数据库的更正事件,包罗在删改查和对表构造的改良事件。log中记载的都以瓜熟蒂落执行了的,但对于回滚的语句是不会记载在binlog日志中的。

    查看工具binlog工具查看看

二进制格式设置

         基于段的格式 binlog_format = STATEMENT

         基于行的格式 binlog_format = ROW 推荐     binlog_row_image =
[FULL|MINIMAL(推荐)|NOBLOG] 

            (误操作改正了数据库中的数据,同不常间又还未备份能够还原,大家就足以透过剖析二级制日志,对日记中著录的数额校勘操作做反向管理的方法来达到苏醒数据的目标)

        混合日志格式 binlog_format = MIXED

    查看日志格式

图片 1

    查看刷新

图片 2

        慢查询日志、通用日志。

    MySQL存款和储蓄引擎层日志

        如innodb的重做日志和回滚日志

MySQL复制职业规律

图片 3

配置MySQL复制

听别人讲日志点的复制配置步骤

在主DB服务器上确立复制账号

create user ‘repl’ @ ‘ip段’ identified by ‘password’

授权

grant replication slave on ‘.’ to ‘repl’ @ ‘ip段’

安顿主数据库服务器

bin_log = mysql-bin //运转mysql的二进制日志

server_id = 100

配置从数据库服务器

bin_log = mysql-bin

server_id = 101

relay_log = mysql-relay-bin

log_slave_update = on (可选)

read_only = on

开头化从服务器数据

经过主数据库备份成效相比高

    mysqldump –master-data=2 -single-transaction //逻辑备份存为sql文件

    xtrabackup –slave-info //innodb推荐

起始复制电路

图片 4

相关文章