This is a very small change, but I think it will help out those who have either a) larger databases or b) a slow internet connection, or some combination of them. Most servers have a PHP timeout limit set at 30 seconds, and meeting this can result in incomplete backups when taken through the ACP.
My solution: Add the following to the beginning of /admin/modules/tools/backupdb.php.
set_time_limit(0);
I've done this on my forum, which has a DB size of 24MB, and I no longer experience timeout issues. What are your thoughts?
That's not a bad idea from what I can tell. However, I have been an advocate for removing the backupdb.php file and instead using PHPMyAdmin or mysqldump instead. It lowers the risk of your database being stolen in the event of a hack.
Shared hosts usually enforce a time limit so set_time_limit would not help because it's not allowed. But as long as it doesn't raise an error outright, sure, why not.
It should also store the backup in a temporary file and only rename the file when it's done. So you can tell by the filename if the backup is complete or not. That's the real problem with backup process timing out sometimes, that it looks like it succeeded when it's really an incomplete file.
Today I noticed I had incomplete backups on a shared host for the first time. Posts table grew too large for the backup.
Turns out it's not timing out at all, instead it's hitting the memory limit (in my case, 52M). Selecting the entire posts table blew the limit. If you suffer from this issue, see if you can increase the memory limit (for the backup task).
I found two workarounds, one is to limit the query to 10000 rows. And then query the next 10000 and so on. The problem is that this is potentially unreliable, as the order is not guaranteed. So you could get some duplicate and missing rows.
The other is to use MYSQLI_USE_RESULT instead of the default MYSQLI_STORE_RESULT. Very memory friendly for large result sets, downside is that it's a bit slower and locks the tables longer. However this is the same method phpMyAdmin uses when exporting.
inc/tasks/backupdb.php - note this is a hack for
mysqli only.
+++ backupdb.php 2013-03-14 02:19:00.000000000 +0100
@@ -66,7 +66,8 @@
$contents .= $structure;
clear_overflow($fp, $contents);
- $query = $db->simple_select($table);
+ //$query = $db->simple_select($table);
+ $query = mysqli_query($db->read_link, "SELECT * FROM {$db->table_prefix}{$table}", MYSQLI_USE_RESULT);
while($row = $db->fetch_array($query))
{
$insert = "INSERT INTO {$table} ($fields) VALUES (";
@@ -87,6 +88,7 @@
$contents .= $insert;
clear_overflow($fp, $contents);
}
+ $db->free_result($query);
}
$db->set_table_prefix(TABLE_PREFIX);
Note that the backup tool in the Admin CP is a different file (admins/modules/tools/backupdb.php) which duplicates this code, so you have to change two files if you want the workaround for both of them.
The workaround uses mysqli_query() since the $db object does not expose an interface for unbuffered queries.
Very cool, any benefit in using phpMyAdmin for backup over MyBB Admin CP other than for big boards? Which do you prefer to use?
Not really that I can think of. Honestly if you're a really large board you'll have ssh access and probably do all of your backups through there.
Even when taken through backupdb.php file can result in timeouts but on backend, the process is working on taking backups. Every time you'll refresh the page, the size to db will keep on being added until it finishes. I've tested it on localhost.
(2013-03-14, 06:26 AM)WebDevandPhoto Wrote: [ -> ]Very cool, any benefit in using phpMyAdmin for backup over MyBB Admin CP other than for big boards? Which do you prefer to use?
phpMyAdmin is fine, if you can find a way to automate it. There are scripts that attempt to log in and grab an export, but lots of them are years old and do not work properly at all.
Of course on anything but a shared host you would always use cron job / ssh / mysql client directly and not bother going through any php script for database backups.
I used to just rsync the mysql data folder to another drive as a secondary backup to the WHM backups. Ran a cron job to do that. Its not 100% backup but darn close and nothing really is 100% unless you can stop the SQL server or lock the current table until the copy is done.
Very valuable information guys, thanks!