Backup MySQL to Gmail using PHP

Yesterday I rewrote a cool script to backup a MySQL database to a Gmail account using PHP.

The original source came from here, but I ended up rewriting over half of it because it did not work on our servers, and there was some other stuff that I did not like.

The script uses mysqldump to get a dump of your db, archives it, and emails it as an attachment to your gmail account. Remember, gmail has a 20mb limit on attachments.. but for most small sites that is more than you'll ever need.

Here's the rundown on how to use it:

1) Register a Gmail account to backup your db to.
2) Get these three files: mysql2gmail.php, class.phpmailer.php, class.smtp.php

wget http://dl.getdropbox.com/u/113063/mysql2gmail.php http://dl.getdropbox.com/u/113063/class.phpmailer.php http://dl.getdropbox.com/u/113063/class.smtp.php

3) Change the stuff on the top of mysql2gmail.php until it works.

Here's the source:

#!/usr/local/bin/php.cli
<?php

/**************************************************************************
mysql2gmail Version 0.01               3/19/2009                                    

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>

 mysql2gmail is a branch of bu2gmail, which is available at:
   http://tjkling.com/simple-mysql-backup-to-gmail/ 
   http://tjkling.com/download/bu2gmail.txt
 mysql2gmail depends on class.phpmailer.php and class.smtp.php
 
/*********** CHANGE THIS STUFF UNTIL IT WORKS ************/
$host = 'localhost';                    // set to localhost or your servers ip
$sock = '';                        // if your servers use a sock, set it here (ex: /tmp/mysql5.sock)
$muser = 'mysql_username';                // MySQL username
$mpass = 'mysql_password';                // MySQL password
$dbname = 'database';                    // MySQL database
$g_user = 'mysite.backup@gmail.com';            // the gmail account you registered     
$g_pass = 'gmailPassword';                // your gmail password
$g_name = 'Some Name';                    // The name you want the emails you send to be from
$backupDirectory = '/home/mysite/www/tmp/';        // full path to a temp directory.. don't forget to put a slash at the end!

/*************** CHANGE THIS STUFF IF YOU KNOW WHAT YOU'RE DOING ********************/
require_once("class.phpmailer.php");            // original source: http://phpmailer.codeworxtech.com/
require_once("class.smtp.php");                // original source: http://phpmailer.codeworxtech.com/
if ($sock != '') { $sock = "-S " . $sock; }
$time = time();
$body = "This is a backup of the $dbname database taken on " . date('F j, Y, \a\t g:i a', $time);
$backupName = 'backup_' . date('Y-m-d.G:i:s', $time);
$subj = 'MYSQL ' . date('Y-m-d', $time) . ' at ' . date('g:i A', $time);
$db_conn = mysql_connect($host, $muser, $mpass);

// run mysqldump and get the output
$command = "mysqldump -h ".$host.$sock." -u ".$muser." -p".$mpass." ".$dbname;
exec($command, $output);

// converting the array that exec() outputs into a string..
$count = count($output);
for ($n=0;$n<$count;$n++) {
  $string .= $output[$n]."\n";
}

// archive the output into a gz file...
$filename = $backupDirectory . $backupName . '.gz';
$zp = gzopen($filename, "w9");
gzwrite($zp, $string);
gzclose($zp);

// do stuff
$mail = new PHPMailer();
$mail->IsSMTP();
$mail->SMTPAuth = true;
$mail->SMTPSecure = "ssl";
$mail->Host = "smtp.gmail.com";
$mail->Port = 465;
$mail->Username = $g_user;
$mail->Password = $g_pass;
$mail->AddReplyTo($g_user, $g_name);
$mail->From = $g_user;
$mail->FromName = $g_name;
$mail->Subject = $subj;
$mail->MsgHTML($body);
$mail->AddAddress("$g_user");
$mail->AddAttachment($filename);
$mail->IsHTML(true);
if (!$mail->Send()) {
  echo "Mailer Error: " . $mail->ErrorInfo;
} else {
  echo "Message sent!";
}

// delete the file. why waste valuable webspace when gmail gives you > 7gb for free?
unlink($filename);

?>