Introduction

MPP provides functionalities which could use MySQL, or they will work only with MySQL:

archive/quarantine
per user spam settings
per use WBL's
client host WBL's
ACL
greylists
spam traps
autoblacklists (aka thresholds)
message tracking

Requirements for MySQL server

Minimum requirement is MySQL 4.1, but it is highly recommended to use MySQL 5.0.x. MySQL server 4.1/5.0 should be bundled with any recent OS or it could be compiled from sources.

MPP GUI can create SQL tables for you, however, if you wish to do this manually You can find MPP SQL code in /usr/local/MPP/sql or on our FTP server

MPP SQL

Specific configuration options for MySQL server

MySQL client - server socket location

MySQL functionality bundled with MPP relies on static MySQL client library. The static library assumes existence of MySQL server socket: /tmp/mysqld.sock but it is also searching for my.cnf file in /etc/my.cnf.

MySQL client machine (where MPP is installed) requires existence of /etc/my.cnf with the correct path to MySQL socket in [client] section - the same "socket" as in [mysqld] section

Example of /etc/my.cnf configuration:

[mysqld]
socket = /var/run/mysqld/mysqld.sock
.......

[client]
socket = /var/run/mysqld/mysqld.sock

On RedHat/CentOS/Fedora add [client] section with "socket" option defined exactly as in [mysqld] 
section of /etc/my.cnf. Commenting out #oldpasswords option is recommended too.

On Debian/Gentoo make the following symlink:
ln -s /etc/mysql/my.cnf /etc/my.cnf

NOTE: Define [client] section with "socket" option is required when MPP and MySQL server are on the same
machine.

max_allowed_packet

 In /etc/my.cnf add/change max_allowed_packet = 64M in [mysqld] section.

Create Database and Permissions

On MySQL server host, make sure MySQL server is running before startin to create the tables. We recommended using different databases for spam quarantine, archive and message tracking

 mysql -u root -p
 Enter MySQL root password
 
 mysql> CREATE DATABASE  mpp_spam;
 mysql> CREATE DATABASE  mpp_archive;
 mysql> CREATE DATABASE  mpp_mail_track;
 mysql> GRANT ALL PRIVILEGES ON mpp_spam.* TO mpp@localhost IDENTIFIED BY 'pass' WITH GRANT OPTION;
 mysql> GRANT ALL PRIVILEGES ON mpp_archive.* TO mpp@localhost IDENTIFIED BY 'pass' WITH GRANT OPTION;
 mysql> GRANT ALL PRIVILEGES ON mpp_mail_track.* TO mpp@localhost IDENTIFIED BY 'pass' WITH GRANT OPTION;
 mysql> FLUSH PRIVILEGES;
 mysql> QUIT

 Verify if user connect from localhost:
 mysql -umpp -p 
 Enter mpp's password: ('pass' in our example)

Create tables

MPP required tables can be created with MPP GUI, but from command line is possible too:

 mysql -uroot -p mpp_spam < /usr/local/MPP/sql/mpp-mysql.sql
 Enter MySQL root password

 mysql -uroot -p mpp_archive < /usr/local/MPP/sql/mpp-mysql.sql
 Enter MySQL root password

 mysql -uroot -p mpp_mail_track < /usr/local/MPP/sql/mail_tracking-mysql.sql
 Enter MySQL root password

Starting using MySQL features with MPP

The most convenient way to configure these options is to use MPP GUI.

Examples of configuration options for /usr/local/MPP/mppd.conf.xml

<quarantine type="primary">mysql://mpp:pass@localhost:mpp_spam</quarantine>

<archive>mysql://mpp:pass@localhost:mpp_archive</archive>

<wbl_uri>mysql://mpp:pass@localhost:mpp_spam</wbl_uri>

<track_mail_transfer_enabled>yes</track_mail_transfer_enabled>
<track_mail_transfer_uri>mysql://mpp:pass@localhost:mpp_mail_track</track_mail_transfer_uri>

Metadata - MPP RECOMMENDED STORAGE METHOD

In metadata (sender,recipient,details) are stored in MySQL and messages are stored on disk.

  <quarantine type="primary">mysql://mpp:pass@localhost:mpp_quarantine#/var/MPP/quarantine/%RAND256%/%RAND256%/</quarantine>

  <archive>mysql://mpp:pass@localhost:mpp_archive#/var/MPP/archive/%RAND256%/%RAND256%/</archive>

Retrieved from "http://wiki.mailspect.com/index.php?title=Mailspect_MySQL_Integration&oldid=1978"

Contents