DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Mail Data Diff

10.22.2011
| 1669 views |
  • submit to reddit
        // Compare the current data with yesterday's data, extract differences and mail the zip file as an attachment. Useful only for small DB where the DML changes are moderate.

#!/bin/sh
# send the difference of data to an email specified, useful for small DB changes
# run the script using the following syntax or through cron
# /bin/sh -xv csvdump.sh root passwd DB_Name
# the admin email who will receive the diff file as an attachment
adminmail='admin.site@company.com'

# default Database to be dumped will be test unless fed as positional parameter
db_user=${1:-'root'}
db_passwd=${2:-''}
db_backup=${3:-'test'}

# directories will be created
newdir=`date '+%m%d'`
olddir=`date '+%m%d' --date='1 day ago'`
mkdir -p /pdump/$newdir
chown -R mysql:mysql /pdump
> /pdump/data_diff_$newdir.txt

mysqldump -u$db_user -p$db_passwd $db_backup --tab=/pdump/$newdir --fields-terminated-by=, --fields-enclosed-by='"'

for myfile in `ls /pdump/$newdir `
do

diff /pdump/$olddir/$myfile /pdump/$newdir/$myfile --ignore-matching-lines='Dump completed'

if [ $? -ne 0 ];then
echo $myfile  >> /pdump/data_diff_$newdir.txt
diff /pdump/$olddir/$myfile /pdump/$newdir/$myfile --ignore-matching-lines='Dump completed' >> /pdump/data_diff_$newdir.txt
fi

done

zip /pdump/data_diff_$newdir.txt.zip /pdump/data_diff_$newdir.txt

if [[ -s /pdump/data_diff_$newdir.txt ]];then
echo "differences file as on `date` attached. " | mutt -s "data audit of $db_backup" -a /pdump/data_diff_$newdir.txt.zip -- $adminmail

if [ $? -ne 0 ];then
cat /pdump/data_diff_$newdir.txt | mail -s"data audit of $db_backup" $adminmail
fi

fi