1 Topic by ab.kotecha

  • ab.kotecha
  • Member
  • Offline
  • Registered: 2013-10-17
  • Posts: 11

Topic: iRedMail with remote MySQL Server

Hi All,

I am trying to setup iRedMail on AWS EC2 and AWS RDS. But while trying to configure iRedMail it automatically configures local MySQL installation but I would like to use remote MySQL available at RDS.

I don't understand what should I do in order to separate iRedMail application and Database from a single server.

I would really appreciate help.

==== Required information ====
- iRedMail version: 0.8.5
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Linux/BSD distribution name and version: CentOS 6.4 x64
- Related log if you're reporting an issue:
====


Regards,
Abhishek

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,091

Re: iRedMail with remote MySQL Server

Try this:

*) Make sure you can connect to remote MySQL server on iRedMail server before installing. NOTE: iRedMail will create new databases, grant permissions, create new tables/indexes.

*) Find below lines in file 'conf/global' in iRedMail before installation:

# MySQL server                                                                     
export MYSQL_SERVER="${LOCAL_ADDRESS}"                                                                                        
export MYSQL_SERVER_PORT='3306'                                                    
export MYSQL_ROOT_USER='root'

*) Change the MySQL server address (MYSQL_SERVER) to your remote MySQL server, and update port number (MYSQL_SERVER_PORT) and root user name (MYSQL_ROOT_USER) if needed. Save your changes.

*) Start iRedMail installation, when it prompts to input MySQL root password, enter the correct one (password for your remote MySQL server).

That's all. If it doesn't work for you, please do let us know, so that we can improve it.

3 Reply by ab.kotecha

  • ab.kotecha
  • Member
  • Offline
  • Registered: 2013-10-17
  • Posts: 11

Re: iRedMail with remote MySQL Server

Hello. smile

Firstly, thank you for the suggestion and ofcource I did try this. But while following your suggestion I got captured by following error.

< INFO > Configure MySQL database server.
ERROR 1419 (HY000) at line 341 in file: '/tmp/iRedMail-0.8.5/samples/iredmail.mysql': You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

It is an error. Do you have any experience with such error as well.

Friendly,
Abhishek

4 Reply by ab.kotecha (edited by ab.kotecha 2013-10-19 20:29:05)

  • ab.kotecha
  • Member
  • Offline
  • Registered: 2013-10-17
  • Posts: 11

Re: iRedMail with remote MySQL Server

Hello.

I found some curious thing in my Remote MySQL even after following your suggestion.

mysql> select Host,User from mysql.user;
+-----------+------------+
| Host      | User       |
+-----------+------------+
| %         |            |
| %         | thedbr00t  |
| localhost | amavisd    |
| localhost | iredadmin  |
| localhost | policyd    |
| localhost | rdsadmin   |
| localhost | roundcube  |
| localhost | vmail      |
| localhost | vmailadmin |
+-----------+------------+
9 rows in set (0.00 sec)

mysql>

Above code mentions that for all the users created inside my table (other than my AWS RDS root user "thedbr00t") the Host is mentioned as 'localhost' where as in my root user it is mentioned as "%" Could this be the reason of my installation failure.

Also, after I follow the installation script, whenever I try log-in to my database it gives me below error and I have to reboot the DB instance in order to allow me log-in.

[root@ip-172-31-4-168 ~]# mysqladmin flush-hosts -h fgdd1asing.ch1l0fcpykx6.ap-southeast-1.rds.amazonaws.com -P 3306 -u thedbr00t -pabc
mysqladmin: connect to server at 'fgdd1asing.ch1l0fcpykx6.ap-southeast-1.rds.amazonaws.com' failed
error: 'Host '172.31.4.168' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts''
[root@ip-172-31-4-168 ~]#

Looking at the AWS RDS DB instance its very clear that the iRedMail was able to log-in and create the users. Now something is going wrong after it.

I hope this information may help understand the issue somewhat more better. smile


Regards,
Abhishek

5 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,091

Re: iRedMail with remote MySQL Server

Please try again with below steps:

*) Make sure you can connect to remote MySQL server from iRedMail server before installing.

*) Please make sure iRedMail server can login to remote MySQL server with proper privileges. A simple way is granting all privileges with below command (on remote MySQL server):

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin_on_iredmail'@'[ip_of_iredmail_server]' IDENTIFIED BY '[admin_password]' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

