Rebuilding the Spam Tables v3

Users tips and ideas

Moderators: FlorianB, Pascal, bourgeois, mentor

uncltom
Posts: 495
Joined: Tue Aug 26, 2008 3:01 am
How did you hear about Mailcleaner: I dont remember probably google?
Location: Spokane, WA

Rebuilding the Spam Tables v3

Postby uncltom » Fri Mar 25, 2011 11:34 am

So after more issues with our mailcleaner freezing up because of hardware and/or backup problems.
I decided to sit down and write a script that will wipe out the spam tables and recreate them from the exim files.

Note: The code in these files are taken from existing mailcleaner files. It's not the cleanest most efficient way but it works.

What it will do...
1. Stop mail flow by stopping exim stage 1.
2. Repair all of the databases. (This appears to be necessary before the tables can be dumped.)
3. Backup the spool database to /root/mc_spool_yymmdd.tar.gz.
4. Wipe out the current spam tables.
5. Rebuild the spam tables by scanning the exim files for each user of each domain and gather the sender, subject, spam score, time and date, and running all of this back into the master & slave databases.
6. Restart exim stage 1.

All of this is logged to /root/repair_yymmdd.log for your review.

What it wont do...
1. This will NOT put the released messages back in the database. These are deleted when released so there is no way to recreate what isnt there.
2. Do your laundry! :lol:

Note: There appears to be an issue where the quarantine reasons are not put back properly. The scores are accurate but the reasons why are not complete. :(

How to do it...
1. Download the attached file and extract it to /usr/mailcleaner/bin (You can probably run it from other locations but I havent tried that yet.)
2. Install a perl file by running

Code: Select all

aptitude install libtime-format-perl

3. Run rebuild_spam_db.sh
4. Sit back and wait. If you have lots of spam from lots of users over lots of domains this can really take a while.

rebuild_spam_db.tar.gz
(5.02 KiB) Downloaded 760 times
Last edited by uncltom on Thu Sep 22, 2011 10:10 am, edited 1 time in total.
dwright
Posts: 5
Joined: Mon Sep 19, 2011 10:23 pm
How did you hear about Mailcleaner: google

Re: Rebuilding the Spam Tables v3

Postby dwright » Wed Sep 21, 2011 5:43 pm

Thanks for sharing your script. I am currently evaluating mailcleaner to replace our Barracuda which our subscription will be expiring shortly.

One thing I had to do before your script would run is install the time::format perl module

Code: Select all

aptitude install libtime-format-perl


This is running on 2010 Beta 3 with all the current updates applied.
uncltom
Posts: 495
Joined: Tue Aug 26, 2008 3:01 am
How did you hear about Mailcleaner: I dont remember probably google?
Location: Spokane, WA

Re: Rebuilding the Spam Tables v3

Postby uncltom » Thu Sep 22, 2011 10:09 am

Ahh... I think I may have installed that as part of my update script. Thanks!
directict
Posts: 75
Joined: Tue Jul 12, 2011 6:04 pm
How did you hear about Mailcleaner: google

Re: Rebuilding the Spam Tables v3

Postby directict » Mon Sep 26, 2011 9:30 pm

dear uncltom,

this might fix my issue with: (?)
viewtopic.php?f=12&t=1260&start=0
uncltom
Posts: 495
Joined: Tue Aug 26, 2008 3:01 am
How did you hear about Mailcleaner: I dont remember probably google?
Location: Spokane, WA

Re: Rebuilding the Spam Tables v3

Postby uncltom » Wed Sep 28, 2011 6:16 pm

directict wrote:dear uncltom,

this might fix my issue with: (?)
viewtopic.php?f=12&t=1260&start=0


It could. You could backup the spam databases before you begin so you can go back to the old databases if it makes things worse.
(I have never seen that happen but there is always a first for everything.)
User avatar
toothycardus
Posts: 293
Joined: Fri Jul 13, 2012 11:51 am
How did you hear about Mailcleaner: Google
Location: United Kingdom

Re: Rebuilding the Spam Tables v3

Postby toothycardus » Thu May 23, 2013 4:29 pm

Does this need to be run on the master and the slaves?

Return to “Tips”

Who is online

Users browsing this forum: No registered users and 1 guest