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 or MySQLi - this article will help you decide which. — Benjamin Gruenbaum
– Benjamin Gruenbaum, Commented Aug 27, 2013 at 5: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
– Burhan Khalid, Commented Aug 27, 2013 at 5: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
– Abdul Hamid, Commented Aug 27, 2013 at 6:08

sudhakar · Accepted Answer · 2013-08-27 05:55:49Z

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, 2013 at 5:55

sudhakar

5721 gold badge3 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Abdul Hamid Over a year ago

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?

sudhakar Over a year ago

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 Over a year ago

the above code will work, just give a sql path and run this script it will insert data into database

Abdul Hamid Over a year ago

$qry = file_get_contents($source); is not yeilding me any results, checked filename, which exists still then file_get_contents is not displaying anything.

Nir Alfasi · Accepted Answer · 2013-08-27 05:39:11Z

0

-- 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;

answered Aug 27, 2013 at 5:39

Nir Alfasi

53.6k11 gold badges93 silver badges138 bronze badges

Comments

Marc Delisle · Accepted Answer · 2013-08-27 14:23:25Z

0

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

answered Aug 27, 2013 at 14:23

Marc Delisle

9,0253 gold badges32 silver badges29 bronze badges

Comments

Davide Arduini · Accepted Answer · 2017-01-24 11:53:12Z

0

//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]);
}

answered Jan 24, 2017 at 11:53

Davide Arduini

1

Collectives™ on Stack Overflow

PHP script to restore a database into another empty database

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related