Script for MySQL backup

[pete3005]

In relation to a thread about cron jobs and MySQL backup, here is a script I use which doesn't eat into CPU.

Create a folder outside your root such called 'sql' it should be on the same level as your public_html folder, so not downloadable.

In your cron tab put:

php -q /home/yourusername/locationofscript/backup_db.php

so as an example that would be:

php -q /home/pete/sql/backup_db.php

If you have multiple databases it might be best to stagger your times, like 1am, 2 am or whatever time you wish, but there is no need to run 5 backups at the same time, that is not very considerate of other users.

I do plan on making a better script with more options and making a little 'config wizard', so its easy to use even for those that don't like digging around in code.

The options below are set to 1 for meial which emails you the db, so you may wish to set that to 0.

I hope this helps some of you cron jobers!

<?php




// Mysql Database Information:
$database = 'username_dbname'; // name of the database.
$username = 'username_user'; // username with access to database.
$password = 'password'; // password for username.

// Database Backup Filename & Location
$backupas = 'backup.sql'; // filename to backup the database as.
$backupto = '/home/username/sql'; // absolute path to folder containing database - no trailing slash.

// E-mail Information:
$mailto = 'you@yourdomain.com'; // email address to send the database to.
$subject = 'mySQL db backup'; // subject of email.
$message = 'Here\'s the latest backup!'; // message body.

// Options:
$send_email = '1'; // 1 = send backup copy via e-mail. 0 = just backup data, don't e-mail.
$delete_local = '0'; // 1 = delete local copy when done. 0 = leave local copy when done.


// END of variables.
// Do Not Edit Beyond This Point


//Global Variables
$mail_boundary = '';

// Call Functions
backupdb();
if ($send_email) {makeandsend();}
if ($delete_local) {removedb();}



// Functions

function backupdb() {
global $username,$password,$database,$backupto,$backupas;
$backupcommand = "mysqldump -u$username -p$password $database >$backupto/$backupas";
passthru ("$backupcommand", $error);
if($error) { echo ("Problem: $error\n");exit;}
}

function removedb() {
global $backupto,$backupas;
$removecommand = "rm $backupto/$backupas";
passthru ("$removecommand",$error);
if($error) { echo ("Problem: $error"); exit;}
}

function makeandsend() {
global $backupto,$backupas,$message,$mailto,$subject;
$mail_boundary = md5(uniqid(time()));
$mail_head = "MIME-Version: 1.0\r\n";
$mail_head .= "Content-type: multipart/mixed;boundary=\"$mail_boundary\"";
$mail_head .= "\r\n\r\n";
$mail_head .= "This is a multi-part message in MIME format.";
$mail_head .= "\r\n\r\n";


$db_file = "$backupto/$backupas";
$fp = fopen($db_file, "r");
$file = fread($fp, filesize($db_file));
$file = chunk_split(base64_encode($file));

$mail_body = "--$mail_boundary\n";
$mail_body .= "Content-type: text/plain; charset=us-ascii\r\n";
$mail_body .= "Content-transfer-encoding: 8bit\r\n\r\n";
$mail_body .= " $message\r\n";
$mail_body .= "--$mail_boundary\r\n";

$filename = basename($db_file);

$mail_body .= "Content-type: application/octet-stream; name=$filename\r\n";
$mail_body .= "Content-transfer-encoding:base64\r\n\r\n";
$mail_body .= $file. "\r\n\r\n";
$mail_body .= "--$mail_boundary--";

mail($mailto, $subject, $mail_body, $mail_head);
}

?>

[ed_lynn]

pete

thanks for the script!!! works like a dream!!