Restoring A MySQL Database Via PHP

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Brian
Forum Contributor
Posts: 116
Joined: Thu Apr 18, 2002 5:33 pm

Restoring A MySQL Database Via PHP

Post 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. :)
Last edited by Brian on Mon Apr 29, 2002 3:20 pm, edited 1 time in total.
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post by mydimension »

try putting a new line character at the end of the string ti simulate hitting enter and the command line.
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post 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.
User avatar
sam
Forum Contributor
Posts: 217
Joined: Thu Apr 18, 2002 11:11 pm
Location: Northern California
Contact:

Post 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
Brian
Forum Contributor
Posts: 116
Joined: Thu Apr 18, 2002 5:33 pm

passthru()

Post 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.
Last edited by Brian on Mon Apr 29, 2002 12:23 pm, edited 1 time in total.
Brian
Forum Contributor
Posts: 116
Joined: Thu Apr 18, 2002 5:33 pm

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

Post 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.
Brian
Forum Contributor
Posts: 116
Joined: Thu Apr 18, 2002 5:33 pm

Some Insights!

Post 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.
Post Reply