PHP script to restore a database into another empty database

Question

This question might seem a duplicate, However i tried all the examples to restore my database but none seems to work for me. I am trying to run a script that will restore my backup sql file to new database. I tried these lines to restore my database, but none seem to work.

     $mysql_host = 'localhost';
     $mysql_username = 'my_username';
     $mysql_password = 'somepassword';
     $db_name = 'test_db';


    $source = 'C:/wamp/www/my_folder/test_db.sql';

    $conn = mysql_connect( $mysql_host, $mysql_username, $mysql_password ) or die('Error connecting to MySQL server: ' . mysql_error());

    mysql_query("CREATE DATABASE $db_name", $conn ) or die('Error connecting to MySQL server: ' . mysql_error());

    restore_my_database( $mysql_host, $mysql_username, $mysql_password, $db_name, $source );

    function restore_my_database( $mysql_host, $mysql_username, $mysql_password,  $db_name, $source ) {

        exec("mysql --opt -h $mysql_host -u $mysql_username -p $mysql_password $db_name < $source");
    }

I also tried in function restore_my_database following lines

$command = "mysqldump --opt -h $mysql_host -u $mysql_username -p $mysql_password $db_name > $source";
system($command);

Upto database create, the code is working fine, but restore is not working. Can any one help me with the php restore code to restore my database. Thanks in advance

Is this new code? If it is you might want to consider avoiding `mysql_*` functions (they're deprecated). Instead, consider using [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Benjamin Gruenbaum, Aug 27 '13 at 05:37
The `mysql` and the `mysqldump` commands are not in the path for the user executing the script. Use the full path to these commands and try again. — Burhan Khalid, Aug 27 '13 at 05:58
I have actually used full path only like the one given exec("C:/wamp/bin/mysql/mysql5.5.8/bin/mysql --opt -h $mysql_host -u $mysql_username -p $mysql_password $db_name < $source"); — Abdul Hamid, Aug 27 '13 at 06:08

score 2 · Answer 1 · answered Aug 27 '13 at 05:55

2

you can achieve it by below code, first get content from .sql file and then execute the query

$source = 'C:/wamp/www/my_folder/test_db.sql';

$conn = mysql_connect( $mysql_host, $mysql_username, $mysql_password ) or die('Error connecting to MySQL server: ' . mysql_error());

mysql_query("CREATE DATABASE $db_name", $conn ) or die('Error connecting to MySQL server: ' . mysql_error());
mysql_select_db($db_name);
$qry = file_get_contents($source);
mysql_query($qry, $conn);

and also increase max execution time, memory limit in php.ini file

answered Aug 27 '13 at 05:55

sudhakar

582
1
3
13

Thanks for your help. I want the restore from source file to destination file. Will the above code do so or do? what else do i need to do? – Abdul Hamid Aug 27 '13 at 06:03
this script will update the database, not in files, if you want to write in file you can do it by write the file contents to the destination file using file cancepts – sudhakar Aug 27 '13 at 06:08
the above code will work, just give a sql path and run this script it will insert data into database – sudhakar Aug 27 '13 at 06:29
$qry = file_get_contents($source); is not yeilding me any results, checked filename, which exists still then file_get_contents is not displaying anything. – Abdul Hamid Aug 27 '13 at 07:33

score 0 · Answer 2 · answered Aug 27 '13 at 05:39

-- you have to create a schema:

mysql
mysql> create database [your schema name];
mysql> grant all privileges on [your schema name].* to [your username]@localhost identified by '[your password]';

-- create sql dump on your computer:

mysqldump [your schema name] > [your schema name].sql

-- and import it to the new DB:

mysql [your schema name] <   [your schema name].sql;

score 0 · Answer 3 · answered Aug 27 '13 at 14:23

0

Since you are tagging this with "phpmyadmin", try with phpMyAdmin to create your database then import your .sql file.

answered Aug 27 '13 at 14:23

Marc Delisle

8,879
3
29
29

score 0 · Answer 4 · answered Jan 24 '17 at 11:53

//recupero il nome del file .sql
$file = "C:/wamp/www/my_folder/test_db.sql";

//definisco il nome del database
$db = 'name_db';

//effettuo la connessione al databse
$conn = mysqli_connect($mysql_host, $mysql_username, $mysql_password) or die('Errore di connessione al server MySQL: ' . mysqli_error());

//seleziono il database
mysqli_select_db($conn,$db);

//leggo il contenuto del file .sql
$qry = file_get_contents($file);

//divido tutte le query presenti nel file .sql
$query = explode(";",$qry);

//conto quante query esistono
$arrlenght = count($query);

//ciclo tutte le query del file
for($i=0;$i<$arrlenght;$i++){
    mysqli_query($conn, $query[$i]);
}

PHP script to restore a database into another empty database

4 Answers4