mysql資料庫清空表格中資料恢複

資料庫被誤删除時有發生，資料恢複變得非常重要像oracle資料庫，我們常用的辦法就是采用閃回flashback，或者通過logmnr在分析日志完成資料的恢複，但是在mysql中，資料的恢複變成了很困難的一件事情。有一次，同僚的資料庫由于開發人員的資料訂正誤操作，導緻了一張表的所有資料被清空，由于該庫的資料容量已經達到了幾百G，從備份中恢複需要很長的時間，是以聯系到我幫助恢複，由于資料庫采用的是row模式，删除的操作在binlog中會一行一行的記錄，是以恢複操作就是将binlog中的内容進行解析為對應的插入語句

恢複步驟如下：

1.用mysqlbing将binlog檔案進行解析：

mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000004 >/tmp/master.log.20120925

2.由于被誤删除的表有13個字段，在加上兩行delete和where，是以取其中的15行：

grep “###” master.log.20120925 | grep “DELETE FROM master.agentgroup” -A 15 >/tmp/xx.log

[email protected] # more /tmp/xx.log

### DELETE FROM master.del_table

### WHERE

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

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

### @3=2010-09-07 18:03:13 /* DATETIME meta=0 nullable=0 is_null=0 */

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

### @5=2012-09-24 01:13:56 /* DATETIME meta=0 nullable=0 is_null=0 */

### @6=’yahoo_yst’ /* VARSTRING(384) meta=384 nullable=0 is_null=0 */

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

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

### @9=b’0′ /* BIT(1) meta=1 nullable=0 is_null=0 */

### @10=b’1′ /* BIT(1) meta=1 nullable=0 is_null=0 */

### @11=NULL /* BIT(1) meta=0 nullable=1 is_null=1 */

### @12=b’0′ /* BIT(1) meta=1 nullable=1 is_null=0 */

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

3.用sed替換’###’:

DELETE FROM master.del_table

WHERE

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

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

@3=2010-09-07 18:03:13 /* DATETIME meta=0 nullable=0 is_null=0 */

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

@5=2012-09-24 01:13:56 /* DATETIME meta=0 nullable=0 is_null=0 */

@6=’yahoo_yst’ /* VARSTRING(384) meta=384 nullable=0 is_null=0 */

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

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

@9=b’0′ /* BIT(1) meta=1 nullable=0 is_null=0 */

@10=b’1′ /* BIT(1) meta=1 nullable=0 is_null=0 */

@11=NULL /* BIT(1) meta=0 nullable=1 is_null=1 */

@12=b’0′ /* BIT(1) meta=1 nullable=1 is_null=0 */

@13=18170 /* INT meta=0 nullable=1 is_null=0 */

4.替換’*/’為’,':

[email protected] # sed -i ‘s/\*\//\*\/,/g’ /tmp/xx.log

@1=15 /* INT meta=0 nullable=0 is_null=0 */,

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

@3=2010-09-07 18:03:13 /* DATETIME meta=0 nullable=0 is_null=0 */,

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

@5=2012-09-24 01:13:56 /* DATETIME meta=0 nullable=0 is_null=0 */,

@6=’yahoo_yst’ /* VARSTRING(384) meta=384 nullable=0 is_null=0 */,

@7=5259 /* INT meta=0 nullable=1 is_null=0 */,

@8=22 /* INT meta=0 nullable=1 is_null=0 */,

@9=b’0′ /* BIT(1) meta=1 nullable=0 is_null=0 */,

@10=b’1′ /* BIT(1) meta=1 nullable=0 is_null=0 */,

@11=NULL /* BIT(1) meta=0 nullable=1 is_null=1 */,

@12=b’0′ /* BIT(1) meta=1 nullable=1 is_null=0 */,

@13=18170 /* INT meta=0 nullable=1 is_null=0 */,

5.替換日志中的最後一個’,'為’;':

a.delete前加’;':

sed -i ‘s/DELETE/;DELETE/g’ /tmp/xx.log

;DELETE FROM master.del_table

b.delete 前的’,;’替換為’;':

vi /tmp/xx.log —–>:%s/,$\n^ ;/;/g

@1=29 /* INT meta=0 nullable=0 is_null=0 */,

@5=2012-06-01 13:05:00 /* DATETIME meta=0 nullable=0 is_null=0 */,

@6=’alipay_front_jx’ /* VARSTRING(384) meta=384 nullable=0 is_null=0 */,

@7=5267 /* INT meta=0 nullable=1 is_null=0 */,

@8=58 /* INT meta=0 nullable=1 is_null=0 */,

@13=NULL /* BIT(1) meta=0 nullable=1 is_null=1 */

@1,@2,@3….對應的是表的字段；

6.最後将delete from table xx where 改為insert into xx values(”,”,”,”…..)既可以；

通過上面的6個步驟就可以從binlog中恢複出删除的資料，看上去很繁瑣，是以parse_binlog 工具就産生了，這個工具是@俊達 所寫，可以将row模式的binlog轉換為對應的sql語句：

mysql> USE T1

Database changed

mysql> delete from t1 where id<12;

Query OK, 2 rows affected (0.00 sec)

mysqlbinlog -vvv /home/mysql/data3006/mysql/mysql-bin.000004 |/root/parse_binlog.pl >/tmp/parse.sql1

more /tmp/parse/sql1

–DML type: DELETE, num of cols: 2

replace into t1.t1 values ( 10 , ‘ni hao1′);

replace into t1.t1 values ( 11 , ‘ni hao1′);

這樣DBA就可以友善的進行資料的恢複了；

最近@plinux已經完成該mysql閃回方案的更新檔，在row模式的binlog下，記錄了每個ROW的完整資訊，INSERT會包含每個字段的值，DELETE也會包含每個字段的值，UPDATE會在SET和WHERE部分包含所有的字段值。是以binlog就是個完整的邏輯redo，把它的操作逆過來，就是需要的“undo”；@吳炳錫 這個好人已經把他編譯好了放在開源社群上，可以在這裡下載下傳:

mysql> show master logs;

+——————+———–+

| Log_name | File_size |

| mysql-bin.000004 | 2293035 |

mysql> use t1

mysql> delete from t1 where id=15;

Query OK, 1 row affected (0.00 sec)

| mysql-bin.000004 | 2293211 |

[email protected] # ./mysqlbinlog.txt -v –base64-output=decode-rows -B –start-position=2293035 /home/mysql/data3006/mysql/mysql-bin.000004 >/tmp/1.sql

[email protected] # more /tmp/1.sql

DELIMITER ;

#121004 19:59:35 server id 3703006010 end_log_pos 2293211 Xid = 13145226

COMMIT/*!*/;

#121004 19:59:35 server id 3703006010 end_log_pos 2293143 Table_map: `t1`.`t1` mapped to number 1584

#121004 19:59:35 server id 3703006010 end_log_pos 2293184 Delete_rows: table id 1584 flags: STMT_END_F

### INSERT INTO t1.t1

### SET

### @1=15

### @2=’ni xxx’