1 Topic by usafitz

  • usafitz
  • Member
  • Offline
  • Registered: 2013-02-02
  • Posts: 71

Topic: Separate MySQL Server

Is there a way to set a remote MySQL server (mysql.domain.com) for the original installation of iRedMail?  If not, is there any documentation explaining how to redirect the tables used in each program?  Thanks!

my default tables:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| amavisd            |
| iredadmin          |
| mysql              |
| policyd            |
| roundcubemail      |
| test               |
| vmail              |
+--------------------+


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

----

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,090

Re: Separate MySQL Server

How about this:

- Setup iRedMail with a fresh OS. This way, iRedMail will install and configure local MySQL server automatically.
- Export initial MySQL records, import to remote server.
- Update config files which have MySQL queries to use remote server as MySQL server. e.g. /etc/postfix/mysql/*, /etc/dovecot/dovecot-mysql.conf.

3 Reply by usafitz

  • usafitz
  • Member
  • Offline
  • Registered: 2013-02-02
  • Posts: 71

Re: Separate MySQL Server

Sounds like a plan. Thanks so much.

4 Reply by imknight

  • imknight
  • Member
  • Offline
  • Registered: 2012-07-24
  • Posts: 27

Re: Separate MySQL Server

usafitz wrote:

Sounds like a plan. Thanks so much.

Is there a full list of files that would need updating?

5 Reply by imknight

  • imknight
  • Member
  • Offline
  • Registered: 2012-07-24
  • Posts: 27

Re: Separate MySQL Server

Also is there any issues with changing table types from MYISAM to INNODB throughout? as i would like to use a high availability galera cluster for the mysql backend, the MYISAM tables seem to be at a quick glance in all areas.

mysql> SELECT count(*),engine FROM INFORMATION_SCHEMA.TABLES group by engine;
+----------+--------+
| count(*) | engine |
+----------+--------+
|        2 | CSV    |
|       17 | InnoDB |
|       20 | MEMORY |
|       64 | MyISAM |
+----------+--------+

mysql> SELECT TABLE_SCHEMA,TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where engine="MyISAM" AND TABLE_SCHEMA!="mysql" AND TABLE_SCHEMA!="information_schema";
+--------------+------------------------+
| TABLE_SCHEMA | TABLE_NAME             |
+--------------+------------------------+
| amavisd      | mailaddr               |
| amavisd      | policy                 |
| amavisd      | users                  |
| amavisd      | wblist                 |
| iredadmin    | log                    |
| iredadmin    | sessions               |
| iredadmin    | updatelog              |
| policyd      | blacklist              |
| policyd      | blacklist_dnsname      |
| policyd      | blacklist_helo         |
| policyd      | blacklist_sender       |
| policyd      | helo                   |
| policyd      | policy                 |
| policyd      | policy_training        |
| policyd      | rcpt_acl               |
| policyd      | spamtrap               |
| policyd      | throttle               |
| policyd      | throttle_from_instance |
| policyd      | throttle_rcpt          |
| policyd      | triplet                |
| policyd      | whitelist              |
| policyd      | whitelist_dnsname      |
| policyd      | whitelist_sender       |
| vmail        | admin                  |
| vmail        | alias                  |
| vmail        | alias_domain           |
| vmail        | anyone_shares          |
| vmail        | domain                 |
| vmail        | domain_admins          |
| vmail        | mailbox                |
| vmail        | recipient_bcc_domain   |
| vmail        | recipient_bcc_user     |
| vmail        | sender_bcc_domain      |
| vmail        | sender_bcc_user        |
| vmail        | share_folder           |
+--------------+------------------------+
35 rows in set (0.00 sec)

6 Reply by ZhangHuangbin

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

Re: Separate MySQL Server

imknight wrote:

is there any issues with changing table types from MYISAM to INNODB throughout?

It should be fine.

7 Reply by imknight

  • imknight
  • Member
  • Offline
  • Registered: 2012-07-24
  • Posts: 27

Re: Separate MySQL Server

ZhangHuangbin wrote:
imknight wrote:

is there any issues with changing table types from MYISAM to INNODB throughout?

It should be fine.

Cheers Zhang,

Do you happen to have  a full list of files that will need updates - ie including amavis/roundcube etc.

8 Reply by ZhangHuangbin

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

Re: Separate MySQL Server

- /etc/postfix/mysql/*
- /etc/dovecot/dovecot-mysql.conf
- /var/www/roundcubemail/config/, main.inc.php and db.inc.php
- /var/www/roundcubemail/plugins/password/config.inc.php
- /var/www/iredadmin/settings.ini
- /etc/policyd*.conf
- /etc/amavisd/amavisd.conf

Don't forget to let us know if i missed some files.

9 Reply by imknight

  • imknight
  • Member
  • Offline
  • Registered: 2012-07-24
  • Posts: 27

Re: Separate MySQL Server

ZhangHuangbin wrote:

- /etc/postfix/mysql/*
- /etc/dovecot/dovecot-mysql.conf
- /var/www/roundcubemail/config/, main.inc.php and db.inc.php
- /var/www/roundcubemail/plugins/password/config.inc.php
- /var/www/iredadmin/settings.ini
- /etc/policyd*.conf
- /etc/amavisd/amavisd.conf

Don't forget to let us know if i missed some files.


Thanks that should make it a bit easier smile

Time to go play smile

10 Reply by remington

  • remington
  • Member
  • Offline
  • Registered: 2013-03-10
  • Posts: 9

Re: Separate MySQL Server

Is there a chance you could fix the script to add data to mysql database on a different server or IP address (not localhost)?  It would be nice to have preconfigured installation script for us to modify with mysql information like root, password, customized database name for different services (dovecot, iredadmin, roundcube, etc.) so everything will be installed without having two mysql servers on one server.

11 Reply by ZhangHuangbin

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

Re: Separate MySQL Server

Hi remington,

You can try this:

*) Before installing iRedMail, open iRedMail-x.y.z/conf/global, find below lines and change it to match your need (remote MYSQL server address and port). Then iRedMail will use remote MySQL server directly.

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

*) Open iRedMail-x.y.z/conf/mysql, find below line and set correct MySQL root user name. default is 'root'. (NOTE: This parameter will be moved to file conf/global in iRedMail-0.8.4.)

export MYSQL_ROOT_USER='root'

*) During iRedMail installation, it will show you dialog to input password of MySQL root user, please type the correct password for your remote MySQL server.

Others keep the same. Let me know whether it doesn't work for you, so that i can fix it.

12 Reply by remington

  • remington
  • Member
  • Offline
  • Registered: 2013-03-10
  • Posts: 9

Re: Separate MySQL Server

Thank you and it works very well. I had too many problems getting iRedMail to work properly with its own mysql in the same localhost or same jail IP address.  I think it has to do with the mysql script not having proper permission to add root user to mysql for services such as dovecot to have access to it.  I had to uninstall mysql-server after iRedMail installation since it's no longer needed.  However, mysql-client is still required for it to work properly.