Postfix mySQL to SQLite export/import scripts

postfixdb-export.sh:

#/bin/bash
TMPDIR=/tmp/postfix_sqlite
DATABASENAME=postfix.sqlite
MYSQLDATABASE=postfix
MYSQLUSER=postfix
MYSQLPASS=postfix
HTTPDIR=/var/www/mail.mattrude.com/noc

if [ ! -e $TMPDIR ]; then
 mkdir -p $TMPDIR
 chmod a+w $TMPDIR
else
 chmod a+w $TMPDIR
fi

if [ ! -e $HTTPDIR ]; then
 mkdir -p $HTTPDIR
 rm -rf $TMPDIR/csv_md5check.md5
else
 if [ ! -e $HTTPDIR/$DATABASENAME.gpg ]; then
  rm -rf $TMPDIR/csv_md5check.md5
 fi
fi

rm -rf $TMPDIR/alias.csv $TMPDIR/domain.csv $TMPDIR/mailbox.csv
echo "SELECT * FROM alias
INTO OUTFILE '$TMPDIR/alias.csv'
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'" |mysql $MYSQLDATABASE

echo "SELECT * FROM domain
INTO OUTFILE '$TMPDIR/domain.csv'
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'" |mysql $MYSQLDATABASE

echo "SELECT * FROM mailbox
INTO OUTFILE '$TMPDIR/mailbox.csv'
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'" |mysql $MYSQLDATABASE

if [ -e $TMPDIR/csv_md5check.md5 ]; then
 MD5STATUS=`md5sum -c $TMPDIR/csv_md5check.md5 |grep -v ".csv: OK" |wc -l` 2>&1
else
 MD5STATUS=999
fi

if [ $MD5STATUS != '0' ]; then
 rm -rf $TMPDIR/$DATABASENAME $TMPDIR/$DATABASENAME.md5 $TMPDIR/schema.txt
 md5sum $TMPDIR/*.csv > $TMPDIR/csv_md5check.md5
 echo "Database $MYSQLDATABASE is being updated"
 echo "
 CREATE TABLE alias (
  address varchar(255) NOT NULL,
  goto text NOT NULL,
  domain varchar(255) NOT NULL,
  created datetime NOT NULL default '0000-00-00 00:00:00',
  modified datetime NOT NULL default '0000-00-00 00:00:00',
  active tinyint(1) NOT NULL default '1');

 CREATE TABLE domain (
  domain varchar(255) NOT NULL,
  description varchar(255) NOT NULL,
  aliases int(10) NOT NULL default '0',
  mailboxes int(10) NOT NULL default '0',
  maxquota bigint(20) NOT NULL default '0',
  quota bigint(20) NOT NULL default '0',
  transport varchar(255) NOT NULL,
  backupmx tinyint(1) NOT NULL default '0',
  created datetime NOT NULL default '0000-00-00 00:00:00',
  modified datetime NOT NULL default '0000-00-00 00:00:00',
  active tinyint(1) NOT NULL default '1' );

 CREATE TABLE mailbox (
  username varchar(255) NOT NULL,
  password varchar(255) NOT NULL,
  name varchar(255) NOT NULL,
  maildir varchar(255) NOT NULL,
  mailstore varchar(255) NOT NULL default 'mdbox:',
  quota bigint(20) NOT NULL default '0',
  domain varchar(255) NOT NULL,
  created datetime NOT NULL default '0000-00-00 00:00:00',
  modified datetime NOT NULL default '0000-00-00 00:00:00',
  quota_usage varchar(255),
  quota_usage_date varchar(255),
  active tinyint(1) NOT NULL default '1',
  local_part varchar(255) NOT NULL );
 " > $TMPDIR/schema.txt

 echo ".read $TMPDIR/schema.txt" |sqlite3 $TMPDIR/$DATABASENAME
 echo ".import $TMPDIR/alias.csv alias" |sqlite3 $TMPDIR/$DATABASENAME
 echo ".import $TMPDIR/domain.csv domain" |sqlite3 $TMPDIR/$DATABASENAME
 echo ".import $TMPDIR/mailbox.csv mailbox" |sqlite3 $TMPDIR/$DATABASENAME

 rm -rf $HTTPDIR/$DATABASENAME $HTTPDIR/$DATABASENAME.gpg $HTTPDIR/$DATABASENAME.sig
 cp $TMPDIR/$DATABASENAME $HTTPDIR/$DATABASENAME
 cd $HTTPDIR/
 gpg -b $DATABASENAME
 gpg -se -r E2E70CD8 -r D2FAA865 -r 6503BE11 $DATABASENAME
 rm -f $HTTPDIR/$DATABASENAME
 #md5sum $DATABASENAME > $DATABASENAME.md5
 #bzip2 $DATABASENAME
 #ssh odin.mattrude.com /home/matt/bin/sbin/postfix_sqlite_import.sh
fi

postfixdb-import.sh:

#/bin/bash
DBNAME=postfix.sqlite
DBDIR=/etc/postfix/database
URL=https://mail.mattrude.com/noc/postfix.sqlite
TMPDIR=/tmp/$DBNAME
LOGDIR=$TMPDIR

if [ ! -e $DBDIR ]; then
 mkdir -p $DBDIR
 chmod 700 $DBDIR
 chown 0:0 $DBDIR
fi

if [ ! -e $TMPDIR ]; then
 mkdir -p $TMPDIR
fi

if [ ! -e $LOGDIR ]; then
 mkdir -p $LOGDIR
fi

rm -rf $LOGDIR/postfix_import*.log $TMPDIR/$DBNAME.sig $DBDIR/$DBNAME.sig
wget --no-check-certificate -P $DBDIR $URL.sig > $LOGDIR/postfix_import_sig_download.log  2>&1
SIGDOWNLOAD=$?

if [ $SIGDOWNLOAD != 0 ]; then
 echo "Did not download file $URL.sig; The host may be down or too busy."
 echo "Here is the download log:"
 cat $LOGDIR/postfix_import_sig_download.log
 echo ""
 echo "exiting script"
 exit 1
fi

cd $DBDIR
gpg --verify $DBNAME.sig > /dev/null 2>&1
MD5STATUS=`echo $?`
if [ $MD5STATUS != '0' ]; then
 echo "Update needed, downloading updated $DBNAME."
 cd $TMPDIR
 rm -f $TMPDIR/$DBNAME.sig
 mv $DBDIR/$DBNAME.sig $TMPDIR/$DBNAME.sig
 rm -rf $TMPDIR/$DBNAME.gpg
 wget --no-check-certificate -P $TMPDIR $URL.gpg > $LOGDIR/postfix_import_download.log 2>&1
 if [ -e $TMPDIR/$DBNAME.gpg ]; then
  cd $TMPDIR
  gpg $TMPDIR/$DBNAME.gpg >> $LOGDIR/postfix_import_download.log 2>&1
  echo "sig test 2"
  ls -lh
  gpg --verify $DBNAME.sig > /dev/null 2>&1
  SIGTEST=`echo $?`
  if [ $SIGTEST == '0' ]; then
   cd $DBDIR
   echo "Database file $DBNAME.gpg was successfully downloaded"
   ARCHIVEDATE=`date +%Y%m%d_%H%M`
   mv $DBDIR/$DBNAME $DBDIR/$DBNAME.$ARCHIVEDATE
   mv $TMPDIR/$DBNAME $DBDIR/$DBNAME
   bzip2 $DBDIR/$DBNAME.$ARCHIVEDATE
   chown 0:0 $DBDIR/$DBNAME
   chmod 666 $DBDIR/$DBNAME
   if [ -e $DBDIR/$DBNAME ]; then
    cd $DBDIR
    cp $TMPDIR/$DBNAME.sig $DBDIR/$DBNAME.sig
    MD5TEST2=`gpg --verify $DBNAME.sig |grep -v "gpg: Good signature" |wc -l`
    if [ $MD5TEST2 = '0' ]; then
     echo "$DBNAME was updated successfully!"
     rm -rf $DBDIR/$DBNAME.sig
    else
     echo "$DBNAME was downloaded successfully but the downloaded file dose not match the signature file."
     exit 1
    fi
   else
    echo "$DBNAME was not copied to the database location ($DBDIR)."
    exit 1
   fi
  else
   echo "Database $DBNAME was not updated, the signature check failed."
   echo "Here's the Signature Check Log:"
   cd $TMPDIR
   gpg --verify $DBNAME.sig
   rm -rf $TMPDIR/$DBNAME*
  fi
 else
  echo "Database $DBNAME was not updated, file was not downloaded."
  echo ""
  echo "Here's the download log:"
  cat $LOGDIR/postfix_import_download.log
  rm -rf $TMPDIR/$DBNAME*
  rm -rf $DBDIR/$DBNAME.sig
 fi
fi
rm -rf $TMPDIR/$DBNAME.sig $DBDIR/$DBNAME.sig
Advertisements

WordPress Auto Backup Script

A quick backup script for WordPress. If you pass a config file containing the block of variables, the script will auto run for your install of WordPress.

# /bin/bash
BKNAME=
DIR=
DBHOST=
DBNAME=
DBUSER=
DBPASS=

# Backup Varibles
BKDIR=

# Restore Variables
RSDIR=
RSARDIR=
RSUSER=
RSGROUP=

OFFSITE=

# Offsite Rsync
RSYNCHOST=
RSYNCUSERNAME=
RSYNCPASSWORD=

# Offiste SCP
SCPUSER=
SCPHOST=
SCPDIR=

if [ `date +%e` =  1 ]; then
 DAY=`date +%b`
 DATE=$DAY.`date +%Y-%m-%d`
else
 DAY=`date +%w`
 DATE=$DAY.`date +%Y-%m-%d`
fi

if [ -e $1 ]; then
 source $1
 CONFIGSTATUS="Using config file found at $1"
else
 echo "No Config file found"
 exit
fi

if [ ! -e $DB_HOST ]; then
  DBHOST=`grep "DB_HOST" $DIR/wp-config.php |sed "s/define('DB_HOST', '//" |sed "s/');//"`
  DBNAME=`grep "DB_NAME" $DIR/wp-config.php |sed "s/define('DB_NAME', '//" |sed "s/');//"`
  DBUSER=`grep "DB_USER" $DIR/wp-config.php |sed "s/define('DB_USER', '//" |sed "s/');//"`
  DBPASS=`grep "DB_PASS" $DIR/wp-config.php |sed "s/define('DB_PASSWORD', '//" |sed "s/');//"`
fi
BKDIRNAME=$BKNAME

if [ `date +%e` = 1 ]; then
 ARCHIVE=1
else
 if [ `date +%w` = 0 ]; then
  ARCHIVE=1
 else
  ARCHIVE=0
 fi
fi

if [ ! -e $BKDIR ];then
 mkdir -p $BKDIR
fi

chown -R root:root $DIR/
chown -R apache:apache $DIR/wp-content $DIR/wp-admin/update.php
chown apache:apache $DIR

if [ -e $DIR/sitemap.xml ]; then
  chown apache:apache $DIR/sitemap.xml
fi

if [ -e $DIR/sitemap.xml.gz ]; then
  chown apache:apache $DIR/sitemap.xml.gz
fi
case "$2" in
restore)
  rm -rf $RSARDIR/$BKNAME.$DAY.*
  rm -rf $RSDIR/$BKNAME
  rm -rf $DIR/$BKNAME.$DAY.*
  rm -rf $DIR/$DBNAME.*
  if [ ! -e $RSARDIR ]; then
   mkdir -p $RSARDIR
  fi
  export RSYNC_PASSWORD=$RSYNCPASSWORD
  rsync -rvzht --delete --stats $RSYNCUSERNAME@$RSYNCHOST::ibackup/odin/$BKNAME/$BKNAME.$DAY.* $RSARDIR/ --port=45873 >> $DIR/$BACKUPNAME.log 2>&1
  cd $RSARDIR
  md5sum -c $BKNAME.$DATE.tgz.md5 > /dev/null 2>&1
  RESTOREMD5=`echo $(($?))`
  if [ $RESTOREMD5 = 0 ]; then
   cd $RSDIR/
   cp $RSARDIR/$BKNAME.$DATE.tgz $RSDIR/$BKNAME.$DATE.tgz
   tar -xzf $RSDIR/$BKNAME.$DATE.tgz
   mv $RSDIR$DIR $RSDIR
   cd $RSDIR/$BKNAME
   md5sum -c $DBNAME.$DATE.sql.md5 > /dev/null 2>&1
   RESTORESQLMD5=`echo $(($?))`
   if [ $RESTORESQLMD5 = 0 ]; then
    cd $RSDIR
    mysql -u $DBUSER -p$DBPASS $DBNAME > $DIR/$BKNAME.$DATE.log 2>&1
  done
  mysqldump -h $DBHOST -u $DBUSER -p$DBPASS $DBNAME > $DIR/$DBNAME.$DATE.sql 2> $DIR/$BKNAME.$DATE.log
  a=$?
  mysqldump -h $DBHOST -u $DBUSER -p$DBPASS $DBNAME --xml > $DIR/$DBNAME.$DATE.xml 2> $DIR/$BKNAME.$DATE.log
  a2=$?
  cd $DIR/
  md5sum $DBNAME.$DATE.sql $DBNAME.$DATE.xml > $DBNAME.$DATE.sql.md5 2>> $DIR/$BKNAME.$DATE.log
  b=$?
  SQLERROR=`echo $(($a+$a2+$b))`
  if [ $SQLERROR != 0 ]; then
   BKNAME=$BKNAME-NOSQL
   echo "Exiting on SQL error"
   echo "DBHOST $DBHOST"
   echo "DBNAME $DBNAME"
   echo "DBUSER $DBUSER"
   echo "DBPASS $DBPASS"
   exit 1
  fi
  tar -czf $BKDIR/$BKNAME.$DATE.tgz --totals $DIR >> $DIR/$BKNAME.$DATE.log 2>&1
  c=$?
  cd $BKDIR/
  md5sum $BKNAME.$DATE.tgz > $BKNAME.$DATE.tgz.md5 2>> $DIR/$BKNAME.$DATE.log
  d=$?
  TOTAL=`ls -lh $BKDIR/$BKNAME.$DATE.tgz |awk '{ print $5 }'`
  ERROR=`echo $(($a+$a2+$b+$c+$d))`
  if [ $ERROR = 0 ]; then
   STATUS=Good
  else
   STATUS=Failed
  fi

  echo "" >> $DIR/$BKNAME.$DATE.log
  echo "Starting GPG encryption" >> $DIR/$BKNAME.$DATE.log
  for a in $GPGKEY
  do
   GPGKEYD="$GPGKEYD -r $a"
  done
  #gpg -e -r $GPGKEY $BKDIR/$BKNAME.$DATE.tgz
  echo "" >> $DIR/$BKNAME.$DATE.log
  if [ $ARCHIVE = '0' ]; then
   case "$OFFSITE" in
    rsync|RSYNC)
      export RSYNC_PASSWORD=$RSYNCPASSWORD
      rsync -rvzht --delete --stats $BKDIR/ $RSYNCUSERNAME@$RSYNCHOST::ibackup/odin/$BKDIRNAME --port=45873 --exclude=Archive/ >> $DIR/$BACKUPNAME.log 2>&1
    ;;

    scp|SCP)
      echo "" >> $DIR/$BKNAME.$DATE.log
      echo "Starting SCP Transmition" >> $DIR/$BKNAME.$DATE.log
      ssh $SCPUSER@$SCPHOST "mkdir -p $SCPDIR"
      ssh $SCPUSER@$SCPHOST "rm -f $SCPDIR/$BKNAME.$DAY.*"
      scp -q $BKDIR/$BKNAME.$DATE.tgz $SCPUSER@$SCPHOST:$SCPDIR/ 2>> $DIR/$BKNAME.$DATE.log
      scp -q $BKDIR/$BKNAME.$DATE.tgz.md5 $SCPUSER@$SCPHOST:$SCPDIR/ 2>> $DIR/$BKNAME.$DATE.log
      #ssh $SCPUSER@$SCPHOST "chmod 666 $SCPDIR/$BKNAME.$DAY.*"
      echo "" >> $DIR/$BKNAME.$DATE.log
    ;;
   esac
  fi

  echo "final error status is: $ERROR" >> $DIR/$BKNAME.$DATE.log
  sed '/tar: Removing leading */d' $DIR/$BKNAME.$DATE.log > $BKDIR/backuptmp.log
  #echo "INSERT INTO Backup_Log ( System, Backup_Job, Label, Output, Bytes, Status, Log ) VALUES ('`hostname -s`', 'Wiki', '$DIR', '$BKNAME.$DATE', '$TOTAL', '$STATUS', '`cat $BKDIR/backuptmp.log`');" |mysql -t -h localhost -u backup Status
  rm -rf $BKDIR/backuptmp.log
  rm -rf $DIR/$DBNAME.$DAY.*
  rm -rf $DIR/$BKNAME.$DAY.*
  ;;
