1

Topic: Documentation on how to use AWS RDS instance with iRedMail server

==== Required information ====
- iRedMail version (check /etc/iredmail-release):
- Linux/BSD distribution name and version:
- Store mail accounts in which backend (LDAP/MySQL/PGSQL):
- Web server (Apache or Nginx):
- Manage mail accounts with iRedAdmin-Pro?
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Can anyone please point me to any documentation on how use AWS RDS (MYSQL) instance as external remote database on iRedMail server?  Thank you.

----

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

2

Re: Documentation on how to use AWS RDS instance with iRedMail server

i believe it will be useful:
http://www.iredmail.org/docs/install.ir … erver.html

3 (edited by jaideyan 2017-07-06 05:08:06)

Re: Documentation on how to use AWS RDS instance with iRedMail server

The instruction in the link below on how to install iRedMail with a remote MySQL is based on the fact that you are using a server for your MySQL.  However, AWS RDS is a managed database instance managed by Amazon; you don't have access to the underlying server to run the commands as specified in the link below. 

http://www.iredmail.org/docs/install.ir … erver.html

Any assistance will be appreciated.  Thanks.

4

Re: Documentation on how to use AWS RDS instance with iRedMail server

Can you connect to RDS with a MySQL "root" user? Does this root user have privilege to create new database and grant privileges?

5

Re: Documentation on how to use AWS RDS instance with iRedMail server

AWS RDS does not have the option to connect as a root user. You can use the AWS RDS admin user for running the script. I have tried it and it works.

6

Re: Documentation on how to use AWS RDS instance with iRedMail server

LordZ wrote:

AWS RDS does not have the option to connect as a root user. You can use the AWS RDS admin user for running the script. I have tried it and it works.

Thank you for your feedback.  Could you please provide a high level overview on how you did this?  I'm kind of new to this.
Especially now that iRedMail 0.9.7 is out.  Thanks once again.

7

Re: Documentation on how to use AWS RDS instance with iRedMail server

LordZ wrote:

AWS RDS does not have the option to connect as a root user. You can use the AWS RDS admin user for running the script. I have tried it and it works.

I'm continuing to have issue on a new install of -0.9.7 with remote mysql.  Database do not get created in the remotes mysql server. I'm using AWS RDS.
Below are my steps:
1. created user/passwd in remote mysql server(AWS RDS) with the appropriate permissions/privileges.
2. user is able to create database/tables in remote mysql server (AWS RDS), which means that user has appropriate permissions/privileges
3. I run the below commands b/4 the bash.sh script:
USE_EXISTING_MYSQL='YES' \
    >INITIALIZE_SQL_DATA='NO' \
    >MYSQL_SERVER_ADDRESS='mailserverdb.xx0xxxx6xxxx.eu-west-2.rds.amazonaws.com' \   (AWS RDS only have endpoint; no available server ip)
    >MYSQL_SERVER_PORT='3306' \
    >MYSQL_ROOT_USER='admin_iredmail' \
    >MYSQL_ROOT_PASSWD='xxxxxxxxx' \
    >MYSQL_GRANT_HOST='XX.XXX.XXX.XX' \
    bash iRedMail.sh

*****No I do not get "ERROR 1045 (28000): Access denied"*****

4. The installation wizard will go thru the necessary screens:
    Welcome and thanks for your use (Yes)
    Default mail storage path (accept default)
    Preferred web server (apache)
    Choose preferred backend used to store mail accounts (there's no option for mysql so I choose mariadb; I'm using mysql in my remote db server)
    Your first mail domain name (example.com)
    Password for the mail domain administrator (xxxxx)
    Optional components
   
5. Continue? [y|N]y
********************************************************************
* Start iRedMail Configurations
********************************************************************
[ INFO ] Create self-signed SSL certification files (2048 bits).
[ INFO ] Generate Diffie Hellman Group with openssl, please wait.
[ INFO ] Create required system account: vmail, iredadmin, iredapd.
[ INFO ] Configure Apache web server.
[ INFO ] Configure PHP.
[ INFO ] Configure MariaDB database server.
[ INFO ] Setup daily cron job to backup SQL databases with /var/vmail/backup/backup_mysql.sh
[ INFO ] Configure Postfix (MTA).
[ INFO ] Configure Dovecot (POP3/IMAP/Managesieve/LMTP/LDA).
[ INFO ] Configure ClamAV (anti-virus toolkit).
[ INFO ] Configure Amavisd-new (interface between MTA and content checkers).
[ INFO ] Configure SpamAssassin (content-based spam filter).
[ INFO ] Configure iRedAPD (postfix policy daemon).
[ INFO ] Configure iRedAdmin (official web-based admin panel).
[ INFO ] Configure Fail2ban (authentication failure monitor).
[ INFO ] Configure Roundcube webmail.
[ INFO ] Configure Awstats (logfile analyzer for mail and web server).

*************************************************************************
* iRedMail-0.9.7 installation and configuration complete.
*************************************************************************


6. [ INFO ] Disable SELinux in /etc/selinux/config.
< Question > Would you like to use firewall rules provided by iRedMail?
< Question > File: /etc/firewalld/zones/iredmail.xml, with SSHD port: 22. [Y|n]y

7. < Question > Would you like to use MySQL configuration file shipped within iRedMail now?
< Question > File: /etc/my.cnf. [Y|n] n

8.  When I go to the remote mysql server, no database(s) is created.

Any thoughts?  Thank you

8

Re: Documentation on how to use AWS RDS instance with iRedMail server

jaideyan wrote:

