#!/bin/bash # # # purpose: create mysql backup for all db's # has to have nagios-plugins installed # has to have GPG key already generated PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin" INIT_CHECK () { if [ "`id -u`" -gt "0" ]; then echo "Run script as SuperUser (root), please..." fi } # Hostname MyHOST="localhost" MyUSER='' MyPASS='' # Servername SERVER="$HOSTNAME" # mysqldump options MYSQLDUMPOPTS="--opt" # Linux bin paths, change this if it can't be autodetected via which command MYSQL="$(which mysql)" MYSQLDUMP="$(which mysqldump)" CHOWN="$(which chown)" CHMOD="$(which chmod)" GZIP="$(which gzip)" # Backup Dest directory, change this if you have someother location DEST="/var/backups" # Main directory where backup will be stored MBD="$DEST/mysql" # Get data in dd-mm-yyyy format NOW="$(date +"%Y-%m-%d-%H-%M")" #Backup file-name FNAME="mysql-backup-encrypted-$NOW" # File to store current backup file FILE="" if [ $MyUSER ] && [ $MyPASS ]; then MyLOGINOPTS="-u $MyUSER -p$MyPASS" else MyLOGINOPTS="" fi # Store list of databases DBS="" #working dir TMP="/srv/tmp/$NOW" #Number of archives to keep n=7 # DO NOT BACKUP these databases IGGY="information_schema performance_schema" [ ! -d $MBD ] && mkdir -p $MBD || : [ ! -d $TMP ] && mkdir -p $TMP || : # Only root can access it! $CHOWN root.root -R $DEST $confdir $CHMOD 600 $DEST $confdir #remove old for l in $(find $MBD -maxdepth 1 -type f -name 'mysql-backup*tar.gz*' | sort | head -n -$n); do rm -f $l done #check for freespace /usr/lib64/nagios/plugins/check_disk -w 70 -c 90 / 1>/dev/null 2>/dev/null if [ $? -ne 0 ]; then echo "MySQL Backup Aborted! Disk Space low!" | sendmail -t [email protected] exit 100 fi # Get all database list first DBS="$($MYSQL -h $MyHOST $MyLOGINOPTS -Bse 'show databases')" for db in $DBS do skipdb=-1 if [ "$IGGY" != "" ]; then for i in $IGGY do [ "$db" == "$i" ] && skipdb=1 || : done fi if [ "$skipdb" == "-1" ] ; then FILE="$db.$SERVER.$NOW.sql" # do all inone job in pipe, # connect to mysql using mysqldump for select mysql database # and pipe it out to gz file in backup dir :) $MYSQLDUMP -h $MyHOST $MyLOGINOPTS $MYSQLDUMPOPTS $db | gpg --output $TMP/$FILE.gpg --encrypt --recipient [email protected] 1>/dev/null fi done tar czvf $MBD/$FNAME.tar.gz $TMP 1> /dev/null rm -r $TMP cp -p $MBD/$FNAME.tar.gz $MBD/weekly/$(date +%W)/full-mysql-encrypted.tar.gz scp -i "/root/.ssh/sshkey" $MBD/$FNAME.tar.gz [email protected]:/var/backups/$HOSTNAME/latest-full-mysql-encrypted.tar.gz 2>/dev/null 1>/dev/null