Universal DB Backup

[lfhost]

Hey All,

It was asked on the forum for a DB backup via cron.

So here it is.

Save this in a file called sql_backup.php for example.

<?php
ob_start();
set_time_limit(0);
//enter your MySQL database host name, often it is not necessary to edit this line
define('DBHOST', 'localhost');

//enter your MySQL database username
define('DBUSERNAME', '');

//enter your MySQL database password
define('DBPASSWORD', '');

//enter your MySQL database name
define('DBNAME', '');

//full path to the backup directory (this should be a writable directory i.e. chmodd to 777)
$filelocation = "/home/cpanel_username/bk/";

$conn = mysql_connect(DBHOST, DBUSERNAME, DBPASSWORD) or die(mysql_error());
$db = mysql_select_db(DBNAME, $conn) or die(mysql_error());

$traffic_time = time();
$traffic_reset = mktime(0,0,0,date("n"),date("j"),date("Y"));
$traffic_reset2 = mktime(0,2,0,date("n"),date("j"),date("Y"));

//////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////                                                     /////////////////////////
/////////////////////////                DO NOT EDIT BELOW THIS LINE !         /////////////////////////
/////////////////////////                                                     /////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////

$today = date("h.i.s_dMY");
$backup = $today . "_dbdump.sql";

if($traffic_time >= $traffic_reset && $traffic_time <= $traffic_reset2)
{ 
    $data = '';
    $tables_sql = mysql_query("SHOW TABLES") or die(mysql_error());

    while($tables = mysql_fetch_row($tables_sql)) 
    {
        $tables_create_sql = mysql_fetch_row(mysql_query("SHOW CREATE TABLE ".$tables[0])); 
         $data .="\r\n#\r\n# Table structure for table `".$tables_create_sql[0]."`\r\n#\r\n\r\n"; 
         $data .="DROP TABLE IF EXISTS `".$tables[0]."`;\r\n"; 
         $data .=str_replace(" 
    ", "\r\n", "$tables_create_sql[1]").";\r\n\r\n";

         $data .="#\r\n# Dumping data for table `".$tables[0]."`\r\n#\r\n\r\n"; 

         $sql = mysql_query("SELECT * FROM ".$tables[0]." limit 2500") or die(mysql_error()); 
         $num_rows = mysql_num_rows($sql); 
         if ($num_rows!=0) 
         { 
            while ($row = mysql_fetch_assoc($sql)) 
            { 
                $count = 0; 
                $data .="INSERT INTO `".$tables[0]."` VALUES (";
                
                foreach ($row as $inserts) 
                {     
                    $data .="'".addslashes($inserts)."'";   
                    
                    $count=$count+1; 
                    if (count($row) > $count) 
                    { 
                       $data .=", "; 
                    } 
                    else 
                    { 
                       $data .=""; 
                    }                 
                }     
                
                      $data .=");\r\n";               
            } 
         } 
    }
    $fp = fopen($filelocation.$backup,"w"); 
    $written= fputs($fp, $data); 
    fclose($fp);
    if(!$written)
    {
        echo "backup failed";
    }
    else
    {
        echo "backup successful";
    }
}    
exit(); 
ob_end_flush();
?>

This is non script dependent. It will read your db, all tables and content and back to a file on your server. DO NOT set it to be a public folder, as you are then allowing people access to your DB information freely. If you are on a cpanel based server, then add it before public_html/ like /home/CPANELUSERNAME/bk/ You would then need to login to FTP to back this up.

If you dont have the option of not sending it to a non public folder, then lock the folder with .htpssd (password protected directory)

You will need to modify the following to your server settings.

//enter your MySQL database host name, often it is not necessary to edit this line
define('DBHOST', 'localhost');

//enter your MySQL database username
define('DBUSERNAME', '');

//enter your MySQL database password
define('DBPASSWORD', '');

//enter your MySQL database name
define('DBNAME', '');

//full path to the backup directory (this should be a writable directory i.e. chmodd to 777)
$filelocation = "/home/cpanel_username/bk/"; 

read the notes next to each field before editing.

The file is sitelocked so bots, rogue users cannot run the file.
If you want the file to be run at another time, then you can change

$traffic_reset = mktime(0,0,0,date("n"),date("j"),date("Y"));
$traffic_reset2 = mktime(0,2,0,date("n"),date("j"),date("Y"));

mktime is set in the following order

mktime(HOURS,MINUTES,SECONDS,MONTH,DAY,YEAR);

So say you want to run it at 11pm you would change it to

$traffic_reset = mktime(11,0,0,date("n"),date("j"),date("Y"));
$traffic_reset2 = mktime(11,2,0,date("n"),date("j"),date("Y"));

The reason it is set to 2 minutes, is just in case the file gets delayed by your server.

Setup a cron to be at midnight at an interval of your choosing. The settings you need for the cron to run everyday at midnight are

0 0 * * * php -q /home/cpanel_username/public_html/sql_backup.php

you break it down as follows

Minute: 0
Hour: 0
Day: *
Month: *
Weekday: *
Command: php -q /home/cpanel_username/public_html/sql_backup.php

You can modify this to how many times you want to run the importer.

Remember to change cpanel_username above to your username.
Or change the whole path to the correct path to the sql_backup.php file.

If you wish to test the script without using a cron, then comment out

if($traffic_time >= $traffic_reset && $traffic_time <= $traffic_reset2)
{ 

like

/*
if($traffic_time >= $traffic_reset && $traffic_time <= $traffic_reset2)
{ 
*/ 

and the last

} 

like

/*
}
*/ 

Last but not least, I limited the DB records to be 2500 as my test site was timing out on large table sets. You can always remove this by changing

         $sql = mysql_query("SELECT * FROM ".$tables[0]." limit 2500") or die(mysql_error()); 

to

         $sql = mysql_query("SELECT * FROM ".$tables[0]) or die(mysql_error()); 

Entirely up to you,

Enjoy.

[ZyPhiX]

Ok thanks a lot!!! cool :-)

[RickyG]

code monkey at work!

[lfhost]

Your welcome :P

Gotta live up to my name LOL.

But im still hungry for moar lol.