Percona Data Recovery tool for Innodb--mysql InnoDB Engine table unconventional repair tools

If the data in the MySQL production database on the line is mistakenly deleted, and the DBA goes to recover the data, it is discovered that the database is not backed up, Binlog, or open. Is there any other means to try to recover the data? Percona Company provides an unconventional repair tool that can be used to repair table data. Of course, this tool is limited:

1, only for the InnoDB engine table 2, the table Row_format must be redundant or compact, generally recommended for the compact. and mysql5.7.8 above default is dynamic, this should pay special attention to. 3, in the event of misoperation, it is necessary to stop writing the accident table as soon as possible, and copy the IDB file. 4, data may not always be able to recover, such as the situation is re-written, etc.

Now make the test on the virtual machine:

1. Prepare the test table first:

[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 ($)  NOT NULL,   ' invoke_log_app_id '  varchar ( 255)  NOT NULL COMMENT  ' call interface app ID ',   ' invoke_log_ipaddress '  varchar (255)  NOT NULL COMMENT  ' Invoke interface application IP ',   ' invoke_log_method '  varchar (255)  not  NULL COMMENT  ' Call interface mode ',   ' Invoke_log_status '  int (one)  NOT NULL  comment  ' Call interface return value ',   ' Invoke_log_invoketime '  datetime NOT NULL COMMENT  ' Interface call time ',   ' 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 (one)  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, analog error operation, the table data is emptied

[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. Immediately back up the IBD file for the table

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

4. Installation Tools

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. Parsing IBD Files

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

Parameter explanation:-5: Represents Row_format for Compact-f: Represents the file to parse

[Email protected]:mysql3306.sock 15:54: [Information_schema]>select i.index_id, i.name from INNODB_SYS_INDEXES as I I Nner 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)

This process resolves the IDB file of the table to a number of Page,innodb page that are divided into two parts, part of the first index part (primary key), and the other part as the two-level index part (secondary key). So the parsed IDB includes the primary key data and the index data in two parts (if the table has multiple two-level indexes, multiple files are generated)

You can know that 60 is the primary key index index_id, and 61 is the secondary index.

    6, getting the definition of a table

[[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 the above command will pass the table structure definition of the T_bibasic_storage table into table_defs.h and then make again. [[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, recover data from table

[[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,&NBSP;INVOKE_LOG_PARAM2,&NBSP;INVOKE_LOG_PARAM3,&NBSP;INVOKE_LOG_PARAM4,&NBSP;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" &NBsp;   "172" ". 16.112.40:8080<80>    -1777204477 

Parameters: -5-f parameters are the same as Page_parser;-D: The meaning of this parameter is to restore the deleted data page

As you can see from the SQL file, the tool has bugs and is not supported for datetime field types above mysql5.6 and requires modifications to the tool. For specific reference: https://bugs.launchpad.net/percona-data-recovery-tool-for-innodb/+bug/1190976, the SQL file generated after re-make is as follows:

[[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&nbsP;protected] "        " [email protected]*** "         "***n/a"          "N/a"     "172"     ". 16.112.40:8080<80>    -1777204477

As you can see, after modifying the PRINT_DATA.C, you have been able to display the DateTime type of the field details normally. However, the invoke_log_instance_id field fills an unconventional symbol resulting in the resulting SQL file exception, that is, the information for that field cannot be repaired, and the information in the subsequent Invoke_log_cost field cannot be repaired.

8. Rewind the data

[Email protected]:mysql3306.sock 17:26: [linzj]>load DATA INFILE '/tmp/linzj.sql ' REPLACE into TABLE ' linzj ' fields T erminated by ' \ t ' optionally enclosed by ' "' LINES starting by ' linzj\t ' (invoke_log_id, invoke_log_app_id, Invoke_log_ipa Ddress, 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 '

The error here is because the invoke_log_instance_id field has special characters that cause the information of subsequent fields to be repaired.

9. Summary

In summary, in fact, the tool is not guaranteed to 100% repair data. Therefore, as a DBA, the first thing to do is to make a backup of the production database, and to check the validity of the backup from time to time. As long as the backup in, the heart will not mess. Hopefully, this tool will never be used in a production library.

This article is from the "Lin Zhijian blog" blog, make sure to keep this source http://linzhijian.blog.51cto.com/1047212/1946409

Percona Data Recovery tool for Innodb--mysql InnoDB Engine table unconventional repair tools