Cron db Backup

[mstyers]

I have searched the forum for answers to this question but I must not be using the right keywords.

I have been trying to create a cron job that would backup all of my databases and email them to me 2 times a week. I asked JPC support for help but they "do not offer support on scripting". Understandable.

Here is the cron job as it stands now and it doesn't work.

/usr/bin/mysqldump -ce –user=username –password=password dbname | /bin/gzip | /usr/bin/uuencode dbbackup_e-$(date +\%Y-\%m-\%d).gz | /bin/mail -s 'db Backup' email@domain.com

I do get an email but this is the content without any attachment.
begin 644 dbbackup_e-2011-01-03.gz
4'XL(`#)`]4X``P,`````````````
`
end

Any help would be appreciated.

[JPC-Katrina]

I saw this backup script somewhere that might help. Use a cron to run the script after editing it.

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

/* CONFIGURE THE FOLLOWING SEVEN VARIABLES TO MATCH YOUR SETUP */
$dbuser = ""; // Database username
$dbpwd = ""; // Database password
$dbname = ""; // Database name. Use --all-databases if you have more than one
$filename= "backup-$datestamp.sql.gz"; // The name (and optionally path) of the dump file
$to = "you@remotesite.com"; // Email address to send dump file to
$from = "you@yourhost.com"; // Email address message will show as coming from.
$subject = "MySQL backup file"; // Subject of email

$command = "mysqldump -u $dbuser --password=$dbpwd $dbname | gzip > $filename";
$result = passthru($command);

$attachmentname = array_pop(explode("/", $filename)); // If a path was included, strip it out for the attachment name

$message = "Compressed database backup file $attachmentname attached.";
$mime_boundary = "<<<:" . md5(time());
$data = chunk_split(base64_encode(implode("", file($filename))));

$headers = "From: $from\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-type: multipart/mixed;\r\n";
$headers .= " boundary=\"".$mime_boundary."\"\r\n";

$content = "This is a multi-part message in MIME format.\r\n\r\n";
$content.= "--".$mime_boundary."\r\n";
$content.= "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n";
$content.= "Content-Transfer-Encoding: 7bit\r\n\r\n";
$content.= $message."\r\n";
$content.= "--".$mime_boundary."\r\n";
$content.= "Content-Disposition: attachment;\r\n";
$content.= "Content-Type: Application/Octet-Stream; name=\"$attachmentname\"\r\n";
$content.= "Content-Transfer-Encoding: base64\r\n\r\n";
$content.= $data."\r\n";
$content.= "--" . $mime_boundary . "\r\n";

mail($to, $subject, $content, $headers);

unlink($filename); //delete the backup file from the server
?>

[mstyers]

Katrina,

Thank you very much. This is exactly what I was looking for. It works great. You have been a huge help.

One other question. Is there a way to backup current users from the MySQL db?

[Ron]

Just a quick note from experience: if your database is sizable, I would hesitate to pipe directly through gzip for a number of performance reasons. Create the dump as a .sql file then gzip the .sql file into the .sql.gz file.

Since in your case you intend to email the file, I assume the file is relatively small so this may not apply to you (now) but you might want to write the extra step now to avoid future frustrating issues.

Also instead of simply deleting the backup file, you should adopt a rotation system keeping a couple of generations locally to facilitate a more rapid recovery and protect against potential email corruption.

Finally, you should test the backup system. Test the local copy by loading it into a database and upload one of the emailed dumps and load it into a database.

Good luck!

[Ron]

I made the changes necessary to split the dump and the compression into separate steps. Keeping to form of the original, I didn't do any error checking to check for failures of the dump or of the compression. To make the changes easier for you to implement, I kept "filename" as the file name used unchanged through the rest of the script.

You might want to add some options to the mysqldump command.

I also have not tested this... YMMV

Changes highlighted in red

I saw this backup script somewhere that might help. Use a cron to run the script after editing it.

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

/* CONFIGURE THE FOLLOWING SEVEN 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
$to = "you@remotesite.com"; // Email address to send dump file to
$from = "you@yourhost.com"; // Email address message will show as coming from.
$subject = "MySQL backup file"; // Subject of email


$filename = $filename_temp . ".gz"; // The compressed filename
$command = "mysqldump -u $dbuser --password=$dbpwd $dbname > $filename_temp";
$result = passthru($command);
$command = "gzip -9 $filename_temp";
$result = passthru($command);


$attachmentname = array_pop(explode("/", $filename)); // If a path was included, strip it out for the attachment name

$message = "Compressed database backup file $attachmentname attached.";
$mime_boundary = "<<<:" . md5(time());
$data = chunk_split(base64_encode(implode("", file($filename))));

$headers = "From: $from\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-type: multipart/mixed;\r\n";
$headers .= " boundary=\"".$mime_boundary."\"\r\n";

$content = "This is a multi-part message in MIME format.\r\n\r\n";
$content.= "--".$mime_boundary."\r\n";
$content.= "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n";
$content.= "Content-Transfer-Encoding: 7bit\r\n\r\n";
$content.= $message."\r\n";
$content.= "--".$mime_boundary."\r\n";
$content.= "Content-Disposition: attachment;\r\n";
$content.= "Content-Type: Application/Octet-Stream; name=\"$attachmentname\"\r\n";
$content.= "Content-Transfer-Encoding: base64\r\n\r\n";
$content.= $data."\r\n";
$content.= "--" . $mime_boundary . "\r\n";

mail($to, $subject, $content, $headers);

unlink($filename); //delete the backup file from the server
?>

[mstyers]

Thanks Ron. Good suggestions.

I added your changes. The zip file was empty. The .sql file is created fine. I changed this line:
$command = "gzip -9 $filename_temp";
to
$command = "gzip -9 $filename_temp > $filename";

I also received an empty zip file. Do you think it might be a permissions issue or path issue after the .sql is created?

***Update***
Ron, your original command line did work. Not sure why I received an empty file the first time. Thanks.

[Ron]

Check the filename line. I had an error that I fixed.

The correct line should be
$filename = $filename_temp . ".gz"; // The compressed filename

[mstyers]

Do you have a suggestion on how to backup current users from the database?

[JPC-Katrina]

Would you please re-post the full working script code. I'm sure others would appreciate it. Best backup for database users is keeping them written down somewhere when you create them.

[Ron]

Assuming it now works...

I had already edited the script to correct the issue... again, I haven't tested it, and I really don't recommend this script as a permanent solution. In my opinion it doesn't do what is needed; I just modified it for performance since he was already adopting it.

In my opinion it needs error checking and local backup rotation.

In addition, it could probably use a more robust transport mechanism than email.

That said, I perform backups manually. Usually. LOL

[mstyers]

Sure. Here is the working code as I have tested it. This will leave the .gz file on the server so that you can create a backup rotation.

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

/* CONFIGURE THE FOLLOWING SEVEN 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 = "dbBackup-$datestamp.sql"; // The name (and optionally path) of the dump file
$filename = $filename_temp.".gz"; // The compressed file name
$to = "you@remotesite.com"; // Email address to send dump file to
$from = "you@yourhost.com"; // Email address message will show as coming from.
$subject = "MySQL backup file"; // Subject of email

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

$attachmentname = array_pop(explode("/", $filename)); // If a path was included, strip it out for the attachment name

$message = "Compressed database backup file $attachmentname attached.";
$mime_boundary = "<<<:" . md5(time());
$data = chunk_split(base64_encode(implode("", file($filename))));

$headers = "From: $from\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-type: multipart/mixed;\r\n";
$headers .= " boundary=\"".$mime_boundary."\"\r\n";

$content = "This is a multi-part message in MIME format.\r\n\r\n";
$content.= "--".$mime_boundary."\r\n";
$content.= "Content-Type: text/plain; charset=\"iso-8859-1\"\r\n";
$content.= "Content-Transfer-Encoding: 7bit\r\n\r\n";
$content.= $message."\r\n";
$content.= "--".$mime_boundary."\r\n";
$content.= "Content-Disposition: attachment;\r\n";
$content.= "Content-Type: Application/Octet-Stream; name=\"$attachmentname\"\r\n";
$content.= "Content-Transfer-Encoding: base64\r\n\r\n";
$content.= $data."\r\n";
$content.= "--" . $mime_boundary . "\r\n";

mail($to, $subject, $content, $headers);

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

[JPC-Katrina]

Script has been working fine for me for years but I have small databases and yes, do manually check the resulting backups now and then for errors. Error checking would be a good addition to script. For larger DBs, you need to either change script to FTP them to another server or leave them on server and FTP them down manually. Just leaving them on the server is not good in case of a server crash and takes up unnecessary amount of space. I do have similar script for FTP if you want it.

[mstyers]

Please post the FTP script as well. That would be great!

[Ron]

Probably should add the 5th parameter to mail "-fyouremailaddy@yourdomain.com" as well.

[JPC-Katrina]

Backup MySQL by cron and FTP

Again, I didn't create this. Just saw it somewhere long ago and saved it. Alter as you wish.

<?
$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= "backup-$datestamp.sql.gz"; // The name (and optionally path) of the dump file

$command = "mysqldump -u $dbuser --password=$dbpwd $dbname | gzip > $filename";
$result = passthru($command);

/* 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 = "anonymous"; // FTP account username
$ftp_password = ""; // FTP account password - blank for anonymous

// 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))
{
echo "FTP connection has failed.";
echo "Attempted to connect to $ftp_server for user $ftp_username";
exit;
}
else
{
echo "Connected to $ftp_server, for user $ftp_username";
}

// upload the file
$upload = ftp_put($ftp_conn, $filename, $filename, FTP_BINARY);

// check upload status
if (!$upload)
{
echo "FTP upload has failed.";
}
else
{
echo "Uploaded $filename to $ftp_server.";
}

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

unlink($filename); //delete the backup file from the server
?>