MySQL Database Backup Using PHP

If your site is live and using Database then it is very important to take backup of you Database in regular time interval. But it is not possible to take the backup manually every time. So lets create a simple PHP function to do this job for us and we can call that function using Cron Job in regular time interval.

Here is the sample of php code

<?php
include("config.php");
function backup_db(){
/* Store All Table name in an Array */
$allTables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result)){
     $allTables[] = $row[0];
}

foreach($allTables as $table){
$result = mysql_query('SELECT * FROM '.$table);
$num_fields = mysql_num_fields($result);

$return.= 'DROP TABLE IF EXISTS '.$table.';';
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
$return.= "nn".$row2[1].";nn";

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] = str_replace("n","\n",$row[$j]);
       if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; }
       else { $return.= '""'; }
       if ($j<($num_fields-1)) { $return.= ','; }
     }
   $return.= ");n";
}
}
$return.="nn";
}

//Generate Backup Folder
$folder = 'DB_Backup/';
if (!is_dir($folder))
mkdir($folder, 0777, true);
chmod($folder, 0777);

$date = date('m-d-Y-H-i-s', time());
$filename = $folder."db-backup-".$date; 

$handle = fopen($filename.'.sql','w+');
fwrite($handle,$return);
fclose($handle);
}

// Call the function
backup_db();
?>

config.php file
<?php
  $host="hostname";
  $uname="uname";
  $pass="password";
  $database = "databasename";
$connection=mysql_connect($host,$uname,$pass);
or die("Database Connection Failed");
$selectdb=mysql_select_db($database) or die("Database could not be selected");
$result=mysql_select_db($database)
or die("database cannot be selected <br>");
?>

Save the above code in a PHP file and upload the file to your web server
then run the file. And check if it create a folder called DB_Backup  and
create the backup .sql  file  inside DB_Backup folder. If the file  works
fine then  you can assign the file to your Cron Job.

Need more help?

Hi there, was your problem or query resolved? If not & need more assistance, please do reach out to us at info@nanostuffs.com, we'll be more than delighted to help. Nanostuffs has 7+ years of extensive Salesforce & iOS/Android experience.
Holler Box