Database Data Recovery Experiment process

Access to the database operation, there will inevitably be some mis-operation. Then we need to recover the data. A brief introduction to the process of recovering the contents of a table after it is deleted.

First, let's simulate the experimental environment:

After installing the database, enter into/ETC/MY.CNF.

Check to see if logging is enabled (data recovery is based on the log. ）

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/91/F0/wKiom1j5m-7xNykPAABD0aH182Q173.jpg-wh_500x0-wm_ 3-wmp_4-s_1843065427.jpg "title=" capture. JPG "alt=" Wkiom1j5m-7xnykpaabd0ah182q173.jpg-wh_50 "/>

This environment has been set up.

After entering the database, a new library is named VIANET_BSS

Enter the library and create a new table named Stb_active

CREATE TABLE Stb_active (server_id char (5), Server_code char (Ten), server_name char, Server_desc char (TEN), State char (5 ));

Then insert a few data

INSERT into inserts data, and after a few data appears in the table, our data is created successfully.

650) this.width=650; "Src=" https://s4.51cto.com/wyfs02/M02/91/F0/wKioL1j5nZzSGyppAAB2FrqUejU496.jpg-wh_500x0-wm_ 3-wmp_4-s_2857834123.jpg "title=" capture. JPG "alt=" Wkiol1j5nzzsgyppaab2frqueju496.jpg-wh_50 "/>

We then use the delete from stb_active where server_id=2 we delete the content with ID 2 and restore it later.

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/91/F1/wKiom1j5nk2jRkasAACC1D49YHc973.jpg-wh_500x0-wm_ 3-wmp_4-s_725993451.jpg "title=" 666.JPG "alt=" Wkiom1j5nk2jrkasaacc1d49yhc973.jpg-wh_50 "/>

Look again for the ID 2 content is gone.

Then we use Ps-ef | grep MySQL checks where the location of the Mysqldata is located and Mysql-bin is placed under the data directory.

Then go to the data directory to find the latest log to see. Of course the garbled needs to be converted into. sql to see the content.

Mysqlbinlog-u root-p123.com mysql-bin.000006 >/opt/backup/4.sql

Then Vim/opt/backup/4.sql

Find the appropriate time period when we deleted the content.

And then find the command that we just executed to delete the content.

650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M00/91/F1/wKiom1j5n9azLQ9GAAA7we8cGnY165.jpg-wh_500x0-wm_ 3-wmp_4-s_2679020398.jpg "title=" capture. JPG "alt=" Wkiom1j5n9azlq9gaaa7we8cgny165.jpg-wh_50 "/>

and delete it, you cannot restore it if you perform a restore here, there will be data duplication. So we're going to delete the data that we're not going to restore. Avoid duplication, but be sure to match the data inside the database.

The following is the result of not removing valid data from the log

650) this.width=650; "Src=" https://s1.51cto.com/wyfs02/M02/91/F1/wKioL1j5oQeww3dFAAB_nypW1o0993.jpg-wh_500x0-wm_ 3-wmp_4-s_4109643564.jpg "title=" capture. JPG "alt=" Wkiol1j5oqeww3dfaab_nypw1o0993.jpg-wh_50 "/>

After that, I'll delete all the data inside and finally re-import a copy of the data into the data recovery complete.

650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M02/91/F1/wKioL1j5oaGBXWwFAAB2nk2VtzA429.jpg-wh_500x0-wm_ 3-wmp_4-s_444474038.jpg "title=" capture. JPG "alt=" Wkiol1j5oagbxwwfaab2nk2vtza429.jpg-wh_50 "/>

This is the end of the experiment.

The first time to write is not too good, but also hope that more advice.

Database Data Recovery Experiment process