how to restore backup mysql file in php

Question

I have backup successfully but now i need to restore that data in localhost j my backup drive is C:\wamp\www\my Pawning Project\backup_restore this is my restore code.

<?php
include '../Connection/connect.php';
$restore_data = $_GET['restore_data'];//file time
$base="http://localhost/my%20Pawning%20Project/backup_restore/";
$query = "select * from backups where time='$restore_data'";
    $result = mysql_query($query);
    while ($row = mysql_fetch_array($result)) {
        $file_path =$row['file_name'];
    }

$sql=file_get_contents($base.$file_path);
mysql_query($sql);

if(mysql_query($sql))
    {
    /*Success*/
    echo "Successfully restored";
    }
else
    {
    /*Fail*/
    echo "Error: Fail to Restore";
    }
?>

Side note: you should not use mysql_* functions. More info here: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — rap-2-h, Jul 20 '16 at 15:29

DevLoots · Answer 1 · 2016-07-20T15:35:50.423

1

You can use exec (or shell_exec) function in php :

exec("mysql -hHost -uUsername -pPassword database < ~/backupdir/backupFile.sql")

edited Jul 20 '16 at 15:35

answered Jul 20 '16 at 15:30

DevLoots

747
1
6
21

score 1 · Answer 2 · answered Jul 20 '16 at 15:32

1

If you were to use Mysqli (recommended over mysql_*) you could use the following

$sql = file_get_contents($file_location);
if($conn->multi_query($sql)){
    while($conn->more_results() and $conn->next_result()){
        ;
    }
};

answered Jul 20 '16 at 15:32

CT14.IT

1,635
1
15
31

Have you tried this? How does it handle MySQL client builtin commands like `DELIMITER`? – Bill Karwin Jul 20 '16 at 15:34
I use this for an installation script that I set up for a client, so it does work as far as restoring a mysqldump backup for the projects I needed, but I couldn't confirm either way about the `DELIMITER` command, so that's something to look out for – CT14.IT Jul 20 '16 at 15:37
If you have triggers or stored procedures in your mysqldump, the delimiter needs to change, since semicolons occur within the body of those routines. But `multi_query()` doesn't support MySQL client builtin commands like `DELIMITER`, so this method will fail. – Bill Karwin Jul 20 '16 at 17:06

BeetleJuice · Answer 3 · 2016-07-20T15:39:21.967

You're running the restore query twice

mysql_query($sql);

if(mysql_query($sql))

Remove the 1st and just do:

if(mysql_query($sql))

If your backup file contains many queries, this won't work. You'll need to do one of the following:

Execute mysql directly and feed it the backup file (see Pierre's answer)
Switch from mysql_ to MySQLi functions and use mysqli::multi_query (docs)
Trnsform the file contents into an array of queries with explode(';',$sql) then execute the queries on at a time by looping through the aray (will be slow)

score 0 · Answer 4 · answered Jul 20 '16 at 15:32

0

You can't execute multiple statements with php mysql_* queries. Therefore you should first get fully qualified path of the file you want to use and execute a SQL statement:

$path = $base . $file_path;
$query = "SOURCE $path"

mysql_query($query);

answered Jul 20 '16 at 15:32

Ryan

14,392
8
62
102

Have you tried this? `SOURCE` is a builtin of the MySQL client. You can't run it as a query. – Bill Karwin Jul 20 '16 at 15:33
Is that so? If it is I'll delete the answer. – Ryan Jul 20 '16 at 15:34

how to restore backup mysql file in php

4 Answers4

Linked