Shulou(Shulou.com)06/01 Report--

If the data in the online MySQL production database is mistakenly deleted, and then DBA goes to recover the data, it is found that the database is not backed up and binlog is not open. Is there any other way to try to recover the data? Percona provides an unconventional repair tool to repair table data. Of course, this tool is limited:

1. Table 2 and table row_format for innodb engine only must be REDUNDANT or COMPACT, and COMPACT is generally recommended. And mysql5.7.8 above the default is Dynamic, this should be paid special attention. 3. In the event of misoperation, it is necessary to stop writing the accident table and copy the idb file as soon as possible. 4. Data may not always be recovered, such as when it is rewritten

Now do a test on the virtual machine:

1. Prepare the test table first:

Root@localhost:mysql3306.sock 15:35: [linzj] > show create table linzj.linzj\ gateway * 1. Row * * Table: linzjCreate Table: CREATE TABLE `linzj` (`ID`bigint (22) NOT NULL, `APP_ ID`varchar (255) NOT NULL, `IPADDRESS` varchar (255th) NOT NULL, `METHOD` varchar (255th) 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 `ID` (`ID`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) root@localhost:mysql3306.sock 15:36: [linzj] > select count (*) from linzj.linzj +-+ | count (*) | +-+ | 10000 | +-+ 1 row in set (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: 0root@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: 0root@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. Simulate misoperation and empty the table data.

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. Back up the ibd file of the table immediately

[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. 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. Parse the ibd file

[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 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 [root@mysql02 percona-data-recovery-tool-for-innodb-0.5] # cd pagepage_parser page_parser.c pages-1499759549/ [root@mysql02 percona-data-recovery-tool-for-innodb-0.5] # cd pagepage_parser Page_parser.c pages-1499759549/ [root@mysql02 percona-data-recovery-tool-for-innodb-0.5] # cd pages-1499759549/ [root@mysql02 pages-1499759549] # lltotal 4drwxr-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] # ls0-600-61

Parameter explanation:-5: represents row_format as Compact-f: represents the file to be parsed

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.NAMEcargo LINZJUP Linzj' +-+-+ | INDEX_ID | NAME | +-+-+ | 60 | PRIMARY | | 61 | idx_id | +-+-+ 2 rows in set (0.00 sec)

This process parses the idb file of the table into many page,innodb page into two parts, one is the first-level index part (primary key), the other is the second-level index part (secondary key), so the parsed idb includes both primary key data and index data (if the table has multiple secondary indexes, multiple files will be generated)

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

6. Get the definition of the table

[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 the command above passes the table structure definition of the tweak bibasic _ story table to table_defs.h Then re-make. [root@mysql02 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-Downs LARGEFILES 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. Restore the data of the table

[root@mysql02 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) [root@mysql02 percona-data-recovery-tool-for-innodb-0.5] # vim linzj.sql linzj 9893202823 "CMMAIL@MSS.CMCC"172.16.115.7"authenticateUserByPassword" 0 "2017-05-01 00:00:01"yusgs@js.cmcccmm"ail@mss.cmcc***"* nmob a"namea"172" ".16.112.23: 8080-1844313341linzj 9893202825" CMMAIL@MSS.CMCC "" 172.16.115.10 "" authenticateUserByPassword "0" 2017-05-01 00:00:01 "" ghaijing_lf@he.cmcccmm "" ail@mss.cmcc*** "" * * ngamard a "" nCMA "" 172 ".16.112.40: 8080-1777204477

Parameter: the parameter of-5-f is the same as that of page_parser;-D: this parameter means to restore the deleted data page

It can be seen from the sql file that the tool has bug and does not support datetime field types above mysql5.6, so it needs to be modified. For more information, please see https://bugs.launchpad.net/percona-data-recovery-tool-for-innodb/+bug/1190976. The sql file generated after re-make is as follows:

[root@mysql02 percona-data-recovery-tool-for-innodb-0.5] # vim linzj.sql linzj 9893202823 "CMMAIL@MSS.CMCC"172.16.115.7"authenticateUserByPassword" 0 "2017-05-01 00:00:01"yusgs@js.cmcccmm"ail@mss.cmcc***"* nmob a"namea"172" ".16.112.23: 8080-1844313341linzj 9893202825" CMMAIL@MSS.CMCC "" 172.16.115.10 "" authenticateUserByPassword "0" 2017-05-01 00:00:01 "" ghaijing_lf@he.cmcccmm "" ail@mss.cmcc*** "" * * ngamard a "" nCMA "" 172 ".16.112.40: 8080-1777204477

It can be seen that after modifying the print_data.c, the field details of the datetime type can be displayed normally. However, filling the INVOKE_LOG_INSTANCE_ID field with unconventional symbols results in an exception in the generated sql file, that is, the information in the field cannot be repaired, and the information in the following INVOKE_LOG_COST field cannot be repaired.

8. Rewind the data

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'

The error here is that the information of the subsequent fields cannot be repaired because the INVOKE_LOG_INSTANCE_ID field has special characters.

The records with abnormal characters are removed, and there is not much data that can be recovered in the end. Here I only restore the first 100 records.

| | 112197 | lw112197 | | 267083b86da116407435de6467ea7ad8 | | C8CEDCB5C06D4CD899A978AF36F982F4 | 2014-10-07 00:00:00 |-128,128 | NULL |-2139095040 |-2139095040 |-2145386464 | NULL | NULL | 112198 | lw112198 | | af0bd3fe2af1ddadede17552d82bfb9b | | B8E8E4D64D9547D6B10487898304CA26 | 00:00:00 |-12824 | NULL | NULL |-2122317824 |-2139095040 |-2139095040 |-2145386464 | NULL | NULL | | | 112199 | lw112199 | | a0f8d1a649eeaa158448bb193f957f66 | | C1FD7CBB10E045688A61B405DC65B4CC | 2014-10-07 00:00:00 |-128,128 | NULL |-2139095040 |-2139095040 |-2145386464 | NULL | NULL | 112200 | lw112200 | | efd80a0e27f833d1f873225be034f3cb | | 5C6010521E0F4D7C87EC76BA08BABF7D | 00:00:00 |-12824 | NULL |-2122317824 |-2139095040 |-2139095040 |-2145386464 | NULL | | | NULL | | 112201 | lw112201 | | 1d0969bab6336865d92dd8de967877e7 | | 22D0DCC57E244C0992002DF4DBDA7403 | 00:00:00 on October 10, 2014 | NULL |-2139095040 |-2139095040 |-2145386464 | NULL | NULL | | 112202 | lw112202 | | dd4fcf04ab444bc1098488b2f0359d41 | 17F7EE34D673476FA8D1A9CC205E2625 | 00:00:00 | NULL | NULL |-2122317824 |-2139095040 |-2139095040 |-2139095040 |-2145386464 | NULL | NULL | |

9. Summary

To sum up, in fact, the tool can not guarantee 100% repair of data. Therefore, as a DBA, we should first make a good backup of the data in the production database, and verify the validity of the backup from time to time. As long as the backup is there, the heart will not be confused.

Reference:

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/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.