Want to Backup your MySQL databases automagically?

**sam** · July 17th, 2004, 7:30 PM

Background

Surmunity.com clamored for a backup solution that works outside of cPanel and works without user intervention. If you follow these instructions you will have automagic backups of a mysql db in a few simple steps.

This backup solution will email the gzip file to you on a schedule.

Credits

This script is a revision of a file named dbsender.php written by Eric Rosebrock. It's available in original form at http://www.phpfreaks.com/script/view/11.php. Several modifications were made for clarity, and the implementation instructions are provided by Surpass Hosting.

Create a directory

1) In your home directory create a new directory

/home/username/backup_mysql

where backup_mysql is the new directory name.

NOTE: this is outside your public web directory.

2) Chmod the directory created in step 1 to 644.

Grant Db permissions

3) Go cPanel > MySQLDatabase.

4) Create a new user - password.

5) Click Add User.

6) Indicate LOCK and SELECT permissions for the user created in step 4.

7) Click Add User to Db.

Configure the source

8) Below are the variables you MUST adjust in backup_mysql.php.

$dbuser = 'username_backupaccountname';
$dbpass = 'yourpassword';
$dbname = 'username_dbnametobackup';
$savepath = "/home/username/backup_mysql";
$to = "ausername[at]yourdomain.com";
$from = "anotherusername[at]yourdomain.com";

These are variables that you adjust to suit your needs.

$subject = "Full backup of $dbname completed - $senddate";
$message = "See attached file for mysqldump of $dbname";

These last 3 variables are all up to you. "yes" or "no"

$use_gzip = "yes";
$remove_sql_file = "yes";
$remove_gzip_file = "yes";

Upload the source

9) Save changes to backup_mysql.php

10) Upload the backup_mysql.php file to the directory created in step 2.

11) Chmod the file to 644.

Schedule a cron job

12) Go cPanel > Chron Jobs > Advanced ( Unix Style )

13) Enter an email address for the chron output ( in case there are errors )

14) Decide when / how often you want the chron job to run in the example below it runs 1x a day at 01:02 AM

2 1 * * *

15) Enter the command - in our example - it invokes the backup_mysql.php - and sends output to a log file in the same directory

php /home/username/backup_mysql/backup_mysql.php > /home/username/backup_mysql/backup_mysql.log

16) Click Commit changes

VOILA! You got backup!

Now when the chron job runs - you'll get an email with a backup of the mysql database specified, to the email address specified. As configured in this example - the gzip / sql files will be wiped from the savedpath after mailing.

Questions?

Question: Can this solution be triggered from a link on my web site for on demand backups?
Answer: Yes, but not as configured and installed. There are some security issues with doing that. However, I fully expect others to add instructions for doing so to this thread. What's posted here is the recommended configuration.

Question: Do I have to write the chron output to a log file?
Answer: No, if you like you can send output to > /dev/null

Question: What if I want the backups to live on the server after the chron job runs?
Answer: Just set $remove_sql_file = "no" and/or $remove_gzip_file = "no"

Question: Can I FTP these backups somewhere?
Answer: Ah, very good question. Yes, it would take a few more lines of code but that can be done as well. Another challenge for Surmunity.

Question: My db is too big for email!
Answer: Just set $send_email = "no"; and leave the backups in the directory by setting $remove_sql_file / $remove_gzip_file to "no". Or get that FTP bit working.

Question: My website involves a whole pile of databases, can I use this solution?
Answer: Yes, but not without a few modifications. Basically you would build an array of dbnames to backup - and then loop through them.

Question: How about only backing up some of the tables?
Answer: Sounds like you have some work to do! Get busy mister! Or miss!

Yeah so what's next?

Obviously this automated mysql backup solution could be much more elegant and exhaustive. So my hope for Surmunity is to go crazy go nuts on this script/method and make this better. Post your improvements back here in this thread. Let's keep this all in one place.

The Source

Copy this code and save it in a file named backup_mysql.php

