How to recover accidentally deleted MySQL database files if someone accidentally removed all table files from a MySQL Server’s data directory with a linux shell script? Will be surprised to find out that the linux server continued to serve requests and the web site is still fully operational, even though /var/lib/mysql/ is completely emtpy! The reason for this in a nutshell: the rm command only removed the reference to the table files from the database directory, the files itself were not removed from the file system yet as the mysqld process still had the files opened. So as long as a process keeps a file open, the kernel will not release the disk space occupied by the file and it will remain intact, but no longer visible.

1.st solution: You can use is 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’;

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 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 … Database was be restored.

Other solution is 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 3-10 minutes 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 that this tricks only works on table files that were removed on the file system level, not after you used DROP TABLE/DATABASE,