Full database backup

From Zarafa wiki

Revision as of 09:37, 5 September 2012 by JSmit (Talk | contribs)
Jump to: navigation, search

To create a full backup of the Zarafa MySQL database, the easiest way is to make full back is via the default mysqldump command.
When using this command, it's important that you do not lock the tables.

You can create a full consistent backup with the following command:

mysqldump --single-transaction -p zarafa > zarafa.dump

Note: Never use --opt or --lock with mysqldump (which unfortunately is default e.g. in Debian!)

Additional note: We don't want --opt e.g. because of locking, but we may lose other useful options. You can alternatively use (with Debian):

--single-transaction --skip-opt --add-drop-table --create-options --extended-insert --quick --set-charset

When the attachments are stored outside the database, a full mysqldump is not enough. To backup the attachments on the filesystem you can use the opensource rsync tool or create a tar of all attachments.

Backup Script

Administrators use the command mysqldump to backup their MySQL databases. Many of them however do NOT correctly check for exit codes of the mysqldump command. This may result in having an incomplete backup of your MySQL databases. In this script the command set -o pipefail is used to prevent this.


Below you can find an example bash script which will check for mysqldump exit codes. The script is also able to send an email with the backup results. Extended logging (stdout) is not being send in the email, the script is easy to modify if you would want to. You can also check your crontab emails when you receive an error.


The script:

#!/bin/bash
#

# Modify the variables below to your need

# Mysql Credentials
MyUSER="mysqluser"
MyPASS="password"
MyHOST="mysqlhost"
MYSQL="$(which mysql)"

# Owner of mysql backup dir
OWNER="root"
# Group of mysql backup dir
GROUP="root"

# Which databases to backup
DBS="zarafa"
# Or get all databases
#DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"

# DO NOT BACKUP these databases
IGGY="test"

# Backup Dest directory, change this if you have someother location
DEST="/path/to/backup/dir"

# mysqldump parameters
DUMP_OPTS="-Q --single-transaction"

# Send Result EMail
SEND_EMAIL=1
NOTIFY_EMAIL="user@domain.com"
NOTIFY_SUBJECT="MySQL Backup Notification"

# Delete old backups
DELETE_OLD_BACKUPS=1
DELETE_BACKUPS_OLDER_THAN_DAYS=10

# Usually there is no need to modify the variables below

# Linux bin paths, change this if it can't be autodetected via which command
MYSQLDUMP="$(which mysqldump)"
GREP="$(which grep)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"
MAIL="$(which mail)"
FIND="$(which find)"
DF="$(which df)"

# Get hostname
HOST="$(hostname)"

# Get data in yyyy-mm-dd format
NOW="$(date +"%Y%m%d")"

# Function for generating Email
function gen_email {
  DO_SEND=$1
  TMP_FILE=$2
  NEW_LINE=$3
  LINE=$4
  if [ $DO_SEND -eq 1 ]; then
    if [ $NEW_LINE -eq 1 ]; then
      echo "$LINE" >> $TMP_FILE
    else
      echo -n "$LINE" >> $TMP_FILE
    fi
  fi
}

# Main directory where backup will be stored
if [ ! -d $DEST ]; then 
  mkdir -p $DEST
  # Only $OWNER.$GROUP can access it!
  $CHOWN $OWNER:$GROUP -R $DEST
  $CHMOD 0750 $DEST
fi

# Create backup directory
MBD="$DEST/$NOW"
if [ ! -d "$MBD" ]; then
  mkdir "$MBD"
  # Only $OWNER.$GROUP can access it!
  $CHOWN $OWNER:$GROUP -R $MBD
  $CHMOD 0750 $MBD
fi

# Temp Message file
TMP_MSG_FILE="/tmp/$RANDOM.msg"
if [ $SEND_EMAIL -eq 1 -a -f "$TMP_MSG_FILE" ]; then
  rm -f "$TMP_MSG_FILE"
fi

set -o pipefail

# Start backing up databases
STARTTIME=$(date +%s)
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="$MBD/$db.$HOST.$NOW"
	# 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 $DUMP_OPTS -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > "$FILE.gz"
        ERR=$?
        if [ $ERR != 0 ]; then
	  NOTIFY_MESSAGE="Error: $ERR, while backing up database: $db"	
	else
	  NOTIFY_MESSAGE="Successfully backed up database: $db"
	fi	
        gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "$NOTIFY_MESSAGE"
        echo $NOTIFY_MESSAGE
    fi
done
ENDTIME=$(date +%s)
DIFFTIME=$(( $ENDTIME - $STARTTIME ))
DUMPTIME="$(($DIFFTIME / 60)) minutes and $(($DIFFTIME % 60)) seconds."

# Empty line in email and stdout
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 ""
echo ""

# Log Time
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "mysqldump took: ${DUMPTIME}"
echo "mysqldump took: ${DUMPTIME}"

# Empty line in email and stdout
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 ""
echo ""

# Delete old backups
if [ $DELETE_OLD_BACKUPS -eq 1 ]; then
  find "$DEST" -maxdepth 1 -mtime +$DELETE_BACKUPS_OLDER_THAN_DAYS -type d | $GREP -v "^$DEST$" | while read DIR; do
    gen_email $SEND_EMAIL $TMP_MSG_FILE 0 "Deleting: $DIR: "
    echo -n "Deleting: $DIR: "
    rm -rf "$DIR" 
    ERR=$?
    if [ $ERR != 0 ]; then
      NOTIFY_MESSAGE="ERROR"
    else
      NOTIFY_MESSAGE="OK"
    fi
    gen_email $SEND_EMAIL $TMP_MSG_FILE 1 "$NOTIFY_MESSAGE"
    echo "$NOTIFY_MESSAGE"
  done
fi

# Empty line in email and stdout
gen_email $SEND_EMAIL $TMP_MSG_FILE 1 ""
echo ""

# Add disk space stats of backup filesystem
if [ $SEND_EMAIL -eq 1 ]; then
  $DF -h "$DEST" >> "$TMP_MSG_FILE"  
fi
$DF -h "$DEST"

# Sending notification email
if [ $SEND_EMAIL -eq 1 ]; then
  $MAIL -s "$NOTIFY_SUBJECT" "$NOTIFY_EMAIL" < "$TMP_MSG_FILE"
  rm -f "$TMP_MSG_FILE"
fi
Personal tools