Creating cron job backup that omits selected tables.

Regular Joined: Jun 02, 2004 Posts: 88

I have been looking for a cron job backup that omits selected tables of a MySQL database. Maybe I am crazy, but I just do not see the value of including some tables in a back up. For example, forum word search and hourly statistics. Those tend to add a lot of data that is of no real value when needing to use the backup.

Can anyone point me in a direction on how to do this?

thanks in advance......

Posted: Wed Sep 29, 2004 11:30 am

I have a script that backups up the complete database as a cron job. It could be adapted to do what you want with the addition of one table to your database. The new table would store all the names of your db's tables and have a setting for 0 or 1.

Where 1 means include in the backup. The table could be updated with an admin script that was written just for that so that you could have it scan the db for any new or removed tables then you could select which ones you wanted to be part of the dump.

Of course this would take several hours of testing and development to ensure it was right.

Posted: Wed Sep 29, 2004 2:06 pm

cool..... sounds like a nice solution.....

ok.... How can I get the script so I can "play"?

oh, btw.... Do you live around Lexington? about 2 weeks ago I played the Houston Oaks golf course between Paris and Lexington. Smile

Wish I had the contract for the gray paint used on the farms there. Smile

Posted: Wed Sep 29, 2004 2:28 pm

Louisville

Posted: Wed Sep 29, 2004 7:24 pm

This is the script I use to dump both my database and my site nightly. I have it in a password protected directory so that only the cron can run it.

Code:

<?php 





$emailaddress = "webmaster@yoursite.com";


$host="localhost"; // database host address


$dbuser=""; // database user name


$dbpswd=""; // database password


$mysqldb=""; // name of database


$day = (date("Y-m-d"));


$path1 = "/home/PATHTO/dumps"; // path to your backups storage


$path2 = "/home/PATHTO/public_html/"; // path to your web root


$filename1 = $path1."/".$mysqldb."_".$day.".sql";


$filename2 = $path1."/".$mysqldb."_".$day.".sql.gz";


$filename3 = $path1."/".$mysqldb."_".$day.".tar.gz";





if( file_exists($filename1) ) { unlink("$filename1"); }


if( file_exists($filename2) ) { unlink("$filename2"); }


if( file_exists($filename3) ) { unlink("$filename3"); }





system( "/usr/bin/mysqldump --opt --no-create-db --user=$dbuser --password=$dbpswd --host=$host $mysqldb > $filename1",$result);


system( "tar -czf $filename2 $filename1",$result);


unlink("$filename1");


system( "tar -czf $filename3 $path2",$result);





$runtime = (date(" F d h:ia"));


$message .= "The Site/DB backups have been run.\n\n";


$message .= "Server time of the Site/DB backups: $runtime\n\n";


mail($emailaddress, "Site/DB Backup Message" , $message, "From: Your Site <$emailaddress>");





?>

The dumps directory must be CHMODed to 777 and preferable be above your root directory. The public_html directory is your root nuke directory, this apllies to the site dump part of the script.

The cron should be called as follows:
GET [ Only registered users can see links on this board! Get registered or login! ] > /dev/null

pass-dir in the password protected directory you have this script placed in.

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

Just came across this. It looks very interesting and I am going to examine it more closely! Not everyone has permissions to run/add cron jobs. So, along comes Pseudo-Cron [ Only registered users can see links on this board! Get registered or login! ] !

Site Admin Joined: Jun 04, 2004 Posts: 6432

I'm evaluating different backup alternatives and found this.

It allows you to:

backup multiple databases
email or ftp backup
specify excluded tables
some sort of scheduling function - not clear on this yet

It does require MySQL. I'm looking at several others, too, and will post an update if I find something better. It should work manually, as a cron job, or, with Pseudo-Cron.

New Member Joined: Oct 31, 2004 Posts: 1

BobMarion wrote:

This is the script I use to dump both my database and my site nightly. I have it in a password protected directory so that only the cron can run it.

Code:

<?php 





$emailaddress = "webmaster@yoursite.com";


$host="localhost"; // database host address


$dbuser=""; // database user name


$dbpswd=""; // database password


$mysqldb=""; // name of database


$day = (date("Y-m-d"));


$path1 = "/home/PATHTO/dumps"; // path to your backups storage


$path2 = "/home/PATHTO/public_html/"; // path to your web root


$filename1 = $path1."/".$mysqldb."_".$day.".sql";


$filename2 = $path1."/".$mysqldb."_".$day.".sql.gz";


$filename3 = $path1."/".$mysqldb."_".$day.".tar.gz";





if( file_exists($filename1) ) { unlink("$filename1"); }


if( file_exists($filename2) ) { unlink("$filename2"); }


if( file_exists($filename3) ) { unlink("$filename3"); }





system( "/usr/bin/mysqldump --opt --no-create-db --user=$dbuser --password=$dbpswd --host=$host $mysqldb > $filename1",$result);


system( "tar -czf $filename2 $filename1",$result);


unlink("$filename1");


system( "tar -czf $filename3 $path2",$result);





$runtime = (date(" F d h:ia"));


$message .= "The Site/DB backups have been run.\n\n";


$message .= "Server time of the Site/DB backups: $runtime\n\n";


mail($emailaddress, "Site/DB Backup Message" , $message, "From: Your Site <$emailaddress>");





?>

The dumps directory must be CHMODed to 777 and preferable be above your root directory. The public_html directory is your root nuke directory, this apllies to the site dump part of the script.

The cron should be called as follows:
GET [ Only registered users can see links on this board! Get registered or login! ] > /dev/null

pass-dir in the password protected directory you have this script placed in.

Great little script! I installed and ran it on a test database (I always believe in testing first) and it created the database backup OK. However it seemed to have trouble creating the tar.gz for the site files. It came out corrupted. That's not a real problem for me. All I would want is to backup the database anyway via a cronjob. I do a full manual backup of the site files once a week via my hosts' Cpanel. But I like the idea of doing auto backups for my databases on a daily basis. What would have to be deleted from your script to just include a database and not the site files?
Thanks in advance, John

Worker Joined: Apr 14, 2004 Posts: 193

Can you please offer the same script with just the database backup? I have four nukes on one domain and dont have the space on the server to keep file backups. I would like to have daily backups of all of the databases tho............

Posted: Fri Nov 26, 2004 4:21 pm

Bob seems to be busy right now....... Can anyone help me by editing the above script so that it only calls for the database backup? Thanky!