MySQL data recovery after mis-operation (Update,delete forgot where condition)

In the daily maintenance of the database, the developers are the most headache, many times due to the SQL statement written by the problem caused the server problems, resulting in resource exhaustion. The most dangerous operation is to forget the where condition when doing the DML operation, which causes the whole table to be updated, how do we deal with it as OPS or DBA? Below I have forgotten the where condition causes the whole table update processing method for update and delete operation respectively.

I. Update forgot to add where condition error operation recovery data (Binglog format must be row)

1. Create a data sheet for testing

mysql> CREATE TABLE t1 (    ID int unsigned NOT NULL auto_increment,    name Char (a) not NULL,    Sex enum (' f ', ' m ') not null default ' m ',    address varchar (+) NOT NULL,    primary key (ID)    ); Query OK, 0 rows affected (0.31 sec)

2. Inserting test data

mysql> INSERT INTO T1 (name,sex,address) VALUES (' Daiiy ', ' m ', ' Guangzhou '); Query OK, 1 row affected (0.01 sec) mysql> insert into T1 (name,sex,address) VALUES (' Tom ', ' F ', ' Shanghai ');   Query OK, 1 row Affected (0.00 sec) mysql> insert into T1 (name,sex,address) VALUES (' Liany ', ' m ', ' Beijing '); Query OK, 1 row Affected (0.00 sec) mysql> insert into T1 (name,sex,address) VALUES (' Lilu ', ' m ', ' Zhuhai ');  

3. You now need to change the address of the user with ID equal to 2 to zhuhai,update without adding a where condition

Mysql> SELECT * from t1;+----+-------+-----+-----------+| ID | Name  | sex | address   |+----+-------+-----+-----------+|  1 | Daiiy | M   | guangzhou | |  2 | Tom   | f   | shanghai  | |  3 | Liany | M   | Beijing   | |  4 | Lilu  | m   | Zhuhai    |+----+-------+-----+-----------+4 rows in Set (0.01 sec) mysql> Update T1 set address = ' Zhuhai '; Query OK, 3 rows affected (0.09 sec) rows Matched:4  changed:3  warnings:0mysql> select * from T1;              +----+-------+-----+---------+| ID | Name  | sex | address |+----+-------+-----+---------+|  1 | Daiiy | M   | Zhuhai  | |  2 | Tom   | f   | Zhuhai  | |  3 | Liany | M   | Zhuhai  | |  4 | Lilu  | m   | zhuhai  

4. Start recovery, on-line words, should be more complex, to first lock the table, so as to avoid the data is again contaminated. (lock table to see which binary log is being written)

mysql> lock tables T1 read; Query OK, 0 rows Affected (0.00 sec) mysql> Show Master status;+------------------+----------+--------------+-------- ----------+| File             | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000024 |     1852 |              |                  

5. Analyze the binary log, and find the relevant records in it, the update is address= ' Zhuhai ', we can filter out in the log.

[Email protected] mysql]# Mysqlbinlog--no-defaults-v-V--base64-output=decode-rows mysql-bin.000024 | Grep-b ' Zhuhai '

