[L4N0]

I read this on the internet and I thought it would be interesting to post it here.
How to make an automatic MySQL backup and have it sent to you by email:

Quote

1. Log on to Cpanel. Go to Cron Jobs. Click Standard.
2. Where it says "Please enter an email address where the cron output will be sent:" put your email address or cpanel username ( in this case the results will be sent to your email account on the server)
3. Where it says "Command to run" type this mysqldump -u YOURMYSQLUSERNAME --password=YOURMYSQLPASSWORD YOURDATABASENAME
4. For the time interval I'd recommend that you select once a day or once a week depending on your site traffic.
5. You are done.

It should work for everyone here since I tested it on my account on this server.

This post has been edited by L4N0: 15 March 2005 - 02:40 AM

[elyk]

thanks. I had tried to do this with a shell script, but I couldn't get it to work. Now I just need my cpanel to come back up so I can set this up...anyone know what's wrong?

[Boles Roor]

Pinned

[elyk]

how do I restore from this? do I just copy and paste sql from the email into phpmyadmin? and is there anyway to get it to send it as an attachment (probably not, but worth asking)

[psyfish]

what does YOURDBNAME meen?

[L4N0]

elyk, on Mar 14 2005, 08:41 PM, said:

how do I restore from this? do I just copy and paste sql from the email into phpmyadmin? and is there anyway to get it to send it as an attachment (probably not, but worth asking)

yeah if you paste it into phpMyAdmin it should work.
I don't think that it can be sent as an attachment, since the Cron Daemon gets the result of the command and emails it to you but you can't specify any options on the way it is emailed.
I edited the original post to clarify psyfish's question. (It is databasename)

[nilayz]

Errr... I still couldn't get it working.
What password do I give for backing up my DB that is used by Advanced Guestbook? I tried it with no password and with my cpanel password. It didn't work.

[L4N0]

I installed the guestbook on my account and this is what I found out:
When you install a script from Cpanel it creates a new database, username and a random password which are used for that script. For the Advanced Guestbook, after the installation you should check the source of config.inc.php located in the admin/ folder. The first four variables are the ones that you need. It should look something like this:

/* database settings */

$GB_DB["dbName"] = "xxxx_agbook1";
$GB_DB["host"] = "localhost";
$GB_DB["user"] = "xxxx_agbook1";
$GB_DB["pass"] = "xxxxxxxxx";

[nilayz]

Yes... it's working now. Thanks mate.

[SnowboardAlliance]

Anyone have a way to have this email the .sql file to you?

[elyk]

Not with this one. I saw one somewhere that could though. I'll see if I can find it.
*goes checking*

[elyk]

EDIT: Sorry about the double post. I thought I hadn't submitted it yet, but I guess it did and my browser just didn't refresh.
Not with this one. I found one that could though. It will generate a gzipped sql file and email it to you as an attachment. Copy and paste this into a php file, and place it into a folder that is chmoded 777.Change the variables to their correct values in the php file. Then either run the php file using a cron job or visit it using your browser. I think the correct syntax for running a php file using cron is:

php /path/to/file

<?
$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
?>

This post has been edited by elyk: 23 May 2005 - 09:49 PM

[SnowboardAlliance]

elyk, on May 23 2005, 05:48 PM, said:

EDIT: Sorry about the double post. I thought I hadn't submitted it yet, but I guess it did and my browser just didn't refresh.
Not with this one. I found one that could though.  It will generate a gzipped sql file and email it to you as an attachment. Copy and paste this into a php file, and place it into a folder that is chmoded 777.Change the variables to their correct values in the php file. Then either run the php file using a cron job or visit it using your browser. I think the correct syntax for running a php file using cron is:

php /path/to/file

<?
$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
?>

I'll try it, but won't CHMODing to 777 let someone be able to see my database password? Will 755 work?

[elyk]

No. The file itself doesn't have to be chmoded to 777. 755 should work just fine. What has to be chmoded to 777 is the directory that you place it in, so that the script is allowed to create the sql file there in preparation for emailing it to you.

[SnowboardAlliance]

elyk, on May 23 2005, 09:13 PM, said:

No. The file itself doesn't have to be chmoded to 777. 755 should work just fine. What has to be chmoded to 777 is the directory that you place it in, so that the script is allowed to create the sql file there in preparation for emailing it to you.

Oh, read it wrong, I get it.

[SnowboardAlliance]

I couldn't get a cronjob to save, but I tried to run that file and get errors

Warning: file(phpBB-2005-06-14.sql.gz): failed to open stream: No such file or directory in /home/snoboard/public_html/backup.php on line 20

Warning: implode(): Bad arguments. in /home/snoboard/public_html/backup.php on line 20

Warning: unlink(phpBB-2005-06-14.sql.gz): No such file or directory in /home/snoboard/public_html/backup.php on line 41

Looks like it never creates the file, can someone add that so it will work?

EDIT: It mailed it to me, but it is just a blank file, the command is never run-just the variable is set. How do you run it?

This post has been edited by SnowboardAlliance: 14 June 2005 - 05:14 PM

[SnowboardAlliance]

Any help???