dijexi.com

Image via CrunchBase

This simple PHP script can be used to backup MySQL databases and then send the zipped backup file to email addresses. The backup file can then be restored by any MySQL front end program like the mysql command line, Navicat MySQL, or phpMyAdmin.

The logic of the script is really simple:

1. initialization
2. create backup sql file
3. zip the file
4. send email

To do the backup automatically you can run the script through cron (*nix) or Task Scheduler (Windows) as described in next section.

This script uses PHP PEAR library so you need PEAR installed with the Mail and Mail_Mime packages. Read more about PEAR here: http://pear.php.net.

This script utilize a ZIP.exe command line program to do the compressing which can be downloaded from http://stahlforce.com/dev/index.php?tool=zipunzip. In *nix environtment you can use tar command line instead to create a tgz file.

Make sure you have write access to the $tmpDir directory.

<?php

/*********************************************************************\

initialization

\*********************************************************************/

ini_set('SMTP','smtp.telkom.net');

require_once('Mail.php');

require_once('Mail/mime.php');


// mysql & minor details..

$mysqlbinpath    = "c:/xampp/mysql/bin";

$tmpDir        = "c:/temp";

$zip        = "c:/xampp/zip/zip.exe";

$user        = "root";

$password    = "pwd";

$dbName        = "db";

$sqlFiles    = "";


//databases to backup

$dbs        = array ('cibpdb', 'mysql');


// email settings...

$to            = "anemail@gmail.com";

$from        = "anemail@gmail.com";

$subject    = "db - backup";

$attachment    = $tmpDir.'/backup-'.date('Y_M_d-H_i_s') . ".zip"; // or tgz in *nix


/*********************************************************************\

create backup sql file and the zip

\*********************************************************************/

foreach ($dbs as $dbname)

{

$sqlFile    = $tmpDir.'/'.$dbname.'-'.date('Y_m_d').".sql";

echo "creating $sqlFile\n";

$creatBackup    = $mysqlbinpath . "/mysqldump --add-drop-table -u ".$user." --password=".$password." ".$dbname." > ".$sqlFile;

exec($creatBackup);

$sqlFiles    .= ' ' . $sqlFile;

}


/*********************************************************************\

create the zip file

\*********************************************************************/


echo "creating $attachment\n";


//use this command in *nix environment

//$createZip    = "tar cvzf $attachment $sqlFiles";


//use this command in windows, must have zip.exe

$createZip    = "$zip $attachment $sqlFiles";

exec($createZip);


/*********************************************************************\

send zip to email

\*********************************************************************/

echo "sending $attachment to $to\n";

$headers = array('From' => $from, 'Subject' => $subject);


$textMessage = $attachment;

$htmlMessage = "";


$mime = new Mail_Mime("\n");

$mime->setTxtBody($textMessage);

$mime->setHtmlBody($htmlMessage);

$mime->addAttachment($attachment, 'text/plain');

$body = $mime->get();

$hdrs = $mime->headers($headers);

$mail = &Mail::factory('mail');

$mail->send($to, $hdrs, $body);


unlink($sqlFile);

unlink($attachment);


echo "done.";


?>

Please note on the $header above. I’ve found that PHP mail function failed to send email to a MDaemon SMTP server. If you encounter the same problem, here is the solution that works for me, to change the $header like the following:

$headers = array(

'From'            => $from,

'Reply-To'        => $from,

'X-Mailer'        => 'Microsoft Outlook Express 6.00.2900.5512',

'X-Rcpt-To'        => $to,

'X-MDRcpt-To'        => $to,

'X-MDRemoteIP'        => '10.5.68.249',

'X-Return-Path'        => $from,

'X-Envelope-From'    => $from,

'X-MDaemon-Deliver-To' => $to

);

Save the script above to a file for example backup-script.php and save it to a folder for example c:\xampp\

Setting up Windows Task Scheduler

If you need to run the backup script periodically using the Windows Task Scheduler, do the following steps:

Click on Start – Settings – Control Panel – Scheduled Tasks

Double click Add Scheduled Task

Click Next.

Click Browse… and locate the PHP.EXE executable file, for example C:\XAMPP\PHP\PHP.EXE.

Select the execution period of Daily, Weekly, Monthly, etc depending on your backup need. Click Next.

Select the start time and Date of the task. Also select the daily operation for the task, eg Every Day, Weekdays only, or Every x days. Click Next.

Next, Enter the username and password that will run the program as. Click Next.

Refine the Scheduled Task

On the dialog, the task periodic schedule is shown, you can refine it by checking “Open advanced properties for this task when I click Finish”. Then click Finish.

You need to change the Run field from

c:\xampp\php\php.exe

to

C:\xampp\php\php.exe c:\xampp\htdocs\backup-script.php

You may need to put a double quote on the command line parameter if your script resides on a folder that contains spaces, for example:

C:\xampp\php\php.exe  “C:\Documents and Settings\Toshiba\My Documents\backup-script.php”

This will run php program with an argument, which is our backup script created before.

If you need to adjust the period, you can click on the Schedule tab.

and then click Advanced button

On the above example, we check on Repeat task, which means we will run the backup program every 6 hours for 24 hours a day, starting from 29 June 2009. Click OK.

Our scheduled task will be listed at the Scheduled Tasks window as follow:

Run the Scheduled Task

When you are done, just wait until the schedule for the task to run, or you can right-click on it and click Run to run it now.

Backup file created by this periodic backup task can be restored using mysql command line, phpMyAdmin, Navicat MySQL, or other MySQL front end programs.

References:

• ZIP.EXE download http://stahlforce.com/dev/index.php?tool=zipunzip
• PEAR manual http://pear.php.net

Akhmad Daniel Sembiring

vITraining.com – Qualified IT Products, Outsourcing, and Services

Ligarwangi.com – Linux, E-book, Coffee, Gift, etc

Related articles by Zemanta