mysql不小心把表清空_mysql 清空表 delete后怎么恢复

展开全部

|

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

1.模拟误删除数据mysql> select * from t1;

+----+-------+-----+-----------+

|62616964757a686964616fe59b9ee7ad9431333363383961 id | name  | sex | address   |

+----+-------+-----+-----------+

|  1 | daiiy | m   | guangzhou |

|  2 | tom   | f   | shanghai  |

|  3 | liany | m   | beijing   |

|  4 | lilu  | m   | zhuhai    |

+----+-------+-----+-----------+

rows in set (0.00 sec)

mysql> delete from t1;

Query OK, 4 rows affected (0.03 sec)

mysql> select * from t1;

Empty set (0.00 sec)

mysql>

2、在binglog中去查找相关记录[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000024 | sed -n '/### DELETE FROM db01.t1/,/COMMIT/p' > delete.txt

[root@localhost mysql]# cat delete.txt

### DELETE FROM db01.t1

### WHERE

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

###   @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */

###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

###   @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

### DELETE FROM db01.t1

### WHERE

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

###   @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */

###   @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

###   @4='shanghai' /* VARSTRING(90) 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(60) meta=65084 nullable=0 is_null=0 */

###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

###   @4='beijing' /* VARSTRING(90) 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(60) meta=65084 nullable=0 is_null=0 */

###   @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */

###   @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */

# at 2719

#140305 11:41:00 server id 1  end_log_pos 2746  Xid = 78

COMMIT/*!*/;

[root@localhost mysql]#

3、将记录转换成sql语句[root@localhost 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

[root@localhost mysql]# cat t1.sql

INSERT INTO db01.t1

SELECT

,

'daiiy' ,

,

'guangzhou' ;

INSERT INTO db01.t1

SELECT

,

'tom' ,

,

'shanghai' ;

INSERT INTO db01.t1

SELECT

,

'liany' ,

,

'beijing' ;

INSERT INTO db01.t1

SELECT

,

'lilu' ,

,

'zhuhai' ;

[root@localhost mysql]#

4、导入数据，验证数据完整性。mysql> source t1.sql;

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.02 sec)

Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.02 sec)

Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t1;

ERROR 1046 (3D000): No database selected

mysql> select * from db01.t1;

+----+-------+-----+-----------+

| id | name  | sex | address   |

+----+-------+-----+-----------+

|  1 | daiiy | m   | guangzhou |

|  2 | tom   | f   | shanghai  |

|  3 | liany | m   | beijing   |

|  4 | lilu  | m   | zhuhai    |

+----+-------+-----+-----------+

rows in set (0.00 sec)

mysql>

到这里数据就完整回来了。

将binglog格式设置为row有利有弊，好处是记录了每一行的实际变化，在主从复制时也不容易出问题。但是由于记录每行的变化，会占用大量磁盘，主从复制时带宽占用会有所消耗。到底是使用row还是mixed，需要在实际工作中自己去衡量，但从整体上来说，binglog的格式设置为row，都是不二的选择