Cron Job for Tar and Mysql backups

[matthewamzn]

I'm using Plesk 7.5.4 and it has a built in cron job interface. I want to use it to automate my backups. Right now I'm making a mysqldump and tar file of httpdocs directory (every 2 weeks).

I want to create a cron job for each of these processes. Would the commands be the same?

Mysqldump:
mysqldump --opt -uusername -ppassword database > /www/var/vhosts/website.com/private/backupname.sql

Tar:
tar -cvvf private/tarbackup.tar httpdocs/

[physics]

Yes the commands you would do are pretty much the same but remember to always specify which directories output should go in. You may want to remove the v from the flags though since you won't need to see verbose output.
Also, if you ad

2>&1 > /dev/null 

At the end of each line it will ensure that the user running the jobs doesn't get emailed a bunch of output from the commands.
Finally, why not use compression?

tar -cvzf foo.tgz foo

creates a gzipped tar archive, to unzip it

tar -xvzf foo.tgz

Also you could add a line to compress your mysqldump output too.

[matthewamzn]

I'll change it to zip the tar file. Also what about the backup names? How can I make them change for each weekly backup?

ie,
backupjune22.tar
backupjune29.tar

[physics]

The way I do this is to write a small shell script and then run that from cron (I don't use that plesk thing though so your mileage may vary):
Fore example:

#!/bin/sh
#backup foo folder daily
#delete copies older than one month
DATE=`date +%Y%m%d`
cd /home/me/
tar -cvf /opt/backups/foo/foo-backup-$DATE.tar foo
cd /opt/backups/foo/
bzip2 -9 foo-backup-$DATE.tar
find /opt/backups/foo/ -mtime +31 ¦ xargs rm -f

That script actually uses the bzip2 compression algo which makes smaller files than gzip.
The date +%Y%m%d syntax puts the date in the most reasonable format, i.e. YYYYMMDD which is nice because sorting by number always gets the dates in the right order that way. Read the unix manual for date for more options...

[matthewamzn]

I'm not married to Plesk. It came with my server, so I've always used it. Have I got this right? How does that last line work (finds files with a date less 31?)

#!/bin/sh
#backup website weekly
#delete copies older than one month

DATE=`date +%Y%m%d`

mysqldump --opt -uusername -ppassword database > /var/www/vhosts/website.com/private/backupname.sql

cd /var/www/vhosts/website.com/

tar -cvzf /private/website-backup-$DATE.tar httpdocs/

find /private/ -mtime +31 ¦ xargs rm -f

[matthewamzn]

Assuming that script is right would I store it in:

bin/backupscript

and execute it (somehow) from:

etc/cron.weekly/startbackupscript

[physics]

You may just be able to drop all of the lines in your plesk program, I'm not sure. Put the script somewhere and add the extension .sh to it, also do chmod 755 or whatever to make it executeable. To do it from the command line you would do:

crontab -e

(at this point you're taken into the crontab editing interface, it will be either vi or emacs depending on your system configuration. if it's vi type i to start typing and then to exit press Esc then : then q then enter)

Add the line

0 0 * * 0 /bin/bash /home/you/backupscript.sh 2>&1 > /dev/null

For cron syntax help see:
[developer.apple.com...]

[physics]

BTW, 0 0 * * 0 means run on sunday at midnight every week.

[matthewamzn]

This is what my crontab looks like:

crontab -e
14 5 * * * /usr/local/psa/bin/mysqldump.sh >/dev/null 2>&1
7 4 * * * /usr/local/psa/admin/sbin/statistics >/dev/null 2>&1
*/15 * * * * /usr/local/psa/admin/sbin/backupmng >/dev/null 2>&1
17 7 * * * /usr/local/psa/admin/bin/php /usr/local/psa/admin/plib/report/autoreport.php --auto daily >/dev/null 2>&1
17 7 * * 1 /usr/local/psa/admin/bin/php /usr/local/psa/admin/plib/report/autoreport.php --auto weekly >/dev/null 2>&1
17 7 1 * * /usr/local/psa/admin/bin/php /usr/local/psa/admin/plib/report/autoreport.php --auto monthly >/dev/null 2>&1
~
~
~

[matthewamzn]

I was going to put the backup script in the root bin directory of my server. Then adding this to the crontab:

0 0 * * 0 /bin/backupscript.sh >/dev/null 2>&1

I'm not sure what removing the bin/bash part would do.

[physics]

Do this instead:

0 0 * * 0 /bin/bash /bin/backupscript.sh 2>&1 >/dev/null

You need to give the full path to the program which will execute your backupscript.sh script, just as is done with the PHP line in there /usr/local/psa/admin/bin/php
Because it is being run by the system and not a logged in user the system does not know what to do with a file (there are no environment variables) unless you tell it exactly what programming language to use to process it (bash in this case).

You can test it but changing 0 0 * * 0 to be, say 2 minutes from now so something like 47 13 * * * if it is currently 1:45pm. Just remember to change it back to 0 0 * * 0 or it will run every dat at 1:47pm.

[matthewamzn]

Is there anything wrong with creating the shell script in notepad and uploading it via ftp?

[wheel]

It's OK to write a script in notepad then upload via ftp. It's all text.

I'm not sure why you're backing up every two weeks. If you're backing up locally, then there's no reason not to backup at least daily. nothing much going on in most servers at 3a.m.

If you're backup up offsite, you might consider looking into the rsync command. That will backup only changed files, and again you can then backup nightly (since you're not doing a full backup each night).

Here's the thread where I detail my backup. It's working flawlessly, and I've had to use it a few times and been happy I didn't have a two week old backup.
[webmasterworld.com...]

I think you're likely missing two things in your backup. The first is as noted, more frequent backups are far better (and since it's just computer power, might as well do it regularly). The second is archival copies - don't just keep one backup, keep a history of backups. For the one time when you need to get something from three weeks ago that you didn't realize you'd lost until now.

[physics]

It's all text.

It's probably OK but sometimes there are problems because of windows end of line characters ... all text is not created equal ;)
I prefer to use one that does unix line breaks properly, like EditPlus.
[editplus.com...]

Also, you might want to have a look-see at the book Linux Server Hacks as it has a hack on backing up with rsync and turbo logins with ssh keys.

[matthewamzn]

Can't seem to get this to work.

------------
My crontab file looks like this:

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/

# run-parts
01 * * * * root run-parts /etc/cron.hourly
02 4 * * * root run-parts /etc/cron.daily
22 4 * * 0 root run-parts /etc/cron.weekly
42 4 1 * * root run-parts /etc/cron.monthly
56 11 * * * /bin/bash /bin/backupscript.sh 2>&1 >/dev/null

------------
My backup script is stored in bin/backupscript.sh and looks like this:

#!/bin/sh
#backup website weekly

DATE=`date +%Y%m%d`

mysqldump --opt -uusername -ppassword dbname > /var/www/vhosts/backupsite.com/private/website-db-$DATE.sql

cd /var/www/vhosts/backupsite.com/

tar -cvzf private/website-backup-$DATE.tar httpdocs/

[matthewamzn]

The time for the backup script to execute was just for testing. I want it to actually execute once a week.

[matthewamzn]

OK, I finally got it working. The solution for me was to use the vi editor. Thanks for the help.