Backup your MySQL db through php?

[skaterdav85]

Is there any way to basically export a MySQL db from a php script, rather than exporting through the typical phpMyAdmin interface?

[thescientist]

http://www.google.com/#hl=en&source=hp...537d44c67dd0bd9seems pretty straightforward

[skaterdav85]

ya i tried that but it didnt work. This is my error:Can't create/write to file 'C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\Data\backup\employees.sql' (Errcode: 2)Anyone know what this means? How does phpMyAdmin do it with the export database function?

[justsomeguy]

Error code 2 means no such file or directory.http://dev.mysql.com/doc/refman/5.0/en/perror.htmlSo, I would assume that phpMyAdmin creates the file first.

[skaterdav85]

So I tried this since this seemed like it would export the whole db into a .gz file, but when i load the page, its a blank screen and i see nothing in my folder:

$backupFile = $databasename . date("Y-m-d-H-i-s") . '.gz';$command = "mysqldump --opt -h $hostname -u $dbuser -p $dbpassword $databasename | gzip > $backupFile";system($command);

Any possible reasons why this is not working?

[Synook]

Maybe your host has disabled shell execution? Make sure you have all error messages enabled. Also try echoing $command to make sure it is formed as you think it is.

[justsomeguy]

You should either print the return value from system, or use passthru instead. The system function returns the output, but you're not doing anything with it. If there was an error message from mysqldump you wouldn't see it.

[skaterdav85]

i tried using passthru like the following, and it echoed 255 out to the page. What does this mean?

passthru($command, $passThruResult);echo $passThruResult;

Anything wrong with this command? I echoed it out to the page and it says:

mysqldump --opt -h virtualserver1 -u root -p thePW testdb | gzip > testdb2010-05-12-12-29-11.gz

[justsomeguy]

i tried using passthru like the following, and it echoed 255 out to the page. What does this mean?

That means the return status of the command was 255, for whatever that's worth. You don't need to echo anything with passthru, as the name implies the output from the command goes straight to the browser.

Anything wrong with this command?

You'll probably want to check the documentation for mysqldump to see about the command line options. With mysql, for example, the -p option doesn't have a space after it. I'm not sure if that's different for mysqldump, but it would be worth looking up.

[skaterdav85]

ok i checked out the mysqldump command line documentation, and i have slight improvement. I tried the following:

$command = 'mysqldump --opt -h '.$hostname.' -u '.$dbuser.' -p'.$dbpassword.' '.$databasename.' > backup/backup3.sql';$command = 'mysqldump --opt -h '.$hostname.' -u '.$dbuser.' -p'.$dbpassword.' '.$databasename.' | gzip > backup/GZbackup.gz';

The first one at least creates a new file, but the 2nd one doesnt. The only difference is that the 2nd one has this gzip thing in it. Why would I not be getting anything in the file? Am I failing to connect to the db even though i get a return value from passthru of 255?

[justsomeguy]

I haven't looked at the Linux documentation, but I assume that a status of 255 just means the process quit successfully. That doesn't indicate what it did, just that it stopped in a normal way. I'm not very familiar with the pipe syntax for Linux commands though, so I'm not real sure specifically what that second command is telling the OS to do.

[skaterdav85]

hmm ok i cant figure this out. Is there another way of generating prepared SQL statements of a db or even just a table? maybe a php class available?