如果線上的MySQL生產(chǎn)數(shù)據(jù)庫的數(shù)據(jù)被誤刪除，然后DBA去會恢復(fù)數(shù)據(jù)的時候，發(fā)現(xiàn)該數(shù)據(jù)庫沒有做備份、binlog也沒有開啟的話。還有其他手段去盡力去恢復(fù)數(shù)據(jù)嗎？ percona公司提供了一個非常規(guī)的修復(fù)工具，可以去修復(fù)表數(shù)據(jù)。當然這個工具是有限制的：
創(chuàng)新互聯(lián)專注于企業(yè)成都全網(wǎng)營銷、網(wǎng)站重做改版、雙遼網(wǎng)站定制設(shè)計、自適應(yīng)品牌網(wǎng)站建設(shè)、HTML5建站、商城網(wǎng)站建設(shè)、集團公司官網(wǎng)建設(shè)、成都外貿(mào)網(wǎng)站建設(shè)公司、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁設(shè)計等建站業(yè)務(wù)，價格優(yōu)惠性價比高，為雙遼等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。
1、僅針對innodb引擎的表 2、表的row_format必須是REDUNDANT或者COMPACT，一般建議為COMPACT。而mysql5.7.8以上默認為Dynamic，這個要特別注意。 3、一旦發(fā)生誤操作，需要盡快停止對事故表的寫入，將idb文件拷貝出來。 4、數(shù)據(jù)不一定總是能恢復(fù)，比如被重新寫入的情況等
現(xiàn)在在虛擬機上做下測試：
1、先準備好測試表：
root@localhost:mysql3306.sock 15:35: [linzj]>show create table linzj.linzj\G *************************** 1. row *************************** Table: linzj Create Table: CREATE TABLE `linzj` ( `ID` bigint(22) NOT NULL, `APP_ID` varchar(255) NOT NULL , `IPADDRESS` varchar(255) NOT NULL , `METHOD` varchar(255) NOT NULL , `STATUS` int(11) NOT NULL , `INVOKETIME` datetime NOT NULL , `PARAM1` varchar(255) DEFAULT NULL, `PARAM2` varchar(255) DEFAULT NULL, `PARAM3` varchar(255) DEFAULT NULL, `PARAM4` varchar(255) DEFAULT NULL, `PARAM5` varchar(255) DEFAULT NULL, `INSTANCE_ID` varchar(255) DEFAULT NULL, `COST` int(11) DEFAULT '0', PRIMARY KEY (`ID`), KEY `idx_id` (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) root@localhost:mysql3306.sock 15:36: [linzj]>select count(*) from linzj.linzj ; +----------+ | count(*) | +----------+ | 10000 | +----------+ 1 row in set (0.00 sec) root@localhost: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 root@localhost: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 root@localhost: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、模擬誤操作，將表數(shù)據(jù)清空
root@localhost:mysql3306.sock 15:37: [linzj]>truncate table linzj.linzj; Query OK, 0 rows affected (0.11 sec) root@localhost:mysql3306.sock 15:38: [linzj]>select count(*) from linzj.linzj ; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
3、馬上備份表的ibd文件
[root@mysql02 tmp]# cp /data/mysql/mysql3306/data/linzj/linzj.* /tmp/ [root@mysql02 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.gz tar -xvf percona-data-recovery-tool-for-innodb-0.5.tar.gz ./configure make
5、解析ibd文件
[root@mysql02 percona-data-recovery-tool-for-innodb-0.5]# mv /tmp/linzj.ibd ./ [root@mysql02 percona-data-recovery-tool-for-innodb-0.5]# ./page_parser -5 -f linzj.ibd Opening file: linzj.ibd: 2050 ID of device containing file 781917 inode number 33184 protection 1 number of hard links 0 user ID of owner 0 group ID of owner 0 device ID (if special file) 114688 total size, in bytes 4096 blocksize for filesystem I/O 224 number of blocks allocated 1499758773 time of last access 1499758773 time of last modification 1499759529 time of last status change 114688 Size to process in bytes 104857600 Disk cache size in bytes [root@mysql02 percona-data-recovery-tool-for-innodb-0.5]# cd page page_parser page_parser.c pages-1499759549/ [root@mysql02 percona-data-recovery-tool-for-innodb-0.5]# cd page page_parser page_parser.c pages-1499759549/ [root@mysql02 percona-data-recovery-tool-for-innodb-0.5]# cd pages-1499759549/ [root@mysql02 pages-1499759549]# ll total 4 drwxr-xr-x. 4 root root 4096 Jul 11 15:52 FIL_PAGE_INDEX [root@mysql02 pages-1499759549]# cd FIL_PAGE_INDEX/ [root@mysql02 FIL_PAGE_INDEX]# ls 0-60 0-61
參數(shù)解釋： -5：代表 row_format為Compact -f：代表要解析的文件
root@localhost: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包括了主鍵數(shù)據(jù)和索引數(shù)據(jù)兩大部分(如果該表有多個二級索引，則會生成多個文件)
可以知道60為主鍵索引的index_id，而61為輔助索引。
6、獲取表的定義
[root@mysql02 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表的表結(jié)構(gòu)定義傳入到table_defs.h中，然后重新make. [root@mysql02 percona-data-recovery-tool-for-innodb-0.5]# make gcc -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.o gcc -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.o gcc -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.a gcc -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、恢復(fù)表的數(shù)據(jù)
[root@mysql02 percona-data-recovery-tool-for-innodb-0.5]# ./constraints_parser -5 -D -f pages-1499764924/FIL_PAGE_INDEX/0-79/ > ./linzj.sql LOAD 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); [root@mysql02 percona-data-recovery-tool-for-innodb-0.5]# vim linzj.sql linzj 9893202823 "CMMAIL@MSS.CMCC" "172.16.115.7" "authenticateUserByPassword" 0 "201 7-05-01 00:00:01" "yusgs@js.cmcccmm" "ail@mss.cmcc***" "***n/a" "n/a" "172" ".16.112 .23:8080<80> -1844313341 linzj 9893202825 "CMMAIL@MSS.CMCC" "172.16.115.10" "authenticateUserByPassword" 0 "201 7-05-01 00:00:01" "ghaijing_lf@he.cmcccmm" "ail@mss.cmcc***" "***n/a" "n/a" "172" ".16.112.40:8080<80> -1777204477
參數(shù)： -5 -f的參數(shù)和page_parser相同； -D:該參數(shù)的含義為代表恢復(fù)刪除的數(shù)據(jù)頁
從sql文件中看出，該工具有bug,對mysql5.6以上的datetime字段類型并不支持，需要對工具做下修改。具體可以參考：https://bugs.launchpad.net/percona-data-recovery-tool-for-innodb/+bug/1190976 ， 重新make后生成的sql文件如下：
[root@mysql02 percona-data-recovery-tool-for-innodb-0.5]# vim linzj.sql linzj 9893202823 "CMMAIL@MSS.CMCC" "172.16.115.7" "authenticateUserByPassword" 0 "201 7-05-01 00:00:01" "yusgs@js.cmcccmm" "ail@mss.cmcc***" "***n/a" "n/a" "172" ".16.112 .23:8080<80> -1844313341 linzj 9893202825 "CMMAIL@MSS.CMCC" "172.16.115.10" "authenticateUserByPassword" 0 "201 7-05-01 00:00:01" "ghaijing_lf@he.cmcccmm" "ail@mss.cmcc***" "***n/a" "n/a" "172" ".16.112.40:8080<80> -1777204477
從中可以看出，修改了print_data.c后，已經(jīng)能正常展現(xiàn)出datetime類型的字段明細。但是INVOKE_LOG_INSTANCE_ID字段填充非常規(guī)的符號導致生成的sql文件異常，也就是說，該字段的信息無法修復(fù)，也導致了后面的INVOKE_LOG_COST字段的信息也無法修復(fù)出來。
8、倒回數(shù)據(jù)
root@localhost: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字段有特殊字符導致后續(xù)字段的信息也無法修復(fù)出來。
對有異常字符的記錄做了剔除，最后能恢復(fù)的數(shù)據(jù)其實不多，這里我只恢復(fù)前100條的記錄。
| 112197 | lw112197 | | 267083b86da116407435de6467ea7ad8 | | C8CEDCB5C06D4CD899A978AF36F982F4 | 2014-10-07 00:00:00 | -128 | NULL | NULL | NULL | -2122317824 | -2139095040 | -2139095040 | -2145386464 | NULL | NULL | | 112198 | lw112198 | | af0bd3fe2af1ddadede17552d82bfb9b | | B8E8E4D64D9547D6B10487898304CA26 | 2014-10-07 00:00:00 | -128 | NULL | NULL | NULL | -2122317824 | -2139095040 | -2139095040 | -2145386464 | NULL | NULL | | 112199 | lw112199 | | a0f8d1a649eeaa158448bb193f957f66 | | C1FD7CBB10E045688A61B405DC65B4CC | 2014-10-07 00:00:00 | -128 | NULL | NULL | NULL | -2122317824 | -2139095040 | -2139095040 | -2145386464 | NULL | NULL | | 112200 | lw112200 | | efd80a0e27f833d1f873225be034f3cb | | 5C6010521E0F4D7C87EC76BA08BABF7D | 2014-10-07 00:00:00 | -128 | NULL | NULL | NULL | -2122317824 | -2139095040 | -2139095040 | -2145386464 | NULL | NULL | | 112201 | lw112201 | | 1d0969bab6336865d92dd8de967877e7 | | 22D0DCC57E244C0992002DF4DBDA7403 | 2014-10-07 00:00:00 | -128 | NULL | NULL | NULL | -2122317824 | -2139095040 | -2139095040 | -2145386464 | NULL | NULL | | 112202 | lw112202 | | dd4fcf04ab444bc1098488b2f0359d41 | | 17F7EE34D673476FA8D1A9CC205E2625 | 2014-10-07 00:00:00 | -128 | NULL | NULL | NULL | -2122317824 | -2139095040 | -2139095040 | -2145386464 | NULL | NULL |
9、總結(jié)
綜上所述，其實該工具也并非可以保證100%修復(fù)數(shù)據(jù)。所以作為一名DBA，首先要做好生產(chǎn)庫的數(shù)據(jù)備份，并要時不時對備份進行檢驗其有效性。只要備份在，心才不會亂。
資料參考：
https://www.percona.com/docs/wiki/innodb-data-recovery-tool_start.html
https://www.percona.com/blog/2012/02/20/how-to-recover-deleted-rows-from-an-innodb-tablespace/
重慶分公司
重慶分公司
