No full backup or binlog enabled, and use percona to restore Deleted Data

When we forget to make a full backup, and did not enable binlog, and executed deletefromsbtest; all data is lost, it is very difficult to restore. Today, PerconaDataRecoveryToolforInnoDB is used

When we forget to make a full backup, and did not enable binlog, and executed deletefromsbtest; all data is lost, it is very difficult to restore. Today, PerconaDataRecoveryToolforInnoDB is used

When we forget to make a full backup, and did not enable binlog, and executed

Delete from sbtest;

It is very difficult to recover all the data that is lost.

Today, we can use Percona Data Recovery Tool for InnoDB (only InnoDB is supported, and MyISAM does not) to retrieve deleted Data.

Principle: In the InnoDB engine, the delete operation does not actually delete rows on physical files, but adds a delete mark. Have we used WORD? When changing the font, there is a strikethrough mark, such as MySQL management path. This tool uses this feature to retrieve the data marked with strikethrough and store it in a single text, run the load data command to insert data to the table in batches.

Note: truncate cannot be restored (truncate directly clears data rows rather than adding deletion tags. You can view physical files and perform the truncate operation to reduce ibd files, after the delete operation is executed, the ibd file is as big as before. The drop operation cannot be restored (the data files are all gone. How can we restore them ?).

1. Install Percona Data Recovery Tool for InnoDB

# 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

# Cd percona-data-recovery-tool-for-innodb-0.5/mysql-source #./configure # cd .. # make

Ii. Delete the sbtest table from the full table

Delete from sbtest;

Iii. Recovery

1. Extract ibd physical files and store them separately at 16 KB per page.

# Cd percona-data-recovery-tool-for-innodb-0.5/#./page_parser-5-f/usr/local/mysql-5.5.37/data/test/sbtest. ibd

Here, 0-28 is the primary key (id) of the sbtest table, and 0-29 is the index (k) of the sbtest table ). Remember the number of this directory. We need to use this directory to restore data.

2. Generate the table structure

# Cd percona-data-recovery-tool-for-innodb-0.5/#./create_defs.pl -- host localhost -- port 3306 -- user root -- password 123456 -- db test -- table sbtest> include/table_defs.h

-- Host address

-- Port

-- User Username

-- Password

-- Db database name

-- Table Name

3. Execute the make compilation command again.

# D percona-data-recovery-tool-for-innodb-0.5/# make

4. Restore Deleted Data

# Cd percona-data-recovery-tool-for-innodb-0.5/#./constraints_parser-D-5-f pages-1410414511/FIL_PAGE_INDEX/0-28/>/tmp/sbtest.txt

-D. Restore the deleted row.

-File Format of table 5. The default format is Compact.

(You can run the show table status Command to view the details)

Summary:

The above method successfully completes the delete data recovery. After the data is deleted, remember to back up the ibd data file and do not overwrite it. Otherwise, the repair will not be completed. Currently, this tool does not support the string set type.

This article is from the "hechun's technical column" blog. Be sure to keep this source