Komplet database-rotering imellem 2 eller flere servere i sh

Dette forum bruges på EGET ANSVAR til at lege med scripts og andre ting med risiko for at beskadige sit eget og andres systemer.
Brugeravatar
NickyThomassen
Admin
Indlæg: 3553
Tilmeldt: 5. mar 2010, 19:58
IRC nickname: nicky
Geografisk sted: 192.168.20.42

Komplet database-rotering imellem 2 eller flere servere i sh

Indlægaf NickyThomassen » 17. nov 2012, 14:58

Hvis man har en mailserver med tilhørende backup MX, så har backup MX'en brug for at have en kopi af mailserverens database, for ellers ved den ikke hvilket mailadresser den skal modtage post for. En løsning ville være MySQL's databasereplikering, men til min bedste viden kræver systemet en manuel initialisering efter en genstart af serveren, og det duer ikke for mig.

Så i sh har jeg i ét script skrevet det, som skal bruges af en master til at ligge databasen ud på et ftp-drev, og en eller flere slaver til at hente og integrere databasen. Selvom udgangspunktet er Postfix, så kan scriptet sagtens bruges til andre jobs også.

Kode: Vælg alt

#!/bin/bash
#
#
# Database backupscript between two or more Postfix-servers
# Made by mad hound nicky
#
# Please note that this entire script may fail silently if the ftp service is down for the master
# The reason is that eroor checking on the upload is not done
#
# A future solution would be to have the slave(s) report it if they fetch the same dump over and over again
#
# Exit code 10 means the program which could not be found (both)
# Exit code 11 is the misplacement of the MySQL dump (masters)
# Exit code 12 is presumeably when $MODE is set wrong (both)
# Exit code 13 indicates the ftp service was offline or nothing could be fetched (slaves)
# Exit code 14 means the MySQL dump could not be found for renaming after the local rotation (slaves)
# Exit code 15 is that the backup could not be found for integration (slaves)
#
# License = /dev/null
#
# The settings in this script needs to be identical between the servers
#
#
######################################################################################
# *** Start Settings ***
######################################################################################
#
#
# At what mode should this script run
# Standard = MODE="server" --- MODE="slave"
# CAUTION  -  If set wrong, the primary databasse may be deleted!
MODE="server"
#
# Set to anything but "always" or "failure" to deactivate sending mail
# Standard = MAIL_SEND="always" --- MAIL_SEND="failure"
MAIL_SEND="always"
#
# What's the mail of the server admin. Used in the body as a reply to, in case of problems
# Standard = SERVER_ADMIN_MAIL=""
SERVER_ADMIN_MAIL=""
#
# Who should recieve the mail  -  Multiple adresses --> "a@a.tld,b@b.tld"
# Standard = MAIL_ADDRESS="a@a.tld"
MAIL_ADDRESS=""
#
# And what should the subject be
# Standard = MAIL_SUBJECT="Status: $0 på $HOSTNAME d.$(date +%d.%m.%Y) kl.$(date +%k:%M)"
MAIL_SUBJECT="Status: $0 på $HOSTNAME d.$(date +%d.%m.%Y) kl.$(date +%k:%M)"
#
# Absolute path for the backup dir (with no trailing slash please)
# Note that directorys will be made if need be and is only needed if the script runs in master mode
# Standard = BACKUP_DIR="/home/backup/postfix_db"
# CAUTION  -  If set wrong, then contents of the folder may be deleted!
BACKUP_DIR="/home/backup/postfix_db"
#
# What user and password should be used by mysqldump
# Note, on servers only read is needed, on slaves both read and write is needed
# Standard = DUMP_USER="" --- DUMP_PASS=""
DUMP_USER=""
DUMP_PASS=""
#
# How many dumps should be stored and rotated on the server
# Be aware that if the number is reduced, then a manuel delete of the backups over the rotation number is needed
# Standard = ROTATE_DATABASE=7
ROTATE_DATABASE=3
#
# Servers only:
# And what should the dump of the database be called  -  Letters and underscore only please
# The name will be $DUMP_NAME.sql.gz.$ROTATE_DATABASE, and if changed, then old ones have to be deleted by hand
# Standard = DUMP_NAME="postfix_db"
DUMP_NAME="postfix"
#
# Servers only:
# What options should be used by mysqldump
# Standard = DUMP_OPTIONS="--add-drop-database --databases postfix"
DUMP_OPTIONS="--add-drop-database --databases postfix"
#
# Slaves only
# What is the name of the database that needs to be integrated
# Note that if the name of the database is mentioned in $DUMP_OPTIONS it have to be the same here
# Standard = DATABASE_NAME="postfix"
DATABASE_NAME="postfix"
#
# Servers only:
# How many dumps should be kept and rotatet on the FTP server
# Be aware that if the number is reduced, then a manuel delete of the backups over the rotation number is needed
# Standard = ROTATE_FTP=7
ROTATE_FTP=3
#
# These 3 needs to be filled out with the proper information about the FTP account
# Set host to hostname or public IP address, and fill in user and pass to something appropriate
# Standard = FTP_HOST="" --- FTP_USER="" --- FTP_PASS=""
FTP_HOST=""
FTP_USER=""
FTP_PASS=""
#
#
#
######################################################################################
# *** Settings End ***
# Changes below here is not needed for daily usage
######################################################################################
#
#
# We really need which and can't (nor won't we) go on without it
if [ -e "/usr/bin/which" ] ; then
   WHICH="/usr/bin/which"
