Hi,

I'm a Linux administrator in my company, and I'm working in with a server in a different location from the production server of my office. I've a task of pulling daily database backups (mysql) from the production server to my local server.

The original backups are of course in the production server. Backups are done daily in that server, using automysql backup script. So for example, for the database named myob, the backups are as follows :

If today were Thursday, and the backup time set in cron was 12.30 p.m. , then the backup would be named as below :

myob_2009-12-03_12h30m.Thursday.sql.gz

If it were Friday, then the backup name would be :

myob_2009-12-04_12h30m.Friday.sql.gz

and so it goes.

So how do I pull the database backups daily from Monday to Friday to my local server. Is there a way to use scp command in a script and then put that script in cron as a daily cron job to pull the database backups?

Pls help. Tqs.

Hi, welcome to LQ!

The best tool for the job in this case is probably rsync

Have a read of its man-page. It's well written, and full
of good usage examples.


Cheers,
Tink

You can indeed use rsync. Alternately, you can use scp with authorised keys to avoid it prompting for a passwd.

I would think it would be easier to combine the transfer with the script that does the backup, on the remote machine. That simplifies things, because it generates the dump file, gzips it, and then scp's it to your server. As chrism01 says, use authorized keys (whichever way you go). Detailed howto here http://sial.org/howto/openssh/publickey-auth/.

A completely different way of dealing with it would be to run the MySQL backups from your machine using ZRM for MySQL -- http://www.zmanda.com/backup-mysql.html. There are "quick start" examples at the end of the Users Manual -- http://wiki.zmanda.com/index.php/Zma...L_Users_Manual.

So does this mean that I've to create a separate script which holds the line:

scp -r [filename]/ user@serveradd:/path

and call it in cron?

But the name of the backups are different everyday, because the backups are named according to the day, so how do I use the method mentioned?

rsync would handle that - only copy stuff that's different between
the two machines. Alternatively you could make a shell-script
that creates a matching time-stamp for you name and build the
name on the fly before scp-ing.

Ok....works successfully. All the database backups till the current date is pulled into my local server from the production server using rsync.

Now my task is to upload these backups to my client's server.

That I have not done yet. But what I have to do is to create a excel sheet or sth to track/show the downloading/uploading process...

Pls guide me on this..


Tqs.

You need to tell us a bit more about what you want.
If its an after-the-fact he can check situation, just create an entry in a DB and use a website to pull up the records; assuming a non-tech end-user.
You could just email him the result, it's easy enough.

Is there a way to actually make an excel sheet track the downloading and uploading of each of the backups automatically?

Maybe I did not use the word automatically? Is it possible to make rsync or sth work automatically daily (to pull/download the backups from the production server and then push/upload into the client's server) ?

What I mean is I do not have to interfere. I just set things up and it is supposed to work the way I want it.

A good example would be using the automysqlbackup script. If the script is located in cron, then the backups are done automatically without my interference. At the specified time everyday, the backups are made.

Is it possible to do the same with pulling and pushing the backups? AND ALSO making excel track these at the same time?

I had to clear my doubt. Thanks.

rsync can push or pull. Do it by hand to see it work. Then put it in cron. It can be automated in the same way as automysqlbackup or anything else.

If you really want a spreadsheet, a spreadsheet will easily import a comma delimited or tab delimited file. Decide what fields you want and then append a record to the file each time your script runs. Have a spreadsheet with a macro that reads that file in. Then your client/boss/whoever can open the spreadsheet and it will automatically (with the macro) import and format the file. You'll have to play with that and figure it out. It will depend, of course, on your spreadsheet and platform of choice at that end.

Yep, a csv file import is simplest. That's why I was asking exactly what you want.
You can certainly build a script to do the file moving, then create/append to the csv.
Put the script in cron (after testing) and away you go.
NB: in cron the default env/path is minimal, so it's recommended to use full/absolute paths to all cmds & files mentioned in the script.

You may find these useful
http://tldp.org/LDP/Bash-Beginners-G...tml/index.html
http://www.tldp.org/LDP/abs/html/
http://www.adminschoice.com/docs/cro...Crontab%20file

Hi,

I created the script with the rsync command below :

rsync -avz 7-h2.my00001.cserver.mygrid.asia:/backups/daily/myob/ /backups/myob

I then saved the script as pull.sh and made it executable and located it in the /sbin directory.

After that I created a cronjob for that script in using crontab -e :

I don't get any results (the backups are not pulled into my server, and there is no output in the log file.

So I assume there must be some syntax problem with the script. Pls help me with this as I do not know what else to write in the script for it to work.

Take it one step at a time to figure out what's up. In post #7, you said it works. So, you had an rsync command that you executed by hand that worked. Presumably, that is what you put in your script. Is the script executable? Did you try just running it by hand? Are you sure there were changes on the remote server to copy when the script ran? The crontab entry looks alright, and, if you edited it using `crontab -e` it would have complained if there were format errors. Yours is set to execute at 6:45pm every day. If the cron job generates error output, it should also generate an email to the user the cron job is running as. If you aren't getting email, running it by hand would show you any errors.

Also see my note in post #11 about using the full path to cmds in a cron script. cron likely can't find the rsync cmd.

I've done some research, and am wondering, is this a correct reference material ?

Using Rsync and SSH

Do I really have to setup the ssh key and use a port number and use ssh with rsync for it to happen?

Previously, the script that I used in cron was :

It did not work. (The backup files were not pulled to my local server and there was no output in the output.txt)