Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I am using rsnapshot and my config is as follows:

config_version  1.2
snapshot_root   /home/user/.snapshots/
cmd_cp  /bin/cp
cmd_rm  /bin/rm
cmd_rsync       /usr/bin/rsync
cmd_ssh /usr/bin/ssh
cmd_logger      /usr/bin/logger
cmd_du  /usr/bin/du
cmd_rsnapshot_diff      /usr/bin/rsnapshot-diff
interval        hourly  24
interval        daily   7
interval        weekly  4
verbose 2
loglevel        3
logfile /home/user/rsnapshot.log
lockfile        /home/user/rsnapshot.pid
backup  root@website.com:/home/user/ website/
backup_script   root@website.com:/home/user/backup_mysql.sh website/mysql/

my sql backup file: (purposely left stuff out)

### Setup dump directory ###
BAKRSNROOT=/.snapshots/tmp

#####################################
### ----[ No Editing below ]------###
#####################################
### Default time format ###
TIME_FORMAT='%H_%M_%S%P'

### Make a backup ###
backup_mysql_rsnapshot(){
        local DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
        local db="";
        [ ! -d $BAKRSNROOT ] && ${MKDIR} -p $BAKRSNROOT
        ${RM} -f $BAKRSNROOT/* >/dev/null 2>&1
#       [ $VERBOSE -eq 1 ] && echo "*** Dumping MySQL Database ***"
#       [ $VERBOSE -eq 1 ] && echo -n "Database> "
        for db in $DBS
        do
                local tTime=$(date +"${TIME_FORMAT}")
                local FILE="${BAKRSNROOT}/${db}.${tTime}.gz"
#               [ $VERBOSE -eq 1 ] && echo -n "$db.."
                ${MYSQLDUMP} --single-transaction -u ${MUSER} -h ${MHOST} -p${MPASS} $db | ${GZIP} -9 > $FILE
        done
#               [ $VERBOSE -eq 1 ] && echo ""
#               [ $VERBOSE -eq 1 ] && echo "*** Backup done [ files wrote to $BAKRSNROOT] ***"
}

### Die on demand with message ###
die(){
        echo "$@"
        exit 999
}

### Make sure bins exists.. else die
verify_bins(){
        [ ! -x $GZIP ] && die "File $GZIP does not exists. Make sure correct path is set in $0."
        [ ! -x $MYSQL ] && die "File $MYSQL does not exists. Make sure correct path is set in $0."
        [ ! -x $MYSQLDUMP ] && die "File $MYSQLDUMP does not exists. Make sure correct path is set in $0."
        [ ! -x $RM ] && die "File $RM does not exists. Make sure correct path is set in $0."
        [ ! -x $MKDIR ] && die "File $MKDIR does not exists. Make sure correct path is set in $0."
        [ ! -x $MYSQLADMIN ] && die "File $MYSQLADMIN does not exists. Make sure correct path is set in $0."
        [ ! -x $GREP ] && die "File $GREP does not exists. Make sure correct path is set in $0."
}

### Make sure we can connect to server ... else die
verify_mysql_connection(){
        $MYSQLADMIN  -u $MUSER -h $MHOST -p$MPASS ping | $GREP 'alive'>/dev/null
        [ $? -eq 0 ] || die "Error: Cannot connect to MySQL Server. Make sure username and password are set correctly in $0"
}

### main ####
verify_bins
verify_mysql_connection
backup_mysql_rsnapshot

How do I remotely execute that .sh file to grab the database and pull it back?

EDIT: ERROR:

 rsnapshot hourly
----------------------------------------------------------------------------
rsnapshot encountered an error! The program was invoked with these options:
/usr/local/bin/rsnapshot hourly
----------------------------------------------------------------------------
ERROR: /usr/local/etc/rsnapshot.conf on line 18:
ERROR: backup_script \
         root@website.com:/home/user/backup_mysql.sh" \
     is not executable or can't be found. Please use an absolute \
     path.
ERROR: ---------------------------------------------------------------------
ERROR: Errors were found in /usr/local/etc/rsnapshot.conf,
ERROR: rsnapshot can not continue. If you think an entry looks right, make
ERROR: sure you don't have spaces where only tabs should be.

the file is chmod 777 so its not hidden

I tried something like this but im not getting anything copied/pulled over.

backup_script  /usr/bin/ssh root@example.com 'mysqldump -u root --all-databases | gzip --rsyncable > ~/all.sql.gz'    unused2

tried:

backup_script /usr/bin/ssh user@website.com "/home/user/backup.script"  whatever/

it worked but failed to copy files over.

EDIT:

backup_script   /usr/bin/ssh root@remote.com "/home/user/backup_mysql.sh"   blank/
backup_script   /usr/bin/scp -r root@remote.com:/.rsnapshots/tmp/ /.rsnapshots/tmp/       user/mysql/

get:

usage: scp [-1246BCpqrv] [-c cipher] [-F ssh_config] [-i identity_file]
           [-l limit] [-o ssh_option] [-P port] [-S program]
           [[user@]host1:]file1 ... [[user@]host2:]file2
----------------------------------------------------------------------------
rsnapshot encountered an error! The program was invoked with these options:
/usr/local/bin/rsnapshot hourly
----------------------------------------------------------------------------
ERROR: backup_script /usr/bin/scp -r root@remote.com:/.rsnapshots/tmp/ returned 1
WARNING: Rolling back "user/mysql/"
share|improve this question
    
Don't forget rsnapshot needs tabs between "fields" in the config. Is the whitespace before website/mysql/ (on the backup_script line) definitely a tab? –  fukawi2 Dec 21 '12 at 2:29
    
Yea, that was the first problem. I've edited the post with new info –  Tiffany Walker Dec 21 '12 at 2:35
    
And running that command manually works OK? –  fukawi2 Dec 21 '12 at 3:19
    
The .sh file works fine locally when using rsnapshot. However, not sure how to access it remotely and use it. –  Tiffany Walker Dec 21 '12 at 3:24
    
Sorry, I meant the backup_script part: /usr/bin/ssh root@example.com 'mysqldump -u root --all-databases | gzip --rsyncable > ~/all.sql.gz' Your quoting is wrong I think; you want the closing ' before the redirect so the data comes back to the local machine and is written to a local file. At the moment, "all.sql.gz" is being written to disk by the shell on the remote SSH session. –  fukawi2 Dec 21 '12 at 4:48

1 Answer 1

up vote 3 down vote accepted

First of all you should move the backup script to the remote system. Bear in mind that the file is being executed on the remote system here, not on the local one (the one that runs rsnapshot). Then once the program is executed on the remote system you should move the files back to the local system.

Something like this should work :

backup_script /usr/bin/ssh user@website.com "/home/user/backup.script"  whatever/
backup_script /usr/bin/scp -r user@website.com:/home/user/your_data/ /.your_snapshot_root/rsnapshots/tmp/ target_dir_to_store_backups/
share|improve this answer
    
I did that top one. It worked. The script is on the remote server that rsnapshot is connecting too. It dumped the mysql however it did not copy them over.. hm.. –  Tiffany Walker Dec 21 '12 at 18:33
    
Have you checked where are the files being created on the remote system ? –  drcelus Dec 21 '12 at 18:57
    
Yea, under BAKRSNROOT=/.snapshots/tmp. - IT works great locally, because the files get grabbed and then deleted. But from remote location they get dumped and never grabbed or deleted. –  Tiffany Walker Dec 21 '12 at 19:35
    
That's why on the second line of my script it gets copied from the remote system to the local one with scp. –  drcelus Dec 21 '12 at 20:25
    
Oh, my bad. I thought they were two ways of doing it. facepalm. Does the last line finish the job and then remove the files after? –  Tiffany Walker Dec 21 '12 at 20:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.