# at 1629# at 1679#140305 10:52:24 Server ID 1 end_log_pos 1679 table_map: ' db01 '. ' T1 ' mapped to number 38#140305 10:52:24 server ID 1 en D_log_pos 1825 update_rows:table ID flags:stmt_end_f### Update db01.t1### where### @1=1/* INT meta=0 nullable=0 I S_null=0 */### @2= ' daiiy '/* STRING (each) meta=65084 nullable=0 is_null=0 */### @3=2/* ENUM (1 byte) meta=63233 nullable =0 is_null=0 */### @4= ' guangzhou '/* varstring (All) meta=90 nullable=0 is_null=0 */### set### @1=1/* INT meta=0 Nullab Le=0 is_null=0 */### @2= ' daiiy '/* STRING ($) meta=65084 nullable=0 is_null=0 */### @3=2/* ENUM (1 byte) meta=63233 nu Llable=0 is_null=0 */### @4= ' Zhuhai '/* varstring (All) meta=90 nullable=0 is_null=0 */### UPDATE db01.t1### WHERE### @1 =2/* INT meta=0 nullable=0 is_null=0 */### @2= ' Tom '/* STRING (a) meta=65084 nullable=0 is_null=0 */### @3=1/* ENUM (   1 byte) meta=63233 nullable=0 is_null=0 */### @4= ' Shanghai '/* varstring (All) meta=90 nullable=0 is_null=0 */### SET### @1=2/* INT meta=0 NULLAble=0 is_null=0 */### @2= ' Tom '/* STRING ($) meta=65084 nullable=0 is_null=0 */### @3=1/* ENUM (1 byte) meta=63233 nu Llable=0 is_null=0 */### @4= ' Zhuhai '/* varstring (All) meta=90 nullable=0 is_null=0 */### UPDATE db01.t1### WHERE### @1 =3/* INT meta=0 nullable=0 is_null=0 */### @2= ' liany '/* STRING (a) meta=65084 nullable=0 is_null=0 */### @3=2/* ENU   M (1 bytes) meta=63233 nullable=0 is_null=0 */### @4= ' Beijing '/* varstring (All) meta=90 nullable=0 is_null=0 */### SET###  @1=3/* INT meta=0 nullable=0 is_null=0 */### @2= ' liany '/* STRING (a) meta=65084 nullable=0 is_null=0 */### @3=2/* ENUM (1 byte) meta=63233 nullable=0 is_null=0 */### @4= ' Zhuhai '/* varstring (All) meta=90 nullable=0 is_null=0 */

You can see that the changes in each line are recorded, which is why the Binglog format must be a row. where @1,@2,@3,@4, respectively, corresponds to the Id,name,sex,address field in the table. I believe you see a little bit of this here, right, yes, you guessed it, we convert the related records into SQL statements and re-import the database.

6. Processing binary logs for analytical processing

[Email protected] mysql]# Mysqlbinlog--no-defaults-v-V--base64-output=decode-rows mysql-bin.000024 | Sed-n '/# at 1679/,/commit/p ' > t1.txt [[email protected] mysql]# cat T1.txt# at1679#140305 10:52:24 Server ID 1 end_log_pos 1679 table_map: ' db01 '. ' T1 ' mapped to number 38#140305 10:52:24 server ID 1 en D_log_pos 1825 update_rows:table ID flags:stmt_end_f### Update db01.t1### where### @1=1/* INT meta=0 nullable=0 I S_null=0 */### @2= ' daiiy '/* STRING (each) meta=65084 nullable=0 is_null=0 */### @3=2/* ENUM (1 byte) meta=63233 nullable =0 is_null=0 */### @4= ' guangzhou '/* varstring (All) meta=90 nullable=0 is_null=0 */### set### @1=1/* INT meta=0 Nullab Le=0 is_null=0 */### @2= ' daiiy '/* STRING ($) meta=65084 nullable=0 is_null=0 */### @3=2/* ENUM (1 byte) meta=63233 nu Llable=0 is_null=0 */### @4= ' Zhuhai '/* varstring (All) meta=90 nullable=0 is_null=0 */### UPDATE db01.t1### WHERE### @1 =2/* INT meta=0 nullable=0 is_null=0 */### @2= ' Tom '/* STRING (a) meta=65084 nullable=0 is_null=0 */### @3=1/* ENUM (   1 byte) meta=63233 nullable=0 is_null=0 */### @4= ' Shanghai '/* varstring (All) meta=90 nullable=0 is_null=0 */### SET### @1=2/* INT meta=0 NULLAble=0 is_null=0 */### @2= ' Tom '/* STRING ($) meta=65084 nullable=0 is_null=0 */### @3=1/* ENUM (1 byte) meta=63233 nu Llable=0 is_null=0 */### @4= ' Zhuhai '/* varstring (All) meta=90 nullable=0 is_null=0 */### UPDATE db01.t1### WHERE### @1 =3/* INT meta=0 nullable=0 is_null=0 */### @2= ' liany '/* STRING (a) meta=65084 nullable=0 is_null=0 */### @3=2/* ENU   M (1 bytes) meta=63233 nullable=0 is_null=0 */### @4= ' Beijing '/* varstring (All) meta=90 nullable=0 is_null=0 */### SET###  @1=3/* INT meta=0 nullable=0 is_null=0 */### @2= ' liany '/* STRING (a) meta=65084 nullable=0 is_null=0 */### @3=2/* ENUM (1 byte) meta=63233 nullable=0 is_null=0 */### @4= ' Zhuhai '/* varstring (All) meta=90 nullable=0 is_null=0 */# at 182 5#140305 10:52:24 Server ID 1 end_log_pos 1852 Xid = 26commit/*!*/;  [[email protected] mysql]#

The SED is a bit complicated here, and I don't have to say much about the need for children's shoes to do their own research.

[[Email protected] mysql]# sed '/where/{:a; N;/set/!ba;s/\ ([^\n]*\) \n\ (. *\) \n\ (. *\)/\3\n\2\n\1/} ' T1.txt | Sed-r '/where/{:a; n;/@4/!ba;s/###   

[email protected] mysql]# cat recover.sql UPDATE db01.t1set  @1=1,  @2= ' Daiiy ',  @3=2,  @4= ' Guangzhou ', WHERE  @1=1; UPDATE db01.t1set  @1=2,  @2= ' Tom ',  @3=1,  @4= ' Shanghai ', where  @1=2; UPDATE db01.t1set  @1=3,  @2= ' Liany ',  @3=2,  @4= ' Beijing ', where  

Replace the @1,@2,@3,@4 in the file with the Id,name,sex,address field in the T1 table and remove the "," number from the last field

[Email protected] mysql]# sed-i ' s/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/address/g ' recover.sql[[email protected]  mysql]# sed-i-R ' s/(address=.*),/\1/g ' recover.sql[[email protected] mysql]# cat recover.sql UPDATE db01.t1set  id=1 ,  name= ' Daiiy ',  sex=2,  address= ' Guangzhou ' WHERE  id=1; UPDATE db01.t1set  id=2,  name= ' Tom ',  sex=1,  address= ' Shanghai ' WHERE  id=2; UPDATE db01.t1set  id=3,  name= ' Liany ',  sex=2,  address= ' Beijing ' WHERE  

7. Here the log is processed, now import (after importing data, unlock the table);

Mysql> source Recover.sql; Query OK, 1 row affected (0.12 sec) rows matched:1  changed:1  warnings:0query OK, 1 row Affected (0.00 sec) Rows M Atched:1  changed:1  warnings:0query OK, 1 row affected (0.01 sec) Rows matched:1  changed:1  warnings:0m Ysql> SELECT * from t1;+----+-------+-----+-----------+| ID | Name  | sex | address   |+----+-------+-----+-----------+|  1 | Daiiy | M   | guangzhou | |  2 | Tom   | f   | shanghai  | |  3 | Liany | M   | Beijing   | |  4 | Lilu  | m   | zhuhai    

You can see that the data has been fully recovered, and the advantages of this method are fast and convenient.

two. Delete forgot add where condition delete restore (binglog format must be row)In fact, this and update forgot to add the same conditions, but this is easier to deal with, here is the table above the test it 1. Simulate accidental deletion of data

Mysql> SELECT * from t1;+----+-------+-----+-----------+| ID | Name  | sex | address   |+----+-------+-----+-----------+|  1 | Daiiy | M   | guangzhou | |  2 | Tom   | f   | shanghai  | |  3 | Liany | M   | Beijing   | |  4 | Lilu  | m   | zhuhai    

2. Find related records in Binglog

[[email protected] mysql]# mysqlbinlog--no-defaults--base64-output=decode-rows-v-V mysql-bin.000024 |  Sed-n '/### delete from db01.t1/,/commit/p ' > delete.txt[[email protected] mysql]# cat delete.txt # # # Delete from db01.t1### where### @1=1/* INT meta=0 nullable=0 is_null=0 */### @2= ' daiiy '/* STRING (meta=65084 nullable=0 is_n) Ull=0 */### @3=2/* ENUM (1 byte) meta=63233 nullable=0 is_null=0 */### @4= ' guangzhou '/* varstring (All) meta=90 nullabl E=0 is_null=0 */### DELETE from db01.t1### where### @1=2/* INT meta=0 nullable=0 is_null=0 */### @2= ' Tom '/* STRING (6 0) meta=65084 nullable=0 is_null=0 */### @3=1/ENUM (1 byte) meta=63233 nullable=0 is_null=0 */### @4= ' Shanghai '/* V Arstring (meta=90) nullable=0 is_null=0 */### DELETE from db01.t1### where### @1=3/* INT meta=0 nullable=0 is_null=0 */### @2= ' liany '/* STRING ($) meta=65084 nullable=0 is_null=0 */### @3=2/* ENUM (1 byte) meta=63233 nullable=0 Is_nul L=0 */### @4= ' Beijing '/* VarstriNG (meta=90) nullable=0 is_null=0 */### DELETE from db01.t1### where### @1=4/* INT meta=0 nullable=0 is_null=0 */### @2= ' Lilu '/* STRING ($) meta=65084 nullable=0 is_null=0 */### @3=2/ENUM (1 bytes) meta=63233 nullable=0 is_null=0 */#  # # @4= ' Zhuhai '/* varstring (All) meta=90 nullable=0 is_null=0 */# at 2719#140305 11:41:00 server ID 1 end_log_pos 2746 Xid = 78commit/*!*/;  [[email protected] mysql]#

3. Converting records to SQL statements

[email protected] mysql]# Cat Delete.txt | 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 ' > T1.sql[[email protected] mysql]# cat t1.sql INSERT into Db01.t1select  1,  ' Daiiy ',  2 ,  ' Guangzhou '; insert into Db01.t1select  2,  ' Tom ',  1,  ' Shanghai '; INSERT INTO Db01.t1select  3,  ' Liany ',  2,  ' Beijing '; INSERT into Db01.t1select  4,  ' Lilu ',  2,  

4. Import data to verify data integrity

Mysql> source T1.sql;  Query OK, 1 row Affected (0.00 sec) records:1  duplicates:0  warnings:0query OK, 1 row affected (0.02 sec) Records: 1  duplicates:0  warnings:0query OK, 1 row affected (0.02 sec) records:1  duplicates:0  warnings:0query O K, 1 row affected (0.01 sec) records:1  duplicates:0  warnings:0mysql> select * from T1; ERROR 1046 (3d000): No database selectedmysql> SELECT * FROM db01.t1;+----+-------+-----+-----------+| ID | Name  | sex | address   |+----+-------+-----+-----------+|  1 | Daiiy | M   | guangzhou | |  2 | Tom   | f   | shanghai  | |  3 | Liany | M   | Beijing   | |  4 | Lilu  | m   | zhuhai    

The data will be complete back here. The advantage of setting the Binglog format to row is that it records the actual changes in each row and is not prone to problems when it comes to master-slave replication. However, due to the record of each row changes, will occupy a large number of disks, master-slave replication when the bandwidth consumption will be consumed. In the end is the use of row or mixed, need to be measured in the actual work, but overall, the format of Binglog is set to row, are not two choices.

Summarize:

So in the process of database operation we need to be extra careful, of course, development there we need to do a good job of control, but there is a parameter can solve our problem, let us not worry about similar problems occur:

Open this parameter in the [MySQL] paragraph:

Safe-updates

This way, when we forget to add the where condition when we do the DML operation, the MYSQLD server does not perform the operation:

Mysql> SELECT * from  t1;+----+------------------+| ID | name             |+----+------------------+|  1 | Yayun            | |  2 | Atlas            | |  3 | MySQL            | |  

MySQL data recovery after mis-operation (Update,delete forgot where condition)