rsnapshot - not correctly archiving mysql databases
- by Tiffany Walker
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.
