3

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  [email protected]:/home/user/ website/
backup_script   [email protected]:/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 \
         [email protected]:/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 [email protected] 'mysqldump -u root --all-databases | gzip --rsyncable > ~/all.sql.gz'    unused2

tried:

backup_script /usr/bin/ssh [email protected] "/home/user/backup.script"  whatever/

it worked but failed to copy files over.

EDIT:

backup_script   /usr/bin/ssh [email protected] "/home/user/backup_mysql.sh"   blank/
backup_script   /usr/bin/scp -r [email protected]:/.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 [email protected]:/.rsnapshots/tmp/ returned 1
WARNING: Rolling back "user/mysql/"
6
  • 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, 2012 at 2:29
  • Yea, that was the first problem. I've edited the post with new info Dec 21, 2012 at 2:35
  • And running that command manually works OK?
    – fukawi2
    Dec 21, 2012 at 3:19
  • The .sh file works fine locally when using rsnapshot. However, not sure how to access it remotely and use it. Dec 21, 2012 at 3:24
  • Sorry, I meant the backup_script part: /usr/bin/ssh [email protected] '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, 2012 at 4:48

1 Answer 1

3

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 [email protected] "/home/user/backup.script"  whatever/
backup_script /usr/bin/scp -r [email protected]:/home/user/your_data/ /.your_snapshot_root/rsnapshots/tmp/ target_dir_to_store_backups/
6
  • 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.. Dec 21, 2012 at 18:33
  • Have you checked where are the files being created on the remote system ?
    – drcelus
    Dec 21, 2012 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. Dec 21, 2012 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, 2012 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? Dec 21, 2012 at 20:48

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .