Help needed! [Edit: Partially solved, see bottom]
this site is hosted on a cheap hosting site like many others. Recently I tried a backup of the WP database and files without success. The backups stuck (do never finsih and hang at a file or table) or just reported “success” (EZPZ backup). I also tried the hosters PHPAdmin to backup the WP database without success, I got out of memory errors.
Finally the cause of all these strange results is a limitation of the hosting, it has a limit of 20MB per file. As most backup tools for wordpress and even PHPAdmin use temporary files on my host, the backup fails as soon as the limit is reached.
I was finally able to backup the WP database by excluding the larger tables from the backup. To get the table sizes I used the following php script:
Code:
<?php
$link = mysql_connect('xxxxxxx.strato.de', 'username', 'password');
$db_name = "databasename";
$tables = array();
$txtOut="";
mysql_select_db($db_name, $link);
$result = mysql_query("SHOW TABLE STATUS");
while($row = mysql_fetch_array($result)) {
/* We return the size in Kilobytes */
$total_size = ($row[ "Data_length" ] +
$row[ "Index_length" ]) / 1024;
$tables[$row['Name']] = sprintf("%.2f", $total_size);
$txtOut=$txtOut."<tr><td>".$row['Name']."</td><td align=\"right\">".$tables[$row['Name']]."</td></tr>";
}
echo "<h1>Database table sizes</h1>";
echo "<table border=\"1\">";
echo $txtOut;
echo "</table>";
//print_r($tables);
//var_dump($tables);
?>
The “success” failing backups may be related to a problem in php fwrite as described here. Excerpt:
====================================================
This means the example fwrite_stream() code from the docs, as well as all the "helper" functions posted by others in the comments are all broken. You *must* check for a return value of 0 and either abort immediately or track a maximum number of retries.
Below is the example from the docs. This code is BAD, as a broken pipe will result in fwrite() infinitely looping with a return value of 0. Since the loop only breaks if fwrite() returns false or successfully writes all bytes, an infinite loop will occur on failure.
<?php
// BROKEN function – infinite loop when fwrite() returns 0s
function fwrite_stream($fp, $string) {
for ($written = 0; $written < strlen($string); $written += $fwrite) {
$fwrite = fwrite($fp, substr($string, $written));
if ($fwrite === false) {
return $written;
}
}
return $written;
}
?>
====================================================
So, if your WP backups fail, check with your hoster about file size limitations. I found this out myself as the backup folder on the site showed backup files with exact same file size of 21MB.
Please leave a mail or comment if you know a WP backup solution that use splitted files and handles maximum file size limitations.
And as last guess never trust a backup, always try a restore or at least check the backup files.
[Edit 1. march 2011]
Fortunately strato.de support gave a hint and led me to try MySqlDumper. This sql dumper is able to split files and works also with limitted file size on the host. I even got a perl script now that I can simply invoke and that backups the whole database. The uncompressed dump is about 52MB and MySqlDumper did backup it into a 6MB gzipped dump file.
The only wish left is to have it run periodically but that would need cronjob access on the hoster site and is not included with the package I own at the hoster.
So if you need to backup a large WordPress MySql database you may give MySqlDumper a try.
<?php
$link = mysql_connect(‘rdbms.strato.de’, ‘U402706’, ‘chopper’);
$db_name = “DB402706″;
$tables = array();
$txtOut=””;
mysql_select_db($db_name, $link);
$result = mysql_query(“SHOW TABLE STATUS”);
while($row = mysql_fetch_array($result)) {
/* We return the size in Kilobytes */
$total_size = ($row[ “Data_length” ] +
$row[ “Index_length” ]) / 1024;
$tables[$row[‘Name’]] = sprintf(“%.2f”, $total_size);
$txtOut=$txtOut.”<tr><td>”.$row[‘Name’].”</td><td align=\”right\”>”.$tables[$row[‘Name’]].”</td></tr>”;
}
$keynames=array_keys($tables);
echo “<h1>Database table sizes</h1>”;
echo “<table border=\”1\”>”;
echo $txtOut;
echo “</table>”;
//print_r($tables);
//var_dump($tables);
?>
<!–
$sql = ‘SELECT TABLE_SCHEMA AS \’Database\’, TABLE_NAME AS \’Table\’,’
. ‘ CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH – DATA_FREE) / 1024 / 1024),2),” Mb”) AS Size FROM INFORMATION_SCHEMA.TABLES LIMIT 0, 60 ‘;
–>
http://www.led-mg.de