mysql數據庫清空表格中數據恢復
2015/09/26 10:33
瀏覽197
迴響0
推薦0
引用0
數據庫被誤刪除時有發生，數據恢復變得非常重要像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
root@db1.com # 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替換’###’:
root@db1.com # 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 */
4.替換’*/’為’,':
root@db1.com # sed -i ‘s/\*\//\*\/,/g’ /tmp/xx.log
root@db1.com # 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 */,
DELETE FROM master.del_table
5.替換日誌中的最後一個’,'為’;':
a.delete前加’;':
sed -i ‘s/DELETE/;DELETE/g’ /tmp/xx.log
root@db1.com # 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 */,
;DELETE FROM master.del_table
b.delete 前的’,;’替換為’;':
vi /tmp/xx.log —–>:%s/,$\n^ ;/;/g
DELETE FROM master.del_table
WHERE
@1=29 /* 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-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 */,
@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=NULL /* BIT(1) meta=0 nullable=1 is_null=1 */
;DELETE FROM master.del_table
@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′);
–DML type: DELETE, num of cols: 2
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
Database changed
mysql> delete from t1 where id=15;
Query OK, 1 row affected (0.00 sec)
mysql> show master logs;
+——————+———–+
| Log_name | File_size |
+——————+———–+
| mysql-bin.000004 | 2293211 |
+——————+———–+
root@db.com # ./mysqlbinlog.txt -v –base64-output=decode-rows -B –start-position=2293035 /home/mysql/data3006/mysql/mysql-bin.000004 >/tmp/1.sql
root@db.com # 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’
DELIMITER ;
你可能會有興趣的文章：
限會員,要發表迴響,請先登入
