Percona Data Recovery Tool for InnoDB--mysql innodb引擎表非常規修複工具

標籤：mysql innodb recovery

    如果線上的MySQL生產資料庫的資料被誤刪除，然後DBA去會恢複資料的時候，發現該資料庫沒有做備份、binlog也沒有開啟的話。還有其他手段去儘力去恢複資料嗎？ percona公司提供了一個非常規的修複工具，可以去修複表資料。當然這個工具是有限制的：

1、僅針對innodb引擎的表2、表的row_format必須是REDUNDANT或者COMPACT，一般建議為COMPACT。而mysql5.7.8以上預設為Dynamic，這個要特別注意。3、一旦發生誤操作，需要儘快停止對事故表的寫入，將idb檔案拷貝出來。4、資料不一定總是能恢複，比如被重新寫入的情況等

    現在在虛擬機器上做下測試：

    1、先準備好測試表：

[email protected]:mysql3306.sock  15:35:  [linzj]>show create table linzj.linzj\G*************************** 1. row ***************************       Table: linzjCreate Table: CREATE TABLE `linzj` (  `INVOKE_LOG_ID` bigint(22) NOT NULL,  `INVOKE_LOG_APP_ID` varchar(255) NOT NULL COMMENT ‘調用介面應用ID‘,  `INVOKE_LOG_IPADDRESS` varchar(255) NOT NULL COMMENT ‘調用介面應用IP‘,  `INVOKE_LOG_METHOD` varchar(255) NOT NULL COMMENT ‘調用介面方式‘,  `INVOKE_LOG_STATUS` int(11) NOT NULL COMMENT ‘調用介面傳回值‘,  `INVOKE_LOG_INVOKETIME` datetime NOT NULL COMMENT ‘介面調用時間‘,  `INVOKE_LOG_PARAM1` varchar(255) DEFAULT NULL,  `INVOKE_LOG_PARAM2` varchar(255) DEFAULT NULL,  `INVOKE_LOG_PARAM3` varchar(255) DEFAULT NULL,  `INVOKE_LOG_PARAM4` varchar(255) DEFAULT NULL,  `INVOKE_LOG_PARAM5` varchar(255) DEFAULT NULL,  `INVOKE_LOG_INSTANCE_ID` varchar(255) DEFAULT NULL,  `INVOKE_LOG_COST` int(11) DEFAULT ‘0‘,  PRIMARY KEY (`INVOKE_LOG_ID`),  KEY `idx_id` (`INVOKE_LOG_ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)[email protected]:mysql3306.sock  15:36:  [linzj]>select count(*) from linzj.linzj ;+----------+| count(*) |+----------+|    10000 |+----------+1 row in set (0.00 sec)[email protected]:mysql3306.sock  16:54:  [linzj]>alter table linzj add index idx_cost(INVOKE_LOG_COST);alter table linzj add primary key pk_id(INVOKE_LOG_ID);Query OK, 0 rows affected (0.06 sec)Records: 0  Duplicates: 0  Warnings: 0[email protected]:mysql3306.sock  16:53:  [linzj]>alter table linzj row_format=COMPACT;Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0[email protected]:mysql3306.sock  16:46:  [information_schema]>SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA=‘linzj‘ and table_name=‘linzj‘;;+------------+| ROW_FORMAT |+------------+| Compact    |+------------+1 row in set (0.10 sec)

    2、類比誤操作，將表資料清空

[email protected]:mysql3306.sock  15:37:  [linzj]>truncate table linzj.linzj;Query OK, 0 rows affected (0.11 sec)[email protected]:mysql3306.sock  15:38:  [linzj]>select count(*) from linzj.linzj ;+----------+| count(*) |+----------+|        0 |+----------+1 row in set (0.00 sec)

    3、馬上備份表的ibd檔案

[[email protected] tmp]# cp /data/mysql/mysql3306/data/linzj/linzj.* /tmp/[[email protected] tmp]# ll linzj.*-rw-r-----. 1 root root  13463 Jul 11 15:39 linzj.frm-rw-r-----. 1 root root 114688 Jul 11 15:39 linzj.ibd

    4、安裝工具

cd /usr/local/wget https://launchpad.net/percona-data-recovery-tool-for-innodb/trunk/release-0.5/+download/percona-data-recovery-tool-for-innodb-0.5.tar.gztar -xvf percona-data-recovery-tool-for-innodb-0.5.tar.gz./configuremake

    5、解析ibd檔案

[[email protected] percona-data-recovery-tool-for-innodb-0.5]# mv /tmp/linzj.ibd  ./[[email protected] percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f linzj.ibd Opening file: linzj.ibd:2050            ID of device containing file781917          inode number33184           protection1               number of hard links0               user ID of owner0               group ID of owner0               device ID (if special file)114688          total size, in bytes4096            blocksize for filesystem I/O224             number of blocks allocated1499758773      time of last access1499758773      time of last modification1499759529      time of last status change114688  Size to process in bytes104857600       Disk cache size in bytes[[email protected] percona-data-recovery-tool-for-innodb-0.5]# cd pagepage_parser       page_parser.c     pages-1499759549/ [[email protected] percona-data-recovery-tool-for-innodb-0.5]# cd pagepage_parser       page_parser.c     pages-1499759549/ [[email protected] percona-data-recovery-tool-for-innodb-0.5]# cd  pages-1499759549/ [[email protected] pages-1499759549]# lltotal 4drwxr-xr-x. 4 root root 4096 Jul 11 15:52 FIL_PAGE_INDEX[[email protected] pages-1499759549]# cd FIL_PAGE_INDEX/[[email protected] FIL_PAGE_INDEX]# ls0-60  0-61

     參數解釋： -5：代表 row_format為Compact -f：代表要解析的檔案

[email protected]:mysql3306.sock  15:54:  [information_schema]>select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as i INNER JOIN INNODB_SYS_TABLES as t USING(TABLE_ID) WHERE t.NAME=‘linzj/linzj‘;+----------+---------+| INDEX_ID | NAME    |+----------+---------+|       60 | PRIMARY ||       61 | idx_id  |+----------+---------+2 rows in set (0.00 sec)

    此過程會將表的idb檔案解析為很多的page，innodb的page分為兩大部分，一部分一級索引部分(primary key)，另一部分為二級索引部分(secondary key)，所以解析出來的idb包括了主鍵資料和索引資料兩大部分(如果該表有多個二級索引，則會產生多個檔案)

可以知道60為主鍵索引的index_id，而61為輔助索引。

    6、擷取表的定義

[[email protected] percona-data-recovery-tool-for-innodb-0.5]# ./create_defs.pl -host localhost -port 3306 -user root -password linzj -db linzj -table linzj > include/table_defs.h上面的命令會將t_bibasic_storage表的表結構定義傳入到table_defs.h中，然後重新make.[[email protected] percona-data-recovery-tool-for-innodb-0.5]# makegcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c tables_dict.c -o lib/tables_dict.ogcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c check_data.c -o lib/check_data.ogcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -o constraints_parser constraints_parser.c lib/tables_dict.o lib/print_data.o lib/check_data.o lib/libut.a lib/libmystrings.agcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS=64 -D_LARGEFILE64_SOURCE=1 -D_LARGEFILE_SOURCE=1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -static -lrt -o page_parser page_parser.c lib/tables_dict.o lib/libut.a

    7、恢複表的資料

[[email protected] percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser -5 -D -f pages-1499764924/FIL_PAGE_INDEX/0-79/ > ./linzj.sqlLOAD DATA INFILE ‘/usr/local/percona-data-recovery-tool-for-innodb-0.5/dumps/default/linzj‘ REPLACE INTO TABLE `linzj` FIELDS TERMINATED BY ‘\t‘ OPTIONALLY ENCLOSED BY ‘"‘ LINES STARTING BY ‘linzj\t‘ (INVOKE_LOG_ID, INVOKE_LOG_APP_ID, INVOKE_LOG_IPADDRESS, INVOKE_LOG_METHOD, INVOKE_LOG_STATUS, INVOKE_LOG_INVOKETIME, INVOKE_LOG_PARAM1, INVOKE_LOG_PARAM2, INVOKE_LOG_PARAM3, INVOKE_LOG_PARAM4, INVOKE_LOG_PARAM5, INVOKE_LOG_INSTANCE_ID, INVOKE_LOG_COST);[[email protected] percona-data-recovery-tool-for-innodb-0.5]# vim linzj.sql linzj   9893202823      "[email protected]"       "172.16.115.7"  "authenticateUserByPassword"    0       "2017-05-01 00:00:01"   "[email protected]"      "[email protected]***"       "***n/a"        "n/a"   "172"   ".16.112.23:8080<80>    -1844313341linzj   9893202825      "[email protected]"       "172.16.115.10" "authenticateUserByPassword"    0       "2017-05-01 00:00:01"   "[email protected]"        "[email protected]***"       "***n/a"        "n/a"   "172"".16.112.40:8080<80>    -1777204477

    參數： -5 -f的參數和page_parser相同； -D:該參數的含義為代表恢複刪除的資料頁

    從sql檔案中看出，該工具有bug,對mysql5.6以上的datetime欄位類型並不支援，需要對工具做下修改。具體可以參考：https://bugs.launchpad.net/percona-data-recovery-tool-for-innodb/+bug/1190976 ， 重新make後產生的sql檔案如下：

[[email protected] percona-data-recovery-tool-for-innodb-0.5]# vim linzj.sql linzj   9893202823      "[email protected]"       "172.16.115.7"  "authenticateUserByPassword"    0       "2017-05-01 00:00:01"   "[email protected]"      "[email protected]***"       "***n/a"        "n/a"   "172"   ".16.112.23:8080<80>    -1844313341linzj   9893202825      "[email protected]"       "172.16.115.10" "authenticateUserByPassword"    0       "2017-05-01 00:00:01"   "[email protected]"        "[email protected]***"       "***n/a"        "n/a"   "172"   ".16.112.40:8080<80>    -1777204477

    從中可以看出，修改了print_data.c後，已經能正常展現出datetime類型的欄位明細。但是INVOKE_LOG_INSTANCE_ID欄位填充非常規的符號導致產生的sql檔案異常，也就是說，該欄位的資訊無法修複，也導致了後面的INVOKE_LOG_COST欄位的資訊也無法修複出來。    

    8、倒回資料

[email protected]:mysql3306.sock  17:26:  [linzj]>LOAD DATA INFILE ‘/tmp/linzj.sql‘ REPLACE INTO TABLE `linzj` FIELDS TERMINATED BY ‘\t‘ OPTIONALLY ENCLOSED BY ‘"‘ LINES STARTING BY ‘linzj\t‘ (INVOKE_LOG_ID, INVOKE_LOG_APP_ID, INVOKE_LOG_IPADDRESS, INVOKE_LOG_METHOD, INVOKE_LOG_STATUS, INVOKE_LOG_INVOKETIME, INVOKE_LOG_PARAM1, INVOKE_LOG_PARAM2, INVOKE_LOG_PARAM3, INVOKE_LOG_PARAM4, INVOKE_LOG_PARAM5, INVOKE_LOG_INSTANCE_ID, INVOKE_LOG_COST);ERROR 1300 (HY000): Invalid utf8 character string: ‘".16.112.47:8080‘

    這裡的報錯就是因為INVOKE_LOG_INSTANCE_ID欄位有特殊字元導致後續欄位的資訊也無法修複出來。

    9、總結

    綜上所述，其實該工具也並非可以保證100%修複資料。所以作為一名DBA，首先要做好生產庫的資料備份，並要時不時對備份進行檢驗其有效性。只要備份在，心才不會亂。希望永遠都不會在生產庫上用上這個工具。

本文出自 “林志堅的部落格” 部落格，請務必保留此出處http://linzhijian.blog.51cto.com/1047212/1946409

Percona Data Recovery Tool for InnoDB--mysql innodb引擎表非常規修複工具