PHP MySQL backup

[darkhooda]

After my recent hosts crashed, I've been looking to do a Cron backup via a shell script or php script and mailing the backup to a Gmail account.

How can I dump the entire of one database and send it on email? Is there any way to do this in php like in phpMyadmin? How can I do it in Cron only also?

[Cailin]

The command to add to your cron file would be something like this...

mysqldump -u (username) -p(password) (database) | tar -c | mail -s "MySQL Backup" (address to send to)

It's something like that. Basically, mysqldump passes the backup to tar, which compresses it and then mail sends the file to you. This might work. It probably won't. You'll likely have to figure out what to change to get this to work though. This doesn't necessarily go into your cron file though, look up cron file examples on Google to see what you need to do for that (I can't remember it all off the top of my head).

Running the same thing in PHP is simple. The approach that I'd use is to do exec ( string command [, array &output [, int &return_var]] ): http://ca.php.net/manual/en/function.exec.php to execute the command above or whatever it turns out to actually be. Good luck!

[darkhooda]

OK, I set up most of it and made sure that my mail script works, but my shellscript still doesn't seem to.

Here's my mail script at:

/home/account/backup/mail.php

<?php
// Basic information in body
$to	  = '[email protected]';
$from	= '[email protected]';
$subject = 'Backup ' . date(m.d.y);
$message = 'This is a SQL backup made on ' . date("l \\t\h\e jS \of F");;

// Obtain file upload vars
$fileatt	  = '/home/acct/tmp/backup.tar.bz2';
$fileatt_type = 'application/x-bzip2';
$fileatt_name = 'backup.tar.bz2';

$headers = "From: $from";

// Read the file to be attached ('rb' = read binary)
$file = fopen($fileatt,'rb');
$data = fread($file,filesize($fileatt));
fclose($file);

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

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

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

// Base64 encode the file data
$data = chunk_split(base64_encode($data));

// Add file attachment to the message
$message .= "--{$mime_boundary}\n" .
		  "Content-Type: {$fileatt_type};\n" .
		  " name=\"{$fileatt_name}\"\n" .
		  //"Content-Disposition: attachment;\n" .
		  //" filename=\"{$fileatt_name}\"\n" .
		  "Content-Transfer-Encoding: base64\n\n" .
		  $data . "\n\n" .
		  "--{$mime_boundary}--\n";

// Send the message
$ok = @mail($to, $subject, $message, $headers);
if ($ok) {
echo "<p>Mail sent! Yay PHP!</p>";
} else {
echo "<p>Mail could not be sent. Sorry!</p>";
}
?>

Can anyone check my shell script? I rarely use Linux:

/home/phsource/backup/backup.sh

#!/bin/bash
#This script runs as a nightly cron job to back a MySQL database to a Gmail account
#Go to correct directory for backup storage
cd /home/account/tmp

#Dump the database
mysqldump -u account -p password databases > backup.sql
#Archive and compress the file
tar -cvj backup.tar.bz2 backup.sql

#Execute sending script
php /home/account/backup/mail.php

#Remove temporary files
rm /home/account/tmp/backup.tar.bz2
rm /home/account/tmp/backup.sql

Do I need to escape my username or password if it contains ! or ' (exclaimation mark and apostrophe) in it?