PHP5: MySQL DB backup (daily, weekly, monthly, cron, email and ftp options)?

[piku]

Hey,

I've been using Wolf's db backup (http://www.restkultur.ch/personal/wo...pts/db_backup/) which works damn well. However, I'm trying to find another script that is great on PHP5. Wolf's seems to break on it. Anyone got a recommendation for something that allows intervals by day, week and month; as well as options to run cron on it, and have it email and ftp to another server?

TIA
Sherwin


PS. Hotscripts had so many, I rather ask first to pinpoint a good one.

[piku]

Hey,

I've been using Wolf's db backup (http://www.restkultur.ch/personal/wo...pts/db_backup/) which works damn well. However, I'm trying to find another script that is great on PHP5. Wolf's seems to break on it. Anyone got a recommendation for something that allows intervals by day, week and month; as well as options to run cron on it, and have it email and ftp to another server?

TIA
Sherwin


PS. Hotscripts had so many, I rather ask first to pinpoint a good one.

Seems that this broke on PHP4.4.4 as well. Anyone have an update on this script? TIA

[ZYV]

Why wouldn't you simply use mysqldump?

[piku]

I could, but defeats the purpose of the thread right? =)

Maybe I should've put this, automated "MySQL DB backup (daily, weekly, monthly, cron, email and ftp options)" solution...

[samsam]

http://www.phpmybackuppro.net

phpMyBackup Pro is a very easy to use, free, web-based MySQL backup application, licensed under the GNU GPL.
You can create scheduled backups, manage and restore them, download or email them and a lot more!

Major features:
- backup of one or several databases with or without data, table structure, ...
- three types of compression (no compression, gzip or zip)
- scheduled backups (by a small PHP script which must be included in an existing PHP script)
- interface for managment of the backups (viewing, restoring, downloading, deleting)
- backup directly onto FTP server and sending of backups by email
- platform independent - only webserver and PHP needed to run e.g. on MS Windows, Linux or Mac
- shell mode (to use manually or by cron script)
- backup of whole file directories (on a FTP server).
- backup databases from different accounts on several MySQL servers
- one installation can be used for all MySQL users of one MySQL server (used by webhosters)
- highest security through two alternative login methods (HTTP or HTML authentication)
- easy to use interface and very easy to install
- several language packages available
- online help (included in pMBP) and FAQ on this website

Newish v2 released in Feb 2007.

I can confirm it works on Dathorn although I haven't tested in under PHP5. It should work there too.

Scheduling is a bit clumsy - must use cron to execute a php page the app generates, like many of these tools. But it works.

FTP backups to a remote server work.

Nice feature is RESTORE from within the app itself, not only BACKUP.

And it has a support forum on sourceforge too
http://sourceforge.net/forum/forum.php?forum_id=260005

[piku]

Cool thanks sam... I'll take a look at it.

[Andy]

Hey Piku

I have been using this script for the last few years. I havent had any problems. If I remember correctly, I think I gave this to you back then as well.

<?php
  /* HMS_BACKUP.PHP
     Holomarcus MySQL Database Backup   
    
     Version 1.0 - August 15th, 2003
     Changelog: see bottom of script.
     
     (c)2003 Holomarcus ([url]http://www.holomarcus.nl[/url])
     You can distribute this script and use it freely as
     long as this header is not edited in the script.
     
     With HMS_BACKUP you can make a backup of your MySQL-database.
     This backup can be sent by e-mail or uploaded through FTP.
     
     This script doesn't need privileges to execute *nix commands.
     It's 100% pure PHP.
     
     The script needs write-privileges on the directory it resides in!
     
     Change the necessary settings below...
  */

  $dbhost        = 'localhost';  // Server address of your MySQL Server
  $dbuser        = 'username';      // Username to access MySQL database
  $dbpass        = 'password';    // Password to access MySQL database
  $dbname        = 'username';      // Database Name

  $use_gzip      = 'yes';        // Set to No if you don't want the files sent in .gz format

  $remove_file   = 'no';        // Set this to yes if you want to remove the file after sending. Yes is recommended.

  $use_email     = 'no';          // Set to 'yes' if you want the backup to be sent throug email. Fill out next 3 lines.
  $send_to       = 'name@domain.com';   // E-mail to send the mail to
  $send_from     = 'DB_BAckp'; // E-mail the mail comes from
  $subject       = "MySQL Database ($dbname) Backup - " . date("j F Y"); // Subject in the email to be sent.

  $use_ftp       = 'no'; // Do you want this database backup uploaded to an ftp server? Fill out the next 4 lines
  $ftp_server    = 'domain.com';   // FTP hostname
  $ftp_user_name = 'username';   // FTP username
  $ftp_user_pass = 'password';   // FTP password
  $ftp_path      = 'path_to_folder/';  // This is the path to upload on your ftp server!

  $echo_status = 'no';   // Set to 'no' if the script should work silently (no output will be sent to the screen)


# You probably don't need to edit below this line....
#-------------------------------------------------------------------------------

  $db = mysql_connect("$dbhost","$dbuser","$dbpass");
	mysql_select_db("$dbname",$db);

  $path = make_dir();
  
  if ($echo_status == 'yes') {
    print "Dumpfile will be written to $path<br>";
  }

  $result = mysql_query("show tables from $dbname");
  while (list($table) = mysql_fetch_row($result)) {
    $newfile .= get_def($table);
    $newfile .= "\n\n";
    $newfile .= get_content($table);
    $newfile .= "\n\n";
    $i++;
    if ($echo_status == 'yes') {
      print "Dumped table $table<br>";
    }
  }

	$file_name = $dbname . "-" . date("Ymd-Hi") . ".sql";
	$file_path = $path . $file_name;

  if ($use_gzip == "yes") {
    $file_name .= ".gz";
    $file_path .= ".gz";
    $zp = gzopen($file_path, "wb9");
    gzwrite($zp,$newfile);
    gzclose($zp);

    if ($echo_status == 'yes') {
      print "<br>Gzip-file is created...<br>";
    }
  } else {
    $fp = fopen($file_path, "w");
    fwrite($fp, $newfile);
    fclose($fp);

    if ($echo_status == 'yes') {
      print "<br>SQL-file is created...<br>";
    }
  }

  if ($use_email == 'yes') {
    $fileatt_type = filetype($file_path);
  
    $headers = "From: $send_from";
  
    // Read the file to be attached ('rb' = read binary)
    $fp = fopen($file_path,'rb');
    $data = fread($fp,filesize($file_path));
    fclose($fp);
  
    // Generate a boundary string
    $semi_rand = md5(time());
    $mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";
  
    // Add the headers for a file attachment
    $headers .= "\nMIME-Version: 1.0\n" ."Content-Type: multipart/mixed;\n" ." boundary=\"{$mime_boundary}\"";
  
    // Add a multipart boundary above the plain message
    $message = "This is a multi-part message in MIME format.\n\n" ."--{$mime_boundary}\n" ."Content-Type: text/plain; charset=\"iso-8859-1\"\n" ."Content-Transfer-Encoding: 7bit\n\n" .
    $message . "\n\n";
  
    // Base64 encode the file data
    $data = chunk_split(base64_encode($data));
  
    // Add file attachment to the message
    $message .= "--{$mime_boundary}\n" ."Content-Type: {$fileatt_type};\n" ." name=\"{$file_name}\"\n" ."Content-Disposition: attachment;\n" ." filename=\"{$file_name}\"\n" ."Content-Transfer-Encoding: base64\n\n" .
    $data . "\n\n" ."--{$mime_boundary}--\n";
  
    // Send the message
    $ok = @mail($send_to, $subject, $message, $headers);
    
    if ($echo_status == 'yes') {
      print "<br>Mail is sent...<br>";
    }
  }
  
  if ($use_ftp == 'yes') {
    if ($use_gzip == 'yes') {
      $mode = FTP_BINARY;
    } else {
      $mode = FTP_ASCII;
    }
    $ftp_id       = ftp_connect($ftp_server);
    $login_result = ftp_login($ftp_id, $ftp_user_name, $ftp_user_pass);
    $upload       = ftp_put($ftp_id, $ftp_path . $file_name, $file_path, $mode);
    ftp_close($ftp_id);

    if ($echo_status == 'yes') {
      print "<br>Backup is uploaded to $ftp_user_name@$ftp_server...<br>";
    }
  }

  if ($remove_file == "yes") {
    unlink($file_name);
    if ($echo_status == 'yes') {
      print "<br>File is deleted...<br>";
    }
  }

  if ($echo_status == 'yes') {
    print "<br>I am done!<br>";
  }


  function make_dir() {
    $page = split("/", getenv('SCRIPT_NAME'));
    $n = count($page)-1;
    $page = $page[$n];
    $page = split("\.", $page, 2);
    $extension = $page[1];
    $page = $page[0];
    $script 	= "$page.$extension";
    $base_url 	= "http://".$_SERVER['SERVER_NAME'];
    $directory 	= $_SERVER['PHP_SELF'];
    $url_base = "$base_url$directory";
    $url_base = ereg_replace("$script", '', "$_SERVER[PATH_TRANSLATED]");

    $path = $url_base;

    return $path;
  }

  function get_def($table) {
    $def = "";
    $def .= "DROP TABLE IF EXISTS $table;\n";
    $def .= "CREATE TABLE $table (\n";
    $result = mysql_query("SHOW FIELDS FROM $table") or die("Table $table not existing in database");
    while($row = mysql_fetch_array($result)) {
      $def .= "    $row[Field] $row[Type]";
      if ($row["Default"] != "") $def .= " DEFAULT '$row[Default]'";
      if ($row["Null"] != "YES") $def .= " NOT NULL";
      if ($row[Extra] != "") $def .= " $row[Extra]";
      $def .= ",\n";
    }
    $def = ereg_replace(",\n$","", $def);
    $result = mysql_query("SHOW KEYS FROM $table");
    while($row = mysql_fetch_array($result)) {
      $kname=$row[Key_name];
      if(($kname != "PRIMARY") && ($row[Non_unique] == 0)) $kname="UNIQUE|$kname";
      if(!isset($index[$kname])) $index[$kname] = array();
      $index[$kname][] = $row[Column_name];
    }
    while(list($x, $columns) = @each($index)) {
      $def .= ",\n";
      if($x == "PRIMARY") $def .= "   PRIMARY KEY (" . implode($columns, ", ") . ")";
      else if (substr($x,0,6) == "UNIQUE") $def .= "   UNIQUE ".substr($x,7)." (" . implode($columns, ", ") . ")";
      else $def .= "   KEY $x (" . implode($columns, ", ") . ")";
    }
    $def .= "\n);";
    return (stripslashes($def));
  }

  function get_content($table) {
    $content="";
    $result = mysql_query("SELECT * FROM $table");
    while($row = mysql_fetch_row($result)) {
      $insert = "INSERT INTO $table VALUES (";
      for($j=0; $j<mysql_num_fields($result);$j++) {
        if(!isset($row[$j])) $insert .= "NULL,";
        else if($row[$j] != "") $insert .= "'".addslashes($row[$j])."',";
        else $insert .= "'',";
      }
      $insert = ereg_replace(",$","",$insert);
      $insert .= ");\n";
      $content .= $insert;
    }
    return $content;
  }

  /* Changelog
  
     Version 1.0 - August 15th, 2003
     ===============================
     Created this beautiful script...

  */
 ?>