phpmyadmin误删表后的恢复过程（心惊胆跳啊）
话说今天不知道是抽风了还是失魂了，在用phpmyadmin删除测试数据时，竟然将整个表删除了： 等程序运行出错时，才出现整个表都没有了，而且之前也没有备份好！这下蛋疼了，这个可是production服务器，里面的数据可不能丢啊！ 服务器是linux的，我不是很熟悉，
话说今天不知道是抽风了还是失魂了，在用phpmyadmin删除测试数据时，竟然将整个表删除了：
等程序运行出错时，才出现整个表都没有了，而且之前也没有备份好！这下蛋疼了，这个可是production服务器，里面的数据可不能丢啊！ 服务器是linux的，我不是很熟悉，也不知道mysql装在哪。无奈之下，google，发现有不少人也有像我一样犯傻的一回，但是几乎没有看到具体的解决方法（有说用硬盘软件找回，也有说用二进制文件找回），但是我本身对服务器不大懂，觉得好纠结，那边又在催说数据怎么没有了，还说一定得找回来。这下我更急了，晚饭都没心情吃。 这时，我找到了一些专门帮人恢复数据的技术人员。他问了我数据量，数据库引擎，当我说是‘myisam’时，他来了句：那无解了。。当时我一下子懵了。 情急之下，我想到了，之前在testing服务器里面有一个副本数据库，但是那里的数据已经有些时日了，根本没有最新一个月的数据。这根本无法解救我！ 这里我在phpmyadmin中看到了这个： 
脑里出来了google上说的用二进制恢复，就点开看一下。结果大喜！OMG，服务器开通了日志！ 
里面有数据库的操作日志，而且还是sql格式的！！！我之前没有接触过这个东西，但这时它就是我的救命稻草。我浏览了一下，里面有近一个月的更新记录，已经足够了。不过有一个问题，在phpmyadmin里面看，只能显示其中一小部份内容，面对10几w的数据行，想要一个个找出被删表的数据，太难了。 这时，我想到了可以从服务器中下载这些文件，然后得到其中的数据。说干就干，我登录服务器，搜索这些二进制文件： 
上图中红圈的就是了 然后将这些文件一个个导出成可以阅读的sql： 
这样将这些二进制文件一个个输出成了正常的sql文件。 这时，就是要从这些文件中找到被删表相关的数据了，于是我写了个java程序帮我做这个事情：
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: 集成显卡 1053214511@qq.com
* @date&time: Jan 23, 2014
* @change log:
*/
public class ReloadWorker {
public void read(List<String> 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<String> orgs = new ArrayList<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");
}
}
终于松了一口气。（虽然这次导入花了5分钟。。） 最后提醒大家，一定要常备份，谨慎操作啊！
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to set primary key in phpmyadmin
Apr 07, 2024 pm 02:54 PM
The primary key of a table is one or more columns that uniquely identify each record in the table. Here are the steps to set a primary key: Log in to phpMyAdmin. Select database and table. Check the column you want to use as the primary key. Click "Save Changes". Primary keys provide data integrity, lookup speed, and relationship modeling benefits.
Where is the phpmyadmin log?
Apr 07, 2024 pm 12:57 PM
Default location for PHPMyAdmin log files: Linux/Unix/macOS:/var/log/phpmyadminWindows: C:\xampp\phpMyAdmin\logs\ Log file purpose: Troubleshooting Audit Security
How to add foreign keys in phpmyadmin
Apr 07, 2024 pm 02:36 PM
Adding a foreign key in phpMyAdmin can be achieved by following these steps: Select the parent table that contains the foreign key. Edit the parent table structure and add new columns in "Columns". Enable foreign key constraints and select the referencing table and key. Set update/delete operations. save Changes.
Where does the wordpress database exist?
Apr 15, 2024 pm 10:39 PM
The WordPress database is housed in a MySQL database that stores all website data and can be accessed through your hosting provider’s dashboard, FTP, or phpMyAdmin. The database name is related to the website URL or username, and access requires the use of database credentials, including name, username, password, and hostname, which are typically stored in the "wp-config.php" file.
What is the password for the phpmyadmin account?
Apr 07, 2024 pm 01:09 PM
The default username and password for PHPMyAdmin are root and empty. For security reasons, it is recommended to change the default password. Method to change password: 1. Log in to PHPMyAdmin; 2. Select "privileges"; 3. Enter the new password and save it. When you forget your password, you can reset it by stopping the MySQL service and editing the configuration file: 1. Add the skip-grant-tables line; 2. Log in to the MySQL command line and reset the root password; 3. Refresh the permission table; 4. Delete skip-grant-tables line, restart the MySQL service.
why phpmyadmin access denied
Apr 07, 2024 pm 01:03 PM
Reasons and solutions for access denied by phpMyAdmin: Authentication failed: Check whether the username and password are correct. Server configuration error: adjust firewall settings and check whether the database port is correct. Permissions issue: Granting users access to the database. Session timeout: Refresh the browser page and reconnect. phpMyAdmin configuration error: Check the configuration file and file permissions to make sure the required Apache modules are enabled. Server issue: Wait for a while and try again or contact your hosting provider.
How to delete data table in phpmyadmin
Apr 07, 2024 pm 03:00 PM
Steps to delete a data table in phpMyAdmin: Select the database and data table; click the "Action" tab; select the "Delete" option; confirm and perform the deletion operation.
Where is the associated view of phpmyadmin
Apr 07, 2024 pm 01:00 PM
Related views can be found in the Views submenu under the Structure tab in phpMyAdmin. To access them, simply select the database, click the "Structure" tab, and then click the "View" submenu.
