Restore database from file on ftp - script

Hi,

I have a script to make a copy of database and save it as sql file into 'db_backup' folder.
What I need is a php script (can be with jquery) to display names of all backups (files in 'db_backup' folder) in dropdown menu, when you click one of them - display some confirmation box and then restore database from the chosen file.

Thanks for any help or hint.
LVL 8
ZadoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
To list the files in a directory, you can use this:
http://us3.php.net/manual/en/function.readdir.php

Do you have a script now that will restore the copy?
ZadoAuthor Commented:
Thanks.
I need the script to restore database too.
Ray PaseurCommented:
Have you thought about asking your hosting company if they back up your information?  If I were looking at this issue, I think I would want to set up a test case and hire a professional developer to help me build the application.  It sounds too important to be left to chance or to any semi-tested answers at EE.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

ZadoAuthor Commented:
I can restore the database at phpMyAdmin obviously, but I need a php script doing same thing on clients website (admin page). It's not complex database, less than 200 records, but changing regularly.

I appreciate any idea, thanks.
gr8gonzoConsultantCommented:
I don't see any information about which database you are using. I'm taking a guess that you're using MySQL - if so, the best way to restore a copy is to use the mysql command line tool, like so:

mysql -u[YourUsername] -p[YourPassword] [DatabaseName] < /path/to/your/backup/file.sql

You would replace the values so they don't have brackets, like:

mysql -uroot -psecret123 mydb < /tmp/restore5590.sql

The bigger problem is that running this script through your web server will most likely entail security issues. Most web servers are not set up to allow PHP scripts to execute system commands like that (at least not without some security-related changes to the system).

What is your hosting setup like? Do you  have administrative / root access over the server or is it a shared hosting setup?
Ray PaseurCommented:
How many tables are involved?
ZadoAuthor Commented:
I have 1and1 business hosting (1and1.co.uk) and MySQL database, it's quite basic database: one table, four columns, below 200 records. Not sure it will help, but this is working php script to make a copy of database (maybe will give some idea):
<?php
backup_tables('localhost','dbo2926642','password','db2926642');
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
 $link = mysql_connect($host,$user,$pass);
 mysql_select_db($name,$link);

 //get all of the tables
 if($tables == '*')
 {
 $tables = array();
 $result = mysql_query('SHOW TABLES');
 while ($row = mysql_fetch_row($result))
 {
 $tables[] = $row[0];
 }
 }
 else
 {
 $tables = is_array($tables) ? $tables : explode(',',$tables);
 }

 //cycle through
 foreach($tables as $table)
 {
 $result = mysql_query('SELECT * FROM '.$table);
 $num_fields = mysql_num_fields($result);
 $return.= 'DROP TABLE '.$table.';';
 $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
 $return.= "\n\n".$row2[1].";\n\n";
 for ($i = 0; $i < $num_fields; $i++)
 {
 while($row = mysql_fetch_row($result))
 {
 $return.= 'INSERT INTO '.$table.' VALUES(';
 for($j=0; $j<$num_fields; $j++)
 {
 $row[$j] = addslashes($row[$j]);
 $row[$j] = ereg_replace("\n","\\n",$row[$j]);
 if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
 if ($j<($num_fields-1)) { $return.= ','; }
 }
 $return.= ");\n";
 }
 }
 $return.="\n\n\n";
 }

 //save file
 $date = date('d.m.Y_');
 $time = date('H')-1;
 $handle = fopen('db_backup/backup_'.$date.$time.'.sql','w+');
 fwrite($handle,$return);
 fclose($handle);
}
?>

Open in new window

te-eduCommented:
I just wrote that kind of PHP class. Complete backup to file remote upload on fly http, https, ftp, ftps. That is part of  one function. This is sample just adjust for your needs.

		
 $externalFileContent = explode(';', $externalFileContent); // before you get content from backup file
				
				 array_pop($externalFileContent);
					
				  foreach ($externalFileContent as $query) {
					  
					
					$result = $this->sql_query($query, 'EXTERNAL FILE');
					
				
						if (!$result) {
							$this->errorMessage("PROBLEM DURING EXTERNAL FILE EXECUTING");
						}
					 
				  }


}

Open in new window

Ray PaseurCommented:
Here is a script that will back up a data base.
<?php // RAY_mysql_backup_dump.php
error_reporting(E_ALL);
date_default_timezone_set("America/Chicago");


// PATTERN OF THE NAME OF THE BACKUP FILES
// 'backups/mysql' . date('Ymd\THis') . $db_name . '.txt';


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "??"; // PROBABLY 'localhost' IS OK
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
    die();
}