PHP Code:


			
 
 <?php
 
 $dbhost = 'localhost';   
 $dbuser = 'username_backupaccountname';           
 $dbpass = 'yourpassword';                 
 $dbname = 'username_dbnametobackup'; 
 $savepath = "/home/username/backup_mysql";
 
 $send_email = "yes";  
 $to  = "ausername[at]yourdomain.com";               
 $from = "anotherusername[at]yourdomain.com";  
 $senddate = date("j F Y");
 $subject = "Full backup of $dbname completed - $senddate";          
 $message = "See attached zip file for mysqldump of $dbname"; 
 
 $use_gzip = "yes";    
 $remove_sql_file = "yes";           
 $remove_gzip_file = "yes";   
 
 // Do not Modify below this line! It will void your warranty!  Nah, go crazy go nuts
 
 $date = date("mdy-hia");
 $filename = "$savepath/$dbname-$date.sql";    
 passthru("mysqldump --opt -h$dbhost -u$dbuser -p$dbpass $dbname >$filename");
     
 if($use_gzip=="yes"){
     $zipline = "tar -czf ".$dbname."-".$date."_sql.tar.gz $dbname-$date.sql";
     shell_exec($zipline);
 }
 if($remove_sql_file=="yes"){
     exec("rm -r -f $filename");
 }
     
 if($use_gzip=="yes"){
     $filename2 = "$savepath/".$dbname."-".$date."_sql.tar.gz";
 } else {
     $filename2 = "$savepath/$dbname-$date.sql";
 }
     
     
 if($send_email == "yes" ){
 
     $fileatt_type = filetype($filename2);
     $fileatt_name = "".$dbname."-".$date."_sql.tar.gz";
         
     $headers = "From: $from";
         
     // Read the file to be attached ('rb' = read binary)
     $file = fopen($filename2,'rb');
     $data = fread($file,filesize($filename2));
     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 = [at]mail($to, $subject, $message, $headers);
     if ($ok) {
         echo "<h4><center>Database backup created and sent! File name $filename2</center></h4>";
     } else {
         echo "<h4><center>Mail could not be sent. Sorry!</center></h4>";
     }
 }
     
     
 if($remove_gzip_file=="yes"){
     exec("rm -r -f $filename2");
 }
 
 ?>

NOTE: the AT symbol does not display in Surmunity, and instead shows as a [at]. If you copy paste the code, substitute the AT symbol for [at]

**George** · July 17th, 2004, 7:50 PM

The application can start my car right?

:reddance:

imsleepy · July 18th, 2004, 2:34 AM

Good thing I bought PHP & MySQL for Dummies awhile back! :lol3:

**MrPixar** · July 18th, 2004, 2:56 AM

This is excellent sam. Thank you for sharing it...see at least I was patient about it...LOL. Great to see surpassers helping surpassers.

Great work bub!

**sam** · July 21st, 2004, 11:44 PM

is anyone using this? of did i miss the mark that there was interest in this?

**George** · July 21st, 2004, 11:50 PM

Yes, it starts my car and and my teleporters work perfectly. An added plus!

imsleepy · July 22nd, 2004, 12:43 AM

You didn't miss the mark... I simply have to go back to school (via the dummy book) to find out what you said.

Maybe a tutorial to go with it? :brief:

**MrPixar** · July 22nd, 2004, 10:35 AM

Quote:

Originally Posted by sam

is anyone using this? of did i miss the mark that there was interest in this?

Oh yeah there is interest in this (on part at least) I have implemented your solution and it works just fine. Thanks again for documenting the procedure :surpass:

/gz/

sndinc · September 21st, 2004, 12:51 PM

I'm having a problem - the problem is probably understanding permissions on linux when you are used to windows. See I used the cpanel file manager to make my backup_mysql directory but when it's set to 644, if I click in there i get permission denied. So I set it to 777 to upload my backup_mysql.php file after I made the changes and set the directory back to 644. The backup_mysql.php is set to 644, but when the cron job was supposed to run I just got this email that said "/bin/sh: line 1: /home/myusername/backup_mysql/backup_mysql.log: Permission denied"
My user name is actually in place of "myusername" - so.......what do I have setup wrong?

