[MikeDVB]

Our new backup server has more than 4 times the storage capacity of our previous backup server and as such we're quadrupling the restoration points. You will now be able to restore files back as far as a full 28 days, up from the previous 7 days.

[Michael Tran]

Awesome! just out of curiosity, does this backup our mysql database files as well?

[MikeDVB]

Michael Tran, on 07 December 2010 - 03:03 AM, said:

Awesome! just out of curiosity, does this backup our mysql database files as well?

It does, but an administrator has to perform the restoration. We really ask that you use it for emergencies and not rely upon it for situations such as upgrading your script and it messes up.

Almost every script update asks that you take a manual backup of your database, which is easy to do from your cPanel, before you perform the update... You should still do this

[Michael Tran]

Alright, thanks alot. I'll probably do monthly manual backups as well.

[MikeDVB]

Michael Tran, on 07 December 2010 - 03:38 PM, said:

Alright, thanks alot. I'll probably do monthly manual backups as well.

Doing your own backups is always a good idea and I always recommend that hosting clients no matter who they host with (us, or somebody else) always keep their own off-provider backups just in case.

[TotalZen]

Here's a php script that will perform automated backups of your database(s) and email them to you.

What I did was set up a separate Gmail account for it, and then filter all the emails/attachments to the Trash folder so the account doesn't max out. The trash can automatically deletes emails after 20 days, so the whole thing is on auto-pilot with zero maintenance.

Simply run the script nightly as a cron job...

/usr/bin/php / /home/...(your path goes here).../cron_backup.php

<?php

$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_user";            // Database username
$dbpwd = "database_pass";            // Database password
$dbname = "database_name";            // Database name. Use --all-databases if you have more than one
$filename= "dump_filename-$datestamp.gz";   // The name (and optionally path) of the dump file
$to = "email_address";      // Email address to send dump file to
$from = "mysql_backup@domain.com";      // Email address message will show as coming from.
$subject = "domain.com MySql Backup For: $datestamp";      // Subject of email

/* CONFIGURATION END */

$command = "mysqldump --opt --quick --skip-extended-insert -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/x-gzip; 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,"-f $from");

unlink($filename);   // Delete the backup file from the server (optional)

?>

[Blind Bandit]

TotalZen, on 07 December 2010 - 09:18 PM, said:

Here's a php script that will perform automated backups of your database(s) and email them to you.

What I did was set up a separate Gmail account for it, and then filter all the emails/attachments to the Trash folder so the account doesn't max out. The trash can automatically deletes emails after 20 days, so the whole thing is on auto-pilot with zero maintenance.

Simply run the script nightly as a cron job...

/usr/bin/php / /home/...(your path goes here).../cron_backup.php

<?php

$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_user";            // Database username
$dbpwd = "database_pass";            // Database password
$dbname = "database_name";            // Database name. Use --all-databases if you have more than one
$filename= "dump_filename-$datestamp.gz";   // The name (and optionally path) of the dump file
$to = "email_address";      // Email address to send dump file to
$from = "mysql_backup@domain.com";      // Email address message will show as coming from.
$subject = "domain.com MySql Backup For: $datestamp";      // Subject of email

/* CONFIGURATION END */

$command = "mysqldump --opt --quick --skip-extended-insert -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/x-gzip; 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,"-f $from");

unlink($filename);   // Delete the backup file from the server (optional)

?>

Nice, my question is where to do you upload this to (I would assume home directly)? and what do you need to name it? backup.php or something different.

[MikeDVB]

Blind Bandit, on 08 December 2010 - 11:14 AM, said:

Nice, my question is where to do you upload this to (I would assume home directly)? and what do you need to name it? backup.php or something different.

Upload it outside of public_html so nobody can call it from outside the server and then you can call it whatever you want as you'll just fire it via cron.

[Blind Bandit]

MikeDVB, on 08 December 2010 - 01:21 PM, said:

Upload it outside of public_html so nobody can call it from outside the server and then you can call it whatever you want as you'll just fire it via cron.

Thanks mike

[MikeDVB]

Blind Bandit, on 08 December 2010 - 02:25 PM, said:

Thanks mike

No problem

[stocktrader]

TotalZen, on 07 December 2010 - 09:18 PM, said:

Here's a php script that will perform automated backups of your database(s) and email them to you.

What I did was set up a separate Gmail account for it, and then filter all the emails/attachments to the Trash folder so the account doesn't max out. The trash can automatically deletes emails after 20 days, so the whole thing is on auto-pilot with zero maintenance.

Simply run the script nightly as a cron job...

/usr/bin/php / /home/...(your path goes here).../cron_backup.php

<?php

$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_user";            // Database username
$dbpwd = "database_pass";            // Database password
$dbname = "database_name";            // Database name. Use --all-databases if you have more than one
$filename= "dump_filename-$datestamp.gz";   // The name (and optionally path) of the dump file
$to = "email_address";      // Email address to send dump file to
$from = "mysql_backup@domain.com";      // Email address message will show as coming from.
$subject = "domain.com MySql Backup For: $datestamp";      // Subject of email

/* CONFIGURATION END */

$command = "mysqldump --opt --quick --skip-extended-insert -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/x-gzip; 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,"-f $from");

unlink($filename);   // Delete the backup file from the server (optional)

?>

Would it be a pain in the ****** to ask you to rewrite this script to make it save a copy of our MySQL database in our MDD FTP folder (preferably "/etc")?

[Scott S]

stocktrader, on 31 January 2011 - 06:10 PM, said:

Would it be a pain in the ****** to ask you to rewrite this script to make it save a copy of our MySQL database in our MDD FTP folder (preferably "/etc")?

The script seems to create the file on disk and simply deletes it after emailing it to you. Replace the unlink call with something that moves the file the an appropriate location and you should be golden.

[fshagan]

I love this! I book marked it so when I'm over this cold and can think again, I'll implement this. Using Gmail is a nice touch too. Very nice. (I may be more sentimental than usual because of the cold medicine).

[stocktrader]

I tried to run the above script as it is written, but the email I receive says "Permission Denied". Anyone know why that is?

[MikeDVB]

You would need to be more specific - is the script itself giving the error or are you getting the error when trying to run the script for example. Ultimately MDDHosting doesn't provide support for third party scripts but others here on the forum may be able to

[stocktrader]

I understand about not providing support for third-party scripts.

The permission denied error comes in the email that the script sends out. I played around with the settings so that the error isn't there anymore but it was replaced by "error: 1045: Access denied for user 'xxxx'@'localhost' (using password: YES) when trying to connect". From my googling, I thought it might be some kind of MySQL user permission-related thing.

I got the script to work, but only when I write the mysqldump command directly into the cron area in CPanel instead of being stored in an external file. But I nated to know if there is any security risk in having my database username and password stored in a script in CPanel?

[MikeDVB]

Not any more of a security risk than storing it anywhere else in your account.