As Ryan mentioned previously, we quickly realized that just because people are willing to try out an application, they’re not necessarily going to hang around for very long. It’s true that hardware is not all that expensive, but we’d rather not have tens of thousands of unused databases eating up our RAM and other resources. I suggested just deleting the accounts, since we would only remove accounts that aren’t collecting submissions, are free, and haven’t been used for over 3 months. But that was the lazy way out and I was quickly vetoed and given the task of creating a system for backing up and restoring up our inactive users. This is a fairly easy process, but it’s also one of those areas that’s boring and can turn a simple problem into a three hour process. In case you’re ever given the tedious task of backing up a MySQL database with PHP, here’s a pretty quick overview.
Backing up Databases
For backing up our databases, we just run a mysqldump and write the backup file to a folder for backups. You can learn the basics of mysqldump and the php system() command at php.net, SitePoint, or backing up with mysqldump.
When you’re done, your code might look a little something like this:
$backup = "location/of/file/mysqldump" -opt -h $dbhost -P $dbport -u $dbuser
--password=$dbpass $dbname > $filename";system($backup, $return);
Restoring the Databases
Remarkably, it seems that the chances of somebody wanting to log into their very inactive account increases tenfold after you actually remove their account. To restore the database you could always grab the stored .sql file, open up Navicat, and manually restore it, but we program so we don’t have to do that manual stuff, right? To restore your previously deleted account, you’ll just run a script similar to this:
$restore = "location/of/file/mysql" -h $dbhost -P $dbport -u $dbuser
--password=$dbpass $dbname < $filename";system($restore, $return);
Debugging
I’m not going to lie. This process took me longer than it should have, mainly because I wasn’t pointing to the correct location of the mysqldump file, and the $dbhost variable was incorrect. I also wasn’t sure what the weird return values actually meant. If the backup or restore is successful, the $return variable will return a 0. And if the function is unsuccessful you can look up what the return values mean. You should also be able to capture error messages by looking at STDERR from the mysqldump process.
The Next Step
Right now, we just have to push a button in our admin area to restore an account, but even that is too much work for a lazy programmer. The next step is to allow the actual users to restore their accounts by prompting them for their username and password when they try to access an inactive account, and then restoring their account automatically if the credentials check out.
Great tip! I notice you say databases. This seems smart to use a separate database for each user when factoring in this technique, but did you guys know this when deciding to structure it that way? Was there any other reason you guys choose this method as opposed to a single database with multiple tables, or just massive tables with primary keys for users. Possibly another post topic? :)
We’ve actually got a mix of a couple methods going on. For the sections where there are multiple db’s, it was planned that way for reasons other than backing up. We’re far from experts on the subject, and so far there have been definite positives and negatives with it. Almost all of the negatives are hardware related. Maybe we’ll go into more detail on our setup one of these days.
you know, i pondered through this very issue a year ago or so and ended up using the same method. though i felt a little dirty for using the system function. but it was either that or do SELECT INTO OUTFILEs and LOAD DATA INFILEs, which created permissions headaches since my app lives on shared hosting
Very helpful article. I’ve added one more twist to it—compression. Just pipe the result to a bz2 file on unix/linux systems, like this: $backup = “location/of/file/mysqldump†-opt -h $dbhost -P$dbport -u$dbuser —password=$dbpass $dbname | bzip2 -c > $filename.bz2â€;