Automated Mysql backup and email feature

Backups of your database (and files) is very important. If your server crashes and the databases is lost, all your websites content can be lost depending on what systems you use.

This tutorial will tell you how to generate backups of all your databases and put the SQL dump into a tar.gz file. And a php snippet will send it on email every day.

You will need

• Crond running and cron installed
• php-cli

Backup file storage

Start off by creating a local folder on your linux server where you store your backups.

cd /
mkdir backup
mkdir /backup/db

Creating Mysql backup user

Now we need to create a mysql user that only has access to dump data from the databases.

Run these SQL statements in your favorite mysql client ( eg. "mysql -u root -p" ):

CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'MySecretPassword';

GRANT SELECT ,
LOCK TABLES ON * . *
TO 'backupuser'@'localhost'
IDENTIFIED BY 'MySecretPassword'
WITH MAX_QUERIES_PER_HOUR 0
MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0
MAX_USER_CONNECTIONS 0 ;

The backup script

Now create the file /backup/backupdb.php and paste this code into the file:

<?php /// Configuration


$dbUser = "backupuser";
// Database password, change this!
$dbPass = "MySecretPassword"; 
$myEmail = "kjelkenes@gmail.com";
$myName = "Petter Kjelkenes";

$keepBackupsForDays = 7;
$path = "/backup/db/";



/// Code
echo "Backing up all databases...\n";
passthru("mysqldump --all-databases -u $dbUser -p$dbPass | gzip -c | cat ?> $path\$(date +%Y-%m-%d-%H.%M.%S).sql.gz");

echo "Cleaning up old backups...\n";


if ($handle = opendir($path)) {
	while (false !== ($file = readdir($handle))) {
		if (substr($file, -6) == 'sql.gz') {
			$fp = $path . $file;
			if (filemtime($fp)+($keepBackupsForDays*24*60*60)  $cMtime){
				$cMtime = filemtime($fp);
				$fileF = $file;
			}
		}
	}
	closedir($handle);
	
	if ($fileF !== null){
		// Send this file!
		mail_attachment($fileF, $path, $myEmail, 'noreply@'.gethostname(), $myName, 'noreply@'.gethostname(), 'Backup Database: '.gethostname(), 'Here is your backup for '.date('d/m/y'));
	}
	
}


function mail_attachment($filename, $path, $mailto, $from_mail, $from_name, $replyto, $subject, $message) {
	$file = $path.$filename;
	$file_size = filesize($file);
	$handle = fopen($file, "r");
	$content = fread($handle, $file_size);
	fclose($handle);
	$content = chunk_split(base64_encode($content));
	$uid = md5(uniqid(time()));
	$name = basename($file);
	$header = "From: ".$from_name." \r\n";
	$header .= "Reply-To: ".$replyto."\r\n";
	$header .= "MIME-Version: 1.0\r\n";
	$header .= "Content-Type: multipart/mixed; boundary=\"".$uid."\"\r\n\r\n";
	$header .= "This is a multi-part message in MIME format.\r\n";
	$header .= "--".$uid."\r\n";
	$header .= "Content-type:text/plain; charset=iso-8859-1\r\n";
	$header .= "Content-Transfer-Encoding: 7bit\r\n\r\n";
	$header .= $message."\r\n\r\n";
	$header .= "--".$uid."\r\n";
	$header .= "Content-Type: application/octet-stream; name=\"".$filename."\"\r\n"; // use different content types here
	$header .= "Content-Transfer-Encoding: base64\r\n";
	$header .= "Content-Disposition: attachment; filename=\"".$filename."\"\r\n\r\n";
	$header .= $content."\r\n\r\n";
	$header .= "--".$uid."--";
	if (mail($mailto, $subject, "", $header)) {
		echo "Mail sent, OK\n"; // or use booleans here
	} else {
		echo "Error sending mail, ERROR!\n";
	}
}

Testing

Okay, so now you can test if you got php installed and if the script is working by running this command:

php /backup/backupdb.php

Automated: CRON setup

Now it's time to make this stuff automated! Lets go ahead and edit crontab. Notice, i use nano as my editor.

EDITOR=nano crontab -e

Paste this into the crontab file:

0 0 * * * php /backup/backupdb.php

Thats it!

You're done, every day you should get a gzipped sql file with all your databases backed up. Notice, this script only cleans up old backup files from the script, you must remember to delete your old backups in your mail yourself. A good idea is to create a email filter so that your backups comes in a separate folder and not directly in your inbox.