My rsnapshot configuration:

snapshot_root   /.snapshots/
backup  /home/user          localhost/
backup_script   /usr/local/backup_mysql.sh      localhost/mysql/

Using this file:

NOW=$(date +"%m-%d-%Y") # mm-dd-yyyy format
FILE=""                 # used in a loop

### Server Setup ###
#* MySQL login user name *#
MUSER="root"

#* MySQL login PASSWORD name *#
MPASS="YOUR-PASSWORD"

#* MySQL login HOST name *#
MHOST="127.0.0.1"

#* MySQL binaries *#
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"

# get all database listing
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"

# start to dump database one by one
for db in $DBS
do
FILE=$BAK/mysql-$db.$NOW-$(date +"%T").gz
# gzip compression for each backup file
$MYSQLDUMP --single-transaction -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done

It dumps the databases under /

I then tried with the following: http://bash.cyberciti.biz/backup/rsnapshot-remote-mysql-backup-shell-script/

I got:

rsnapshot hourly
----------------------------------------------------------------------------
rsnapshot encountered an error! The program was invoked with these options:
/usr/bin/rsnapshot hourly
----------------------------------------------------------------------------
ERROR: backup_script /usr/local/backup_mysql.sh returned 1
WARNING: Rolling back "localhost/mysql/"


ls -la /.snapshots/hourly.0/localhost/mysql
total 8
drwxr-xr-x 2 root root 4096 Nov 23 17:43 ./
drwxr-xr-x 4 root root 4096 Nov 23 18:20 ../

What exactly am I doing wrong?

EDIT:

# /usr/local/backup_mysql.sh
*** Dumping MySQL Database ***
Database> information_schema..cphulkd..eximstats..horde..leechprotect..logaholicDB_ns1..modsec..mysql..performance_schema..roundcube..test..
*** Backup done [ files wrote to /.snapshots/tmp/mysql] ***
root@ns1 [~]# ls -la /.snapshots/tmp/mysql
total 8040
drwxr-xr-x 2 root root    4096 Nov 23 18:41 ./
drwxr-xr-x 3 root root    4096 Nov 23 18:41 ../
-rw-r--r-- 1 root root    1409 Nov 23 18:41 cphulkd.18_41_45pm.gz
-rw-r--r-- 1 root root  113522 Nov 23 18:41 eximstats.18_41_45pm.gz
-rw-r--r-- 1 root root    4583 Nov 23 18:41 horde.18_41_45pm.gz
-rw-r--r-- 1 root root   71757 Nov 23 18:41 information_schema.18_41_45pm.gz
-rw-r--r-- 1 root root     692 Nov 23 18:41 leechprotect.18_41_45pm.gz
-rw-r--r-- 1 root root    2603 Nov 23 18:41 logaholicDB_ns1.18_41_45pm.gz
-rw-r--r-- 1 root root     745 Nov 23 18:41 modsec.18_41_45pm.gz
-rw-r--r-- 1 root root  138928 Nov 23 18:41 mysql.18_41_45pm.gz
-rw-r--r-- 1 root root    1831 Nov 23 18:41 performance_schema.18_41_45pm.gz
-rw-r--r-- 1 root root    3610 Nov 23 18:41 roundcube.18_41_45pm.gz
-rw-r--r-- 1 root root     436 Nov 23 18:41 test.18_41_47pm.gz

MySQL Backup seems fine.