Backup database cron script

Other, PHP 3 responses so far

In information technology, a backup, or the process of backing up, refers to the copying and archiving of computer data so it may be used to restore the original after a data loss event. The primary purpose is to recover data after its loss, be it by data deletion or corruption. Backing up your database as a cronjob is an easy,efficient way of getting your database backed up as a .sql file into your server directory. Later u can upload or use this .sql file to regenerate database using import tool of phpmyadmin.

In the following cron script an entire database is backed up. One may also backup only some tables instead of the entire database. The Backed up .sql file is named based on the current timestamp and some hashed string. So just set up a periodic time limit in cron manager in cpanel and connect this script as a link there and your database will be backed up automatically and periodically.


#!/usr/local/bin/php.cli
<?
backup_tables('localhost','user','password','database');
/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
 $link = mysql_connect($host,$user,$pass);
 mysql_select_db($name,$link);
 //get all of the tables
 if($tables == '*')
 {
 $tables = array();
 $result = mysql_query('SHOW TABLES');
 while($row = mysql_fetch_row($result))
 {
 $tables[] = $row[0];
 }
 }
 else
 {
 $tables = is_array($tables) ? $tables : explode(',',$tables);
 }
 foreach($tables as $table)
 {
 $result = mysql_query('SELECT * FROM '.$table);
 $num_fields = mysql_num_fields($result);

 $return.= 'DROP TABLE '.$table.';';
 $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
 $return.= "\n\n".$row2[1].";\n\n";
 for ($i = 0; $i < $num_fields; $i++)
 {
 while($row = mysql_fetch_row($result))
 {
 $return.= 'INSERT INTO '.$table.' VALUES(';
 for($j=0; $j<$num_fields; $j++)
 {
 $row[$j] = addslashes($row[$j]);
 $row[$j] = ereg_replace("\n","\\n",$row[$j]);
 if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
 if ($j<($num_fields-1)) { $return.= ','; }
 }
 $return.= ");\n";
 }
 }
 $return.="\n\n\n";
 }

 //save file to a folder using current timestamp as file name
 $handle = fopen('mybackup/db-backup-'.date('Y-m-d h:i:s').'-'.(md5(implode(',',$tables))).'.sql','w+');
 fwrite($handle,$return);
 fclose($handle);
}
?>

The following two tabs change content below.
I am a core php developer and web designer working as a freelancer in elance,odesk etc.

Latest posts by Mudit (see all)

  • chrismccoy

    it would be much easier to just do this in bash wouldnt it? you can do it via just a mysqldump command

  • http://www.pakadtrader.com Waqas

    You can do it using following command. Set it as cron.
    mysqldump –opt -u user_name -db_password db_name | gzip > /path/to/save/backup/file_name.bak.gz

  • http://www.daveismyname.com Dave

    I agree with the other comments it is possible to this this in a command line, but not everyone has access to one, in which case the above script will works fine.