Automatic backup of MySQL database

0
313

Would you like to have a backup of your critical MySQL database emailed to you automatically?

The following script was moified from backup2mail and needs setting up as a cron job, instructions are in the code.

Download it for free here

This is what the code looks like:

<?php

/// Setup ///
// Test it in a browser first and make sure it works.
// To set it to run automatically:
// ssh in and run:
// crontab -e
// add this line
// 1 21 * * 5 php /var/www/username/public_html/backup.php
// the above command will run at 21:01 every Friday, look into cron schedules if you want to run every day etc.

//// NB. Moified version of www.backup2mail.com all credit to original coder.

///////////////// Settings ///////////////////////////
$sqlhost = “localhost”;                                //
$sqldatabase = “enterdbname”;                        //
$sqluser = “enterdbuser”;                            //
$sqlpassword = “enterdbpass”;                        //////////////
$website            = ‘mysite.com’;                               // Your site’s domain (without www. part)
$send_to             = ‘your@email.com’;                      // backup file will be sent to?
$from                 = ‘another@email.com’;                    // some hosting providers won’t let you send backups from invalid e-mail address
$full_path = ‘/var/www/username/public_html’;                // Full path to folder where you are running the script, usually “/home/username/public_html” dont use cwd();
$delete_backup    = true;                                        // delete gziped database from server after sending?
//////////////////////////////////////////////////////////////////

error_reporting(E_ALL);

echo ‘<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head>
<title>Backup status [‘ . $website . ‘]</title>
<style type=”text/css”>body { background: #000; color: #0f0; font-family: \’Courier New\’, Courier; }</style>
</head>
<body>’;

function date_stamp() {
global $html_output;
$backup_date = date(‘Y-m-d-H-i’);
echo ‘Database backup date: ‘ . $backup_date . ‘<br />’;
return $backup_date;
}

function backup_filename() {
global $sqldatabase, $date_stamp, $html_output, $full_path;
$db_backup_filename = $full_path.’/’.($sqldatabase == ” ? ‘all_databases’ : $sqldatabase) . ‘_’ . $date_stamp . ‘.sql.gz’;
echo ‘Database backup file: ‘ . $db_backup_filename . ‘<br />’;
return $db_backup_filename;
}

function db_dump() {
global $sqlhost, $sqldatabase, $sqluser, $sqlpassword, $backup_filename, $html_output;
$cmd = ‘mysqldump -u ‘ . $sqluser . ‘ -h ‘ . $sqlhost . ‘ –password=’ . $sqlpassword . ‘ ‘ . ($sqldatabase == ” ? ‘–all-databases’ : $sqldatabase) . ‘ | gzip > ‘ . $backup_filename;
$sres = shell_exec($cmd);
return ‘OK’;
}

function send_attachment($file, $file_is_db = true) {
global $send_to, $from, $website, $delete_backup, $html_output;

$sent       = ‘No’;

$subject    = ‘MySQL backup – ‘ . ($file_is_db ? ‘db dump’ : ‘report’) . ‘ [‘ . $website . ‘]’;
$boundary   = md5(uniqid(time()));
$mailer     = ‘Sent by Backup2Mail.’;

$body = ‘Database backup file:’ . “\n” . ‘ – ‘ . $file . “\n\n”;
$body .= ‘—‘ . “\n” . $mailer;

$headers  = ‘From: ‘ . $from . “\n”;
$headers .= ‘MIME-Version: 1.0’ . “\n”;
$headers .= ‘Content-type: multipart/mixed; boundary=”‘ . $boundary . ‘”;’ . “\n”;
$headers .= ‘This is a multi-part message in MIME format. ‘;
$headers .= ‘If you are reading this, then your e-mail client probably doesn\’t support MIME.’ . “\n”;
$headers .= $mailer . “\n”;
$headers .= ‘–‘ . $boundary . “\n”;

$headers .= ‘Content-Type: text/plain; charset=”iso-8859-1″‘ . “\n”;
$headers .= ‘Content-Transfer-Encoding: 7bit’ . “\n”;
$headers .= $body . “\n”;
$headers .= ‘–‘ . $boundary . “\n”;

$headers .= ‘Content-Disposition: attachment;’ . “\n”;
$headers .= ‘Content-Type: Application/Octet-Stream; name=”‘ . $file . “\”\n”;
$headers .= ‘Content-Transfer-Encoding: base64’ . “\n\n”;
$headers .= chunk_split(base64_encode(implode(”, file($file)))) . “\n”;
$headers .= ‘–‘ . $boundary . ‘–‘ . “\n”;

if (mail($send_to, $subject, $body, $headers)) {
$sent = ‘Yes’;
echo ($file_is_db ? ‘Backup file’ : ‘Report’) . ‘ sent to ‘ . $send_to . ‘.<br />’;
if ($file_is_db) {
if ($delete_backup) {
unlink($file);
echo ‘Backup file REMOVED from disk.<br />’;
} else {
echo ‘Backup file LEFT on disk.<br />’;
}
}
} else {
echo ‘<span style=”color: #f00;”>’ . ($file_is_db ? ‘Database’ : ‘Report’) . ‘ not sent! Please check your mail settings.</span><br />’;
}

echo ‘Sent? ‘ . $sent;

return $sent;
}

echo ‘<h2>Setup</h2>’;
$date_stamp = date_stamp();
$backup_filename = backup_filename();
$dumpeddata = db_dump();
$sentdata = send_attachment($backup_filename);

echo ‘<br /><br />…<br /><br />Done!</body></html>’;

?>

Download the mysql2email backup script for free here

LEAVE A REPLY

Please enter your comment!
Please enter your name here