// GET A LIST OF THE DATA BASES ON THIS CONNECTION
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-list-dbs.php
if (!$db_list = mysql_list_dbs($db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB LIST: ";
    echo "<br/> $errmsg <br/>";
    die();
}


// ITERATE OVER THE LIST OF NAMES TO MAKE AN ARRAY
while ($row = mysql_fetch_object($db_list))
{
    $db_names[] = $row->Database ;
}


// ANYTHING IN POST DATA YET?
if (empty($_POST["d"]))
{
// NOTHING POSTED - ASK CLIENT TO CHOOSE THE DATA BASE
    echo "<form method=\"post\">\n";
    echo "BACK UP A DATA BASE:<br/>";
    foreach ($db_names as $db_name)
    {
        echo "<input type=\"radio\" name=\"d\" value=\"$db_name\">$db_name <br/>\n";
    }
    echo "<input type=\"submit\" />\n";
    echo "</form>\n";
    die();
}


// THERE IS A RADIO BUTTON IN $_POST
if (!in_array($_POST["d"], $db_names)) die("ERROR: DATABASE {$_POST["d"]} NOT FOUND");
$db_name = $_POST["d"];


// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}


// SET THE NAME OF THE BACKUP WITH A TIMESTAMP
$bkup = 'backups/mysql' . date('Ymd\THis') . $db_name . '.txt';
$fp   = fopen($bkup, "w");


// GET THE LIST OF TABLES
$sql = "SHOW TABLES";
$res = mysql_query($sql);
if (!$res) die( mysql_error() );
if (mysql_num_rows($res) == 0) die( "NO TABLES IN $db_name" );
while ($s = mysql_fetch_array($res))
{
    $tables[] = $s[0];
}


// ITERATE OVER THE LIST OF TABLES
foreach ($tables as $table)
{

// WRITE THE DROP TABLE STATEMENT
    fwrite($fp,"DROP TABLE `$table`;\n");

// GET THE CREATE TABLE STATEMENT
    $res = mysql_query("SHOW CREATE TABLE `$table`");
    if (!$res) die( mysql_error() );
    $cre = mysql_fetch_array($res);
    $cre[1] .= ";";
    $txt = str_replace("\n", "", $cre[1]); // FIT EACH QUERY ON ONE LINE
    fwrite($fp, $txt . "\n");

// GET THE TABLE DATA
    $data = mysql_query("SELECT * FROM `$table`");
    $num  = mysql_num_fields($data);
    while ($row = mysql_fetch_array($data))
    {

// MAKE INSERT STATEMENTS FOR ALL THE VALUES
        $txt = "INSERT INTO `$table` VALUES(";
        for ($i=0; $i < $num; $i++)
        {
            $txt .= "'".mysql_real_escape_string($row[$i])."', ";
        }
        $txt = substr($txt, 0, -2);
        fwrite($fp, $txt . ");\n");
    }
}
// ALL DONE
fclose($fp);

// SHOW THE LINK TO THE BACKUP FILE
echo "<br/>BACKUP OF $db_name CREATED HERE:\n";
echo "<br/><a href=\"$bkup\">$bkup</a>\n";

Open in new window

Ray PaseurCommented:
Here is a script that will restore the data base from the backup.
<?php // RAY_mysql_backup_restore.php
error_reporting(E_ALL);


// PATTERN OF THE NAME OF THE BACKUP FILES
// 'RAY_backups/mysql' . date('Ymd\THis') . $db_name . '.txt';


// IMPORTANT PAGES FROM THE MANUALS
// MAN PAGE: http://us2.php.net/manual/en/ref.mysql.php
// MAN PAGE: http://us2.php.net/manual/en/mysql.installation.php


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "??"; // PROBABLY 'localhost' IS OK
$db_user = "??";
$db_word = "??";


// FIND THE BACKUP FILES
if (!$my_dir = scandir('backups',1)) die('NO BACKUP DIRECTORY FOUND');

// IF NOTHING POSTED YET
if (empty($_POST))
{

// ASK CLIENT TO CHOOSE ONE FOR RESTORE
    $x = FALSE;
    echo "<form method=\"post\">\n";
	echo "RESTORE A DATA BASE:<br/>";
    foreach ($my_dir as $my_file)
    {
        if (!ereg('^mysql', $my_file)) continue;
        echo "<input type=\"radio\" name=\"d\" value=\"$my_file\">$my_file <br/>\n";
        $x = TRUE;
    }
    if (!$x) die('NO BACKUP FILES FOUND');
    echo "<input type=\"submit\" />\n";
    echo "</form>\n";
    die();
}


// THERE IS A RADIO BUTTON IN $_POST
if (!in_array($_POST["d"], $my_dir)) die("ERROR: FILE {$_POST["d"]} NOT FOUND");

// THE NAME OF THE BACKUP FILE
$fname = $_POST["d"];

// GET THE NAME OF THE DB FROM THE BACKUP FILE NAME
$db_name = $fname;
$db_name = ereg_replace("^mysql", '', $db_name);
$db_name = ereg_replace("\.txt$",         '', $db_name);
$db_name = substr($db_name, 15); // LENGTH 15 = strlen("date('Ymd\THis')")


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB CONNECTION: ";
   echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
   $errmsg = mysql_errno() . ' ' . mysql_error();
   echo "<br/>NO DB SELECTION: ";
   echo "<br/> $errmsg <br/>";
   die('NO DATA BASE');
}

// LOAD THE BACKUP DATA QUERIES
$sqls  = file('RAY_backups/' . $fname);

// ITERATE OVER THE QUERIES TO RELOAD THE DATA
foreach ($sqls as $sql)
{
    if (!$res = mysql_query($sql))
	{
	   $errmsg = mysql_errno() . ' ' . mysql_error();
	   echo "<br/>QUERY FAIL: ";
	   echo "<br/>$sql <br/>";
	   die($errmsg);
	}
}

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
Apologies if there are any spacing or alignment issues in the scripts.  Some of my text editors translate poorly between spaces and tabs!
ZadoAuthor Commented:
Thanks a lot, I will try it tomorrow, actually today, now it's 1:18am where I live so time to sleep...
Ray PaseurCommented:
Pleasant Dreams!  And Happy New Year, ~Ray
ZadoAuthor Commented:
Thanks. Happy New Year! :-)
Ray PaseurCommented:
Thanks for the points.  It's a great question! ~Ray
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.