LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 12-03-2009, 11:00 PM   #1
anaigini45
Member
 
Registered: Nov 2009
Posts: 43

Rep: Reputation: 15
Pulling database backups


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.

Last edited by anaigini45; 12-03-2009 at 11:01 PM.
 
Old 12-03-2009, 11:04 PM   #2
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,066
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
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
 
Old 12-03-2009, 11:28 PM   #3
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,241

Rep: Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325
You can indeed use rsync. Alternately, you can use scp with authorised keys to avoid it prompting for a passwd.
 
Old 12-04-2009, 08:03 AM   #4
choogendyk
Senior Member
 
Registered: Aug 2007
Location: Massachusetts, USA
Distribution: Solaris 9 & 10, Mac OS X, Ubuntu Server
Posts: 1,191

Rep: Reputation: 105Reputation: 105
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.
 
Old 12-06-2009, 11:36 PM   #5
anaigini45
Member
 
Registered: Nov 2009
Posts: 43

Original Poster
Rep: Reputation: 15
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?

Last edited by anaigini45; 12-06-2009 at 11:38 PM.
 
Old 12-07-2009, 02:03 AM   #6
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 23,066
Blog Entries: 11

Rep: Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910Reputation: 910
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.
 
Old 12-07-2009, 11:42 PM   #7
anaigini45
Member
 
Registered: Nov 2009
Posts: 43

Original Poster
Rep: Reputation: 15
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.
 
Old 12-08-2009, 01:23 AM   #8
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,241

Rep: Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325
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.
 
Old 12-08-2009, 02:51 AM   #9
anaigini45
Member
 
Registered: Nov 2009
Posts: 43

Original Poster
Rep: Reputation: 15
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.
 
Old 12-08-2009, 08:48 AM   #10
choogendyk
Senior Member
 
Registered: Aug 2007
Location: Massachusetts, USA
Distribution: Solaris 9 & 10, Mac OS X, Ubuntu Server
Posts: 1,191

Rep: Reputation: 105Reputation: 105
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.
 
Old 12-08-2009, 06:19 PM   #11
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,241

Rep: Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325
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
 
Old 12-10-2009, 06:05 AM   #12
anaigini45
Member
 
Registered: Nov 2009
Posts: 43

Original Poster
Rep: Reputation: 15
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 :

Quote:
45 18 * * * /sbin/pull.sh > /tmp/pulling.log
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.
 
Old 12-10-2009, 07:57 AM   #13
choogendyk
Senior Member
 
Registered: Aug 2007
Location: Massachusetts, USA
Distribution: Solaris 9 & 10, Mac OS X, Ubuntu Server
Posts: 1,191

Rep: Reputation: 105Reputation: 105
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.
 
Old 12-10-2009, 05:50 PM   #14
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Centos 6.8, Centos 5.10
Posts: 17,241

Rep: Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325Reputation: 2325
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.
 
Old 12-23-2009, 05:33 AM   #15
anaigini45
Member
 
Registered: Nov 2009
Posts: 43

Original Poster
Rep: Reputation: 15
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 :

Code:
#!/bin/sh
RSYNC=/usr/bin/rsync
LPATH=/backups/daily/myob/
RPATH=root@7-h2.my00001.cserver.mygrid.asia:/backups/daily/myob/


$RSYNC -avz  $RPATH $LPATH >> /tmp/output.txt
It did not work. (The backup files were not pulled to my local server and there was no output in the output.txt)
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Programming (database to database transaction) johncsl82 Programming 7 02-02-2007 09:20 AM
Scheduling MySQL database backups with date dependent filenames pepolez Linux - General 1 01-21-2006 10:45 AM
PopAccounts & Database Backups FantasticalLady Linux - Newbie 0 07-29-2003 03:10 AM
pulling data from mysql database zuessh Linux - Software 5 05-29-2003 10:13 AM
Pulling from CDROM MikeeX Linux - Newbie 8 03-14-2002 08:18 PM


All times are GMT -5. The time now is 12:40 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration