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.
Click here to know how to import mysql database.

PHP Code


<?php
include("connection.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.= "\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] = str_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";
}

// Create 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();
?>


connection.php File

<?php
  $host="localhost";
  $uname="YourUserName";
  $pass="YourPassword";
  $database = "YourDatabaseName"; 
$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

Comments
31 January
2014
Dev
wow i like it this is so cool and realy like this code.
13 December
2013
Robert
Thanks for the script...

As for using the mysqldump command:

Most hosting providers do not allow php to execute shell commands. Again some hosting providers don't allow cron jobs. Again maybe you want to dump the DB and download to another machine...

12 December
2013
User
Do not need PHP to do a MySQL backup with cron.
Use mysqldump command in a cron job.

Easy to find info about such method on web, such as:
http://www.a2hosting.com/kb/developer-corner/mysql/mysql-database-backups-using-cron-jobs
comments powered by Disqus