Script for restoring crashed MySQL innodb database tables. It requires backup of MySQL database folder containing .frm and .ibd files. Description: https://pakjiddat.netlify.app/posts/recovering-innodb-data-after-accidental-removal-of-log-files

restore_innodb.php

<?php

$args             = ParseArgs();

$src_db           = $args["src_db"];
$target_db        = $src_db . "_restore";
$backup_folder    = $args["backup_folder"];
$backup_folder    .= $src_db;
$src_db_folder    = $args["mysql_data_dir"] . $src_db;
$target_db_folder = $args["mysql_data_dir"] . $target_db;
$mysql_user       = $args["user"];
$mysql_pass       = $args["password"];
$log_file         = $args["log_file"];

$file_list        = scandir($src_db_folder);
$file_list        = array_filter($file_list, function($file_name){return (strpos($file_name, ".ibd") !== false);});


$log_output       = LogMsg("\nCreating target database: " . $target_db."\n");
echo shell_exec("mysql -u " . $mysql_user . " -p" . $mysql_pass . " -e 'DROP DATABASE IF EXISTS " . $target_db . ";CREATE DATABASE " . $target_db . "'");
$log_output       .= LogMsg("Restoring " . count(array_values($file_list)) . " tables from " . $src_db . " database\n\n");

$count = 0;
foreach ($file_list as $index => $data_file_name) {
    $frm_file_name = str_replace(".ibd", ".frm", $data_file_name);
    $table_name    = str_replace(".ibd", "", $data_file_name);
    $export_frm    = "mysqlfrm --server='" .$mysql_user . ":" . $mysql_pass . "@localhost' " . $src_db_folder . "/" . $frm_file_name . "   --port=3307 --user=root\n";
    $cmd           = shell_exec($export_frm);
    $cmd           = str_replace("WARNING: Using a password on the command line interface can be insecure.", "", $cmd);
    $cmd           = str_replace($src_db, $target_db, $cmd);
    $fh            = fopen ("export_frm.sh", "w");
    fwrite($fh, $cmd);
    fclose($fh);
    echo shell_exec("mysql -u " . $mysql_user . " -p" . $mysql_pass . " " . $target_db . " < /root/scripts/export_frm.sh");
    echo shell_exec("mysql -u " . $mysql_user . " -p" . $mysql_pass . " " . $target_db . " -e 'ALTER TABLE " . $table_name . " DISCARD TABLESPACE'");
    copy($backup_folder . "/" . $data_file_name, $target_db_folder . "/" . $data_file_name);
    echo shell_exec("chown mysql:mysql " . $target_db_folder . "/" . $data_file_name);
    echo shell_exec("chmod 660 " . $target_db_folder . "/" . $data_file_name);
    echo shell_exec("mysql -u " . $mysql_user . " -p" . $mysql_pass . " " . $target_db . " -e 'ALTER TABLE " . $table_name . " IMPORT TABLESPACE'");

    echo shell_exec("mysql -u " . $mysql_user . " -p" . $mysql_pass . " " . $src_db . " -e 'DROP TABLE " . $table_name . "'");
    unlink($src_db_folder . "/" . $data_file_name);
    echo shell_exec("mysqldump -u " . $mysql_user . " -p" . $mysql_pass . " " . $target_db . " > table_dump.sql");
    echo shell_exec("mysql -u " . $mysql_user . " -p" . $mysql_pass . " " . $src_db . " < table_dump.sql");
    unlink("table_dump.sql");
    unlink("export_frm.sh");

    $row_count = shell_exec("mysql -u " . $mysql_user . " -p" . $mysql_pass . " " . $src_db . " -e 'SELECT count(*) AS total FROM " . $table_name . "'");
    $row_count = str_replace("total\n", "", $row_count);
    $log_output .= LogMsg(($count+1) . ". Restored table: " . $table_name. " with row count: " . $row_count."\n");
    $count++;
}

SaveLog($log_file, $log_output);
echo shell_exec("mysql -u " . $mysql_user . " -p" . $mysql_pass . " -e 'DROP DATABASE IF EXISTS " . $target_db . "'");

function ParseArgs() {
    global $argv;
    
    $msg = <<<EOT
    
Usage: php restore_innodb.php --src-db --backup-folder --mysql_data_dir --log_file --user --password

options:
  --src-db: the name of the database containing crashed innodb tables
  --backup-folder: the path to the MySQL database directory. it is usually /var/lib/mysql
  --mysql_data_dir: the path to the MySQL data folder
  --log_file: the path to the log file generated by this script
  --user: the MySQL root user
  --password: password for the MySQL root user
  
example: 

php restore_innodb.php --src-db="mysql" --backup-folder="/root/scripts/data-backup/" --mysql_data_dir="/var/lib/mysql" --log_file="/root/scripts/restore_innodb.log" --user="root" --password="root-password"


EOT;

    if (count($argv) != 7) die($msg);
    
    $args["src-db"]          = str_replace("--src-db=", "", $argv[1]);
    $args["backup-folder"]   = str_replace("--backup-folder=", "", $argv[2]);
    $args["mysql_data_dir"]  = str_replace("--mysql_data_dir=", "", $argv[3]);
    $args["log_file"]        = str_replace("--log_file=", "", $argv[4]);
    $args["user"]            = str_replace("--user=", "", $argv[5]);
    $args["password"]        = str_replace("--password=", "", $argv[6]);
    
    return $args;
}

function LogMsg($msg) {
    echo $msg;
    flush();
    return $msg;
}

function SaveLog($log_file, $log_output) {
    $fh = fopen($log_file, "w");
    fwrite($fh, $log_output);
    fclose($fh);
}
?>