Automatically Backup Your SQL Database With This...

I am leaving this bit of code that you can add into a php file and then simply attach it to a cron job on your server for the time you would like to automatically optimize and backup your database. This code will also automatically delete backups as they become older. The default I will set is to delete backups older than 7 days but can also be edited to your liking. Be sure to create a folder in your home directory which for the purpose of this will be called MySQL-Backup that will save your database backups.

// replace the $c below with anything you may have defined for your mySQL connection variable
$alltables = mysql_query("SHOW TABLES", $c);

while ($table = mysql_fetch_assoc($alltables))
{

    foreach ($table as $db => $tablename)
    {
        mysql_query("OPTIMIZE TABLE $tablename", $c) or die(mysql_error());
    }

}
$date = date('m-d-y_A-h.i', (time() - 3600)) . "-5.00GMT";
// $filename below will name of your file when it backs up so I will leave an example
$filename = '/home/now/MySQL-Backup/databasename_' . $date . '.sql.gz';
// $run you must enter your username ,db password, db name
$run = "mysqldump -uUSERNAME -pPASSWORD --opt DATABASENAME | gzip > $filename";

exec($run);
print $date . "<br>" . $run . "<br>" . $filename;
//$dir must be the location where you created the folder to save the backups to.
$dir = '/home/now/MySQL-Backup/';
$del_older_than = 7; // Days
$handle = opendir($dir);

while (($file = readdir($handle)) !== false)
{
    $path = $dir . $file;

    if (is_file($path) and (time() - (3600 * 24 * $del_older_than)) > filemtime($path))
    {
        unlink($path);
        $deleteArr[] = $path;
    }
}

closedir($handle);
if (is_array($deleteArr))
{
    $file = fopen('/home/now/MySQL-Backup/deleteLog.txt', 'a');
    fwrite($file, "Deleted " . date('m/d/y g:i:s A', (time() - 3600)) . ":\n" .
        implode("\n", $deleteArr) . "\n\n");
    fclose($file);
}
mysql_close($c);

PHP:

If you find it useful, please give me some positive points as I am trying to build up my profile. Thank you.

 

I've set up similar systems in the past. There's no real reason to use PHP, can just set up a simple shell script.

Not bad though.

 

Yes I agree there are many ways. Just figured if someone wants to do it using a simple PHP file that I would try to add my 2 cents lol.

 

BTW this won't work on webhosting...

 

This WILL work on any hosting account where you can add a cron job and actually have access to your own files. I currently use it, I must add that I find you quite amusing seeing as you have attempted to comment on all my postings with something negative. If only you actually read what I wrote, maybe then you could respond with something sensible.