使用PHP自动备份MySql数据库

Question

I would like to backup my database and mail it to my email id using php. I found a script which backups my database and mail it to me in a zip form, but when I try to execute the script, it works fine but the mail doesnt comes in my inbox. Here is the script:

<?php
$db_host="localhost";   //mysql host  
$db_user="*******";  //databse user name
$db_pass="*******";  //database password
$db_name="******";  //database name
$tables="*";   // use * for all tables or use , to seperate table names
$email="*******";     //your email id
///////////////////////////////////////////////////////////////////////////////////////////

/////////don't need to change bellow //////

backup($db_host,$db_user,$db_pass,$db_name,$tables,$email);
function backup($db_host,$db_user,$db_pass,$db_name,$tables = '*',$email)
{

  $con= mysql_connect($db_host,$db_user,$db_pass);
  mysql_select_db($db_name,$con);

  //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);
  }

  //cycle through
  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.= "

".$row2[1].";

";

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


";
  }

  //save file
  $filename='db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql';
  $handle = fopen($filename,'w+');
  fwrite($handle,$return);
  fclose($handle);
  compress($filename);
  send_mail($filename.".zip",$email);
}

function send_mail($filepath,$email)
{

$from = "Backup <cepheisys.com/mac>";
$subject = "Database backup";
$message="This attachment contains the backup of your database.";
$separator = md5(time());

// carriage return type (we use a PHP end of line constant)
$eol = PHP_EOL;

// attachment name
$filename = "backup.zip";

//$pdfdoc is PDF generated by FPDF
$attachment = chunk_split(base64_encode(file_get_contents($filepath)));

// main header
$headers  = "From: ".$from.$eol;
$headers .= "MIME-Version: 1.0".$eol; 
$headers .= "Content-Type: multipart/mixed; boundary=\"".$separator."\"";

// no more headers after this, we start the body! //

$body = "--".$separator.$eol;
$body .= "Content-Transfer-Encoding: 7bit".$eol.$eol;
$body .= "This is a MIME encoded message.".$eol;

// message
$body .= "--".$separator.$eol;
$body .= "Content-Type: text/html; charset=\"iso-8859-1\"".$eol;
$body .= "Content-Transfer-Encoding: 8bit".$eol.$eol;
$body .= $message.$eol;

// attachment
$body .= "--".$separator.$eol;
$body .= "Content-Type: application/octet-stream; name=\"".$filename."\"".$eol; 
$body .= "Content-Transfer-Encoding: base64".$eol;
$body .= "Content-Disposition: attachment".$eol.$eol;
$body .= $attachment.$eol;
$body .= "--".$separator."--";

// send message
if (mail($email, $subject, $body, $headers)) {
    echo "Your backup sent to your email id";
    header("refresh: 1; main.php");
} else {
    echo "Oops mail can not be send";
}
}
function compress($filepath)

     {

         $zip = new ZipArchive();
  $file=$filepath.".zip";
 if($zip->open($file,1?ZIPARCHIVE::OVERWRITE:ZIPARCHIVE::CREATE)===TRUE)
 {
   // Add the files to the .zip file
   $zip->addFile($filepath);

   // Closing the zip file
   $zip->close();
 }
     }                    

Answer 1

I do this for a hobby, so any input is humbly appreciated.

1. $row[$j] = ereg_replace(" ","\ ",$row[$j]); Is depreciated and should be replaced w/ something like:

   $pattern = '/\\
   /';
   $replacement = '\\\\
   ';
   $row[$j] = preg_replace($pattern, $replacement, $row[$j] );
   

2. I couldn't get your code to email effectively, so I just emailed myself the link. I have to follow the link and log on via ftp, but it's there. LMK if you have any input.

3. I returned the path on your code then added the file location to my database::

   function Add_BU_to_Database ($filename){
       $conn = db_connect(); //connects to my db
       $BU_Hist_num = General_Get_num('BU_History','BU_Hist_num'); //gets primary field
       $now = time(); 
       $BU_Add_Qry = $conn->query("insert into BU_History 
       (BU_Hist_num,BU_Path,BU_Time_Add,BU_Time_Del,BU_Status)                 
       Values('".$BU_Hist_num."','".$filename."','".$now."','','Active')");
   }//end function`
   

4. I then updated the ini at the top of the page so it wouldn't timeout::

   ini_set('memory_limit', '1G'); 
   ini_set('max_execution_time', 300);
   

5. I then wrote the rest of the code to delete old file and update the database on a new php file::

   <?php
   ini_set('memory_limit', '1G'); 
   ini_set('max_execution_time', 300);
   
   $conn = db_connect();
   $AweekAgo = time()-1209600;
   
   $DBDeletionQry = $conn->query("select * from BU_History where BU_Status = 'Active' and BU_Time_Add < '$AweekAgo' ");
   $DBD_rows = mysqli_num_rows($DBDeletionQry);
   
   echo "Files older than two weeks: ".$DBD_rows."<br>";
   $filesdeleted = 0;
   
   for($i=0;$i<$DBD_rows;$i++){
       //Finds each file path
       $DBD_row = mysqli_fetch_assoc($DBDeletionQry);
       $BU_Path = "".$DBD_row['BU_Path'];
       $BU_Hist_num = $DBD_row['BU_Hist_num'];
       //Shows Paths
       echo $BU_Path."<br>";
   
       //Delete's File
       if(unlink($BU_Path)){$filesdeleted = $filesdeleted+1;}
   
       //Updates the Database
       $now = time();
       $DBDUpdate = $conn->query("update BU_History set BU_Time_Del = '$now',     BU_Status = 'Inactive' where BU_Hist_num = '$BU_Hist_num' ");
   
   }//end for
   echo "Files deleted: ".$filesdeleted."<br>";
   ?>
   

6. And last but not least, I used my cron manager to run both the backup and delete-backup .php files.