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 saw this in the phpMyAdmin: The brain came out of the Google said with the 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 these binaries: it's in the red circle. Then export these files to a readable sql: This will output the 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:

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465 66676869707172737475

packagecom.nerve.sql.reload; importjava.io.BufferedReader;importjava.io.BufferedWriter;importjava.io.FileReader;importjava.io.FileWriter;import java.util.ArrayList;importjava.util.List; importorg.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: */publicclassReloadWorker {        publicvoidread(List<String> orgF, String targetF, String table) throwsException{        BufferedWriter bw = newBufferedWriter(newFileWriter(targetF, true));                for(String or:orgF){            BufferedReader br = newBufferedReader(newFileReader(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操作                 */                elseif(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();    }        publicstaticvoidmain(String[] args) throwsException{        long sd = System.currentTimeMillis();        ReloadWorker w = newReloadWorker();        List<String> orgs = newArrayList<String>();        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, out of a run diagram: finally relieved. (Although this import took 5 minutes.) Finally remind everyone, must always backup, cautious operation Ah!

phpMyAdmin the recovery process after deleting the table by mistake