cPanel: PHPList Subscriber Mailing List Maintenance

In my company, I am also responsible to handle and manage the mailing list server. We are using PHPList, a popular mailing list program to blast out mails and we use it to send our latest news, promotions, announcements and notification to our subscribers. We have a lot of subscribers, which include some of them are problematic mail recipients with such following error:

  • mailbox unavailable
  • no such user
  • user rejected
  • domain already expired

In order to do maintenance on the subscriber list, I need to make sure only active subscriber (which have active mailbox) exist in our mailing list. By referring to exim_mainlog and looking for recipient bounce error, we can remove unwanted recipient from our subscriber list. Variable as below:

OS: CentOS 4 64bit
PHPlist version: 2.10.5
Mail server log: /var/log/exim_mainlog
PHPList database: plist_db
PHPList Username: plist_userdb
PHPList password: p412#Yf

1. Lets generate error log from exim_mainlog so we can easily extract the error information. We will use eximstats command and generate the output to html files, same as what you will see under WHM > Email > View Mail Statistics:

$ /usr/sbin/eximstats -html=/root/mailstats.html -nr -nt -nvr /var/log/exim_mainlog

2. If we go through the generated html file, you can see the list of error captured by exim_mainlog. Example error as below:

<li>1 - [email protected] R=fail_remote_domains: The mail server could not deliver mail to [email protected] The account or domain may not exist, they may be blacklisted, or missing the proper dns entries.

The error above describing that the remote domain is no longer exist and unreachable. We do not want this email account anymore in our subscriber list so we need to extract the email address and remove from PHPlist database.

3. Lets extract all emails which related to this error to a file called domain_error.txt:

$ cat /root/mailstats.html | grep "The account or domain may not exist, they may be blacklisted, or missing the proper dns entries" > domain_error.txt

4. From the domain_error.txt list, we can extract email address only so we can pass this value to PHPlist database via SQL statement:

perl -wne'while(/[\w\.\-][email protected][\w\.\-]+\w+/g){print "$&\n"}' domain_error.txt | sort -u > delete_list.txt

5. We should now have a list of email address that we want. We will use a BASH script to automate the deletion process. Create a file called phplist_delete under /root/scripts folder:

mkdir -p /root/scripts
touch /root/scripts/phplist_delete

Copy and paste following scripts and change the configuration value to the one that suits you. You can retrieve the database information inside PHPlist config.php file (usually under config directory):

# Delete PHPlist subscriber who listed in delete_list.txt
# Configuration value
MYSQL="$(which mysql)"
if [ ! -f $DELETE_LIST ]
        echo "List file not found!"
        exit 1
        echo "Deleting email addresses in PHPlist database.."
        cat $DELETE_LIST | while read emailadd; do
                $MYSQL -h $HOST -u $USERNAME -p$PASSWORD <<< "DELETE FROM $DATABASE.phplist_user_user WHERE email like '$emailadd';"
                echo "$emailadd: deleted!"
        $MYSQL -h $HOST -u $USERNAME -p$PASSWORD <<< "DELETE from $DATABASE.phplist_user_user_attribute WHERE userid NOT IN (SELECT  id FROM $DATABASE.phplist_user_user);"
        $MYSQL -h $HOST -u $USERNAME -p$PASSWORD <<< "DELETE from $DATABASE.phplist_user_user_history WHERE userid NOT IN (SELECT id FROM $DATABASE.phplist_user_user);"
        echo "Process completed!"

6. Change the permission:

chmod 755 /root/scripts/phplist_delete

7. Lets run the script and you are done!