Cron db Backup

[the_ancient]

Whoa. How do you know they won't misuse your data?

I store my data in a black hole. I am assured by the smartest theoretical scientists that no information can be lost.

Yes but my Aliens will take me away when the world ends later this year so there

[excalibur]

Hi all,

Speaking about cronjobs, there is a weird behavior in my sites.

I use this simple cronjob:

DBNAME=dbname
DBPASS=*************
DBUSER=username
EMAIL="myemail@whatever.com"
mysqldump --opt -u $DBUSER -p$DBPASS $DBNAME > backup.sql
gzip backup.sql
DATE=`date +%Y%m%d` ; mv backup.sql.gz $DBNAME-backup-$DATE.sql.gz
echo 'sitename: mySQL Backup is attached' | mutt -a $DBNAME-backup-$DATE.sql.gz $EMAIL -s "MySQL Backup"
rm $DBNAME-backup-$DATE.sql.gz

Simple, straight and it works, except for one thing. If I use any symbol in the DB password the cronjob doesn't work.

Without further explanation, tech support told me not to use symbols. How comes?

[Ron]

Two things I'd try:

Put single quotes around the setting of dbpass (and/or using it).
Use \ before the symbol.

Those would be my first tries.

[gohighvoltage]

Wow, all really good information. How do I get in touch with this Alien service? Seems like it would be a great solution.

[excalibur]

Two things I'd try:

Put single quotes around the setting of dbpass (and/or using it).
Use \ before the symbol.

Ron, I tried it but as expected it doesn't work. Those symbols could be used in passwords as well.

[mstyers]

I finally got around to testing Katina's FTP script. I made a few changes per the suggestions from Ron on the email script and added ability to email results. Here is the modified & tested script:

<?
$datestamp = date("Y-m-d"); // Current date to append to filename of backup file in format of YYYY-MM-DD

/* CONFIGURE THE FOLLOWING THREE VARIABLES TO MATCH YOUR SETUP */
$dbuser = "";					// Database username
$dbpwd = "";					// Database password
$dbname = "";					// Database name. Use --all-databases if you have more than one
$filename_temp = "backup-$datestamp.sql";	// The name (and optionally path) of the dump file
$filename = $filename_temp.".gz";		// The compressed file name
$to = "";					// Email address to send dump file to
$from = "";					// Email address message will show as coming from.
$subject = "MySQL db Backup FTP";		// Subject of email
$extra = "-f ".$from;				// Return Path
$headers = "From: ".$from;			// Email Header
$mail_path = "/usr/sbin/sendmail -i -t";	// Sendmail Path

/* CONFIGURE THE FOLLOWING FOUR VARIABLES TO MATCH YOUR FTP SETUP */
$ftp_server = "";				// Shouldn't have any trailing slashes and shouldn't be prefixed with ftp://
$ftp_port = "21";				// FTP port - blank defaults to port 21
$ftp_username = "";				// FTP account username
$ftp_password = "";				// FTP account password - blank for anonymous

$command = "mysqldump -u $dbuser --password=$dbpwd $dbname > $filename_temp";
$result = exec($command);
$command = "gzip -9 $filename_temp";		// gzip compresses .sql to .sql.qz then deletes .sql file
$result = exec($command);

$message = $subject." results.\n\n"; 

// set up basic connection
$ftp_conn = ftp_connect($ftp_server);

// Turn PASV mode on or off
ftp_pasv($ftp_conn, false);

// login with username and password
$login_result = ftp_login($ftp_conn, $ftp_username, $ftp_password);

// check connection
if ((!$ftp_conn) || (!$login_result)) {
  $message .= "FTP connection has failed.\nAttempted to connect to $ftp_server for user $ftp_username.\n";
} else {
  $message .= "Connected to $ftp_server, for user $ftp_username.\n";
} // end if

// upload the file
$upload = ftp_put($ftp_conn, "directory_path".$filename, $filename, FTP_BINARY); // change directory_path for your server

// check upload status
if (!$upload) {
  $message .= "FTP upload has failed.\n";
} else {
  $message .= "Uploaded $filename to $ftp_server.\n";
} // end if

// close the FTP stream
ftp_close($ftp_conn);

ini_set("sendmail_path", $mail_path);
mail($to, $subject, $message, $headers, $extra);	// mail results to admin

/* remove the comment tag from the next line to delete the backup file from the server */
// unlink($filename);				//delete the backup file from the server
?>

[JPC-Sabrina]

Very nice work. Thanks for sharing. I am sure many will find the information helpful and appreciate how you have modified the script.