*)
  echo "The valid commands are backup & restore"
  exit 1
  ;;
esac

Dovecot Auto Update Script

Here is a small auto update script for a Fedora build. This script will auto install all needed software and start Dovecot once it’s finished.

#!/bin/bash

DIR=/var/src

if [ ! -e $DIR ]; then
  echo "Creating base directory $DIR"
  mkdir -p $DIR
fi

if [ ! -e $DIR/dovecot-2.0 ]; then
  echo "Dovecot 2.0 is not installed, Downloading a fresh copy of Dovecot 2.0"
  cd $DIR
  hg clone http://hg.dovecot.org/dovecot-2.0/ dovecot-2.0 > /dev/null
  if [ $? != 0 ]; then
    echo "Failed to download a fresh copy of Dovecot 2.0"
    exit 1
  fi
  echo "Installing any needed dependinces"
  yum -y install mercurial gcc autoconf automake libtool perl-Text-Iconv gettext gettext-devel gettext-libs openssl openssl-devel sqlite sqlite-devel zlib zlib-devel > /dev/null
  cd $DIR/dovecot-2.0
else
  cd $DIR/dovecot-2.0
  echo "Downloading updated version of Dovecot 2.0"
  echo -n "Current Version is: "
  dovecot --version
  hg pull -u > /dev/null
  if [ $? != 0 ]; then
    echo "Failed to update the Dovecot 2.0 install"
    exit 1
  fi
  dovecotver=0
  hgrepover=1
  dovecotver=`dovecot --version |awk '{print $2}' |sed 's/(//g' |sed 's/)//g'`
  hgrepover=`hg summary |grep parent |sed 's/:/ /g' |awk '{print $3}'`
  if [ ! $dovecotver ]; then
    dovecotver=0
  fi
  if [ ! $hgrepover ]; then
    hgrepover=1
  fi
  if [ $dovecotver == $hgrepover ]; then
    echo "The installed version and the downloaded version are both `dovecot --version`, not updating."
    exit 0
  else
    echo "The installed version is $dovecotver, but the current version on hg is $hgrepover, updating!"
  fi
  rm -rf *
  hg revert --all > /dev/null
  if [ $? != 0 ]; then
    echo "Failed to restore Dovecot 2.0 install"
    exit 1
  fi
fi

echo "Starting to comple Dovecot 2.0"
./autogen.sh > /dev/null 2> /dev/null
if [ $? != 0 ]; then
  echo "Failed to run autogen.sh for Dovecot 2.0"
  exit 1
fi

./configure --prefix=/usr --with-ssl=openssl --with-sqlite --with-mysql --with-ldap --with-zlib > /dev/null 2> /dev/null

if [ $? != 0 ]; then
  echo "Failed to run configure for Dovecot 2.0"
  exit 1
fi

make > /dev/null 2> /dev/null
if [ $? != 0 ]; then
  echo "Failed to run make for Dovecot 2.0"
  exit 1
fi

if [ ! -e $DIR/dovecot-2.0-pigeonhole ]; then
  echo "Dovecot Pigeonhole is not installed, Downloading a fresh copy of Dovecot Pigeonhole"
  cd $DIR
  hg clone http://hg.rename-it.nl/dovecot-2.0-pigeonhole/ > /dev/null
  if [ $? != 0 ]; then
    echo "Failed to download a fresh copy of Dovecot Pigeonhole"
    exit 1
  fi
  cd $DIR/dovecot-2.0-pigeonhole
else
  cd $DIR/dovecot-2.0-pigeonhole
  hg pull -u > /dev/null
  if [ $? != 0 ]; then
    echo "Failed to update the Dovecot Pigeonhole install"
    exit 1
  fi
  rm -rf *
  hg revert --all > /dev/null
  if [ $? != 0 ]; then
    echo "Failed to restore Dovecot Pigeonhole install"
    exit 1
  fi
