Page 1 of 1

Restoring A MySQL Database Via PHP

Posted: Mon Apr 29, 2002 3:59 am
by Brian
Perhaps it is due to the late hour and my readiness for slumber, but I am encountering some difficulty with a PHP program to restore a MySQL database.

This works from the command line (with "mypassword", "mydatabase", and "myfile" replaced by the real things, of course):

Code: Select all

mysql --password=mypassword mydatabase < myfile
So why does this not work?

Code: Select all

if (system("mysql --password=$Password $Database < $File")) {...
I even tried passing the exact string that works from the command line, but that did not work either.

I suppose I could open the file, parse it, then send it to MySQL as a query, but it seems that this simple, common action should be accomplished much more quickly.

Is it just me or is mysql_restore() an obvious choice for a future standard PHP function? I have thought of a few others as well. Hmm... I might look into writing some myself then contributing them, but not until after I figure this out. Heh. :)

Posted: Mon Apr 29, 2002 7:13 am
by mydimension
try putting a new line character at the end of the string ti simulate hitting enter and the command line.

Posted: Mon Apr 29, 2002 8:26 am
by jason
Off the top of my head, use passthru(). It outputs the results directly, so you may see an error that you may not have known about before, such as permissions and PHP not being able to access the mysql program through the command line like that.

Posted: Mon Apr 29, 2002 11:42 am
by sam
You might also look into the mysql syntax structure for "INFILE". Or you could just use stright php:

Code: Select all

$data = file("myfile");
mysql_connect("");
mysql_select_db("");

foreach($data as $sql)&#123;
   if(!mysql_query($sql))&#123;
       echo "Query rejected: " . $sql . "<br />
";
   &#125;
&#125;
Good luck...

Cheers Sam

passthru()

Posted: Mon Apr 29, 2002 11:53 am
by Brian
Actually, I already tried passthru(); it returns "127" with or without a newline character and the database is not restored. I am not sure what is going on. I have never had more than limited success with passthru(), system(), etc. Perhaps there is some essential element to using them that I have overlooked or perhaps they are simply incapable of doing what I want.

I wonder why this is not covered by a built-in function; it seems that it is such a common thing that people need to do.

Hmm... this might work... but not for me.

Posted: Mon Apr 29, 2002 12:22 pm
by Brian
I did not remember the "source" command earlier. This might work via mysql_query():

Code: Select all

source /path/to/backup
Unfortunately, my web host is not running a sufficiently recent version of MySQL to support the "source" command.

Some Insights!

Posted: Mon Apr 29, 2002 3:17 pm
by Brian
Okay, I employed the super-secret ancient debugging technique of redirecting standard output like so:

Code: Select all

if (system("mysql --password=$Password $Database < $File &> /tmp/restore.debug")) &#123;...
That results in the following error message being saved within /tmp/restore.debug:
"sh: mysql: command not found"

Out of curiosity, I also tried this:

Code: Select all

if (system("ls &> /tmp/restore.debug")) {...
That results in the following error message being saved within /tmp/restore.debug:
"ls: .: Permission denied"

Perhaps this is all a permissions issue. Blah.