Please enable JavaScript - or get a better browser

mySQL backup and Email Script

Having invested a lot of energy ensuring that all our websites are both safe and secure, we've also put a lot of work into building an off-site backup solution.

We backup and download all our mySQL databases daily as well as making a local daily backup of all site pages and documents ( all images and html pages ) - which pretty well covers everything.

We also have a second server based in the states to which we backup weekly - so a triplicate system that would make the Romans proud, let's hope we never have a need to use it.

However, in the spirit of helping others - and as a roundabout thanks for the help I received in writing the required scripts we now use to automate this process, I'm going to be adding a number of them for download here.

mySQL backup and email script:

It proved pretty hard to find a simple and easily configurable script that would simply backup a single database schema, compress it and then email - but after a little searching and a lot of hair pulling - this is the result.

Take a look at the script below, and if you like what you see why not download it and use it yourself.

<?PHP

/*
this script will backup, compress and email a single mySQL schema, and borrows code from several sources, including:
http://oscarm.org/news/detail/543-how_to_backup_mysql_database_email_results_using_bash
http://www.dagondesign.com/articles/automatic-mysql-backup-script/

there is no error checking - please feel free to add some.

please note this script leaves a copy of your backup on the server, if you make your backup path on a publicly available address, this could be a security issue - either make it above your public_html or add something to secure or delete the file.

script by studio.quintalinda.com - feel free to change and add, no warantee, guarantee, nada.. good luck.
*/

// errors ##
ini_set('error_reporting', E_ALL);

// date format, you can change this, for example date('Y_m_d_h_i_s') ##
$date = date('Y_m_d');

// mySQL details ##
$host = 'xxxxxxx.com';
$username = 'username';
$password = 'password';
$schema = 'database';
$path = '/home/sites/mysql/'; // absolute and with trailing slash ##
$opts = '--quick --lock-tables --add-drop-table'; // backup options for mysqldump ##

// email details ##
$email = 'mysql@domain.com';
$subject = 'mySQL Backup';

// file compression details ##
$zip = 'gzip';
$mime = 'application/gzip';
$extension = '.gz';

// put it all together ##
$backup = $schema.'_'.$date.'.sql'.$extension;

// run mysqldump routine ##
exec(sprintf('mysqldump --host='.$host.' --user='.$username.' --password='.$password.' '.$schema.' '.$opts.' | '.$zip.' > '.$path.$backup.'', $host, $username, $password, $schema, $path.$backup));

// email compressed file as inline attachment ##
$headers = "From: ".$email." <root@localhost>";

// Generate a boundary string ##
$rnd_str = md5(time());
$mime_boundary = "==Multipart_Boundary_x{$rnd_str}x";

// Add headers for file attachment ##
$headers .= "nMIME-Version: 1.0n" .
   "Content-Type: multipart/mixed;n" .
   " boundary="{$mime_boundary}"";

// Add a multipart boundary above the plain message ##
$body = "This is a multi-part message in MIME format.nn" .
   "--{$mime_boundary}n" .
   "Content-Type: text/plain; charset="iso-8859-1"n" .
   "Content-Transfer-Encoding: 7bitnn";

// make Base64 encoding for file data ##
$data = chunk_split(base64_encode(file_get_contents($path.$backup)));

// Add file attachment to the message ##
$body .= "--{$mime_boundary}n" .
   "Content-Type: {$backup_mime};n" .
   " name="{$backup}"n" .
   "Content-Disposition: attachment;n" .
   " filename="{$backup}"n" .
   "Content-Transfer-Encoding: base64nn" .
   $data . "nn" .
   "--{$mime_boundary}--n";

// send ##
$res = mail( $email, $subject, $body, $headers );

// check mail status ##
if ( !$res ) {
   echo 'FAILED to email mysqldump.';
}

?>

Once you've got the gzipped copy of the mySQL database in your inbox, download it and then use mysql to restore it to your local copy - to speed things up I also made a bash script to automate this process, here's the idea.

#!/bin/bash
#
# Settings
#############################
SQLHOST="localhost"
SQLDB="dbname"
SQLUSER="username"
SQLFILE="backup_"$(date %Y)_$(date %m)_$(date %d)".sql"
BACKUPDIR="/home/usr/Desktop/"
#############################
# do it #
echo "* Check if root..."
if [ $UID -ne 0 ]; then
echo "* Please, type root's password..."
su -c "$0 $@"
exit
fi
echo "* Move to backup directory & list files..."
cd $BACKUPDIR
ls
echo "* untar file..."
gunzip -df $SQLFILE.gz

echo "* start mySQL server..."
/opt/lampp/lampp start

echo "* Restore SQL dump to local server..."
mysql -"$SQLUSER" $SQLDB < $SQLFILE

echo "* delete sql file..."
rm $SQLFILE

echo "* move back home..."
cd ~

echo "* SQL backup complete..."
#exit 0

copy and save the script above as a file, call it mysql.sh for example - make it executable and then open your terminal and write ./mysql.sh

I've added a line to start the xampp server - you might not need this, so remove as required.

good luck - the QL team.