else
   #
   # If mail is set we'll try and inform about this error in an email
   if [ "$MAIL_SEND" = "always" ] || [ "$MAIL_SEND" = "failure" ] ; then
      MAIL_BODY="Programmet which blev ikke fundet, men skal bruges. Afslutter..."
      echo $MAIL_BODY | /usr/bin/mail -s "$MAIL_SUBJECT" $MAIL_ADDRESS
   fi
   echo "which was not found, exiting"
   exit 10
fi
#
# We prefer mail but don't really need it, and only if set will we set it
if [ "$MAIL_SEND" = "always" ] || [ "$MAIL_SEND" = "failure" ] ; then
   #
   # This function will serve in case of errors to ease sending emails
   send_mail_failure() {
      MAIL_BODY="$1"
      echo $MAIL_BODY | /usr/bin/mail -s "$MAIL_SUBJECT" $MAIL_ADDRESS
      # Just in case $MAIL_MESSAGE exists we'll clean it up
      if [ -e "$MAIL_MESSAGE" ] ; then
         rm "$MAIL_MESSAGE"
      fi
   }
   #
   # Let's set mail, and check the result
   MAIL_BIN=$($WHICH mail)
   EXIT_STATUS="$?"
   if [ "$EXIT_STATUS" -ne "0" ] ; then
      if [ "$MAIL_SEND" = "always" ] ; then
      send_mail_failure "Programmet mail kunne ikke findes med fejl $EXIT_STATUS, så email vil kun blive lavet i tilfælde af fejl."
      MAIL_SEND="failure"
      fi
   fi
fi
#
# However, FTP is needed, and we'll halt the script if not found
FTP=$($WHICH ftp)
EXIT_STATUS="$?"
if [ "$EXIT_STATUS" -ne "0" ] ; then
   if [ "$MAIL_SEND" = "always" ] || [ "$MAIL_SEND" = "failure" ] ; then
      send_mail_failure "Programmet ftp kunne ikke findes med fejl $EXIT_STATUS, så backuppen KAN IKKE gennemføres! Afslutter..."
   fi
   echo "ftp was not found by which, exiting"
   exit "$EXIT_STATUS"
