mysqlinnodb引擎表非常规修复方法-创新互联

    如果线上的MySQL生产数据库的数据被误删除，然后DBA去会恢复数据的时候，发现该数据库没有做备份、binlog也没有开启的话。还有其他手段去尽力去恢复数据吗？ percona公司提供了一个非常规的修复工具，可以去修复表数据。当然这个工具是有限制的：

成都创新互联主要从事成都网站设计、成都网站建设、网页设计、企业做网站、公司建网站等业务。立足成都服务神池,10余年网站建设经验,价格优惠、服务专业,欢迎来电咨询建站服务:135182197921、仅针对innodb引擎的表 2、表的row_format必须是REDUNDANT或者COMPACT，一般建议为COMPACT。而mysql5.7.8以上默认为Dynamic，这个要特别注意。 3、一旦发生误操作，需要尽快停止对事故表的写入，将idb文件拷贝出来。 4、数据不一定总是能恢复，比如被重新写入的情况等

    现在在虚拟机上做下测试：

    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、模拟误操作，将表数据清空

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

     参数解释： -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包括了主键数据和索引数据两大部分(如果该表有多个二级索引，则会生成多个文件)

可以知道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表的表结构定义传入到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、恢复表的数据

[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

    参数： -5 -f的参数和page_parser相同； -D:该参数的含义为代表恢复删除的数据页

    从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后，已经能正常展现出datetime类型的字段明细。但是INVOKE_LOG_INSTANCE_ID字段填充非常规的符号导致生成的sql文件异常，也就是说，该字段的信息无法修复，也导致了后面的INVOKE_LOG_COST字段的信息也无法修复出来。

    8、倒回数据

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字段有特殊字符导致后续字段的信息也无法修复出来。

    对有异常字符的记录做了剔除，最后能恢复的数据其实不多，这里我只恢复前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、总结

    综上所述，其实该工具也并非可以保证100%修复数据。所以作为一名DBA，首先要做好生产库的数据备份，并要时不时对备份进行检验其有效性。只要备份在，心才不会乱。

资料参考：

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/

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

文章名称：mysqlinnodb引擎表非常规修复方法-创新互联
文章来源：http://cxhlcq.com/article/pdgss.html