Backup MySQL PHP script

Hi guys,

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 (HTTP 500 error).

<?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>";
}
?>

PHP:

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

Any help would be very appreciated.

 

is the output folder set to write permission?

 

Don't think windows will support your command. But I could be wrong

 

Please try to execute the command manually from Command Prompt before running it on PHP and see if it throws any error.

 

Hi Chaps,

Thanks for your help, I have the correct credentials and have tested the mysqldump function on the server manually and it writes to the directory no problem.

Is there anything I can test to see if the system function is working correctly, e.g. replace the $command variable with something simple?

Cheers

 

as the others have suggested try running the command in prompt... not sure if mysqldump is working for you

 

Hi, I'm not 100% sure, but isn't mysqldump a UNIX mysql command? If it was for Windows would it be like mysqldump.exe ?

This command should work fine in UNIX environment, but as I said above, I'm not sure if will ever work on windows server.

Cheers,
Venetsian.

 

hi koolsamule,

greetings from france!

the problem is that you can't be sure that the server you're hosting allows system-commands to be executed!

if your matter is just the backup of a database, i programed the following script and it works fine for me (as a cronjob as well)!

all you have to do is to complete the variables (written in capital-letters) within the "if" at the very end!

the result of the script is a gziped file named depending on the timestamp the script is executed!
it works on any server (linux, windows, etc) and with any DB.

hoping it helps!

<?php
function check_db($host,$database, $username, $password) {
	$result = false;
	$link = @mysql_connect($host, $username, $password);
	if ($link) {
		$db_selected = mysql_select_db($database, $link);
		$result = true;
	} else gzwrite($fh,"no connection possible to: " . mysql_error());
	return array($result,$mysqlInfo);
}
function export_DB($mysql_database) {
	$sql="show tables;";
	$result= @mysql_query($sql);
	if ($result) {
		while ($row=mysql_fetch_row($result)) {
			tableStructure($row[0]);
			tableData($row[0]);
		}
		mysql_free_result($result);
	}	
}
function tableStructure($table) {
	global $fh;
	$data= "not OK";
	$sql="SHOW CREATE TABLE `$table`; ";
	$result=mysql_query($sql);
	if ($result) {
		if ($row=mysql_fetch_assoc($result)) {
			$data=$row['Create Table'].";\n\n";
			gzwrite($fh,$data); 	  
		}
		mysql_free_result($result);
	}
}
function tableData($table) {
	global $fh;
	$sql="select * from `$table`;";
	$result=mysql_query($sql);
	if ($result) {
		$num_rows= mysql_num_rows($result);
		$num_fields= mysql_num_fields($result);
		if ($num_rows > 0) {
			$field_type=array();
			$i=0;
			while ($i<$num_fields) {
				$meta= mysql_fetch_field($result, $i);
				array_push($field_type, $meta->type);
				$i++;
			}
			$index=0;
			while ($row=mysql_fetch_row($result)) {
				gzwrite($fh, "INSERT INTO `$table` VALUES ");
				gzwrite($fh, "(");
				for ($i=0; $i<$num_fields; $i++) {
					if (is_null($row[$i])) gzwrite($fh, "null");
					else {
						switch ($field_type[$i]) {
							case 'int':
								gzwrite($fh, $row[$i]);
								break;
							case 'string':
							case 'blob' :
							default:
								gzwrite($fh, "'".mysql_real_escape_string($row[$i])."'");							  
						}
					}
					if ($i<$num_fields-1) gzwrite($fh, ",");
				}
				gzwrite($fh, ");\n");	      
				$index++;
			}
		}
	}
	mysql_free_result($result);
	gzwrite($fh, "\n");
}
if ($_SERVER['HTTP_HOST']=="localhost") $host = "localhost";
else $host = "PLACE YOUR HOSTNAME HERE!";
$filename = date('Ymd').".sql.gz";
$fh = gzopen($filename,'w');
if ($fh) {
	$res = check_db($host, 'DB_NAME_HERE', 'USERNAME_HERE', 'PASSWORD_HERE');
	if ($res[0]) export_DB($database);
    else gzwrite($fh,"ERROR exporting main-DB ");
	gzclose($fh);
} else gzwrite($fh,"internal file error");
?>

PHP:

 

Thanks for the reply, I'm using an IIS server and neither the exec() nor the system() functions work.
I've tried this:

<?php
$backupFile = "DBJobs_" . date("Y-m-d");
$command = "mysqldump.exe --opt -h localhost -uUser -pAdmin dbjobs > backupFile.sql";
$result = var_dump($command);
if ($result !== false) {
  echo "<p>Backup file created!</p>";
}
else {
  echo "<p>There was a problem</p>";
}
?>

Code (text):

When run through the browser, returns:

But the backupFile isn't created on the server. What else can I try? This is the last bit of code I need to finish this project!

 

rehi,

i just gave you the solution (see post above)!

all you habe to do is to copy/paste the code! save it under the name you like!
upload it on your server, and run the script! it runs on ALL servers, with ANY DB!

 

Hi,
Thanks for that.

I can confirm that the database is backed up, however I still get the HTTP 500 error (although the .sql.gz file is created and populated)

How you have any ideas why this is and can it be solved?

Also, I can't seem to restore from the backup file. . . .it creates tables and enters the data, but doesn't tell mysql to drop if already exists . . .can this be changed?

Cheers

 

rehi,

HTTP500-error? via php? that's odd! that's an internal server-error! are you sure you get this error from php and not from FTP?

however, i'm glad to see that the script helped!
and YES SURE adding the DROP TABLE-line can be added! it's just that i don't find it appropriate! if i ever have to restore a table, i want to be sure that i'm not doing mistakes, that's why i left this line out.
now if you want to know if I can add it, the answer is still "yes", but why should i?

consider the script as yours! do whatever you like with it!

 

instead of just using "backupFile.sql", try using an absolute path.

 

Thanks for your help, I have the correct credentials and have tested the mysqldump function on the server manually and it writes to the directory no problem.