INITIALIZE_SQL_DATA='NO'

Use "YES" instead of "NO".

9

Re: Documentation on how to use AWS RDS instance with iRedMail server

ZhangHuangbin wrote:
jaideyan wrote:

INITIALIZE_SQL_DATA='NO'

Use "YES" instead of "NO".

Thank you very much.  Using the 'YES' worked.  However, new iRedMail install created the databases on the remote server, and also on the iRedMail server.  Should I be worried about two the databases in two locations?  When I try to delete the ones on the local iredmail server, it deleted it also on the remote AWS RDS server.  Any thoughts.  Thank you very much.

10

Re: Documentation on how to use AWS RDS instance with iRedMail server

jaideyan wrote:

However, new iRedMail install created the databases on the remote server, and also on the iRedMail server. 

This is weird enough and not right. Did you run iRedMail installer multiple times?

With a given remote MySQL server, iRedMail will use only remote sql server and do not touch local one. And there's no config for the local sql server to sync databases from remote one. So it doesn't make any sense.

11

Re: Documentation on how to use AWS RDS instance with iRedMail server

ZhangHuangbin wrote:
jaideyan wrote:

However, new iRedMail install created the databases on the remote server, and also on the iRedMail server. 

This is weird enough and not right. Did you run iRedMail installer multiple times?

With a given remote MySQL server, iRedMail will use only remote sql server and do not touch local one. And there's no config for the local sql server to sync databases from remote one. So it doesn't make any sense.

I have done this few times on new servers fresh new installs, and I get similar results.  Could there be a bug?  Below are my servers:
1.  Remote MYSQL server is AWS RDS.  Servername or endpoint is:
     a. mailserverdb.xx0xxxx6xxxx.eu-west-2.rds.amazonaws.com (listening on port 3306
     b. admin_iredmail with privileges/permissions created on this server, and able to connect with no issues

2.  iRedMail Server is: AWS Red Hat Enterprise Linux 7.3 (fresh OS);
    On this server I run:

    USE_EXISTING_MYSQL='mailserverdb.xx0xxxx6xxxx.eu-west-2.rds.amazonaws.com' \       (AWS RDS servername)
    MYSQL_SERVER_ADDRESS='mailserverdb.xx0xxxx6xxxx.eu-west-2.rds.amazonaws.com' \
    MYSQL_SERVER_PORT='3306' \
    MYSQL_ROOT_USER='admin_iredmail' \
    MYSQL_ROOT_PASSWD='xxxxxxxxxx' \
    MYSQL_GRANT_HOST='101.10.10.101' \
    bash iRedMail.sh

db and users are created on the remote and the iredmail server.  See outputs below:

From iRedMail server:
MySQL [(none)]> select user, host from mysql.user;
+----------------+--------------+
| user           | host         |
+----------------+--------------+
| admin_iredmail | %            |
| naijamail      | %            |
| amavisd        | 172.31.0.173 |
| iredadmin      | 172.31.0.173 |
| iredapd        | 172.31.0.173 |
| roundcube      | 172.31.0.173 |
| vmail          | 172.31.0.173 |
| vmailadmin     | 172.31.0.173 |
| iredmailadmin  | localhost    |
| mysql.sys      | localhost    |
| rdsadmin       | localhost    |
+----------------+--------------+


From my remote MYSQL server:
# mysql -h mailserverdb.xxxxxxxxxxxxxxeu-west-2.rds.amazonaws.com -P 3306 -u xxxxxxxx -p

MySQL [(none)]> select user, host from mysql.user;
+----------------+--------------+
| user           | host         |
+----------------+--------------+
| admin_iredmail | %            |
| naijamail      | %            |
| amavisd        | 172.31.0.173 |
| iredadmin      | 172.31.0.173 |
| iredapd        | 172.31.0.173 |
| roundcube      | 172.31.0.173 |
| vmail          | 172.31.0.173 |
| vmailadmin     | 172.31.0.173 |
| iredmailadmin  | localhost    |
| mysql.sys      | localhost    |
| rdsadmin       | localhost    |
+----------------+--------------+
11 rows in set (0.00 sec)



during installation of iRedMail, there's a place that asks for, "Would you like to use MySQL configuration file shipped within iRedMail now?". 
File: /etc/my.cnf. [Y|n].  I choose 'y'


During the iRedMail Configurations section,  I get this error message:
"ERROR 1419 (HY000) at line 398 in file: '/root/iRedMail-0.9.7/runtime/iredmail.sql': 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)"

12

Re: Documentation on how to use AWS RDS instance with iRedMail server

LordZ wrote:

AWS RDS does not have the option to connect as a root user. You can use the AWS RDS admin user for running the script. I have tried it and it works.

LordZ:
Can you share your setup procedure?  Thanks.

13

Re: Documentation on how to use AWS RDS instance with iRedMail server

jaideyan wrote:

    USE_EXISTING_MYSQL='mailserverdb.xx0xxxx6xxxx.eu-west-2.rds.amazonaws.com' \       (AWS RDS servername)

It should be "USE_EXISTING_MYSQL=YES" (or "NO"), but any value which is "NO" will be treated as "YES".

jaideyan wrote:

"ERROR 1419 (HY000) at line 398 in file: '/root/iRedMail-0.9.7/runtime/iredmail.sql': 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)"

Please set "log_bin_trust_function_creators=1" in my.cnf of AWS RDS.

14

Re: Documentation on how to use AWS RDS instance with iRedMail server

I checked iRedMail source code, especially "functions/mysql.sh", it's not expected to populate data in local mysql server. I'm confused.