July 17th, 2004, 7:30 PM	#1 (permalink)
sam Surpass Fan Super #1 Joined in Dec 2003 Lives in NJ 5,057 posts Gave thanks: 0 Thanked 4 times	Want to Backup your MySQL databases automagically? Background Surmunity.com clamored for a backup solution that works outside of cPanel and works without user intervention. If you follow these instructions you will have automagic backups of a mysql db in a few simple steps. This backup solution will email the gzip file to you on a schedule. Credits This script is a revision of a file named dbsender.php written by Eric Rosebrock. It's available in original form at http://www.phpfreaks.com/script/view/11.php. Several modifications were made for clarity, and the implementation instructions are provided by Surpass Hosting. Create a directory 1) In your home directory create a new directory /home/username/backup_mysql where backup_mysql is the new directory name. NOTE: this is outside your public web directory. 2) Chmod the directory created in step 1 to 644. Grant Db permissions 3) Go cPanel > MySQLDatabase. 4) Create a new user - password. 5) Click Add User. 6) Indicate LOCK and SELECT permissions for the user created in step 4. 7) Click Add User to Db. Configure the source 8) Below are the variables you MUST adjust in backup_mysql.php. $dbuser = 'username_backupaccountname'; $dbpass = 'yourpassword'; $dbname = 'username_dbnametobackup'; $savepath = "/home/username/backup_mysql"; $to = "ausername[at]yourdomain.com"; $from = "anotherusername[at]yourdomain.com"; These are variables that you adjust to suit your needs. $subject = "Full backup of $dbname completed - $senddate"; $message = "See attached file for mysqldump of $dbname"; These last 3 variables are all up to you. "yes" or "no" $use_gzip = "yes"; $remove_sql_file = "yes"; $remove_gzip_file = "yes"; Upload the source 9) Save changes to backup_mysql.php 10) Upload the backup_mysql.php file to the directory created in step 2. 11) Chmod the file to 644. Schedule a cron job 12) Go cPanel > Chron Jobs > Advanced ( Unix Style ) 13) Enter an email address for the chron output ( in case there are errors ) 14) Decide when / how often you want the chron job to run in the example below it runs 1x a day at 01:02 AM 2 1 * * * 15) Enter the command - in our example - it invokes the backup_mysql.php - and sends output to a log file in the same directory php /home/username/backup_mysql/backup_mysql.php > /home/username/backup_mysql/backup_mysql.log 16) Click Commit changes VOILA! You got backup! Now when the chron job runs - you'll get an email with a backup of the mysql database specified, to the email address specified. As configured in this example - the gzip / sql files will be wiped from the savedpath after mailing. Questions? Question: Can this solution be triggered from a link on my web site for on demand backups? Answer: Yes, but not as configured and installed. There are some security issues with doing that. However, I fully expect others to add instructions for doing so to this thread. What's posted here is the recommended configuration. Question: Do I have to write the chron output to a log file? Answer: No, if you like you can send output to > /dev/null Question: What if I want the backups to live on the server after the chron job runs? Answer: Just set $remove_sql_file = "no" and/or $remove_gzip_file = "no" Question: Can I FTP these backups somewhere? Answer: Ah, very good question. Yes, it would take a few more lines of code but that can be done as well. Another challenge for Surmunity. Question: My db is too big for email! Answer: Just set $send_email = "no"; and leave the backups in the directory by setting $remove_sql_file / $remove_gzip_file to "no". Or get that FTP bit working. Question: My website involves a whole pile of databases, can I use this solution? Answer: Yes, but not without a few modifications. Basically you would build an array of dbnames to backup - and then loop through them. Question: How about only backing up some of the tables? Answer: Sounds like you have some work to do! Get busy mister! Or miss! Yeah so what's next? Obviously this automated mysql backup solution could be much more elegant and exhaustive. So my hope for Surmunity is to go crazy go nuts on this script/method and make this better. Post your improvements back here in this thread. Let's keep this all in one place. The Source Copy this code and save it in a file named backup_mysql.php PHP Code: <?php $dbhost = 'localhost'; $dbuser = 'username_backupaccountname'; $dbpass = 'yourpassword'; $dbname = 'username_dbnametobackup'; $savepath = "/home/username/backup_mysql"; $send_email = "yes"; $to = "ausername[at]yourdomain.com"; $from = "anotherusername[at]yourdomain.com"; $senddate = date("j F Y"); $subject = "Full backup of $dbname completed - $senddate"; $message = "See attached zip file for mysqldump of $dbname"; $use_gzip = "yes"; $remove_sql_file = "yes"; $remove_gzip_file = "yes"; // Do not Modify below this line! It will void your warranty! Nah, go crazy go nuts $date = date("mdy-hia"); $filename = "$savepath/$dbname-$date.sql"; passthru("mysqldump --opt -h$dbhost -u$dbuser -p$dbpass $dbname >$filename"); if($use_gzip=="yes"){ $zipline = "tar -czf ".$dbname."-".$date."_sql.tar.gz $dbname-$date.sql"; shell_exec($zipline); } if($remove_sql_file=="yes"){ exec("rm -r -f $filename"); } if($use_gzip=="yes"){ $filename2 = "$savepath/".$dbname."-".$date."_sql.tar.gz"; } else { $filename2 = "$savepath/$dbname-$date.sql"; } if($send_email == "yes" ){ $fileatt_type = filetype($filename2); $fileatt_name = "".$dbname."-".$date."_sql.tar.gz"; $headers = "From: $from"; // Read the file to be attached ('rb' = read binary) $file = fopen($filename2,'rb'); $data = fread($file,filesize($filename2)); 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 = [at]mail($to, $subject, $message, $headers); if ($ok) { echo "<h4><center>Database backup created and sent! File name $filename2</center></h4>"; } else { echo "<h4><center>Mail could not be sent. Sorry!</center></h4>"; } } if($remove_gzip_file=="yes"){ exec("rm -r -f $filename2"); } ?> NOTE: the AT symbol does not display in Surmunity, and instead shows as a [at]. If you copy paste the code, substitute the AT symbol for [at]

