If you are running a large-scale Atmail implementation under Linux, by default in most distributions the "updatedb" command is run daily via Cron to index the filesystem HDD.

For large systems with 100k+ email messages, running updatedb against the system maildir directory is time consuming and uses a lot of resources.

A simple solution is to disable indexing of the Atmail users maildir by editing:

/etc/updatedb.conf

Search and append the following in bold:

PRUNEFS = "auto afs iso9660 sfs udf"
PRUNEPATHS = "/afs /media /net /sfs /tmp /udev /var/spool/cups /var/spool/squid /var/tmp /usr/local/atmail/users"

If you are running an NFS or storage array server, check your machine does not index the maildir via the system updatedb command.

This is just one simple configuration option that should not be overlooked.

It happens, you forget the admin user password for Atmail, or your sysadmin leaves without providing the password.

So how do you reset the adminitration password for Atmail? Easy.

1: Find the mysql details for Atmail under webmail/config/dbconfig.ini

2: Connect to the mysql server, e.g

mysql -u root -p

3: Reset the password via SQL

update AdminUsers set Password=MD5('mynewpass') where Username='admin' and UMasterAdmin='1';

4: Login via the Atmail Webadmin with the new password!

Out of the box, MySQL has very limited memory allocation for cache use. If you are using Atmail with MySQL in production, it is imperative you review and optimize MySQL for your hardware and memory. You can increase performance dramatically and reduce I/O usage by tweaking MySQL to use more of your system memory for the cache and buffers.

The two easy options for increasing performance for mysql are the innodb_buffer_pool_size and key_buffer_size options. Atmail uses InnoDB tables for the user authentication & log-files, and if you have a large userbase performance can be dramatically improved by increasing the innodb_buffer_pool_size. Other tables such as the UserSettings, Abook, use the MyISAM table format which uses the key_buffer_size option.

For a production machine running all the Atmail services with 2GB of RAM we'd recommend the following option:

/etc/my.cnf:

innodb_buffer_pool_size=256M
key_buffer_size=256M

If you have 4GB RAM, double the above. Once enabled reboot MySQL, and you will see via the process table the daemon will be allocated more memory.

Tune and check for your environment, and remember not to use the mysqld defaults for a production environment!

This portion of the guide covers the steps necessary to use the MySQL NDB Cluster with two storage engines and one management server. This will allow the two MySQL servers to share data independently, and still have the same information for both. In the case of one server going down, the other server will still be active. Changes made to the other server during this period will be "healed" by the MySQL NDB management system.

Packages for MySQL NDB exist for a lot of systems. However, this guide will cover the process of installing it from source - along with PHP.

For this guide, we will have three servers - server1.local (192.168.0.1), server2.local (192.168.0.2), and server3.local (192.168.0.3). The first two servers will be the replicated storage engines. The third server will act as an NDB management machine.

Setting up the Storage Machines

This step applies to server1.local and server2.local. First, download the MySQL Cluster package from: http://dev.mysql.com/downloads/cluster/. Then, untar to your preferred base directory. For this example, we will use /usr/local/:

% tar xvfz mysql-cluster-gpl-7.1.3-linux-i686-glibc23.tar.gz -C /usr/local/

Create a symlink for ease, or rename the folder:

% ln -s /usr/local/mysql-cluster-gpl-7.1.3-linux-i686-glibc23 /usr/local/mysql

Then, install MySQL:

Then, create a file called /etc/my.cnf. In the file, add:

[mysqld]
ndbcluster
ndb-connectstring=192.168.0.3
[mysql_cluster]
ndb-connectstring=192.168.0.3

Replace "192.168.0.3" with your management server's (server3.local) IP. Then, create the required directories:

% mkdir /var/lib/mysql-cluster

Setting up the Management Server

The MySQL Cluster package will contain the following files:

bin/ndb_mgm
bin/ndb_mgmd

Transfer these files to the server3.local server, as:

/usr/bin/ndb_mgm
/usr/bin/ndb_mgmd

Then, assign permissions:

% chmod +x /usr/bin/ndb_mgm
% chmod +x /usr/bin/ndb_mgmd

Then, create the required directories:

% mkdir /var/lib/mysql-cluster

In the directory, setup the configuration file called "/var/lib/mysql-cluster/config.ini". In the file:

[NDBD DEFAULT]
NoOfReplicas=2
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# NDB MGM Server
[NDB_MGMD]
HostName=192.168.0.3
# MySQL servers
[NDBD]
HostName=192.168.0.1
DataDir= /var/lib/mysql-cluster
[NDBD]
HostName=192.168.0.2    
DataDir=/var/lib/mysql-cluster
[MYSQLD]
[MYSQLD]