IMPORTANT NOTE: 'WITH GRANT OPTION' in above SQL command is required.
NOTES:
* You can use 'root' as remote MySQL user name which will be used on iRedMail server side during iRedMail installation, or a custom user name. for example, 'admin_on_iredmail', etc. A custom user name should be better to help you understand what it's used for, and less confuse with default 'root' user.

* You must replace '[ip_of_iredmail_server]' by the real IP address of your iRedMail server in above command. For example,

… TO 'admin_on_iredmail'@'172.31.4.xx' …

* It's OK to delete this user after iRedMail installation.

*) Find below lines in file 'conf/global' in iRedMail before installation:

# File: conf/global
# MySQL server                                                                     
export MYSQL_SERVER="${LOCAL_ADDRESS}"                                                                                       
export MYSQL_SERVER_PORT='3306'                                                   
export MYSQL_ROOT_USER='root'

Change the MySQL server address (MYSQL_SERVER) to your remote MySQL server, and update port number (MYSQL_SERVER_PORT) and root user name (MYSQL_ROOT_USER) if needed. MYSQL_ROOT_USER must be the same as mentioned in above step (admin_on_iredmail). Save your changes.

*) Find below line in file 'conf/global' in iRedMail before installation:

# File: conf/global
if [ X"${LOCAL_ADDRESS}" == X'127.0.0.1' ]; then
    export SQL_HOSTNAME='localhost'

Replace 'localhost' by the IP address of your iRedMail server (WARNING: not IP address of your remote MySQL server). Save your changes.

*) Start iRedMail installation, when it prompts to input MySQL root password, enter the correct one (password for your remote MySQL server).
That's all. If it doesn't work for you, please do let us know, so that we can improve it.

6 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,091

Re: iRedMail with remote MySQL Server

I improved remote MySQL server support in iRedMail development version, it should be much simpler now:
http://www.iredmail.org/wiki/index.php? … sql.server

NOTE: This article is for upcoming iRedMail release, 0.8.6. But you can checkout the latest development edition and follow above wiki tutorial, it should work as expected. Check out the source code of iRedMail here:
https://bitbucket.org/zhb/iredmail/overview

7 Reply by ab.kotecha

  • ab.kotecha
  • Member
  • Offline
  • Registered: 2013-10-17
  • Posts: 11

Re: iRedMail with remote MySQL Server

Hi Zhang Huangbin,

I was busy during these last few days but I will definitely be giving it a try in a day or two.