fi

./autogen.sh > /dev/null 2> /dev/null
if [ $? != 0 ]; then
  echo "Failed to run autogen.sh for Sieve"
  exit 1
fi

./configure --prefix=/usr --with-dovecot=../dovecot-2.0 > /dev/null
if [ $? != 0 ]; then
  echo "Failed to run configure for Sieve"
  exit 1
fi

make > /dev/null 2> /dev/null
if [ $? != 0 ]; then
  echo "Failed to run make for Sieve"
  exit 1
fi

echo "Stopping Dovecot"
service dovecot stop
if [ $? != 0 ]; then
  echo "Failed to stop the running Dovecot proccess"
fi

cd /var/src/dovecot-2.0
make install > /dev/null 2> /dev/null
if [ $? != 0 ]; then
  echo "failed to install Dovecot"
  exit 1
fi

cd /var/src/dovecot-2.0-pigeonhole
make install > /dev/null 2> /dev/null
if [ $? != 0 ]; then
  echo "Failed to install Sieve"
  exit 1
fi

rm -rf /usr/local/etc/dovecot
ln -s /etc/dovecot /usr/local/etc/dovecot
rm -rf /usr/etc/dovecot
ln -s /etc/dovecot /usr/etc/dovecotecho "Restarting Dovecot"
service dovecot start