Recover accidentally removed table files from a MySQL Server

How to recover MySQL database files accidentally deleted if someone accidentally deleted all the table files from a MySQL Server data directory / from a Linux shell script?

We will be surprised to find that the linux servers has continued to respond to requests and that the website is still fully operational, even if /var/lib/ mysql/ folder is completely empty! The reason for this in a nutshell: The rm command only removed the reference to the table files in the database directory, the files themselves were not removed from the file system yet, because the mysqld process still had the files opened. As long as a process keeps the files open, the kernel will not release the disk space occupied by the files and will remain intact but is no longer visible.

Solution 1 – You can use this mysql dump command : mysqldump.

mysqldump -u USER -pPASSWORD DATABASE > DATABASE_BACKUP.sql

but for me, this command not working, return ZERO !!!
So … next solution.

Login in mysql:

mysql -u USER -pPASSWORD DATABASE

and save all table manual, step-by-step

SELECT * FROM TABLE1 -> INTO OUTFILE ‘TABLE1.txt’;
…
SELECT * FROM TABLEn -> INTO OUTFILE ‘TABLEn.txt’;

and all file will be saved in lib/mysql folder.

After this step you must create new database like deleted database but with empy filed. Database must be 100% with the same structure, tables, filed, etc…

Copy all saved files in lib/mysql/NEW_DATABASE folder

now, login in mysql again:

mysql -u USER -pPASSWORD NEW_DATABASE

and load files in table with mysql command:

LOAD DATA INFILE ‘TABLE1.txt’ INTO TABLE NEW_DATABASE.TABLE1 ;
…
LOAD DATA INFILE ‘TABLEn.txt’ INTO TABLE NEW_DATABASE.TABLEn ;

DONE … accidentally  deleted database was be restored !

3). Other solution – php scripting:

 <?php

function get_struct_table($server, $user, $pass, $db, $table) {
    mysql_connect($server, $user, $pass);
    mysql_select_db($db);
    
    $r = mysql_query("SHOW CREATE table `$table`");
    if ($r) {
        $insert_sql = "";
        $d          = mysql_fetch_array($r);
        $d[1] .= ";";
        $SQL         = str_replace("\n", "", $d[1]);
        $table_query = mysql_query("SELECT * FROM `$table`");
        $num_fields  = mysql_num_fields($table_query);
        while ($fetch_row = mysql_fetch_array($table_query)) {
            $insert_sql .= "INSERT INTO $table VALUES(";
            for ($n = 1; $n <= $num_fields; $n++) {
                $m = $n - 1;
                $insert_sql .= "'" . mysql_real_escape_string($fetch_row[$m]) . "', ";
            }
            $insert_sql = substr($insert_sql, 0, -2);
            $insert_sql .= ");\n";
        }
        if ($insert_sql != "") {
            $SQL = $insert_sql;
        }
    }
    
    return $SQL;
}

// table from Wordpress blog (example)

$table_manual = array(
    'wp_posts',
    'useronline',
    'useronliney',
    'wp_comments',
    'wp_links',
    'wp_options',
    'wp_postmeta',
    'wp_posts',
    'wp_searchmeter',
    'wp_similar_posts',
    'wp_term_relationships',
    'wp_terms',
    'wp_term_taxonomy',
    'wp_usermeta',
    'wp_users'
);

$cnt_tab = count($table_manual);

for ($i = 0; $i < $cnt_tab; $i++) {
    $table   = $table_manual[$i];
    $content = get_struct_table('localhost', 'USER', 'PASSWORD', 'DELETED_DATABASE', $table);
    $fp      = fopen('backupsql/' . $table . '.sql', 'w');
    fwrite($fp, $content);
    fclose($fp);
    echo 'OK...' . $table . '<br />';
}
?>

Atention: Php time-limit script execution must be high, maybe 10 minutes (or more) for big database. You can save secvential/succesives tables:

for ($i=3; $i<$cnt_tab; $i++) (from table 3 to max)
# or
for ($i=10; $i<$cnt_tab; $i++) (from table 10 to max)

until all tables will be saved

Atention: chmod for backupsql/ folder must be 777 !

Saved files can be used for import with phpMyAdmin interface.

Note: this tricks only works on table files that were removed on the file system level, not after you used DROP TABLE/DATABASE,

if you used DROP TABLE/DATABASE only Good can help-you to recover them !