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