Doosh: Incremental database-backup script gets around PHP execution-time limits

The premise: You have a database full of many very important things. However, the server it’s on is owned by some goofus who doesn’t give a damn, on a dusty PII box under his bed. You don’t have shell access and can’t dump it that way. You can’t install utilities. Maybe phpMyAdmin doesn’t even work due to missing libraries (on a Windoze box this is almost certainty). Even if it did, your databases are too big to dump with it. What do you do?

* * *

There are many, many PHP scripts out there that will export (dump) your SQL databases, but all that I have found (even my favorite, phpMyAdmin) suffer a critical flaw: they fail at dumping very large or complex databases. So you wait, and you wait, staring at a blank screen, and within a few minutes your script dies quietly leaving you with an incomplete dump (if that).

Why?
To keep a single user or a runaway script from permanently hogging the server’s CPU, PHP enforces an execution time limit (default of maybe 30 seconds). If your script takes longer than that to run, it gets killed, end of discussion.
Depending on the system, spitting out the file (or writing to disk) takes a significant amount of time that may count against you. On some systems, the time used by the SQL server itself may count against the PHP execution time limit too. Additionally, your database dump may get killed partway through because the SQL server’s response exceeded PHP’s memory limit, since PHP is trying to slurp the entire query result into a variable.

Enter the Doosh:
This script makes a list of all the tables in your database, and starts dumping them one by one, a few hundred records at a time to a backup file(s), recursively calling itself until the job’s done. The number of rows dumped in each iteration can be changed depending on the speed of your server. This way, you can dump out your entire database even if some joker set PHP’s execution time limit to 10 seconds and there’s nothing you can do about it.

The script gets its name from the sound of a large hunk of data being flushed to disk. Some theories suggest the script is named as it is because “usyr_illus_is_a_giant_douche.php” would be too long (and the lawyers would have a field day), however, this is just a rumor.

Download: http://tim.cexx.org/projects/doosh/doosh.zip

Directions: Make the obvious changes in doosh_config.inc.php. For example, set your own username and password, the name of your database, etc. Start script running. Make tea. Take a nap. Wake up, your database is backed up to a set of text files full of SQL statements. You can now import these right at your SQL server console (consult the manual) to recreate the database… if the files are too large, you can use something like BigDump to split up the import too (it’s sort of the inverse of Doosh).

Tags:

Leave a Reply