MySQL export script arbitrarily stops

[webfoo]

I am trying to create a cronned PHP script to backup the entire contents of the database on a daily basis. The output is saved into an SQL file that could be imported back into the database server in the event we need to recover from a database loss.

The script loops through each table as follows:
1. generates a CREATE TABLE statement
2. loops through each row and generates an INSERT statement.

For testing purposes, I placed a LIMIT 0,20 on each table so that I could verify that the queries were being properly generated. After successful testing, I removed the LIMIT so that all records would be backed up. That's when it stopped working.

The script executes properly up to a certain point, when it suddenly stops without giving any error messages. It always stops at the same point, just before the INSERT phase of a particular table. Initially, I figured it may have been a memory or timeout error, so I added a set_time_limit(), ini_set(memory_limit), and ignore_user_abort(true).

Unfortunately, the script still continues to end randomly. I have tried executing it from the browser and from the command line with the exact same result.

<?php
ob_start();
error_reporting(E_ALL);
ignore_user_abort(TRUE);
ini_set('memory_limit','256M');

$con= mysqli_connect(*********);

$tableList = array();
$res = mysqli_query($con,"SHOW TABLES");
while($cRow = mysqli_fetch_array($res)) {
$tableList[] = $cRow[0];
}

echo "-- QUOTE TOOL BACKUP $now\n\n";
foreach($tableList as $table) {

echo "\n-- ".strtoupper($table)." ---------------- \n\n";

echo "CREATE TABLE `$table` (\n";
$lines='';
$q=mysqli_query($con,"DESCRIBE $table");
while($row=mysqli_fetch_array($q)) {
$lines.= "\t`".$row['Field']."` ";
$lines.= $row['Type'];
if($row['Null']=="NO") { $lines.= " NOT NULL"; } else { $lines.= " NULL"; }
if($row['Default']!='') { $lines.= " default '".$row['Default']."'"; }
if($row['Extra']!='') { $lines.= " ".$row['Extra']; }
$lines.= ",\n";
}
$q=mysqli_query($con,"DESCRIBE $table");
while($row=mysqli_fetch_array($q)) {
if($row['Key']!='') {
$lines.="\t";
if($row['Key']=='PRI') { $lines.= "PRIMARY KEY (`".$row['Field']."`)"; }
elseif($row['Key']=='UNI') { $lines.= "UNIQUE KEY (`".$row['Field']."`)"; }
elseif($row['Key']=='MUL') { $lines.= "KEY (`".$row['Field']."`)"; }
$lines.= ",\n";
}
}
echo substr($lines,0,-2);
echo "\n);\n\n";

$q=mysqli_query($con,"SELECT * FROM $table");
if(mysqli_num_rows($q)>=1) {
echo "INSERT INTO $table (";
$fields='';
$q=mysqli_query($con,"DESCRIBE $table");
while($row=mysqli_fetch_array($q)) {
$fields.=$row['Field'].", ";
}
echo substr($fields, 0,-2);
echo ") VALUES \n";

$v='';
$q=mysqli_query($con,"SELECT * FROM $table");
$cols=mysqli_num_fields($q);
while($row=mysqli_fetch_array($q)) {
set_time_limit(200);
$v.= "\t(";
$values='';
$i=0;
while($i<$cols) {
$values.="'".$row[$i]."', ";
$i++;
}
$v.= substr($values,0,-2);
$v.="),\n";
}
echo substr($v,0,-2).';';
}
echo "\n";
}

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 4);

echo "\n\n-- END. ----------- \n\n";

$content = ob_get_clean();

$file = fopen("backups/2013-08-13 14_36_00.sql","w+");
fwrite($file,$content);
fclose($file);

echo "BACKUP FILE SUCCESSFULLY CREATED.";
?>

[rainborick]

If you're on a shared host, the host may have (in fact, probably) set a hard script execution time limit that you cannot override. This is a common cause of the problem you have.

[webfoo]

It's actually hosted here locally at our office. As far as anyone knows, there isn't a hard set time limit.

[DrDoc]

Why not just use

mysqldump

?

[penders]

Yes, I was also wondering why not use mysqldump?

But I am also rather curious as to the way you have written your script... why are you using the output buffer and effectively holding the entire dump in memory before writing it out? Why not open a file for appending and write to that as you go (or at least in chunks)?

You state that the script "always stops at the same point", but in the next paragraph you state, "the script still continues to end randomly"? Is it at the same point or is it random?