Although, I had a quick overlook and found you have changed so many lines of source code at bitbucket. So it made me curious that other than your last complete suggestion (that I'm still about to test within my cloud) on changing conf/global file, should there be any other changes I should do to get better performance from remote MySQL? Otherwise, I feel like to use the next version in my servers. Also, is it fine if I use this development versions for production testing?


Regards,
Abhishek

8 Reply by ab.kotecha

  • ab.kotecha
  • Member
  • Offline
  • Registered: 2013-10-17
  • Posts: 11

Re: iRedMail with remote MySQL Server

Also, I forgot to kudos to your dedicative nature towards the iRedMail, and no doubt it was one of the code.google.com project in it's earlier days.

Kudos to Zhang Huangbin and his efforts. If you have Social profile, you may let me know as well. smile

9 Reply by ab.kotecha

  • ab.kotecha
  • Member
  • Offline
  • Registered: 2013-10-17
  • Posts: 11

Re: iRedMail with remote MySQL Server

Hi Zhang,

I wasn't able to restrict myself from trying you suggestion here.

But again this time I'am facing the attached error.

P.S.: I am doing testing on the same server which faced Database problem earlier even without uninstalling iRedMail first. I hope this is not creating issue here.


Regards,
Abhishek

10 Reply by ab.kotecha

  • ab.kotecha
  • Member
  • Offline
  • Registered: 2013-10-17
  • Posts: 11

Re: iRedMail with remote MySQL Server

Hi Zhang,

I wasn't able to restrict myself from trying you suggestion here.

But again this time I'am facing the attached error.

P.S.: I am doing testing on the same server which faced Database problem earlier even without uninstalling iRedMail first. I hope this is not creating issue here.


Regards,
Abhishek

Post's attachments

error.JPG
error.JPG 19.88 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

11 Reply by ab.kotecha

  • ab.kotecha
  • Member
  • Offline
  • Registered: 2013-10-17
  • Posts: 11

Re: iRedMail with remote MySQL Server

Hi Zhang,

I again tried installing the iRedMail on entirely new EC2 server and this time again I received the same error as connection between roundmail and database couldn't establish. This time I used the very new files referring to the new code available at the moment at https://bitbucket.org/zhb/iredmail/comm … b00503006b

Interestingly, one warning also came while installing and I am attaching both errors again here. Let me know if more logs are required.


Regards,
Abhishek

Post's attachments

error 1.JPG
error 1.JPG 105.71 kb, 1 downloads since 2013-10-22 

error.JPG
error.JPG 19.88 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

12 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,091

Re: iRedMail with remote MySQL Server

ab.kotecha wrote:

Although, I had a quick overlook and found you have changed so many lines of source code at bitbucket. So it made me curious that other than your last complete suggestion (that I'm still about to test within my cloud) on changing conf/global file, should there be any other changes I should do to get better performance from remote MySQL?

The suggestion is based on the latest stable release, iRedMail-0.8.5. So if you're installing iRedMail-0.8.5, please follow it.
The changes in bitbucket will be available in next release.

ab.kotecha wrote:

Otherwise, I feel like to use the next version in my servers. Also, is it fine if I use this development versions for production testing?

The problem is, we don't provide upgrade tutorial for development edition, so you will be lost when new stable release is available. Use iRedMail-0.8.5 in production please.

ab.kotecha wrote:

Kudos to Zhang Huangbin and his efforts. If you have Social profile, you may let me know as well. smile

I don't have Facebook, and my tweets are Chinese, i don't think you want to follow me on Twitter. smile
Ways to contact me is this forum, email, or my BlackBerry Messenger (it's now available on iPhone and Android, http://www.bbm.com/ ).

ab.kotecha wrote:

I again tried installing the iRedMail on entirely new EC2 server and this time again I received the same error as connection between roundmail and database couldn't establish. This time I used the very new files referring to the new code available at the moment at https://bitbucket.org/zhb/iredmail/comm … b00503006b

Interestingly, one warning also came while installing and I am attaching both errors again here. Let me know if more logs are required.

Obviously, you're not running the latest development edition (check your installation log in screenshot).
Could you please try the latest development edition again?

$ hg clone http://bitbucket.org/zhb/iredmail

NOTES:
1) Please execute below SQL commands on remote MySQL server BEFORE installing iRedMail:

mysql> FLUST HOSTS;
mysql> FLUSH PRIVILEGES;

If your iRedMail server raises too many sql related errors, remote MySQL server will reject connection from iRedMail server, that's why we need 'FLUSH HOSTS' to flush error count.

2) Please drop existing databases which will be created by iRedMail on remote MySQL server:

mysql> DROP DATABASE amavisd;
mysql> DROP DATABASE cluebringer;
mysql> DROP DATABASE iredadmin;
mysql> DROP DATABASE roundcubemail;
mysql> DROP DATABASE vmail;

3) Please drop existing MySQL users which will be created by iRedMail on remote MySQL server:

mysql> DROP USER 'amavisd'@'IREDMAIL_IP_ADDRESS';
mysql> DROP USER 'cluebringer'@'IREDMAIL_IP_ADDRESS';
mysql> DROP USER 'iredadmin'@'IREDMAIL_IP_ADDRESS';
mysql> DROP USER 'vmail'@'IREDMAIL_IP_ADDRESS';
mysql> DROP USER 'vmailadmin'@'IREDMAIL_IP_ADDRESS';

13 Reply by ab.kotecha

  • ab.kotecha
  • Member
  • Offline
  • Registered: 2013-10-17
  • Posts: 11

Re: iRedMail with remote MySQL Server

Hi Zhang Huangbin,

Sorry for this late reply, as I was busy with preparations of upcoming Indian festival of lights, Diwali.

As soon as I got some time, I did try following your suggestion very carefully but unfortunately this time I got caught by a new DB error. As I was not much comfortable with that error, I did try solving it with help from Google. But that thing also didn't work.

The Screenshot 1 is the error!!


Regards,
Abhishek

Post's attachments

30-10-2013 20-47-57.jpg 110.13 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

14 Reply by ab.kotecha

  • ab.kotecha
  • Member
  • Offline
  • Registered: 2013-10-17
  • Posts: 11

Re: iRedMail with remote MySQL Server

Another attempt, another error. sad

15 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,091

Re: iRedMail with remote MySQL Server

*) It says you need "RELOAD privilege". Did you grant necessary privileges? It's clearly described in our wiki tutorial:
http://www.iredmail.org/wiki/index.php? … sql.server

*) Please remove existing databases which will be created during iRedMail installation before installing iRedMail. It's clearly mentioned in above wiki tutorial.

*) Please do not post log/screenshot which contains password or sensitive information. I deleted your two screenshots.
*) Please paste TEXT directly if possible, not screenshot.