Backup remote linux mysql and data

Posted on 17.08.2009 by admin

Whit little bash script you can backup remote linux server.
I wrote this script for backup my web server powered by ISPconfig 3.

Backup is started from local linux machine.
For this script ssh key authentication must be set.
This script will create sub directory with week number and sub directory for current date. eg:
/path/34/2009-08-17
On beginning of week (Monday) full directory backup will be made, and for rest days only increment (changed files) backup.
After backup is done, email is send with short log data.
Log files are also implement.
Script is run from local machine.
For directory backup I use local txt files with list of directories for backup and names how backup file will begin:

here is example of lista.txt file:

root /root
etc /etc
www /var/www
usr_ispconfig /usr/local/ispconfig
maildir /var/vmail

variables which you must verify/change:


path=/disk2/pub/Backup
dbuser_x=root #mysql root password
dbpassword_x=mysqlrootpass #mysql password
sshuser="remote_username_root" # remote ssh username
sshhostname="remote_hostname" # remote hostname
sshport="remote ssh port" # remote ssh port
remotepath=/backup # remote path where will be temporary files
lista=/home/franz/bin/lista.txt #path and filename of txt file where are stored remote directory paths

This scrip can be added to cron for daily execution:
2 2 * * * franz /home/franz/bin/backup_web.sh

Here is the script:


#!/bin/bash
#SSH remote backup script
#by Franjo Posavec
#define local path
#This path must exist
path=/disk2/pub/Backup
#define logfile
logfile=$path/log/ISPconfig3-backup-`date +'%F'`
#
#define week number
week_x=`date +'%V'`
#
#define locapl final path path/weeknumber/date
dirname=$path/$week_x/`date +'%F'`
#
#define day of week number
DF_X=`date +'%u'`
#
#define curent date and time: 2009-08-16_23-01
datum_x=`date +'%F_%I-%M'`
#
#define remote database access
dbuser_x=mysqlroot
dbpassword_x=mysqlrootpass
#define remote ssh access
sshuser="remote_username_root"
sshhostname="remote_hostname"
sshport="remote ssh port"
#
#define remote backup path, must exist on remote server and have RW for user
remotepath=/backup
#
#define list of directories for backup in format:
#list is on local machine
#name /path
lista=/home/franz/bin/lista.txt
#
#define temp file for email text
zamail=/tmp/zamail$datum_x.txt
email="franz@franz-net.info"
#
function check_log {
if [ -d $path/log ]; then
log "Log directory exist"
else
mkdir $path/log
log "Log directory creted"
fi
}
#
function log {
curenttime_x=`date +"+%Y-%m-%d %H:%M:%S"`
echo "$curenttime_x - $1 $2 $3 $4 $5 $6 $7 $8 $9" >> $logfile
}
#
function check_dirs {
log "Cheching local directory for saving files..."
if [ -d $path/$week_x ]; then
log "Directory week direcotry exist: $path/$week_x"
else
mkdir $path/$week_x
log "Week directory created: $path/$week_x"
fi
if [ -d $dirname ]; then
rm -r $dirname
mkdir $dirname
log "Destination directory recreated: $dirname"
else
mkdir $dirname
log "Destination directory created: $dirname"
fi
}
#########
# START #
#########
#
log "Starting daily backup"
echo "`date "+%Y-%m-%d %H:%M:%S"` - Starting daily backup" > $zamail
#cheching local path
check_log
check_dirs
log "$dirname"
#
#### define standard commands
SSH="ssh $sshuser@$sshhostname -p $sshport"
SCP="scp -P $sshport $sshuser@$sshhostname:$remotepath"
#
#command for clear all data from remote directory
FIND="find $remotepath -name \*.bz2 -type f -exec rm {} \;"
#
#find all databases for backup
MYSQLDATABASE="mysql -u$dbuser_x -p$dbpassword_x -Bse 'show databases'"
database_x="$($SSH $MYSQLDATABASE)"
#
#clear remote directory
log "Deleting files from remote directory"
$SSH $FIND
log "Deleting OK"
#
echo "All data will be saved in: $dirname. Today is $DF_X day of week" >> $zamail
log "All data will be saved in: $dirname. Today is $DF_X day of week"
#
### Starting database dumps and copy to local host
for i in $database_x; do
log "Starting mysqldump $i"
MYSQLDUMP="mysqldump -u$dbuser_x -p$dbpassword_x $i | bzip2 > $remotepath/mysql-$i-$datum_x.sql.bz2"
# $SSH $MYSQLDUMP
log "Dump OK. Starting SCP"
# $SCP/mysql-$i-$datum_x.sql.bz2 $dirname/
echo "`date "+%Y-%m-%d %H:%M:%S"` - mysql: $i" >> $zamail
log "SCP done. $i database saved OK!!!!"
done
#
#prepare for directory dump
IFS=$'\n'
for i in `cat $lista`; do
XX=`echo $i | awk '{print $1}'`
YX=`echo $i | awk '{print $2}'`
unset IFS
if [ $DF_X == 1 ]; then
log "Deleting TAR increment files...."
BRISI="rm /backup/$XX.increment"
$SSH $BRISI
FileName="dirbackup_$XX-${datum_x}_Full.tar.bz2"
TAR="tar --exclude='mysqld.sock' -g /backup/$XX.increment -cjPf $remotepath/$FileName $YX"
else
FileName="dirbackup_$XX-${datum_x}_Increment_$DF_X.tar.bz2"
TAR="tar --exclude='mysqld.sock' -g /backup/$XX.increment -cjPf $remotepath/$FileName $YX"
fi
log "Starting to prepare files for $XX"
$SSH $TAR
log "Done, starting to copy...."
$SCP/$FileName $dirname/
log "Done. Directory copied."
echo "`date "+%Y-%m-%d %H:%M:%S"` - Directory: $FileName -> $dirname" >> $zamail
done
log "Done copy.."
echo "`date "+%Y-%m-%d %H:%M:%S"` - All Done." >> $zamail
log "send mail to $email:"
cat $zamail >> $logfile
mail -s"Dnevni backup servera `date +'%F'`" "$email" < $zamail
log "All Done."

