Setting up Mailman to store members in a MySQL database

For a project I was looking for a way to use GNU Mailman for mailinglist management (especially its powerful web based moderation, bounce handling and attachment scrubbing features) but storing the mailinglist member information in a SQL database. That's useful when you want to relate mailinglist members to extra information already in a database and functionality on an interactive website, and it's possible on a per-mailinglist basis. This turned out to be not very difficult but just not that clearly documented.

What I used:

• Mailman. I used the 2.1.x version packaged in Debian etch: an "open" system is just one apt-get away!
• The MySQL Member Adaptor available from rezo.net. There are several versions of MySQLMemberAdaptor around the web, but this one seems the most recent.

This explanation assumes you know how to work with Mailman in a regular setup, and things like creating databases and users under MySQL. Take the following steps:

1. Install and set up mailman as you would otherwise.
2. Put the MysqlMemberships.py file into the dir that is Mailman's base, this normally already has files like MemberAdaptor.py in it. For Debian this is /usr/lib/mailman/Mailman. You only need to add MysqlMemberships.py, the other files from rezo.net are not needed.
3. The create table query in MysqlMemberships.py does not work with MySQL 5, I had to apply this patch which I've sent to MySQLMembership's author in the meantime. This patch has been applied by Fil.
4. For bounce handling to work well, you may need this patch aswell: patch for Bouncer.py; it has been applied in Mailman 2.1.11 and up.
5. Create a database and a MySQL user, and add that information into your mm_cfg.py, like this:

   MYSQL_MEMBER_DB_NAME = "mailman"
   MYSQL_MEMBER_DB_USER = "mailman"
   MYSQL_MEMBER_DB_PASS = "somethingrandom"
   MYSQL_MEMBER_DB_HOST = "localhost"
   MYSQL_MEMBER_TABLE_TYPE = "wide"        # wide means one table per list, flat = one large table

6. Create a mailinglist like you normally would (e.g. via newlist). To enable the MySQL backend for just that list, create a file extend.py under /var/lib/mailman/lists/listname with the following content:

   from Mailman.MysqlMemberships import MysqlMemberships
   
   def extend(list):
           list._memberadaptor = MysqlMemberships(list)
   

   (whitespace is significant in Python).
7. It should work now! Upon receiving the first request for your list, the database table will be created automatically, which you can then populate. You may need to restart the mailman qrunner after you make further changes to your setup.