Experimental process of database data recovery
When entering the database operation, there will inevitably be some misoperations. At this point, we need to recover the data. Here is a description of how to delete the contents of the table and restore the process.
First, let's simulate the experimental environment:
Go to / etc/my.cnf after the database is installed
Check to see if logging is enabled (data recovery is based on logs. )
This environment has been set up.
Then go to the database and create a new library named not vianet_bss
Enter the library and create a new table named stb_active
Create table stb_active (SERVER_ID char (5), SERVER_CODE char (10), SERVER_NAME char (20), SERVER_DESC char (10), STATE char (5))
Then insert a few pieces of data
Insert into can insert data, and after a few pieces of data appear in the table, our data is created successfully.
Then we use delete from stb_active where SERVER_ID=2 to delete the content with ID 2 and restore it later.
Check again that the content with an ID of 2 is gone.
Then we use ps-ef | grep mysql to check where the MySQLdata is located, and the mysql-bin is placed under the data directory.
Then go to the data directory to find the latest log and check it. Of course, the garbled code needs to be converted to .sql and you can 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 and when we deleted the content.
Then find out that we just executed the order to delete the content.
And delete it, it cannot be restored at this time. If you perform a restore here, there will be data duplication. So we need to delete the data that is not what we want to restore. Avoid repetition, but make sure it is consistent with the data in the database.
The following is the result of not removing valid data from the log
After that, I delete all the data and re-import a piece of data to complete the data recovery.
This is the end of this experiment.
The first time published and written is not very good, but also hope to give more advice.
Attachment: http://down.51cto.com/data/2366598
