SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot themissingelf's Avatar
    Join Date
    Nov 2001
    Location
    UK
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Backing up MySQL databases

    So, you've designed and built a web site for a customer, complete with a MySQL backend and Content Management System. All kept nice and easy cos they're not techies.

    However, they're going to need to backup their database... How are the supposed to do it without getting their hands dirty with dumping etc?

    In an ideal world all the backups should be taken care of in the background on a Daily basis and deleted after X days.

    Any thoughts?

    Cheers

  2. #2
    SitePoint Zealot dereko's Avatar
    Join Date
    Dec 2003
    Location
    Ireland
    Posts
    189
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this will email you or whoever a ziped backup of the database.. just call a crontab to call in everyday or week .. what ever you prefer

    PHP Code:
    <?php
    /* Database Backup Utility 1.0 By Eric Rosebrock, [url]http://www.phpfreaks.com[/url]
    Written: July 7th, 2002 12:59 AM

    If running from shell, put this above the <?php "#! /usr/bin/php -q" without the quotes!!!

    This script is dedicated to "Salk". You know who you are :)

    This script runs a backup of your database that you define below. It then gzips the .sql file
    and emails it to you or ftp's the file to a location of your choice. 

    It is highly recommended that you leave gzip on to reduce the file size.

    You must chown the directory this script resides in to the same user or group your webserver runs
    in, or CHMOD it to writable. I do not recommend chmod 777 but it's a quick solution. If you can setup
    a cron, you can probably chown your directory! 

    IMPORTANT!!! I recommend that you run this outside of your
    web directory, unless you manually want to run this script. If you do upload it inside your web
    directory source tree, I would at least apply Apache access control on that directory. You don't 
    want people downloading your raw databases!

    This script is meant to be setup on a crontab and run on a weekly basis
    You will have to contact your system administrator to setup a cron tab for this script
    Here's an example crontab:

    0 0-23 * * * php /path/to/thisdirectory/dbsender.php > dev/null

    */

    // configure your database variables below:
    $dbhost 'localhost'// Server address of your MySQL Server
    $dbuser 'root'// Username to access MySQL database
    $dbpass ''// Password to access MySQL database
    $dbname ''// Database Name

    // Optional Options You May Optionally Configure

    $use_gzip "yes"// Set to No if you don't want the files sent in .gz format
    $remove_sql_file "no"// Set this to yes if you want to remove the .sql file after gzipping. Yes is recommended.
    $remove_gzip_file "no"// Set this to yes if you want to delete the gzip file also. I recommend leaving it to "no"

    // Configure the path that this script resides on your server.

    $savepath ""// Full path to this directory. Do not use trailing slash!

    $send_email_w_file "yes"// Do you want this database backup sent to your email? Fill out the next 2 lines
    $to ""// Who to send the emails to
    $from ""// Who should the emails be sent from?
    $senddate date("j F Y");
    $subject "MySQL Database Backup - $senddate"// Subject in the email to be sent.
    $headers "From: $from";
    if (
    $send_email_w_file == "yes") {
    $message "Your MySQL database has been backed up and is attached to this email."// Brief Message.
    } else {
    $message "Your MySQL database has been backed up and placed in $savepath."// Brief Message.
    }

    $use_ftp "no"// Do you want this database backup uploaded to an ftp server? Fill out the next 4 lines
    $ftp_server "localhost"// FTP hostname
    $ftp_user_name "ftp_username"// FTP username
    $ftp_user_pass "ftp_password"// FTP password
    $ftp_path "/"// This is the path to upload on your ftp server!

    // Do not Modify below this line! It will void your warranty!

    //---------GZIP CLASS
    class zipfile   
    {   

        var 
    $datasec = array(); // array to store compressed data 
        
    var $ctrl_dir = array(); // central directory    
        
    var $eof_ctrl_dir "\x50\x4b\x05\x06\x00\x00\x00\x00"//end of Central directory record 
        
    var $old_offset 0

        function 
    add_dir($name)    

        
    // adds "directory" to archive - do this before putting any files in directory! 
        // $name - name of directory... like this: "path/" 
        // ...then you can add files using add_file with names like "path/file.txt" 
        
    {   
            
    $name str_replace("\\""/"$name);   

            
    $fr "\x50\x4b\x03\x04"
            
    $fr .= "\x0a\x00";    // ver needed to extract 
            
    $fr .= "\x00\x00";    // gen purpose bit flag 
            
    $fr .= "\x00\x00";    // compression method 
            
    $fr .= "\x00\x00\x00\x00"// last mod time and date 

            
    $fr .= pack("V",0); // crc32 
            
    $fr .= pack("V",0); //compressed filesize 
            
    $fr .= pack("V",0); //uncompressed filesize 
            
    $fr .= pack("v"strlen($name) ); //length of pathname 
            
    $fr .= pack("v"); //extra field length 
            
    $fr .= $name;   
            
    // end of "local file header" segment 

            // no "file data" segment for path 

            // "data descriptor" segment (optional but necessary if archive is not served as file) 
            
    $fr .= pack("V",$crc); //crc32 
            
    $fr .= pack("V",$c_len); //compressed filesize 
            
    $fr .= pack("V",$unc_len); //uncompressed filesize 

            // add this entry to array 
            
    $this -> datasec[] = $fr

            
    $new_offset strlen(implode(""$this->datasec)); 

            
    // ext. file attributes mirrors MS-DOS directory attr byte, detailed 
            // at [url]http://support.microsoft.com/support/kb/articles/Q125/0/19.asp[/url] 

            // now add to central record 
            
    $cdrec "\x50\x4b\x01\x02"
            
    $cdrec .="\x00\x00";    // version made by 
            
    $cdrec .="\x0a\x00";    // version needed to extract 
            
    $cdrec .="\x00\x00";    // gen purpose bit flag 
            
    $cdrec .="\x00\x00";    // compression method 
            
    $cdrec .="\x00\x00\x00\x00"// last mod time & date 
            
    $cdrec .= pack("V",0); // crc32 
            
    $cdrec .= pack("V",0); //compressed filesize 
            
    $cdrec .= pack("V",0); //uncompressed filesize 
            
    $cdrec .= pack("v"strlen($name) ); //length of filename 
            
    $cdrec .= pack("v"); //extra field length    
            
    $cdrec .= pack("v"); //file comment length 
            
    $cdrec .= pack("v"); //disk number start 
            
    $cdrec .= pack("v"); //internal file attributes 
            
    $ext "\x00\x00\x10\x00"
            
    $ext "\xff\xff\xff\xff";   
            
    $cdrec .= pack("V"16 ); //external file attributes  - 'directory' bit set 

            
    $cdrec .= pack("V"$this -> old_offset ); //relative offset of local header 
            
    $this -> old_offset $new_offset

            
    $cdrec .= $name;   
            
    // optional extra field, file comment goes here 
            // save to array 
            
    $this -> ctrl_dir[] = $cdrec;   

              
        } 


        function 
    add_file($data$name)    

        
    // adds "file" to archive    
        // $data - file contents 
        // $name - name of file in archive. Add path if your want 

        
    {   
            
    $name str_replace("\\""/"$name);   
            
    //$name = str_replace("\\", "\\\\", $name); 

            
    $fr "\x50\x4b\x03\x04"
            
    $fr .= "\x14\x00";    // ver needed to extract 
            
    $fr .= "\x00\x00";    // gen purpose bit flag 
            
    $fr .= "\x08\x00";    // compression method 
            
    $fr .= "\x00\x00\x00\x00"// last mod time and date 

            
    $unc_len strlen($data);   
            
    $crc crc32($data);   
            
    $zdata gzcompress($data);   
            
    $zdata substrsubstr($zdata0strlen($zdata) - 4), 2); // fix crc bug 
            
    $c_len strlen($zdata);   
            
    $fr .= pack("V",$crc); // crc32 
            
    $fr .= pack("V",$c_len); //compressed filesize 
            
    $fr .= pack("V",$unc_len); //uncompressed filesize 
            
    $fr .= pack("v"strlen($name) ); //length of filename 
            
    $fr .= pack("v"); //extra field length 
            
    $fr .= $name;   
            
    // end of "local file header" segment 
              
            // "file data" segment 
            
    $fr .= $zdata;   

            
    // "data descriptor" segment (optional but necessary if archive is not served as file) 
            
    $fr .= pack("V",$crc); //crc32 
            
    $fr .= pack("V",$c_len); //compressed filesize 
            
    $fr .= pack("V",$unc_len); //uncompressed filesize 

            // add this entry to array 
            
    $this -> datasec[] = $fr

            
    $new_offset strlen(implode(""$this->datasec)); 

            
    // now add to central directory record 
            
    $cdrec "\x50\x4b\x01\x02"
            
    $cdrec .="\x00\x00";    // version made by 
            
    $cdrec .="\x14\x00";    // version needed to extract 
            
    $cdrec .="\x00\x00";    // gen purpose bit flag 
            
    $cdrec .="\x08\x00";    // compression method 
            
    $cdrec .="\x00\x00\x00\x00"// last mod time & date 
            
    $cdrec .= pack("V",$crc); // crc32 
            
    $cdrec .= pack("V",$c_len); //compressed filesize 
            
    $cdrec .= pack("V",$unc_len); //uncompressed filesize 
            
    $cdrec .= pack("v"strlen($name) ); //length of filename 
            
    $cdrec .= pack("v"); //extra field length    
            
    $cdrec .= pack("v"); //file comment length 
            
    $cdrec .= pack("v"); //disk number start 
            
    $cdrec .= pack("v"); //internal file attributes 
            
    $cdrec .= pack("V"32 ); //external file attributes - 'archive' bit set 

            
    $cdrec .= pack("V"$this -> old_offset ); //relative offset of local header 
    //        echo "old offset is ".$this->old_offset.", new offset is $new_offset<br>"; 
            
    $this -> old_offset $new_offset

            
    $cdrec .= $name;   
            
    // optional extra field, file comment goes here 
            // save to central directory 
            
    $this -> ctrl_dir[] = $cdrec;   
        } 

        function 
    file() { // dump out file    
            
    $data implode(""$this -> datasec);   
            
    $ctrldir implode(""$this -> ctrl_dir);   

            return    
                
    $data.   
                
    $ctrldir.   
                
    $this -> eof_ctrl_dir.   
                
    pack("v"sizeof($this -> ctrl_dir)).     // total # of entries "on this disk" 
                
    pack("v"sizeof($this -> ctrl_dir)).     // total # of entries overall 
                
    pack("V"strlen($ctrldir)).             // size of central dir 
                
    pack("V"strlen($data)).                 // offset to start of central dir 
                
    "\x00\x00";                             // .zip file comment length 
        

    }   




    //--------- end class

    $date date("mdy-hia");
    $filename "$savepath/$dbname-$date.sql"
    passthru("mysqldump --opt -h$dbhost -u$dbuser -p$dbpass $dbname >$filename");

    if(
    $use_gzip=="yes"){
        
    $zipfile = new zipfile();   

        
    // add the subdirectory ... important! 
        
    $zipfile -> add_dir($savepath); 

        
    // add the binary data stored in the string 'filedata' 
        
    $file6 fopen($filename,'rb');
        
    $filedata fread($file6,filesize($filename));
        
    fclose($file6);
        
    //$filedata = "(read your file into $filedata)";   
        
    $zipfile -> add_file($filedata$filename);   

        
    // OR instead of doing that, you can write out the file to the loca disk like this: 
        
    $filename3 $savepath."/".$dbname.".".$date."_sql.tar.gz"
        
    $fd fopen ($filename3"wb"); 
        
    $out fwrite ($fd$zipfile -> file()); 
        
    fclose ($fd); 


        
    //$zipline = "tar -czf ".$dbname.".".$date."_sql.tar.gz ".$dbname."-".$date.".sql";
        //echo shell_exec($zipline);
    }
    if(
    $remove_sql_file=="yes"){
    exec("rm -r -f $filename");
    }

    if(
    $use_gzip=="yes"){
    $filename2 $filename3;
    } else {
    $filename2 "$savepath/$dbname-$date.sql";
    }


    if(
    $send_email_w_file == "yes" ){
    $fileatt_type filetype($filename2);
    $fileatt_name "".$dbname."-".$date."_sql.tar.gz";


    // Read the file to be attached ('rb' = read binary)
    $file fopen($filename2,'rb');
    $data fread($file,filesize($filename2));
    fclose($file);

    // 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=\"{$fileatt_name}\"\n" ."Content-Disposition: attachment;\n" ." filename=\"{$fileatt_name}\"\n" ."Content-Transfer-Encoding: base64\n\n" .
    $data "\n\n" ."--{$mime_boundary}--\n";
    }
    // Send the message
    $ok = @mail($to$subject$message$headers);
    if (
    $ok) {
    echo 
    "<h4><center>Database backup created and sent! File name $filename2</center></h4>";
    } else {
    echo 
    "<h4><center>Mail could not be sent. Sorry!</center></h4>";
    }

    if(
    $use_ftp == "yes"){
    $ftpconnect "ncftpput -u $ftp_user_name -p $ftp_user_pass -d debsender_ftplog.log -e dbsender_ftplog2.log -a -E -V $ftp_server $ftp_path $filename2";
    shell_exec($ftpconnect);
    echo 
    "<h4><center>$filename2 Was created and uploaded to your FTP server!</center></h4>";

    }

    if(
    $remove_gzip_file=="yes"){
    exec("rm -r -f $filename2");
    }

    ?>

  3. #3
    SitePoint Zealot themissingelf's Avatar
    Join Date
    Nov 2001
    Location
    UK
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh my... thank you.

    Have to get my brain around this.

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2004
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is good for awhile but what happens when the db gets to be larger than a few mb compressed ? Email doesn't work anymore. Eventually, you'll have to make the script create a backup of the db on the server itself and maybe automatically ftp it to the client or to you. Or the client could periodically ftp the file down.

    If anything happens to the file backup while it's on the server, the hosting provider should be able to provide a backup. Just be careful that the permissions are set appropriately so that some stranger can't come along and download the db themselves.

  5. #5
    SitePoint Zealot kamm's Avatar
    Join Date
    Nov 2003
    Location
    Espaņa
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use a cron similar to this:

    Code:
    00 03 * * * /usr/bin/mysqldump --opt database_name -hlocalhost -uusername -ppassword | gzip | uuencode database_name.gz | mail -s "DataBase Backup" info@domain.com
    Seems to work OK on a smallish db...

    Note the flags -h -u and -p
    so it's -hTheHostname -uTheUsername and -pThePassword

  6. #6
    SitePoint Zealot abstraktmedia's Avatar
    Join Date
    Feb 2004
    Location
    Ljubljana
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow...cool...just what I was looking for..I have to check it though...

    But my problem is that I want to backup all the databases not just one.
    How can I do that?
    exit(0);


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •