Simple off-site mysql and website backup

Here are two scripts I wrote that I needed to easily backup databases and some websites.

Having the fact that I use SELinux .. with a custom data dir. I needed to this on my server:

yum install rssh
mkdir /backup/.ssh
cd /backup/.ssh
ssh-keygen -t rsa -f ./backup
cat backup.pub >authorized_keys
sed -i 's/#allowsftp/allowsftp/g' /etc/rssh.conf
adduser -m backup -s /usr/bin/rssh
semanage fcontext -at user_home_dir_t /backup/
semanage fcontext -at ssh_home_t /backup/.ssh/
semanage fcontext -at ssh_home_t /backup/.ssh/authorized_keys
restorecon -Rv /backup

You just need to get /backup/.ssh/backup private key file to the servers (make sure it’s chmod 0600 on the clients too ) you want to backup from so they can use it to connect to this server.

I’ve put the following script on my mysql server

mkdir -p /root/scripts
cat >/root/scripts/backup.sh<<_EOF_
#!/bin/bash
 
USER="root"
PASSWORD='l33tP4ssw0rd'
HOST="localhost"
OUTPUT="/backup"
PORT=5522
KEY="/root/.ssh/backup"
DEST="backup@192.168.1.1:"
 
mkdir "${OUTPUT}"
databases=$(mysql --host=${HOST} --user=${USER} --password=${PASSWORD} --skip-column-names -s -N -e "SHOW DATABASES;")
 
for db in $databases; do
        if [[ "$db" == "information_schema" ]] ; then
                continue
        fi
        if [[ "$db" == "performance_schema" ]] ; then 
                continue
        fi
        if [[ "$db" != _* ]] ; then
                file=sql_$db.`date +%Y%m%d_%s`.sql.gz
                mysqldump --force --opt --host=${HOST} --user=$USER --password=${PASSWORD} --databases $db | gzip > $OUTPUT/${file}
                scp -oPort=${PORT} -i ${KEY} $OUTPUT/${file} ${DEST}
                rm -rf "${OUTPUT}/*"
        fi
done
_EOF_
 
chmod +x /root/scripts/backup.sh
echo '0 2 * * * root nice /root/scripts/backup.sh >/dev/null 2>&1' >> /etc/crontab

I’ve put the following script on my web server, feel free to adapt.

mkdir -p /root/scripts
cat >/root/scripts/backup.sh<<_EOF_
#!/bin/bash
 
OUTPUT="/backup"
SOURCE="/var/www/"
PORT=5522
KEY="/root/.ssh/backup"
DEST="backup@192.168.1.1:"
 
mkdir "${OUTPUT}"
 
for site in $(ls "${SOURCE}" | grep -Ev '(cgi-bin|html)')
do
        file=site_$site.`date +%Y%m%d_%s`.tar.gz
        tar -czf $OUTPUT/${file} -C /var/www ${site}
        scp -oPort=${PORT} -i ${KEY} $OUTPUT/${file} ${DEST}
        rm -rf "${OUTPUT}/*"
done
chmod +x /root/scripts/backup.sh
echo '0 2 * * * root nice /root/scripts/backup.sh >/dev/null 2>&1' >> /etc/crontab

You should probably do a scp connection to the server first so you can accept the newly learned key for the client.

Leave a Reply

Your email address will not be published. Required fields are marked *