backups met rsnapshot

backupper is master
backuppee is slave

VELDEN WORDEN GESCHEIDEN MET TABS (NIET MET SPATIES)

mysql databases:

create user 'backup' with permissions SELECT, SHOW VIEW, EVENT and LOCK TABLES

put the password of this user in /root/.my.cnf (make sure the file is not readable by others)

-------------------START /root/.my.cnf -----
[client]
password=LpQfYxgd32pyWeL2
------------------------------END

create
-------------------- START /root/bin/mysqldump-all-gzip-stdout.sh ----
#!/bin/sh
mysqldump -u backup --all-databases --skip-opt -E | gzip -c
------------

when this script runs, it takes the password from the .my.cnf file

create
--------------------- START /root/bin/validate-rsync ----
#!/bin/sh

case "$SSH_ORIGINAL_COMMAND" in
*\&*)
echo "Rejected"
;;
*\(*)
echo "Rejected"
;;
*\{*)
echo "Rejected"
;;
*\;*)
echo "Rejected"
;;
*\<*)
echo "Rejected"
;;
*\`*)
echo "Rejected"
;;
*\|*)
echo "Rejected"
;;
rsync\ --server*|/root/bin/mysqldump-all-gzip-stdout.sh)
$SSH_ORIGINAL_COMMAND
;;
*)
echo "Rejected"
;;
esac
-----------------

this file is called by ssh to validate that backup script only backups and is not doing other things


create ssh key on backup master (store it before the rsync root eg.
/data/backup/master-rsync-key) and put the corresponding public key
in /root/.ssh/authorized_keys as such
--------- line in authorized_keys
command="/root/bin/validate_rsync" ssh-rsa AAAAB3NzaC.....NFib3e0P9Q== root@master
------------
this ensures that if the backup scripts tries to login in with this key, it can only run the
commands specified by validate_rsync

add this to /home/root/.ssh/config
-------
Host slave_backup
        Hostname slave
        IdentityFile /data/backup/master-rsync-key
------

exclude var/lib/mysql in the regular stanza in rsnapshot.conf
---
backup  root@slave_backup:/             slave/  exclude=var/lib/mysql
---

and add a new stanza to rsnapshot.conf
---
backup_script   /root/bin/slave-mysqldump.sh    slave/var/lib/mysql/
---

and also create /root/bin/slave-mysqldump.sh
-----
#!/bin/sh
ssh root@slave_backup /root/bin/mysqldump-all-gzip-stdout.sh > slave.sql.gz
----