mysql的資料恢複

標籤：

轉載自：http://ourmysql.com/archives/1293

 

 資料庫資料被誤刪除是經常看到的事情，資料的恢複也就自然成為了DBA很重要的一門基本功夫，比較笨拙的辦法是拉出曆史的備份到另外的一台機器恢複出來，但是這種方法如果資料量比較大的話，往往會耗費較長的時間，以前在使用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替換’###’:

   [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 */

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

   [email protected] # sed -i ‘s/\*\//\*\/,/g’ /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 */,

   DELETE FROM master.del_table

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

   a.delete前加’;‘:

   sed -i ‘s/DELETE/;DELETE/g’ /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 */,

   ;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 |

   +——————+———-+

   [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’

   DELIMITER ;

   不得不說開源的力量真大.

mysql的資料恢複