How I Faked Scheduled Database Dumps Without Cron

So there I was, stuck with a handful of problems. I had to find a way to use the platform I had available (Windows XP), keep sensitive passwords hidden from users, and enable periodic cron operations like backing up the database.

My favorite server bundle XAMPP doesn’t come with cron services for Windows. I looked into UniServer, and though it looked pretty cool, it needed additional support to run on XP and I would need to change ionCube loaders for different platforms. Worse, as long as the user had access to the physical server, they could easily see the root MySQL password by clicking on the mysql tab in the UniServer GUI (and time was not on my hand to learn how to disable that).

I almost used WampServer, but then I remembered all the headaches it gave me the last time I used it.

I came across an open-source program called MySqlDumper which I was happy to find, but which also wasn’t without its problems. When performing a database restoration for example, it was capable of displaying everything about all the databases on the server and offered options to drop them. The program was to be installed in various institutions and would use the same root password.

I set my laptop aside and walked to the fridge, thinking of giving up on the cron idea during this first release of my project. But clients’ data backup can never be ignored. I opened the fridge and grabbed the closest bottle of juice; ignoring the glasses on the shelf, I took one big gulp of the juice that chocked me badly. As I turned looking around the boring room and trying to grasp my breath, suddenly an idea struck like lightening.

My program required a accurate username and password to present its features to the user, and there is one predefined user who is idle most of the time and does just two things: wipe stale database entries and restore the database when needed. According to my login procedure, each time a user successfully logs in the system automatically updates the last login date to the current date. And that was the hint I desperately needed.

The solution was to track the last login date of the idle superadmin user. If his last login was yesterday, then the system would call mysqldump to backup the database. So, I created the following code:

<?php
define("MYSQL_DUMP", "C:\xampp\mysql\bin\mysqldump.exe");
define("MYSQL_DUMP_ARGS", "--user=user --password=pass --opt database");
define("MYSQL_DUMP_OUT", "..\system\" . date("l") . ".sql");

$result = $db->query("SELECT last_login FROM users WHERE username = 'superadmin'");
$check = $result->fetchColumn();
$result->closeCursor();

if ($check < strtotime(date("Y-m-d 00:00:00"))) {
    $backup = MYSQL_DUMP . " " . MYSQL_DUMP_ARGS . " > " .
        MYSQL_DUMP_OUT;
    exec($backup, $out);

    if ($_POST["username"] != "superadmin") {
        $db->query("UPDATE USER SET last_login = NOW() WHERE username = 'superadmin'");
    }
}

This handful of lines of code was my solution. The first part of the routine checks the the last login time of the superadmin account. If last login occurred today then the second part will be ignored. Otherwise, the backup is made and the last login time for the superadmin account is updated to today.

In addition to possibly wiping out the entire database, the superadmin user also is able to restore backups, which is why I checked who was logging in before updating the last login time. When the superadmin account logs in regularly, his last login time will be updated by the login code, so either way the field will be updated for him.

It’s amazing how sometimes the most perplexing problems have a simple and elegant solution. One thing I do when I’m stack is to run through all of the processes of the program looking for the convenient hook or loophole. This particular solution may not be for everyone, but it works well enough for my problem and allowed me to use the existing platform that was available to me and still keep sensitive passwords out of site from general users. Thanks for letting me share, and if anything I hope it encourages you to find creative solutions in your own day-to-day programming problems.

Image via Africa Studio / Shutterstock