phpMyAdmin the reply process after deleting the table by mistake

phpMyAdmin the recovery process after deleting the table by mistake

Say today do not know is convulsions or lost soul, when using phpMyAdmin Delete test data, unexpectedly the entire table was deleted:

When the program runs out of error, the entire table is not there, and there is no backup! This egg hurts, this is production server, the data inside can not be lost ah!

The server is Linux, I am not very familiar with, I do not know where MySQL installed.

Helpless under, Google, found a lot of people also have like me silly one back, but almost no concrete solution (say with the hard drive software back, also say with binary files back), but I do not understand the server itself, feel good tangled, there is a reminder that the data how no, also said must find back.

I'm in a hurry and I'm not in the mood for dinner.

At this point, I found some technical personnel to help people recover data.

He asked me the amount of data, the database engine, and when I said it was ' MyISAM ', he came to the sentence: that's no solution.

I lost my tongue at the time.

The feeling of desperation, I think, before the testing server has a copy database, but there are some time, there is no latest one months of data.

There's no way to save me!

Here I see this in the phpMyAdmin:

It's out of the brain. Google said to use binary recovery, just click to open a look. Result exultation! OMG, the server opened the log!

There is a database operation log, but also the SQL format!!!

I haven't touched this thing before, but it's my lifeline.

I took a look, and it was enough to have a record of nearly one months of updates. However, there is a problem, in the phpmyadmin inside, can only show a small part of the content, face 10 a few W data row, want to find out the data of the deleted table, too difficult.

At this point, I thought I could download the files from the server and get the data.

I'll do it. Log in to the server and search for the binaries:

In the red circle.

These files are then exported to a readable sql:

This will output these binary files into a normal SQL file.

At this point, it is to find the deleted table related data from these files, so I wrote a Java program to help me do this thing:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75

package com.nerve.sql.reload; import java.io.BufferedReader; import java.io.BufferedWriter; import java.io.FileReader; import java.io.FileWriter; import java.util.ArrayList; import java.util.List; import org.nerve.util.NumberUtil; /** * @project: cloudOffice_swing * @file: ReloadWorker.java * @package: com.nerve.sql.reload * @description: * 将二进制日志导出的文件中相应表的操作记录提出出来 * @author: 集成显卡 [email protected] * @date&time: Jan 23, 2014 * @change log: */ public class ReloadWorker { public void read(List orgF, String targetF, String table) throws Exception{ BufferedWriter bw = new BufferedWriter(new FileWriter(targetF, true)); for(String or:orgF){ BufferedReader br = new BufferedReader(new FileReader(or)); String t = null; String t2 = null; table = table.toUpperCase(); while((t=br.readLine())!=null){ t2 = t.toUpperCase(); /* * 如果是update操作，直接提出 */ if(t2.startsWith("UPDATE "+table)){ bw.append(t+";\n"); } /* * 如果是insert语句，因为有一些旧服务器的数据 * 所以要先执行delete操作 */ else if(t2.startsWith("INSERT INTO "+table)){ String ids = t2.substring(t2.lastIndexOf(",")); bw.append("delete from "+table+" where id="+NumberUtil.toDigital(ids)+";\n"); bw.append(t+";\n"); } /* * sql语句后面都要加 ; ，因为原来没有，不加的话，在导入到数据库时，出错 */ } br.close(); } bw.flush(); bw.close(); } public static void main(String[] args) throws Exception{ long sd = System.currentTimeMillis(); ReloadWorker w = new ReloadWorker(); List orgs = new ArrayList (); orgs.add("C:/Users/IBM_ADMIN/Desktop/000015.txt"); orgs.add("C:/Users/IBM_ADMIN/Desktop/000016.txt"); orgs.add("C:/Users/IBM_ADMIN/Desktop/000017.txt"); orgs.add("C:/Users/IBM_ADMIN/Desktop/000018.txt"); orgs.add("C:/Users/IBM_ADMIN/Desktop/000019.txt"); String targetS = "C:/Users/IBM_ADMIN/Desktop/000017_sql.txt"; w.read(orgs, targetS, "task"); System.out.println("DONE, on " +(System.currentTimeMillis() - sd)/1000+" s"); } }

　　

After you get the summarized SQL file, you import it into the database.

Finally, a run diagram is shown:

Finally a sigh of relief. (Although this import took 5 minutes.) ）

Finally remind everyone, must always backup, cautious operation Ah!

