Results 1 to 10 of 10

Thread: Database Backup (to Email and FTP) via Cron

  1. #1
    Zash's Avatar
    Zash is offline WiredStorm.com - Lightning Fast Web Hosting
    Join Date
    Feb 2009
    Location
    New York, USA
    Posts
    1,137
    Blog Entries
    1
    Thanks
    64
    Thanked 121 Times in 93 Posts

    Database Backup (to Email and FTP) via Cron

    I did not write this tutorial. I found it very useful and am re-posting it for your convenience. Original: Database Backup via Cron

    This tutorial allows you to backup your MySQL database, and then send it to you either via email or via FTP. You will need to set up a cron for the files below, and specify a time/date (IE Mondays at 4pm) and the file will run a backup at the time/day you specify.

    SQL Backup - Email To You
    Code:
    <?
    $datestamp = date("Y-m-d");      // Current date to append to filename of backup file in format of YYYY-MM-DD
    
    /* CONFIGURE THE FOLLOWING SEVEN VARIABLES TO MATCH YOUR SETUP */
    $dbuser = "";            // Database username
    $dbpwd = "";            // Database password
    $dbname = "";            // Database name. Use --all-databases if you have more than one
    $filename= "backup-$datestamp.sql.gz";   // The name (and optionally path) of the dump file
    $to = "you@remotesite.com";      // Email address to send dump file to
    $from = "you@yourhost.com";      // Email address message will show as coming from.
    $subject = "MySQL backup file";      // Subject of email
    
    $command = "mysqldump -u $dbuser --password=$dbpwd $dbname | gzip > $filename";
    $result = passthru($command);
    
    $attachmentname = array_pop(explode("/", $filename));   // If a path was included, strip it out for the attachment name
    
    $message = "Compressed database backup file $attachmentname attached.";
    $mime_boundary = "<<<:" . md5(time());
    $data = chunk_split(base64_encode(implode("", file($filename))));
    
    $headers = "From: $from\r\n";
    $headers .= "MIME-Version: 1.0\r\n";
    $headers .= "Content-type: multipart/mixed;\r\n";
    $headers .= " boundary=\"".$mime_boundary."\"\r\n";
    
    $content = "This is a multi-part message in MIME format.\r\n\r\n";
    $content.= "--".$mime_boundary."\r\n";
    $content.= "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n";
    $content.= "Content-Transfer-Encoding: 7bit\r\n\r\n";
    $content.= $message."\r\n";
    $content.= "--".$mime_boundary."\r\n";
    $content.= "Content-Disposition: attachment;\r\n";
    $content.= "Content-Type: Application/Octet-Stream; name=\"$attachmentname\"\r\n";
    $content.= "Content-Transfer-Encoding: base64\r\n\r\n";
    $content.= $data."\r\n";
    $content.= "--" . $mime_boundary . "\r\n";
    
    mail($to, $subject, $content, $headers);
    
    unlink($filename);   //delete the backup file from the server
    ?>
    SQL Backup - FTP'ed To You
    Code:
    <?
    $datestamp = date("Y-m-d");      // Current date to append to filename of backup file in format of YYYY-MM-DD
    
    /* CONFIGURE THE FOLLOWING THREE VARIABLES TO MATCH YOUR SETUP */
    $dbuser = "";      // Database username
    $dbpwd = "";         // Database password
    $dbname = "";      // Database name. Use --all-databases if you have more than one
    $filename= "backup-$datestamp.sql.gz";   // The name (and optionally path) of the dump file
    
    $command = "mysqldump -u $dbuser --password=$dbpwd $dbname | gzip > $filename";
    $result = passthru($command);
    
    /* CONFIGURE THE FOLLOWING FOUR VARIABLES TO MATCH YOUR FTP SETUP */
    $ftp_server = "";   // Shouldn't have any trailing slashes and shouldn't be prefixed with ftp://
    $ftp_port = "21";            // FTP port - blank defaults to port 21
    $ftp_username = "anonymous";         // FTP account username
    $ftp_password = "";         // FTP account password - blank for anonymous
    
    // set up basic connection
    $ftp_conn = ftp_connect($ftp_server);
    
    // Turn PASV mode on or off
    ftp_pasv($ftp_conn, false);
    
    // login with username and password
    $login_result = ftp_login($ftp_conn, $ftp_username, $ftp_password);
    
    // check connection
    if ((!$ftp_conn) || (!$login_result))
    {
       echo "FTP connection has failed.";
       echo "Attempted to connect to $ftp_server for user $ftp_username";
       exit;
    }
    else
    {
       echo "Connected to $ftp_server, for user $ftp_username";
    }
    
    // upload the file
    $upload = ftp_put($ftp_conn, $filename, $filename, FTP_BINARY);
    
    // check upload status
    if (!$upload)
    {
       echo "FTP upload has failed.";
    }
    else
    {
       echo "Uploaded $filename to $ftp_server.";
    }
    
    // close the FTP stream
    ftp_close($ftp_conn);
    
    unlink($filename);   //delete the backup file from the server
    ?>
    You need to fill in all the variables and then save the script as .php and upload it to your site via FTP. You need to give it 755 permissions.

    Then in cron add this code for the cron job:
    Code:
    php -q /home/username/public_html/folder/name.php
    You need to edit this so it points to where you uploaded the script.
    WiredStorm Hosting Solutions
    █ Lightning Fast Shared and Reseller Hosting
    █ cPanel with Softaculous and 24/7 Email (Ticket) Support
    Biodegradable Sunscreen - Unblock Facebook

  2. Thanked by:

    Mr.Bill (3 July, 2009), Nick (4 July, 2009)

  3. #2
    Zash's Avatar
    Zash is offline WiredStorm.com - Lightning Fast Web Hosting
    Join Date
    Feb 2009
    Location
    New York, USA
    Posts
    1,137
    Blog Entries
    1
    Thanks
    64
    Thanked 121 Times in 93 Posts
    Hmm, actually I just tried the via email thing, and it is sending me empty databases only. Can anybody here help me out?
    WiredStorm Hosting Solutions
    █ Lightning Fast Shared and Reseller Hosting
    █ cPanel with Softaculous and 24/7 Email (Ticket) Support
    Biodegradable Sunscreen - Unblock Facebook

  4. #3
    Nick's Avatar
    Nick is offline Newbie Net Builder
    Join Date
    Jul 2009
    Location
    Tampa, FL
    Posts
    77
    Thanks
    15
    Thanked 16 Times in 14 Posts

  5. #4
    deluxdon's Avatar
    deluxdon is offline Catch Me If you Can....
    Join Date
    Jun 2009
    Location
    Deluxdon.In
    Posts
    591
    Blog Entries
    1
    Thanks
    21
    Thanked 66 Times in 59 Posts
    Quote Originally Posted by Nick View Post
    This seems very useful. I will definitely try it out.
    Check just above post of OP that its not working (empty backup through email).

    I am using same thing through plugin at my wp driven blogs (mysql backup direct to your email using schedule backup option).

    DON.

  6. #5
    Nick's Avatar
    Nick is offline Newbie Net Builder
    Join Date
    Jul 2009
    Location
    Tampa, FL
    Posts
    77
    Thanks
    15
    Thanked 16 Times in 14 Posts
    Quote Originally Posted by deluxdon View Post
    Check just above post of OP that its not working (empty backup through email).

    I am using same thing through plugin at my wp driven blogs (mysql backup direct to your email using schedule backup option).

    DON.
    Until I try it for myself, I don't know. Although highly doubtful, he may not have set it up properly.

  7. #6
    Zash's Avatar
    Zash is offline WiredStorm.com - Lightning Fast Web Hosting
    Join Date
    Feb 2009
    Location
    New York, USA
    Posts
    1,137
    Blog Entries
    1
    Thanks
    64
    Thanked 121 Times in 93 Posts
    That's true, and I haven't tried the FTP one. I'm pretty sure the FTP one works, and maybe the email if I set it up wrong...
    WiredStorm Hosting Solutions
    █ Lightning Fast Shared and Reseller Hosting
    █ cPanel with Softaculous and 24/7 Email (Ticket) Support
    Biodegradable Sunscreen - Unblock Facebook

  8. #7
    ebizlaunch is offline Newbie Net Builder
    Join Date
    Jun 2009
    Posts
    34
    Thanks
    3
    Thanked 6 Times in 2 Posts
    Is there a similar script that will backup the website as well? Not just the MySql, but all files etc..?

    Or maybe a desktop software?

  9. #8
    TopDogger's Avatar
    TopDogger is offline Über Hund
    Join Date
    Jan 2009
    Location
    Hellfire, AZ
    Posts
    3,103
    Thanks
    349
    Thanked 918 Times in 702 Posts
    This script and variations of it have been around for several years. I found that you will get a blank SQL file if the permissions are not set right for the database user.

    Try this. You need the following permissions: SELECT, RELOAD, FILE, SHOW DATABASES, SUPER, LOCK TABLES, SHOW VIEW

    It is a good idea to set up a separate DB user for backups. These are not standard settings.

    One of the basic security rules for DB user permissions is that you never grant anything more than the minimal requirements to any DB user.

    There may be other reasons for a blank file, but this is what fixed my backups when I first encountered the problem.

    Another problem you might run into is with e-mailing large databases. Some ISPs place limits on e-mail file sizes. The --all-databases option may not work if the databases are large. I've also seen problem trying to e-mail WordPress backups if you have the wassup plugin installed and you do not set time limits on the data it retains. By default, wassup saves all the history and the tables can become very large very quickly.




    "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." -- Benjamin Franklin


  10. #9
    deluxdon's Avatar
    deluxdon is offline Catch Me If you Can....
    Join Date
    Jun 2009
    Location
    Deluxdon.In
    Posts
    591
    Blog Entries
    1
    Thanks
    21
    Thanked 66 Times in 59 Posts
    Quote Originally Posted by ebizlaunch View Post
    Is there a similar script that will backup the website as well? Not just the MySql, but all files etc..?

    Or maybe a desktop software?
    You can take full website backup (all files) through cpanel back up option.

    DON.

  11. #10
    ebizlaunch is offline Newbie Net Builder
    Join Date
    Jun 2009
    Posts
    34
    Thanks
    3
    Thanked 6 Times in 2 Posts
    Quote Originally Posted by deluxdon View Post
    You can take full website backup (all files) through cpanel back up option.

    DON.
    Agreed. The problem is that

    1) I have to login to the cpanel
    2) Click on the backup icons
    3) save these in a separate directory.

    Simple enough for one site.

    When you have multiple sites, on different servers, need to take periodic backups (weekly) - this becomes a chore and a time-waster.

    I'm looking to automate this; and no, I'm not a tech geek, so wouldn't be able to rsync or write a script to do this.

    I'm the kind of guy who thinks that a con job is a cron job with the R silent

    Still looking...

Similar Threads

  1. Backup People!
    By Aquarezz in forum Managing
    Replies: 43
    Last Post: 26 March, 2011, 21:08 PM
  2. command line or cron job ?
    By SonnyCooL in forum Managing
    Replies: 3
    Last Post: 26 October, 2010, 18:13 PM
  3. vbulletin backup?
    By sachin410 in forum Managing
    Replies: 5
    Last Post: 2 December, 2009, 17:44 PM
  4. Always save a backup!
    By sam in forum Managing
    Replies: 7
    Last Post: 10 August, 2009, 09:51 AM
  5. Backup of Mysql database using PhpMyAdmin
    By mega in forum Databases
    Replies: 2
    Last Post: 14 May, 2009, 10:06 AM

Tags for this Thread

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
  •