fi
#
# As with email, we'll make a ftp function which can take up to three parametres
ftp_con() {
echo   "open $FTP_HOST
   user $FTP_USER $FTP_PASS
   verbose
   $1 $2 $3
   close
   quit" | $FTP -n
}
#
# mysqldump is needed
MYSQLDUMP=$($WHICH mysqldump)
EXIT_STATUS="$?"
if [ "$EXIT_STATUS" -ne "0" ] ; then
   if [ "$MAIL_SEND" = "always" ] || [ "$MAIL_SEND" = "failure" ] ; then
      send_mail_failure "Programmet mysqldump kunne ikke findes med fejl $EXIT_STATUS, så backuppen KAN IKKE gennemføres! Afslutter..."
   fi
   echo "mysqldump was not found by which, exiting"
   exit "$EXIT_STATUS"
fi
#
# If need be we can now create the temp file needed to hold the body of the mail in the variable $MAIL_MESSAGE
if [ "$MAIL_SEND" = "always" ] ; then
   #
   # "date +%j" is no. of days in the year
   MAIL_MESSAGE="/tmp/Backup_Mail_Message.$(date +%j)"
   touch $MAIL_MESSAGE
   EXIT_STATUS="$?"
   if [ "$EXIT_STATUS" -ne "0" ] ; then
      send_mail_failure "Tempfilen til email'en kunne ikke oprettes med fejl $EXIT_STATUS, så email vil kun blive lavet i tilfælde af fejl."
      MAIL_SEND="failure"
   fi
fi
#
# We'll build a mail function to ease the sending of mail if need be
if [ "$MAIL_SEND" = "always" ] ; then
   send_mail_ok() {
      echo >> $MAIL_MESSAGE
      echo "$1" >> $MAIL_MESSAGE
      echo >> $MAIL_MESSAGE
      echo "---------------" >> $MAIL_MESSAGE
      echo "Der bør ikke svares direkte på denne mail, kontakt i stedet serverens administrator" >> $MAIL_MESSAGE
      echo "på $SERVER_ADMIN_MAIL, og bed om at blive fjernet fra modtagerlisten." >> $MAIL_MESSAGE
      echo >> $MAIL_MESSAGE
      echo "Email sendt kl.$(date +%k:%M)." >> $MAIL_MESSAGE
      $MAIL_BIN -s "$MAIL_SUBJECT" $MAIL_ADDRESS < $MAIL_MESSAGE
      # Better remove the mail from /tmp if it should be there
      if [ -e "$MAIL_MESSAGE" ] ; then
         rm "$MAIL_MESSAGE"
      fi
   }
fi
#
# We'd also have to check the backupdir and make it if it's nonexistent
if [ ! -d "$BACKUP_DIR" ] ; then
   mkdir -p "$BACKUP_DIR"
   EXIT_STATUS="$?"
   #
   # Since the rest of the script depends on this dir we'll check it
   if [ "$EXIT_STATUS" -ne "0" ] ; then
      if [ "$MAIL_SEND" = "always" ] || [ "$MAIL_SEND" = "failure" ] ; then
         send_mail_failure "Backupmappen "$BACKUP_DIR" kunne ikke oprettes med fejl $EXIT_STATUS ! Afslutter..."
      fi
   #
   # We can't very well do a backup without a working dir
   echo "Backupdir could not be made, exiting"
   exit "$EXIT_STATUS"
   fi