Change the IP addresses accordingly, depending on your setup. Now, start the NDB management process:

% /usr/bin/ndb_mgmd

Starting the NDB processes

On the two MySQL servers, execute:

/usr/local/mysql/bin/ndbd --initial
/etc/rc.d/init.d/mysql.server start

You can check if the MySQL NDB is active by typing this in the third server (server3.local):

% ndb_mgm

This will show a terminal. Type:

ndb_mgm> show

It will show something similar to:

Cluster Configuration
---------------------
[ndbd(NDB)]    2 node(s)
id=2    @192.168.0.1  (mysql-5.1.44 ndb-7.1.3, Nodegroup: 0, Master)
id=3    @192.168.0.2  (mysql-5.1.44 ndb-7.1.3, starting, Nodegroup: 0)

Congratuations! You have MySQL NDB active.

As handy and powerful as Atmail's webadmin is there is of course some information it does not glean from the DB and display in the pretty interface. Here are a handful of SQL queries that you may find useful for squeezing some extra info out of the Atmail DB.

Total number of accounts that have never been logged into

mysql> select count(Users.Account) from Users, UserSession where Users.Account = UserSession.Account and from_unixtime(UserSession.LastLogin) = Users.DateCreate;

If you are using an external MySQL server, and an AtMail installation on an operating system that has SELinux enabled, you might experience the following:

- the installation fails when connecting to the MySQL server
- connecting manually using the mysql command succeeds.

In this case, you need to set SELinux so that it allows HTTP modules to connect to the network. This can be done by executing the following command:

% /usr/sbin/setsebool -P httpd_can_network_connect true 

Retry the installation afterwards.

When you see this error, when attempting to connect to a remote MySQL database:

"Client does not support authentication protocol
requested by server; consider upgrading MySQL client"

This usually means that the server you are connecting from is using an older version of mysql-client to access a 4.1.7+ version of MySQL. To fix this, just install the updated MySQL 4.1 client, and the error should go away. Also, install the latest version of DBD-mysql from http://search.cpan.org

Should you recieve this error after upgrading MySQL though:

"Error 1043: Bad Handshake"

This usually means that your MySQL client does not support the authentication protocol given by the server. To fix this, make sure that the MySQL server and client versions on both servers are similar. Say, if the web server is using 4.1.7, the database server should use the same version.

By default, mySQL can ship with default settings that allow only 50+ mySQL connections per user/system.

@Mail requires an increased maximum user-connections via mySQL and a lower timeout value ( due to the persistant database connections and mySQL threads from webmail/pop3/smtp )

Edit the following in the /etc/my.cnf:

[mysqld]
....
set-variable = wait_timeout=120
set-variable = max_user_connections=500
set-variable = max_connections=500
....

This will increase the defaults used by mySQL. Once the configuration file has been edited, restart mySQL for changes to take effect.

Question:

I have a user who has a very large address book. The user wants me to dump all of the addresses to a text file so I can setup a mailing list for them on our mailing list server. How can I do this?

Answer:

Using a quick sql query and file redirection you can create a file to use for importing to a mailing list.

root# echo "select UserEmail from Abook_(first letter of account) where Account='user@domain';" | mysql -u user -ppassword database > file.txtAbook_(first letter of account)  =  e.g.  for jason@mydomain.com this would be Abook_j

user@domain  = the user account you are looking do dump

user  = the mysql user

password  == the password of the mysql user

database = the database your connecting to (usually would be atmail in this case)

file.txt  =  what you are looking to dump the query to.

As a simple example:

echo "select UserEmail from Abook_j where Account='jason@mydomain.com';" | mysql -u root -pchangeme atmail > addressbook.txt

This will create a file (addressbook.txt) with each email address in the users address book on a separate line. You may need to clean this up a bit before importing depending on the syntax the user used to add the addresses.

When you get this error, it usually means that the ibdata tablespace for the table that produced the error is gone.

If you have a backup, this is recoverable. Just do the following:

1.) delete/move the InnoDB frm files first:

% cd /var/lib/mysql/[atmaildb]/
% mv Groups.frm UserSession.frm Users.frm Log_* /tmp/

2.) - drop, recreate the MySQL database:

% mysql -u root -p
mysql> drop database [atmaildb];
mysql> create database [atmaildb];

3.) reimport the database from backup:

% mysql -u root -p [atmaildb]

This should then work. Alternatively, should you selectively want to just restore the affected tables, follow step 1 and edit your atmaildb.sql file so it only has the data for the affected tables.