This entry was posted in ISPConfig 3, Linux tips. Bookmark the permalink.

21 Responses to Backup remote linux mysql and data

  1. Pingback: Update: ISPConfig3 BAckup script – local server « IT Podrška

  2. ben says:
    12.02.2010 at 09:32

    Hi,

    I have tried running the script as root, but getting permission denied on some folders. Attached log.

    =============================================================================
    bash: mysql: command not found
    tar: /backup/root.increment: Cannot open: No such file or directory
    tar: /root: Cannot savedir: Permission denied
    tar: /root: Cannot savedir: Permission denied
    tar: Error exit delayed from previous errors
    tar: /backup/etc.increment: Cannot open: No such file or directory
    tar: /etc/BackupPC: Cannot savedir: Permission denied
    tar: /etc/audispd: Cannot savedir: Permission denied
    tar: /etc/audit: Cannot savedir: Permission denied
    tar: /etc/cron.d: Cannot savedir: Permission denied
    tar: /etc/cups/ssl: Cannot savedir: Permission denied
    tar: /etc/lvm/archive: Cannot savedir: Permission denied
    tar: /etc/lvm/backup: Cannot savedir: Permission denied
    tar: /etc/lvm/cache: Cannot savedir: Permission denied
    tar: /etc/pki/CA: Cannot savedir: Permission denied
    tar: /etc/racoon/certs: Cannot savedir: Permission denied
    tar: /etc/selinux/targeted/modules/active: Cannot savedir: Permission denied
    tar: /etc/BackupPC: Cannot savedir: Permission denied
    tar: /etc/audispd: Cannot savedir: Permission denied
    tar: /etc/audit: Cannot savedir: Permission denied
    tar: /etc/cron.d: Cannot savedir: Permission denied
    tar: /etc/cups/ssl: Cannot savedir: Permission denied
    tar: /etc/lvm/archive: Cannot savedir: Permission denied
    tar: /etc/lvm/backup: Cannot savedir: Permission denied
    tar: /etc/lvm/cache: Cannot savedir: Permission denied
    tar: /etc/pki/CA: Cannot savedir: Permission denied
    tar: /etc/racoon/certs: Cannot savedir: Permission denied
    tar: /etc/selinux/targeted/modules/active: Cannot savedir: Permission denied
    tar: /etc/.pwd.lock: Cannot open: Permission denied
    tar: /etc/aliases.db: Cannot open: Permission denied
    tar: /etc/at.deny: Cannot open: Permission denied
    tar: /etc/autofs_ldap_auth.conf: Cannot open: Permission denied
    tar: /etc/gshadow: Cannot open: Permission denied
    tar: /etc/gshadow-: Cannot open: Permission denied
    tar: /etc/libaudit.conf: Cannot open: Permission denied
    tar: /etc/named.caching-nameserver.conf: Cannot open: Permission denied
    tar: /etc/named.caching-nameserver.conforg: Cannot open: Permission denied
    tar: /etc/named.rfc1912.zones: Cannot open: Permission denied
    tar: /etc/rndc.key: Cannot open: Permission denied
    tar: /etc/securetty: Cannot open: Permission denied
    tar: /etc/shadow: Cannot open: Permission denied
    tar: /etc/shadow-: Cannot open: Permission denied
    tar: /etc/sudoers: Cannot open: Permission denied
    tar: /etc/cups/classes.conf: Cannot open: Permission denied
    tar: /etc/cups/cupsd.conf: Cannot open: Permission denied
    tar: /etc/cups/cupsd.conf.default: Cannot open: Permission denied
    tar: /etc/cups/printers.conf: Cannot open: Permission denied
    tar: /etc/default/useradd: Cannot open: Permission denied
    tar: /etc/logrotate.d/named: Cannot open: Permission denied
    tar: /etc/mail/access.db: Cannot open: Permission denied
    tar: /etc/mail/domaintable.db: Cannot open: Permission denied
    tar: /etc/mail/mailertable.db: Cannot open: Permission denied
    tar: /etc/mail/virtusertable.db: Cannot open: Permission denied
    tar: /etc/mgetty+sendfax/dialin.config: Cannot open: Permission denied
    tar: /etc/mgetty+sendfax/login.config: Cannot open: Permission denied
    tar: /etc/mgetty+sendfax/mgetty.config: Cannot open: Permission denied
    tar: /etc/ntp/keys: Cannot open: Permission denied
    tar: /etc/pki/tls/certs/localhost.crt: Cannot open: Permission denied
    tar: /etc/pki/tls/private/localhost.key: Cannot open: Permission denied
    tar: /etc/ppp/chap-secrets: Cannot open: Permission denied
    tar: /etc/ppp/pap-secrets: Cannot open: Permission denied
    tar: /etc/racoon/psk.txt: Cannot open: Permission denied
    tar: /etc/racoon/racoon.conf: Cannot open: Permission denied
    tar: /etc/security/opasswd: Cannot open: Permission denied
    tar: /etc/selinux/restorecond.conf: Cannot open: Permission denied
    tar: /etc/squid/squid.conf: Cannot open: Permission denied
    tar: /etc/ssh/moduli: Cannot open: Permission denied
    tar: /etc/ssh/ssh_host_dsa_key: Cannot open: Permission denied
    tar: /etc/ssh/ssh_host_key: Cannot open: Permission denied
    tar: /etc/ssh/ssh_host_rsa_key: Cannot open: Permission denied
    tar: /etc/ssh/sshd_config: Cannot open: Permission denied
    tar: /etc/sysconfig/auditd: Cannot open: Permission denied
    tar: /etc/sysconfig/ip6tables-config: Cannot open: Permission denied
    tar: /etc/sysconfig/named: Cannot open: Permission denied
    tar: /etc/vpnc/default.conf: Cannot open: Permission denied
    tar: /etc/vsftpd/ftpusers: Cannot open: Permission denied
    tar: /etc/vsftpd/user_list: Cannot open: Permission denied
    tar: /etc/vsftpd/vsftpd.conf: Cannot open: Permission denied
    tar: /etc/wpa_supplicant/wpa_supplicant.conf: Cannot open: Permission denied
    tar: Error exit delayed from previous errors
    tar: /backup/www.increment: Cannot open: No such file or directory
    tar: Error exit delayed from previous errors
    tar: /backup/usr_ispconfig.increment: Cannot open: No such file or directory
    tar: /usr/local/ispconfig: Cannot stat: No such file or directory
    tar: /usr/local/ispconfig: Cannot stat: No such file or directory
    tar: Error exit delayed from previous errors
    tar: /backup/maildir.increment: Cannot open: No such file or directory
    tar: /var/vmail: Cannot stat: No such file or directory
    tar: /var/vmail: Cannot stat: No such file or directory
    tar: Error exit delayed from previous errors
    =================================================================================

    Thanks
    Ben

  3. Yahoouj says:
    23.02.2010 at 10:26

    Really good work about this website was done. Keep trying more – thanks!

  4. Sonnenschirme says:
    16.03.2010 at 14:51

    Thanks for sharing!

  5. Optotlyenvent says:
    25.03.2010 at 11:01

    i easily enjoy your own posting way, very exciting,
    don’t give up and also keep penning as it just good worth to read it,
    looking forward to see much of your web content, enjoy your day 🙂

  6. admin says:
    20.04.2010 at 15:14

    did you create /backup directory?

  7. Rudolf Pietersma says:
    20.04.2010 at 15:43

    Hi there,
    It doesn’t copy the database dumps to the local computer?
    It the log it sais:

    2010-04-20 15:42:03 – Starting daily backup All data will be saved in: /remotebackup/16/2010-04-20. Today is 2 day of week 2010-04-20 15:42:04 – mysql: information_schema 2010-04-20 15:42:04 – mysql: 1jisko 2010-04-20 15:42:04 – mysql: 1rp 2010-04-20 15:42:04 – mysql: 1rudolftest 2010-04-20 15:42:04 – mysql: asu 2010-04-20 15:42:04 – mysql: bigfun 2010-04-20 15:42:04 – mysql: dbispconfig 2010-04-20 15:42:04 – mysql: dolphin 2010-04-20 15:42:04 – mysql: elgg 2010-04-20 15:42:04 – mysql: jisko 2010-04-20 15:42:04 – mysql: mysql 2010-04-20 15:42:04 – mysql: noserub 2010-04-20 15:42:04 – mysql: oriams 2010-04-20 15:42:04 – mysql: phpmyadmin 2010-04-20 15:42:04 – mysql: prikbordphpbb3 2010-04-20 15:42:04 – mysql: priscilafantin 2010-04-20 15:42:04 – mysql: revou 2010-04-20 15:42:04 – mysql: roesalkaphpbb3 2010-04-20 15:42:04 – mysql: rp 2010-04-20 15:42:04 – mysql: rupi 2010-04-20 15:42:04 – mysql: share 2010-04-20 15:42:04 – mysql: shorty 2010-04-20 15:42:04 – mysql: skoena 2010-04-20 15:42:04 – mysql: stephan 2010-04-20 15:42:04 – mysql: test 2010-04-20 15:42:04 – mysql: testjisko 2010-04-20 15:42:04 – mysql: testrudolf 2010-04-20 15:42:04 – mysql: textpattern 2010-04-20 15:42:04 – mysql: twitrpcom 2010-04-20 15:42:04 – mysql: url 2010-04-20 15:42:04 – mysql: urli 2010-04-20 15:42:05 – mysql: urliyourls 2010-04-20 15:42:05 – mysql: whmcs 2010-04-20 15:42:05 – mysql: whmcstest 2010-04-20 15:42:05 – mysql: wordpress 2010-04-20 15:42:06 – Directory: dirbackup_root-2010-04-20_03-42_Increment_2.tar.bz2 -> /remotebackup/16/2010-04-20 2010-04-20 15:42:06 – All Done.

  8. admin says:
    20.04.2010 at 16:14

    does script create sql dumps on server on directiry /backup?

  9. Rudolf Pietersma says:
    20.04.2010 at 22:20

    No the /backup dir is empty.
    I use /remotebackup in the script, also this dir is empty.

    #This path must exist
    path=/remotebackup

    #define remote backup path, must exist on remote server and have RW for user
    remotepath=/remotebackup

  10. Rudolf Pietersma says:
    21.04.2010 at 13:30

    Franz,
    Any other ideas?

  11. admin says:
    21.04.2010 at 14:32

    do you have bzip2 installed on system?

  12. Rudolf Pietersma says:
    21.04.2010 at 14:49

    Yes there is.
    “bzip2 is already the newest version”

  13. Rudolf Pietersma says:
    21.04.2010 at 18:55

    Franz,
    Any other suggestions?
    You don’t use howtoforge.com anymore?
    Rudolf.

  14. admin says:
    21.04.2010 at 22:36

    very strange. try to run:
    mysqldump -uusername -ppassword 1jisko > /remotebackup/test.sql

    does it create sql dump?

  15. Rudolf Pietersma says:
    22.04.2010 at 09:57

    Franz,
    I run this script from the local computer.

    On the remote computer I have your other script. http://blog.it-podrska.info/2009/09/update-ispconfig3-backup-script-local-server/ and this script works well. So sql dumps work well as well. But I want to use the remote version now to have my backups directly at home.

  17. Rudolf Pietersma says:
    22.04.2010 at 11:53

    Yes I can connect from home to remote server. I can also run the script from local to remote and it does get this

    root@backup:/remotebackup/16/2010-04-20# ls
    dirbackup_root-2010-04-20_03-52_Increment_2.tar.bz2

    Only the sql dumps don’t appear.

  18. Rudolf Pietersma says:
    22.04.2010 at 16:05

    Are you not on howtoforge.com anymore?

  19. Rudolf Pietersma says:
    23.04.2010 at 13:08

    Help me a little more please..

  20. admin says:
    25.04.2010 at 00:19

    can you send me PM on howtoforge?

  21. Pingback: Webmaster

Leave a Reply

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