fi
#
# To avoid duplicate code, we'll build the folloving two functions
#
# If the oldest local backup exists, it needs deleting before the rotation
remove_old_local() {
   if [ -e "$BACKUP_DIR/$DUMP_NAME.sql.gz.$ROTATE_DATABASE" ] ; then
      rm "$BACKUP_DIR/$DUMP_NAME.sql.gz.$ROTATE_DATABASE"
   fi
}
#
# And then the rotation itself
rotate_local_database() {
   #
   # Two variables is needed to rotate, so they will be set first
   X=$ROTATE_DATABASE
   X=$((X-1))
   Y=$ROTATE_DATABASE
   # Then the actual rotation
   while [ "$X" -ne "0" ] ; do
      if [ -e "$BACKUP_DIR/$DUMP_NAME.sql.gz.$X" ] ; then
         mv "$BACKUP_DIR/$DUMP_NAME.sql.gz.$X" "$BACKUP_DIR/$DUMP_NAME.sql.gz.$Y"
      fi
      # And then 1 goes from each variabel so a end will be reached at some point
      X=$((X-1))
      Y=$((Y-1))
   done
}
#
#
#######################
###   SERVER MODE   ###
#######################
#
#
if [ "$MODE" = "server" ] ; then
#
# Lets dump the selected databases and/or tables
   $MYSQLDUMP -u$DUMP_USER -p$DUMP_PASS $DUMP_OPTIONS > "$BACKUP_DIR/$DUMP_NAME.sql"
   EXIT_STATUS="$?"
   # And only if dump is successfull will we go on with the backup
   if [ "$EXIT_STATUS" -ne "0" ] ; then
      if [ "$MAIL_SEND" = "always" ] || [ "$MAIL_SEND" = "failure" ] ; then
         send_mail_failure "MySQL-dump kunne ikke gennemføres med fejl $EXIT_STATUS, så INGEN BACKUP bliver lavet! Afslutter..."
      fi
   echo "mysqldump could not finish, exiting"
   exit "$EXIT_STATUS"
   fi
   #
   # Time to remove the oldest backup
   remove_old_local
   #
   # And then rotate 'em all
   rotate_local_database
   #
   # If the dump is in existence it's time to compress the sucker and save it in the right place
   if [ -e "$BACKUP_DIR/$DUMP_NAME.sql" ] ; then
      gzip -c "$BACKUP_DIR/$DUMP_NAME.sql" > "$BACKUP_DIR/$DUMP_NAME.sql.gz.1"
      EXIT_STATUS="$?"
      if [ "$EXIT_STATUS" -ne "0" ] ; then
         if [ "$MAIL_SEND" = "always" ] || [ "$MAIL_SEND" = "failure" ] ; then
         send_mail_failure "gzip afsluttede med fejl $EXIT_STATUS, så INGEN BACKUP bliver lavet! Afslutter..."
         fi
      echo "gzip could not finish, exiting"
      exit "$EXIT_STATUS"
      fi
      rm "$BACKUP_DIR/$DUMP_NAME.sql"
      #
      # Appends the completion of mysqldump to the mail
      echo >> $MAIL_MESSAGE
      echo "Dump af MySQL blev gennemført og pakket med succes" >> $MAIL_MESSAGE
   else
      #
      # No dump is kind of wierd and not planned
      if [ "$MAIL_SEND" = "always" ] || [ "$MAIL_SEND" = "failure" ] ; then
         send_mail_failure "MySQL dump kunne ikke findes efter dump, så INGEN BACKUP bliver lavet! Afslutter..."
      fi
   echo "MySQL dump not found, exiting"
   exit 11
   fi
   #
   # ftp time, it is
   # First up the old one have to go at the FTP site with the use of the FTP function
   # If it's nonexistent the server will throw a error but that doesn't really matter
   ftp_con delete "$DUMP_NAME.sql.gz.$ROTATE_FTP"
   #
   # Then the variables for the loop needs setting
   X=$ROTATE_FTP
   X=$((X-1))
   Y=$ROTATE_FTP
   # And then comes the rotate itself
   while [ "$X" -ne "0" ] ; do
      # Errors will be thrown if the files doesn't exists, but who cares anyhow
      ftp_con rename "$DUMP_NAME.sql.gz.$X" "$DUMP_NAME.sql.gz.$Y"
   # One rotation down, X minus 1 rotations to go
   X=$((X-1))
   Y=$((Y-1))
   done
   #
   # And up goes the new backup
   ftp_con put "$BACKUP_DIR/$DUMP_NAME.sql.gz.1" "$DUMP_NAME.sql.gz.1"
   #
   # Since no error checking is done on the FTP transfers itself, we'll put a list of files in the email
   if [ "$MAIL_SEND" = "always" ] ; then
      FTP_FILE_LIST=$(ftp_con ls)
      echo >> $MAIL_MESSAGE
      echo "Filer på FTP-serveren:" >> $MAIL_MESSAGE
      echo "$FTP_FILE_LIST" >> $MAIL_MESSAGE
   fi
