Welcome to WebmasterWorld Guest from 54.161.110.186

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

MySQL export script arbitrarily stops

     
6:37 pm on Aug 13, 2013 (gmt 0)

5+ Year Member



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.";
?>
7:40 pm on Aug 13, 2013 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.
8:00 pm on Aug 13, 2013 (gmt 0)

5+ Year Member



It's actually hosted here locally at our office. As far as anyone knows, there isn't a hard set time limit.
9:25 pm on Aug 13, 2013 (gmt 0)

WebmasterWorld Senior Member drdoc is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Why not just use
mysqldump
?
10:46 pm on Aug 14, 2013 (gmt 0)

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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?
 

Featured Threads

Hot Threads This Week

Hot Threads This Month