Question:I have two servers for @Mail, and I want to create a secondary machine as a "cold" backup solution in case the main machines goes down. How can I configure two servers to achieve this?
Answer:Using the multi-server license of @Mail you can install the software on two machines, and use a secondary server as a backup solution.
Excluding MySQL tables is not included in some versions of MySQL. Here's a bash script that can do just that:- create a file called mysqldump.sh
- in the file, put:
#!/bin/sh
for TABLE in $(echo "show tables" | mysql -u [mysqluser] -p[mysqlpassword] [atmaildatabase] | grep -v Tables_in_ | grep -v [Excluded Tables])
do
mysqldump -v --add-drop-table -u [mysqluser] -p[mysqlpassword] [atmaildatabase] ${TABLE} > [mysqldumpfile.sql]
done
The encryption key requires random input from the server to
generate a secure key.
Under linux/windows, the machine requires activitiy to generate
the key ( e.g gnupg uses the hdd/cpu/io states to generate
unique numbers for the key)
Under freebsd, you can use the following command to add randomness
from the IRQ's of the machine.
/usr/sbin/rndcontrol -s 14 -s 8 -s 0
Use 'vmstat -i' to list which IRQ's are active on your server.
Question: I am trying to set up some accounts such as billing and support that can be accessed by other people via IMAP foldersAnswer: On the server-mode of @Mail you can share a users maildir directory with other users on the system.
As an example, this will allow you to setup the support@domain.com to be viewed as a folder on other users accounts, e.g james@domain.com
To enable you need to create a symbolic link , e.g
root# cd /usr/local/atmail/users/j/a/james@domain.com
root# ln -s /usr/local/atmail/users/s/u/support@domain.com/ .Support
You can repeat the process for another number of user-accounts to share a folder.
This will create the Support mailbox as a folder under the james@domain.com email-account. Via WebMail or an IMAP client, the user can view all messages in the support@domain.com mailbox.
-
Alternatively you can mask a single account to point to another maildir directory. This is useful if you want the james@domain.com account, to be masked directly to the support@domain.com mailbox ( without a symbolic link or alias )
To enable use the SQL query:
mysql> update Users set MailDir='/usr/local/atmail/users/s/u/support@domain.com' where Account='james@domain.com';
If the james@domain.com user logs in via WebMail, POP3 or IMAP, they will have direct access to the mailbox of support@domain.com
The following article explains how to add IMAPs or POP3s support to the webmail client via a transparent proxy. This solution requires that the webmail client is hosted on Linux with iptables support or that you have a Linux/iptables gateway to intercept the outbound connections.
The proxying is done via Stunnel. Get and install stunnel on the server that is running @Mail from http://www.stunnel.org/. RedHat based distro's may be able to do a 'yum install stunnel'.
The proxy, although transparent is not dynamic i.e. each external mail server that needs to be contacted has to be added manually to the configuration.
Edit stunnel.conf (/etc/stunnel/stunnel.conf) and add the protocol (imaps or pop3s) and the external mail server as follows:
client = yes [imaps] accept = 127.0.0.1:6100 delay = yes connect = mail.domain.com:993 [pops] accept = 127.0.0.1:6101 connect = mail.domain.com:995
Start stunnel with the following command:
stunnel /etc/stunnel/stunnel.conf
In the example stunnel configuration above we are saying that stunnel is acting as a client, and that it will accept IMAP connections on the localhost port 6100 and proxy those connections to mail.domain.com via IMAPs (port 993) and is doing the same for POP3s.
Now we need to add the iptables rules to intercept any connections destined for mail.domain.com on either the IMAP port (143) or the POP3 port (110) and forward them to the appropriate stunnel port (6100 or 6101)
# IMAPs proxy for mail.domain.com
iptables -t nat -A OUTPUT -p tcp -d mail.domain.com --dport 143 -j DNAT --to 127.0.0.1:6100
# POP3s proxy for mail.domain.com
iptables -t nat -A OUTPUT -p tcp -d mail.domain.com --dport 110 -j DNAT --to 127.0.0.1:6101
I add these rules to rc.local so I can easily manage them. To add additional hosts add another entry in stunnel.conf using a unique port on the accept line. Starting at 6100 I keep incrementing from there.
I am migrating from a legacy mail-server where users login via POP3/IMAP as user%domain.comI need to support this authentication mode, so I can migrate to @Mail without having end users change their mail-client username to login. How do I support this?
This is possible to achieve by altering the SQL query used to authenticate users in @Mail.To enable visit the Webadmin > Services > POP3/IMAP
Set a default domain for authentication, and select the SQL-query type = "Fast"
Next the /usr/local/atmail/mailserver/etc/authmysqlrc controls how the POP3/IMAP server handles authentication for end users.
Change the entry MYSQL_SELECT_CLAUSE to read:
MYSQL_SELECT_CLAUSE SELECT distinct UserSession.Account, '', UserSession.Password, '3000','3000',Users.MailDir, Users.MailDir, '', 'allowimap=1,allowpop3=1' FROM UserSession, Users WHERE (UserSession.Account = CONCAT('$(local_part)', '@', '$(domain)') OR UserSession.Account = REPLACE('$(local_part)', '%', '@') ) AND UserSession.Account = Users.Account and (Users.Account=CONCAT('$(local_part)', '@', '$(domain)') or Users.Account=REPLACE('$(local_part)', '%', '@') ) and (Users.UserStatus != '1' or Users.UserStatus is null)
This will supporting logging in as user@domain or user%domain
To test the changes restart services:
/etc/init.d/atmailserver restart
-
In order to support the Webadmin scripts updating the authmysqlrc you need to edit the source
/usr/local/atmail/webmail/libs/Atmail/Exim_Config.php
Locate the following code and replace the code in bold:
if($pref['pop3imap_querytype'] == "quick") {
$courier_conf['MYSQLQUERY'] =
# The SQL query used by @Mail to authentication with the mySQL database
# Note the UID 3000 is used for the query, which is the atmail username that has permissions
# over the /usr/local/atmail/users directory
#Short query - No group support to toggle POP3/IMAP on/off on a group basis
MYSQL_SELECT_CLAUSE SELECT distinct UserSession.Account, $crypt, UserSession.Password, '3000','3000',Users.MailDir, Users.MailDir, '', 'allowimap=1,allowpop3=1' FROM UserSession, Users WHERE (UserSession.Account = CONCAT('$(local_part)', '@', '$(domain)') OR UserSession.Account = REPLACE('$(local_part)', '%', '@') ) AND UserSession.Account = Users.Account and (Users.Account=CONCAT('$(local_part)', '@', '$(domain)') or Users.Account=REPLACE('$(local_part)', '%', '@') ) and (Users.UserStatus != '1' or Users.UserStatus is null)
-
Once modified you now have a system that can authenticate with user%domain.com or user@domain
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.
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?
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.