#
# Cool, all done for the server
   if [ "$MAIL_SEND" = "always" ] ; then
   send_mail_ok "Dump, rotering og upload af databasen afsluttede med succes."
   fi
exit 0
# This is the one and only missing fi
# Without her we'd be lost http://tomorrowseries.wikia.com/wiki/Fiona_Maxwell
fi
#
#
######################
###   SLAVE MODE   ###
######################
#
#
if [ "$MODE" = "slave" ] ; then

   #
   # Time to fetch the backup from ftp
   ftp_con get "$DUMP_NAME.sql.gz.1" "$BACKUP_DIR/$DUMP_NAME.sql.gz.new"
   #
   # We'll want to abort if ftp_con didn't got us anything
   if [ ! -e "$BACKUP_DIR/$DUMP_NAME.sql.gz.new" ] ; then
      if [ "$MAIL_SEND" = "always" ] || [ "$MAIL_SEND" = "failure" ] ; then
         send_mail_failure "dumpet kunne ikke hentes fra ftp-serveren! Afslutter..."
      fi
   echo "dump could not be fetched from ftp service, exiting"
   exit 13
   fi

   #
   # Time to remove the oldest backup
   remove_old_local

   #
   # And then rotate the local databases
   rotate_local_database

   #
   # Now the dump can be named correctly
   if [ -e "$BACKUP_DIR/$DUMP_NAME.sql.gz.new" ] ; then
      mv "$BACKUP_DIR/$DUMP_NAME.sql.gz.new" "$BACKUP_DIR/$DUMP_NAME.sql.gz.1"
   else
   # Nothing to rename is kind of wierd
      if [ "$MAIL_SEND" = "always" ] || [ "$MAIL_SEND" = "failure" ] ; then
         send_mail_failure "dumpet kunne ikke findes til omdøbning! Afslutter..."
      fi
   echo "dump could not be found for renaming, exiting"
   exit 14
   fi

   #
   # And then integrate it
   # Note that the command spands multiple lines
   # mysql -u [username] -p [password] [database_to_restore] < [backupfile]
   if [ -e "$BACKUP_DIR/$DUMP_NAME.sql.gz.1" ] ; then
      gunzip < "$BACKUP_DIR/$DUMP_NAME.sql.gz.1" |\
      mysql --user=$DUMP_USER --password=$DUMP_PASS $DATABASE_NAME
   else
      # No backup is a little wierd
      if [ "$MAIL_SEND" = "always" ] || [ "$MAIL_SEND" = "failure" ] ; then
         send_mail_failure "Backuppen kunne ikke findes til integrering! Afslutter..."
      fi
   echo "The backup could not be found for integration, exiting"
   exit 15
   fi
#
# Better set all of the domains as backup domains
# In bash, a here-documant must end on a seperate line without leading whiespaces
mysql -u$DUMP_USER -p$DUMP_PASS -D$DATABASE_NAME << END_SQL
UPDATE domain SET backupmx='1' WHERE backupmx='0';
END_SQL

   if [ "$MAIL_SEND" = "always" ] ; then
   send_mail_ok "Rotering, download og integrering af databsen er afsluttet med succes."
   fi
#
# All done for the slave
exit 0
fi
#
# The reason this script still runs must be modes fault. Let's email the error
   if [ "$MAIL_SEND" = "always" ] || [ "$MAIL_SEND" = "failure" ] ; then
      send_mail_failure "MODE er sikkert sat forkert (med indholdet $MODE), hvilket betyder at scriptet ikke kan køre korrekt! Afslutter..."
   fi
exit 12

(Opdateret d.19/11 så slaven automatisk sætter domænerne i databasen til backupmx=1, så slaven faktisk fungerer som backup MX)

Tilbage til "Sandkassen"

Hvem er online

Brugere der læser dette forum: [Crawler] og 0 gæster