PHP Backup Script for mySQL

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • brett
    Member
    • Mar 2004
    • 45

    PHP Backup Script for mySQL

    There were a couple of these posted in the previous forums and I'm rather bummed that I didn't acquire them when I had the chance.

    If anyone has scripts they've used in production to email and/or dump the mySQL database (or certain tables) to another server, please feel free to drop them in the forums again. I remember one script that handled:
    - zipping database
    - emailing database
    - FTP'ing db to another server, etc...

    If Dathorn would have been using something like this, maybe I wouldn't have to post this request... I just assumed the data would always be accessible. Oh well...

    Thanks for reposting if you have one of these fantastic tools available!!!
  • Jonathan
    Senior Member
    • Mar 2004
    • 1229

    #2
    I believe if you buy the Daily/Weekly/Monthly (you pick which)
    backups from Dathorn for $2 per GB/month, it not only basicly
    backs up all your files to an seperate FTP Server, but also the mySQL databases;

    Not sure of how the mySQL db's are kept; if zipped, etc.
    as I've yet to need it to retrieve mine, nor my customer's, data.
    "How can someone be so distracted yet so focused?"
    - C

    Comment

    • piku
      Senior Member
      • Mar 2004
      • 153

      #3
      here's the one from andy's post in the old forums...

      Code:
      <?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        = 'xxxx_xxxx';      // Username to access MySQL database
        $dbpass        = 'xxxx';    // Password to access MySQL database
        $dbname        = 'xxxxx_xxxx';      // 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       = 'xxx@xxxxx.ca';   // 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    = 'xxxxx.net';   // FTP hostname
        $ftp_user_name = 'xxxx';   // FTP username
        $ftp_user_pass = 'xxxx';   // FTP password
        $ftp_path      = "/DB_Backups";  // 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...
      
        */
       ?>
      my signature was deleted. i miss my signature-filled-with-helpful-dathorn-links *sniff*sniff*

      Comment

      • brett
        Member
        • Mar 2004
        • 45

        #4
        You are the man piku! I should be able to figure out the few line breaks that vBulletin added to wrap it within the code window.

        Could this be executed with a cron job then, being kept out of the web directories? Now to best execute this script every month. Any suggestions are welcome, but I'm sure I can figure it out over time.

        Thanks!

        Comment

        • piku
          Senior Member
          • Mar 2004
          • 153

          #5
          thank andy for giving them to the community as well. but as for cron--its possible as others have ran it here successfully.
          my signature was deleted. i miss my signature-filled-with-helpful-dathorn-links *sniff*sniff*

          Comment

          • Josh
            Junior Member
            • Mar 2004
            • 6

            #6
            To set this up in cron you should set the time you would like to run the script and enter this command:

            php /home/username/path/to/file
            Remember, it's best to run this sometime early morning when the server would not be in peak usage.

            Josh

            Comment

            • BigMike
              Junior Member
              • Mar 2004
              • 7

              #7
              In the script above is the sentence:
              The script needs write-privileges on the directory it resides in!
              I think I should know this by now, but can't get it to work ... how do I do this? I plan to have the script in the home directory.

              Thanks

              Comment

              • Andy
                Senior Member
                • Mar 2004
                • 257

                #8
                It should work. I have been using it for awhile now. I created a folder in my /home directory, left it with default permissions (755) and it writes to it no problem.

                I think it will create an error log if it fails. Do you see an log file. error.log i think. That will tell you why.
                Andy

                Comment

                • Slider
                  Junior Member
                  • Mar 2004
                  • 16

                  #9
                  For a few of my sites, I run a shell script that exports the databases of my choice, TAR's them and then FTP's them to another server of my choice. It runs off a cron job every night at 4AM CST. I cannot post them from work right now, but when I get home, I can post them up here for anyone who wants to use them. When I first created them back last year, I got a lot of help from people in this forum and I think it's time to start giving some help back...
                  Broadband Rangers
                  Project Goodtimes

                  Comment

                  • BigMike
                    Junior Member
                    • Mar 2004
                    • 7

                    #10
                    Originally posted by Andy
                    It should work. I have been using it for awhile now. I created a folder in my /home directory, left it with default permissions (755) and it writes to it no problem.

                    I think it will create an error log if it fails. Do you see an log file. error.log i think. That will tell you why.
                    I don't see an error log, but the error that gets reported is:
                    Warning: gzopen(/home/cpuser/backups/xxxx_xx-20040331-1058.sql.gz): failed to open stream: Permission denied in /home/cpuser/backups/dbbackup.php on line 85
                    line 85 looks like:
                    $zp = gzopen($file_path, "wb9");
                    I have set the dbbackup.php file permissions to 755.

                    Mike

                    Comment

                    • BigMike
                      Junior Member
                      • Mar 2004
                      • 7

                      #11
                      Got it ... I had to set the permissions on the backups folder too.

                      All seems to be working now.

                      Thanks,

                      Mike

                      Comment

                      • Andy
                        Senior Member
                        • Mar 2004
                        • 257

                        #12
                        Excellent. Enjoy
                        Its a nice little program
                        Andy

                        Comment

                        • reviewum.com
                          Member
                          • Mar 2004
                          • 63

                          #13
                          Nice backup script... now what?

                          This is a nice little piece of script. I had a problem at first, but just had to remove the hard returns in the code and all worked fine.

                          Okay, so I have this nice little file that is suppose to be my backup... now what?

                          For those of us who are MySQL challenged <raising my hand> what is the best way to restore this file / DB if my site blows up? Should we use Cpanel or use PhpMyAdmin? What are the stepss in either case to restore a DB using this file alone?

                          Thanks in advance!!!

                          oh... btw, is this file secure simply by the fact someone will not know the file location or name (since it has password info)?
                          www.reviewum.com - Teacher Reviews - Professor Ratings
                          www.nifty-stuff.com - All Kinds Of Nifty Stuff (mostly LEDs)
                          www.LudCon.com - Ludlow Concepts

                          Comment

                          • Jonathan
                            Senior Member
                            • Mar 2004
                            • 1229

                            #14
                            Originally posted by reviewum.com
                            oh... btw, is this file secure simply by the fact someone will not know the file location or name (since it has password info)?

                            A person cannot download (like right-click link),
                            a *.PHP/PHTML/PHP3 file (all same, just diff extentions)
                            where they can see the actual PHP coding;

                            Same with viewing it, all the source
                            is put out as "standard" HTML, abiet messy and all cramped
                            "How can someone be so distracted yet so focused?"
                            - C

                            Comment

                            • Andy
                              Senior Member
                              • Mar 2004
                              • 257

                              #15
                              You can use myphpadmin to restore the DB
                              Andy

                              Comment

                              Working...