I. Applicable background
There is a database named test, whose data is in the device test, and the log is in the device test_log;
The test database has a table tab1 with 5 Records (1-5 );
Complete database backup: dump database test to '/opt/data/test. dump ';
Then, five records (6-10) are inserted into the tab1 table. The time is: 17:30:09;
Then, record 1 in Table tab1 is deleted. At this time, table tab1 has nine records (2-10 );
At this time, we found that record 1 in the previous step should not be deleted. It is a mistaken deletion operation. What should we do if we want to restore it back ?!
2. Prerequisites for recovery
Database Data and logs must be stored separately on different devices.
The database has not truncated Database Transaction logs;
A complete backup of a database before accidental deletion;
3. Restore the deleted data by mistake
1. Back up the transaction logs of the test Database (do not use the with no_truncate option after the backup statement; otherwise, data cannot be recovered from the database log by Time Point ):
Dump transaction test to '/opt/data/test_tran.dump'
2. Create a clean database test1 For Load, and store its data and logs on two different devices (you can also directly recover from the existing database test1, however, we recommend that you use a clean database for data recovery to ensure data security in the existing test database );
3. Use the full database backup file/opt/data/test. dump to restore the data to the database test1:
Load database test1 from '/opt/data/test. dump'
4. Restore the status from the Database Transaction Log backup file/opt/data/test_tran.dump to the moment before data deletion:
Load transaction test1 from '/opt/data/test_tran.dump' with until_time = '2017-4-29 17:30:09'
(Here, the until_time format is recommended to be the same as the database date format, for example: Apr 29 2009 17:30:09 + millisecond + PM)