How to restore a mysql database in php

Question

I am trying to restore a mysql database using php. I googled and found out this piece of code :-

<?php

$restore_file  = "backup.bkp";
$server_name   = "localhost";
$username      = "root";
$password      = "pass";
$database_name = "db";

$cmd = "mysql -h {$server_name} -u {$username} -p{$password} {$database_name}  
<  $restore_file";
exec($cmd);

?>

I am using a shared server (linux) and the file was restored using phpmyadmin successfully. But I want to use php to restore. The code does not work. Where am I being wrong?

Make echo $cmd; and run the printed string in console. Most probably the problem is in new line symbol before < (if, of course, it is not there and you made it to format the code in question). Or path to $restore_file is invalid. — Cheery
– Cheery, Commented Oct 1, 2014 at 4:16
If it's a shared server it is quite likely that your scripts will not have permission to use exec. Make sure your error_reporting is set to E_ALL and let us know what messages you are getting. — Scopey
– Scopey, Commented Oct 1, 2014 at 4:17

Deval Khandelwal · Accepted Answer · 2014-10-01 05:56:58Z

Thanks to @Scoopy, I found that exec() was disabled my by hosting provider. However, I have solved my problem using the following code I found here :-

$db = mysql_connect ( 'localhost', 'username', 'pass' ) or die('not connected');
mysql_select_db( 'test', $db) or die('Not found');
$fp = fopen ( 'backup-file', 'r' );
$fetchData = fread ( $fp, filesize ( 'backup-file') );
$sqlInfo = explode ( ";\n", $fetchData); // explode dump sql as a array data    

foreach ($sqlInfo AS $sqlData )
{
mysql_query ( $sqlData ) or die('Query not executed');
}

BUT, THE CODE IS DEPRECATED. So, I made up this code :-

$host = 'localhost'; 
$user = 'root';
$password = 'root';
$database = 'test';

$conn = new mysqli($host, $user, $password, $database);
$conn->autocommit(FALSE);

$fp = fopen('bkp-file', 'r');
$fetchData = fread($fp, filesize('bkp-file'));
$sqlInfo = explode(";\n", $fetchData); // explode dump sql as a array data

foreach ($sqlInfo AS $sqlData) {
    $conn->query($sqlData);
}
$conn->commit();
$conn->close();

This code is perfect to use. It uses transactions to prevent multiple writes.

Thanks very much, this code works perfectly. though I need some help on how to restore to a different schema from the original one. Thanks again

Scopey · Accepted Answer · 2014-10-01 04:30:50Z

Generally a shared hosting server will disable access to system functions as it introduces a security risk for them. You can confirm this if your server provider allows you to use the phpinfo command (docs).

Create a page that dumps the PHP info and then look on that page for a setting called disable_functions - it's under the "Core" section. If exec appears there then you will know that exec is a disabled function and you will not be able to use it. Unfortunately one of the drawbacks of using some shared hosting services as it is impossible/very difficult to implement the sorts of things you are talking about.

Sorry..had to remove the tick.coz had to show how i solved the problem..thus ticking my own answer (in the next 2 days) but +1 for your answer

meda · Accepted Answer · 2014-10-01 04:20:27Z

0

I don't see why you need PHP, you can do this directly in the terminal.

Backup

mysqldump -u root -p db> backup.bkp

Restore

mysql -u root -p db < backup.bkp

answered Oct 1, 2014 at 4:20

meda

45.5k14 gold badges96 silver badges123 bronze badges

2 Comments

Deval Khandelwal Over a year ago

I do not have access to the terminal

meda Over a year ago

what do you have access to, how do you access the PHP server? you can still hardcode this command in your php if you have no choice. you can omit the password

Collectives™ on Stack Overflow

How to restore a mysql database in php

3 Answers 3

1 Comment

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Related