July 17th, 2004, 7:50 PM	#2 (permalink)
George O Admin sem semelhante Super #1 Joined in Nov 2003 Lives in An undisclosed location in Mississippi, USA Hosted on Pass58 1,198 posts Gave thanks: 13 Thanked 9 times	The application can start my car right? The application can start my car right? :reddance: __________________ Surpass Administrator Some people are like Slinkies . . . not really good for anything, but you still can't help but smile when you see one tumble down the stairs. - unknown Server: Pass 58 Project: nunaya.bz - - A)bort, R)etry, I)nfluence with a large hammer.

July 18th, 2004, 2:56 AM	#4 (permalink)
MrPixar Pixel Smasher Comfy Contributor Joined in Feb 2004 Lives in Miami, FL Hosted on Pass51 139 posts Gave thanks: 0 Thanked 1 Time in 1 Post	This is excellent sam. Thank you for sharing it...see at least I was patient about it...LOL. Great to see surpassers helping surpassers. Great work bub! __________________ Pass51 \| garyzullo.com

July 21st, 2004, 11:50 PM	#6 (permalink)
George O Admin sem semelhante Super #1 Joined in Nov 2003 Lives in An undisclosed location in Mississippi, USA Hosted on Pass58 1,198 posts Gave thanks: 13 Thanked 9 times	Yes, it starts my car and and my teleporters work perfectly. An added plus! __________________ Surpass Administrator Some people are like Slinkies . . . not really good for anything, but you still can't help but smile when you see one tumble down the stairs. - unknown Server: Pass 58 Project: nunaya.bz - - A)bort, R)etry, I)nfluence with a large hammer.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

July 18th, 2004, 2:34 AM	#3 (permalink)
imsleepy Senior Member Excelling Contributor Joined in Jun 2004 748 posts Gave thanks: 0 Thanked 0 times	Good thing I bought PHP & MySQL for Dummies awhile back! :lol3:

July 21st, 2004, 11:44 PM	#5 (permalink)
sam Surpass Fan Super #1 Joined in Dec 2003 Lives in NJ 5,057 posts Gave thanks: 0 Thanked 4 times	is anyone using this? of did i miss the mark that there was interest in this?

July 22nd, 2004, 12:43 AM	#7 (permalink)
imsleepy Senior Member Excelling Contributor Joined in Jun 2004 748 posts Gave thanks: 0 Thanked 0 times	You didn't miss the mark... I simply have to go back to school (via the dummy book) to find out what you said. Maybe a tutorial to go with it? :brief:

September 21st, 2004, 12:51 PM	#9 (permalink)
sndinc Registered User Fresh Surpasser Joined in Aug 2004 27 posts Gave thanks: 0 Thanked 0 times	I'm having a problem - the problem is probably understanding permissions on linux when you are used to windows. See I used the cpanel file manager to make my backup_mysql directory but when it's set to 644, if I click in there i get permission denied. So I set it to 777 to upload my backup_mysql.php file after I made the changes and set the directory back to 644. The backup_mysql.php is set to 644, but when the cron job was supposed to run I just got this email that said "/bin/sh: line 1: /home/myusername/backup_mysql/backup_mysql.log: Permission denied" My user name is actually in place of "myusername" - so.......what do I have setup wrong?

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)