Backup MySQL database on IIS

[Guest]

Hi guuys,

I have a simple script that backup the contents of a database and place the file on the server.

The problem is that this script works fine on my Apache server, but not on the IIS server.

Do I have to change the $command variable or the system function?

<?php
$backupFile = "../DBJobs_" . date("Y-m-d");
$command = "mysqldump --opt -h localhost -u (user) -p(password) dbjobs > $backupFile.sql";
system($command);

if ($command) {
$mess = "<p>Backup file created!</p>";
}
else {
  $mess = "<p>There was a problem with the backup routine.</p>";
}
?>

Any help would be very appreciated.

[David_Powers]

You probably need to include the path to mysqldump.

[Guest]

Something like this?:

$command = "//Server/c/Program Files/MySQL/MySQL Server 5.0/bin/mysqldump --opt -h localhost -u (user) -p(password) dbjobs > $backupFile.sql";

[David_Powers]

I have just tested this on my Windows machine, and it works (although I'm using Apache, not IIS):

<?php
$backupFile = "DBJobs_" . date("Y-m-d");
$command = "\"C:\\Program Files\\MySQL\\MySQL Server 5.0\\bin\\mysqldump.exe\"
  --opt -h localhost -uroot -psecret mydb > $backupFile.sql";
$result = system($command);

 

if ($result !== false) {
  echo "<p>Backup file created!</p>";
}
else {
  echo "<p>Houston, we have a problem!</p>";
}
?>

Note that you call the .exe file, not just mysqldump.

Reading the notes in the PHP online manual, it seems as though the path to the .exe file needs to be in quotes within the string that you pass to system().

I also discovered that you need to use !== false to check the return value of system().

[Guest]

Hi David,

Thanks for the code, unfortunately, it doesn't seem to work on my IIS server. I don't even get the "<p>Houston, we have a problem!</p>", just an HTTP 500 error.

The MySQL credentials are correct, so I'm guessing that it doesn't like the $command.

I've tried a few different variations, but nothing seems to work:

$command = "c:\path\to\dump\command\mysqldump --opt -h localhost -u(user) -p(password) dbjobs > $backupFile.sql";

$command = "C:\Program Files\MySQL\MySQL Server 5.0\bin"\mysqldump -hlocalhost -uUsername -pPassword database >c:\backup\filename.sql;

[David_Powers · Accepted solution]

Look at my code again. It points to mysqldump.exe, not mysqldump. Also, the path to mysqldump.exe is enclosed in quotes. I haven't tested mixing single and double quotes, because the notes in the PHP online manual referred to double quotes. That's why there's a backslash before the quotes surrounding the path, because they're enclosed in a string that also uses double quotes. Finally, all the backslashes in the path are doubled.

It's

"\"C:\\Program Files\\MySQL\\etc\\mysqldump.exe\" options"

NOT

"C:\Program Files\MySQL\etc\mysqldump options"

[Guest]

Hi David,

Many thanks for your help, but it still doesn't work. I'm trying to get the command to work on the Command Prompt on the server manually first.

I also tried to test to see if the system function was working, but I don't know if I'm doing it correctly:

system(C:\backup.sql);

Should produce an empty file called backup.sql on the C:\ drive of the server, but I still get the HTTP 500 error, which would be it doesn't matter what command you give it, it won't run.

Is that right?

[David_Powers]

No, that won't work. To run PHP from the command line you need to enter php -r followed by the PHP code in quotes. For details, see http://docs.php.net/manual/en/features.commandline.php.

However, I have tried running one of the examples directly copied from there, and get a parse error.

I have no use for running PHP from the Windows command line, so I can't really help you with this. Sorry.

[Guest]

Turns out it was a permissions issue on the remote server

C:/Windows/System32/cmd.exe - Right-click - Properties - Security - Add " I_User "