Automatically repair and backup the CMS or blog database

There are endless ways to backup your blog's or your CMS' database. Some use platform specific plugins, some run thing manually, many - unfortunately - don't bother backing up their databases at all. If you don't back up your database - you should, it's not a complicated thing to set up.

I personally prefer the method of a simple and straightforward PHP script executed daily by a cron job. Following is the simple script I build for each of the databases I need to backup. Once you configure and run it, it will perform the following :

1. Check and repair your database for any consistency errors.
2. Create a database SQL dump with a date timestamp in the designated backup directory.
3. Archive the SQL dump using GZip.

Here's the PHP file.

<?php

       

        // BACKUP directory
        $backupDir = "/home/user/tmp/";
        // username for MySQL
        $user = "user";
        // password for MySQl
        $password = "password";
        // database name to backup
        $dbName = "dbname";

 

        $sqlFile = $backupDir.$prefix.date('Y_m_d').".sql";
        $fixDB = "mysqlcheck -p --auto-repair -p".$password," -u". $user ." ".$dbName;
        $creatBackup = "mysqldump --user=".$user." --host=localhost --password=".$password." ".$dbName." > ".$sqlFile;
        $createZip = "gzip --force $sqlFile > $attachment";

 

        exec($fixDB);
        exec($creatBackup);
        exec($createZip);

?>

To use it :

1. Copy and paste this into a file and name it with a .php extension.
2. Edit the settings to match those of the database you wish to backup.
3. Make sure that the backup directory is writable. 
4. Test it through SSH or PHPshell.

If you wish to execute this through a cronjob, put it in your /etc folder and configure your cronjob to this:

/usr/bin/php /home/user/etc/backup-db.php >/dev/null

Obviously, it's up to you how often you want to run this. Note that the file will rewrite if saved again on the same day. You can change that by playing with "$prefix.date('Y_m_d')" bit. I'm also assuming you're running on localhost.

Ofcourse, this will only store daily backups of your DB into your account. If the whole server is gone, then that requires a more complicated solution. I'm planning to post a more detailed script with automatic DB delivery by e-mail and FTP synch with other accounts on future posts, but this should be a good first solution.