Mail Mapping Files

Mail Mapping Release 0.7
========================

Web based (PHP) internal to external email mapper to keep track and update mail mappings.
Database can be exported and imported via the GUI ready for updating the email system or via python script direct from MySQL database.
Linux specific command line used though mostly will work for all platforms.

 *  Requires php, mysql-server, apache, php-mysql
 *  
 *  Extras :-
 * 
 *  php-cli (Ubuntu only) for command line usage
 *  MySQL-python (python-mysqldb) for python scripts
 *  phpMyAdmin for easy mysql admin via web GUI

Changes 0.7

Add features to config file to allow to be set to on/off
Add secure email flag to address table
Add email maxsize and secure to database export for import in external database
Add copy user to user admin
Add history andd notes to user admin
Increase restricted domain field to 256 chrs in user admin  (0.5/0.6 also).
Removed undelete procedure from import update 'upd' format. Must now use add to undelete and update. 


Changes: 0.6

New weblogs and maillogs database with auto table creation based on timestamp
Compress weblogs and maillogs when 2359 detected
Warn when importing a new mapping contains a domain not listed in domain tables
Install scripts can be run from the command line with password
Change server variable to varchar and add as an index
Password aging not working properly
Sort users by id number. 
Subnet web search
Display directory list instead of using apache index list
Add mysql port number so can use a different one if required
Change int variables to unsigned - -done 0.6
Move OS specific commands to config.php variables.
PDF creation started
Flags (on/off) moved to tinyint.

Upgrade
=======
See Upgrades.txt for upgrading MailMapping.

New Install
===========
Extract files in to your webservers public directory. 
Download this archive e.g. to /tmp

Create a MySQL database called mapping. E.g.

mysqladmin create mapping -uroot -p

For Fedora/Redhat : cd /var/www/html   
For Ubuntu : cd /var/www

sudo tar xvfz /tmp/mailmapping0.6.gz  ( or su -c 'tar xvfz /tmp/mailmapping0.6.gz' )


sudo chown -R `whoami` mapping  (or su -c 'chown -R `whoami` mapping' )
cd mapping


Copy the config.php.default to config.php. E.g. cp config.php.default config.php.
 
Edit config.php and add database name, username, password and install/upgrade password.

Run the install script (as root or using sudo if available). This sets the group to apache or www-data depending if www-data exists in /etc/passwd. 
This may be some other user on others so change the script.

chmod 700 ./tools/install.sh
sudo ./tools/install.sh       ( or use su -c './tools/install.sh' )

Go to http://localhost/mapping/install.php

and enter the password you set in config.php

Logon as superuser, password mmsuperuser or admin, password mmadmin (if prompted) then select Options and change the admin password and add other users.

Thats it!

Additional Useful Software
==========================

phpMyAdmin - PHP GUI for MySQL database admin. Available direct from repositories in Fedora and Ubuntu.
http://www.phpmyadmin.net/home_page/index.php

phpMyFAQ - Questions and Answers help pages can be used and linked to in the support option (add link in config.php)
http://www.phpmyfaq.de

Wink - Screen capture and caption editor (Linux and Windows).
http://debugmode.com/wink/download.php
Note libexpat.so install problem
http://www.debugmode.com/userforums/viewtopic.php?t=7100&sid=0277e9e355e987879beb6591005812cd

mtop (Solaris freeware site) and mytop (Linux, use `sudo yum install mytop`) 

Advanced
========

See tools directory for useful bits.

Edit /etc/php.ini (Ubuntu /etc/php5/apache2/php.ini) and change the upload_max_filesize. For files of 90,000 lines, try 10M.
post_max_size should be larger than upload_max_filesize, but left the same. Also for large file verification on import add memory_limit. 120M for 90,000 line verification.

upload_max_filesize = 10M
post_max_size = 10M
memory_limit = 120M

Not also the php.ini max execution time a script can run for, e.g. 30 seconds, which is about a 50,000 line import, 90s for 140,000.

max_execution_time = 30     ; Maximum execution time of each script, in seconds

Reload Apache to pick up changes to php.ini (Fedora: sudo service httpd reload Ubuntu: sudo /etc/init.d/apache2 reload)

Mysql /etc/my.cnf [mysqld] section, import file size of 1MB should be ok but may need to increase it to 70M :-

#set-variable=max_allowed_packet=1M
set-variable=max_allowed_packet=70M

Example Fedora file.
-------------------------------------------
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_connections = 800
max_user_connections = 800
key_buffer = 36M
myisam_sort_buffer_size = 64M
join_buffer_size = 4M
read_buffer_size = 3M
sort_buffer_size = 3M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 30
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 999999
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
mp_table_size = 16M
long_query_time=3
log-slow-queries=/var/log/mysql-slow.log
---------------------------------------------

If adding /var/log/mysql-slow.log, you may need to create the file before restarting mysql.
sudo touch /var/log/mysql-slow.log
sudo chown mysql.mysql /var/log/mysql-slow.log

Restart mysql (Fedora:  sudo service mysqld restart  Ubuntu:  sudo /etc/init.d/mysqld restart)

Crontabs
========

Example cron files are in the tools directory.

Remove session files over 1 hour old to force a new logon.
sudo cp ./tools/mmchecksession /etc/cron.hourly/

As your normal user with permissions to write in the mapping directory, copy the cron entries in mmcrontab :-

crontab -e

Cut and paste entries, then 

:wq

followed by enter to write the entries.

crontab -l

will list your added entries.

Mysql Optimisation
==================

To check your my.cnf against your own server, run the mysqltuningprimer and mysqltuner.pl scripts after a few days/weeks of running your database.
It will advise what parameters need tweaking. Example changes to file in tools/mm_my.cnf .

Errors and Solutions
====================

Fatal error: Maximum execution time of 30 seconds exceeded in /var/www/html/mapping/admfileimport.php on line 1077

The 30s timeout in /etc/php.ini has been reached.  Increase max_execution_time. E.g. 100,000 lines increase to 60 and retry. Remember to reload apache
to pick up new php.ini settings (sudo service httpd reload).

Import a file and the screen goes blank but does not import.

Check /etc/php.ini upload_max_filesize and post_max_size are both greater than the file size you are trying to import e.g. 20M

Importing a file, Integer overflow in memory_limit check detected. File was 9,000,000 lines, 2.8GB, and /etc/php.ini memory_limit set to 11000M.
Stopped at 5,020,000 lines. Appears to be limit of 4G for count variable? size_t in PHP appears to be bound by 32bit limit even on 64bit OS.

Additional Databases
====================
mysqladmin create addressmapping -uroot -p

install3.php will create table for addressmapping.

mysqladmin create maillogs -uroot -p
mysqladmin create weblogs -uroot -p

No need to run install2.php or install4.php as tables are automatically created based on log dates.

It appears that even on 64bit OS the number of lines integer is still restricted to 4GB in PHP. Therefore imported log files cannot exceed this.
It may be safer to restrict files size to 2GB and this is reflected in the coding. Split in to 4,000,000 line chunks does work (2x Quad core 2.4GHz, 12GB RAM).
For weblogs and maillogs, the tables are created per day automatically based on the time_stamp in the log. When it sees 2359, it knows this is the final log and compresses
the table (then read-only and cannot be added to without uncompressing first). If 2359 is not reached, the output will explain how to manually compress the logs.