Difference between revisions of "BackupRTDB"

From Request Tracker Wiki
Jump to navigation Jump to search
 
m (3 revisions imported)
 
(2 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
'''Introduction'''
 
'''Introduction'''
  
The following script was written to provide a quick way to run a backup of the Request Tracker database. This is a good tool for routine daily backups and can be run as part of the daily cron batch.
+
The following script was written to provide a quick way to run a backup of the Request Tracker ''mysql'' database. This is a good tool for routine daily backups and can be run as part of the daily cron batch.
  
 
'''Requirements'''
 
'''Requirements'''
Line 17: Line 17:
  
 
  <nowiki>#!/bin/bash
 
  <nowiki>#!/bin/bash
#
+
  #
# backupRTDB.sh
+
  # backupRTDB.sh
#
+
  #
# $Author: www-data $
+
  # $Author: www-data $
# $Revision: 1.3 $
+
  # $Revision: 1.3 $
# $Id: backupRTDB,v 1.3 2006/04/10 15:48:21 www-data Exp $
+
  # $Id: backupRTDB,v 1.3 2006/04/10 15:48:21 www-data Exp $
#
+
  #
# Runs a mysqldump backup of the contents of the
+
  # Runs a mysqldump backup of the contents of the
# RT Database.
+
  # RT Database.
#
+
  #
# Into files named rtdb-main-{date}.tar.gz
+
  # Into files named rtdb-main-{date}.tar.gz
#                  rtdb-attach-{date}.tar.gz
+
  #                  rtdb-attach-{date}.tar.gz
#
+
  #
# This script assumes that root can login to mysql locally
+
  # This script assumes that root can login to mysql locally
# without a password. If you change this then you will need to revise
+
  # without a password. If you change this then you will need to revise
# the connection strings.
+
  # the connection strings.
#
+
  #
+
 
#
+
  #
# Implementation Specific Variables
+
  # Implementation Specific Variables
#
+
  #
# You can change this to another directory for the tmp files.
+
  # You can change this to another directory for the tmp files.
#  I don't like to dump in /tmp
+
  #  I don't like to dump in /tmp
SCRTMPDIR=/root/tmp/
+
  SCRTMPDIR=/root/tmp/
MAILHEADER=$SCRTMPDIRrtbakfiles-mail
+
  MAILHEADER=$SCRTMPDIRrtbakfiles-mail
rtDB=###YOURRTDATABASENAME###
+
  rtDB=###YOURRTDATABASENAME###
rtbakWATCHER=###YOUREMAIL###
+
  rtbakWATCHER=###YOUREMAIL###
MAILSERVER=###YOUREMAILSERVER###
+
  MAILSERVER=###YOUREMAILSERVER###
rtUSER=###YOURRTUSERNAME###
+
  rtUSER=###YOURRTUSERNAME###
+
 
#
+
  #
# Script Variables
+
  # Script Variables
#
+
  #
curday=`date +"%Y%m%d%H%M%S"`
+
  curday=`date +"%Y%m%d%H%M%S"`
backdir=/var/backups/rt/
+
  backdir=/var/backups/rt/
mainprefn="rtdb-main-"
+
  mainprefn="rtdb-main-"
attachprefn="rtdb-attach-"
+
  attachprefn="rtdb-attach-"
mainfullfn=$backdir$mainprefn$curday.gz
+
  mainfullfn=$backdir$mainprefn$curday.gz
attachfullfn=$backdir$attachprefn$curday.gz
+
  attachfullfn=$backdir$attachprefn$curday.gz
rtdbbakCHANGES_FILE=$SCRTMPDIRrtbakfiles
+
  rtdbbakCHANGES_FILE=$SCRTMPDIRrtbakfiles
HOSTNAME=`/bin/hostname`
+
  HOSTNAME=`/bin/hostname`
FQDN=`/bin/hostname -f`
+
  FQDN=`/bin/hostname -f`
rtMainTables="ACL Attributes CachedGroupMembers CustomFieldValues CustomFields GroupMembers \
+
  rtMainTables="ACL Attributes CachedGroupMembers CustomFieldValues CustomFields GroupMembers \
Groups Links ObjectCustomFieldValues ObjectCustomFields Principals Queues ScripActions \
+
  Groups Links ObjectCustomFieldValues ObjectCustomFields Principals Queues ScripActions \
ScripConditions Scrips Templates Tickets Transactions Users sessions"
+
  ScripConditions Scrips Templates Tickets Transactions Users sessions"
+
 
#
+
  #
# Make/Verify target dirs
+
  # Make/Verify target dirs
#
+
  #
if [ ! -d $backdir ]; then
+
  if [ ! -d $backdir ]; then
  mkdir $backdir;
+
    mkdir $backdir;
fi
+
  fi
+
 
if [ ! -d $SCRTMPDIR ]; then
+
  if [ ! -d $SCRTMPDIR ]; then
  mkdir $SCRTMPDIR;
+
    mkdir $SCRTMPDIR;
fi
+
  fi
+
 
# do the Main backup
+
  # do the Main backup
mysqldump $rtDB --opt $rtMainTables  | gzip &gt; $mainfullfn
+
  mysqldump $rtDB --opt $rtMainTables  | gzip &gt; $mainfullfn
+
 
# do the Attachment backup
+
  # do the Attachment backup
mysqldump $rtDB --opt --default-character-set=binary Attachments  | gzip &gt; $attachfullfn
+
  mysqldump $rtDB --opt --default-character-set=binary Attachments  | gzip &gt; $attachfullfn
+
 
+
 
#create message file and send to watcher
+
  #create message file and send to watcher
if [ -s $CHANGES_FILE ] ; then
+
  if [ -s $CHANGES_FILE ] ; then
  echo HELO $MAILSERVER &gt; $MAILHEADER
+
    echo HELO $MAILSERVER &gt; $MAILHEADER
  echo MAIL FROM:root@$FQDN  &gt;&gt; $MAILHEADER
+
    echo MAIL FROM:root@$FQDN  &gt;&gt; $MAILHEADER
  echo RCPT TO:$rtbakWATCHER &gt;&gt; $MAILHEADER
+
    echo RCPT TO:$rtbakWATCHER &gt;&gt; $MAILHEADER
  echo DATA &gt;&gt; $MAILHEADER
+
    echo DATA &gt;&gt; $MAILHEADER
  echo Subject: $HOSTNAME - $curday - RT Database backup &gt;&gt; $MAILHEADER
+
    echo Subject: $HOSTNAME - $curday - RT Database backup &gt;&gt; $MAILHEADER
  echo Backup of files from the RT install on ${HOSTNAME}: &gt;&gt; $MAILHEADER
+
    echo Backup of files from the RT install on ${HOSTNAME}: &gt;&gt; $MAILHEADER
  echo This file is located at: &gt;&gt; $MAILHEADER
+
    echo This file is located at: &gt;&gt; $MAILHEADER
  echo ${HOSTNAME}:$mainfullfn &gt;&gt; $MAILHEADER
+
    echo ${HOSTNAME}:$mainfullfn &gt;&gt; $MAILHEADER
  echo &gt;&gt; $MAILHEADER
+
    echo &gt;&gt; $MAILHEADER
  echo . &gt;&gt; $MAILHEADER
+
    echo . &gt;&gt; $MAILHEADER
  echo QUIT &gt;&gt; $MAILHEADER
+
    echo QUIT &gt;&gt; $MAILHEADER
  exim4 -bs &lt; $MAILHEADER
+
    exim4 -bs &lt; $MAILHEADER
fi
+
  fi
+
 
+
 
</nowiki>
+
  </nowiki>
  
 
[[MichaelErana]] <u>g33k (at) efamilynj.org</u>
 
[[MichaelErana]] <u>g33k (at) efamilynj.org</u>

Latest revision as of 16:03, 6 April 2016

Introduction

The following script was written to provide a quick way to run a backup of the Request Tracker mysql database. This is a good tool for routine daily backups and can be run as part of the daily cron batch.

Requirements

I wrote this for Debain Sarge/Stable and it depends on having the exim4 mailer installed. I also requires that there is a ''tmp'' dir in /root. It also assumes that ''root'' (or what ever account the cron job will run as) will have DB access rights to the RT Database.

Caveat Emptor

This script is provided with no warranties expressed or implied. Use your own risk. Milage may vary.

TODO

  • Add section to detect mailer and automatically set this envvar
  • Add automatic tmpfile cleanup #!/bin/bash # <h1>backupRTDB.sh</h1> # <h1>$Author: www-data $</h1> <h1>$Revision: 1.3 $</h1> <h1>$Id: backupRTDB,v 1.3 2006/04/10 15:48:21 www-data Exp $</h1> # <h1>Runs a mysqldump backup of the contents of the</h1> <h1>RT Database.</h1> # <h1>Into files named rtdb-main-{date}.tar.gz</h1> <h1>rtdb-attach-{date}.tar.gz</h1> # <h1>This script assumes that root can login to mysql locally</h1> <h1>without a password. If you change this then you will need to revise</h1> <h1>the connection strings.</h1> # # <h1>Implementation Specific Variables</h1> # <h1>You can change this to another directory for the tmp files.</h1> <h1>I don't like to dump in /tmp</h1> SCRTMPDIR=/root/tmp/ MAILHEADER=$SCRTMPDIRrtbakfiles-mail rtDB=###YOURRTDATABASENAME### rtbakWATCHER=###YOUREMAIL### MAILSERVER=###YOUREMAILSERVER### rtUSER=###YOURRTUSERNAME### # <h1>Script Variables</h1> # curday=<code>date +"%Y%m%d%H%M%S"</code> backdir=/var/backups/rt/ mainprefn="rtdb-main-" attachprefn="rtdb-attach-" mainfullfn=$backdir$mainprefn$curday.gz attachfullfn=$backdir$attachprefn$curday.gz rtdbbakCHANGES_FILE=$SCRTMPDIRrtbakfiles HOSTNAME=<code>/bin/hostname</code> FQDN=<code>/bin/hostname -f</code> rtMainTables="ACL Attributes CachedGroupMembers CustomFieldValues CustomFields GroupMembers \ Groups Links ObjectCustomFieldValues ObjectCustomFields Principals Queues ScripActions \ ScripConditions Scrips Templates Tickets Transactions Users sessions" # <h1>Make/Verify target dirs</h1> # if [ ! -d $backdir ]; then mkdir $backdir; fi if [ ! -d $SCRTMPDIR ]; then mkdir $SCRTMPDIR; fi <h1>do the Main backup</h1> mysqldump $rtDB --opt $rtMainTables | gzip > $mainfullfn <h1>do the Attachment backup</h1> mysqldump $rtDB --opt --default-character-set=binary Attachments | gzip > $attachfullfn <h1>create message file and send to watcher</h1> if [ -s $CHANGES_FILE ] ; then echo HELO $MAILSERVER > $MAILHEADER echo MAIL FROM:root@$FQDN >> $MAILHEADER echo RCPT TO:$rtbakWATCHER >> $MAILHEADER echo DATA >> $MAILHEADER echo Subject: $HOSTNAME - $curday - RT Database backup >> $MAILHEADER echo Backup of files from the RT install on ${HOSTNAME}: >> $MAILHEADER echo This file is located at: >> $MAILHEADER echo ${HOSTNAME}:$mainfullfn >> $MAILHEADER echo >> $MAILHEADER echo . >> $MAILHEADER echo QUIT >> $MAILHEADER exim4 -bs < $MAILHEADER fi

MichaelErana g33k (at) efamilynj.org