How to Restore Large Database with PHP Script

Restoring a large MySQL database can sometimes be a daunting task, especially when using phpMyAdmin, which may fail due to size limitations or timeouts. In such cases, utilizing SSH/Terminal is often the go-to solution. However, if you lack SSH access, you can still restore your database using a simple PHP script.

Create a PHP Script

Create a new PHP file, for example, restore.php, and add the following code:

<?php
# Author: ServerOK
# Web: https://serverok.in

$mysqlDatabase = 'MYSQL_DB_NAME';
$mysqlUser = 'MYSQL_USER';
$mysqlPassword = 'MYSQL_PW';
$backupFile = 'BACKUP_FILE.sql';

// Increase execution time limits
ini_set('max_execution_time', 0);
set_time_limit(0);
ini_set('mysql.connect_timeout', 0);
ini_set('default_socket_timeout', 0);

$sqlCmd = "mysql -u $mysqlUser -p'$mysqlPassword' $mysqlDatabase < $backupFile";

exec($sqlCmd . " 2>&1", $output, $returnValue);

if ($returnValue == 0) {
    echo "<h1 style='color:green'>Database restored successfully.</h1>";
} else {
    echo "<h2 style='color:red'>ERROR: Database restore failed.</h2>";
    echo "cd " . __DIR__ . "<br>";
    echo "$sqlCmd<br>";
    echo "<pre>";
    print_r($output);
    var_dump($returnValue);
}

Replace Placeholder Values

In the script above, replace the following placeholders with your actual values:

MYSQL_DB_NAME: The name of the database you want to restore.
MYSQL_USER: Your MySQL username.
MYSQL_PW: Your MySQL password.
BACKUP_FILE.sql: The name of your backup file.

Upload the Backup File

Upload your .sql backup file to your server’s document root folder. This is typically the same location where your restore.php script will reside. You can use FTP or File Manager to do it.

On cPanel server, the directory name is public_html, for Plesk, it is httpdocs. If you use another control panel, check with the control panel documentation.

Execute the Script

Now, navigate to your script in a web browser by entering the following URL:

http://yourdomain.com/restore.php

Delete Restore Script

After the restoration process, ensure you delete restore.php and the MySQL backup (.sql) file from your server to prevent any unauthorized access.

Back MySQL Restore