IRedMail/FAQ/Store.Realtime.Quota.In.MySQL

From iRedMail

(Difference between revisions)
Jump to: navigation, search
(→Steps)
(→Steps)
Line 25: Line 25:
</pre>}}
</pre>}}
-
* Create MySQL tables '''realtime_quota''' in database '''iredadmin''' used to store realtime quota info. (Of course you can use other database instead of '''iredadmin'''.)
+
* '''[For OpenLDAP backend]''' Create MySQL tables '''realtime_quota''' in database '''iredadmin''' used to store realtime quota info. (Of course you can use other database instead of '''iredadmin'''.)
{{cmd|<pre>
{{cmd|<pre>
$ mysql -uroot -p iredadmin
$ mysql -uroot -p iredadmin
mysql> CREATE TABLE IF NOT EXISTS realtime_quota (
mysql> CREATE TABLE IF NOT EXISTS realtime_quota (
     `username` VARCHAR(255) NOT NULL,
     `username` VARCHAR(255) NOT NULL,
-
     `bytes` BIGINT NOT NULL DEFAULT 0,
+
     `currentbytes` BIGINT(20) NOT NULL DEFAULT 0,
-
     `messages` BIGINT NOT NULL DEFAULT 0,
+
     `currentmessages` BIGINT(20) NOT NULL DEFAULT 0,
     PRIMARY KEY (`username`)
     PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
</pre>}}
</pre>}}
 +
 +
* '''[For MySQL backend]''' If you're using iRedMail-0.6.1 or order version, you can simply add two columns in table '''vmail.mailbox''':
 +
{{cmd|<pre>
 +
$ mysql -uroot -p vmail
 +
mysql> ALTER TABLE mailbox ADD COLUMN `currentbytes` BIGINT(20) NOT NULL DEFAULT 0;
 +
mysql> ALTER TABLE mailbox ADD COLUMN `currentmessages` BIGINT(20) NOT NULL DEFAULT 0;
 +
</pre>
* Create file '''/etc/dovecot-realtime-quota.conf''':
* Create file '''/etc/dovecot-realtime-quota.conf''':
Line 43: Line 50:
     table = realtime_quota
     table = realtime_quota
     username_field = username
     username_field = username
-
     value_field = bytes
+
     value_field = currentbytes
}
}
map {
map {
Line 49: Line 56:
     table = realtime_quota
     table = realtime_quota
     username_field = username
     username_field = username
-
     value_field = messages
+
     value_field = currentmessages
}
}
</pre>}}
</pre>}}

Revision as of 15:36, 12 October 2010

Contents


Requirements

  • Dovecot 1.2.x

Steps

  • Replace file based quota by quotadict in /etc/dovecot.conf:
File: /etc/dovecot.conf 
plugin {
    #quota = maildir                       # <- Comment this line.
    quota = dict:user::proxy::quotadict       # <- Add this line.

    # ... SKIP OTHER CONFIG HERE ...
}
  • Add quotadict in dict section in /etc/dovecot.conf:
File: /etc/dovecot.conf 
dict {
    quotadict = mysql:/etc/dovecot-realtime-quota.conf

    # ... SKIP OTHER CONFIG HERE ...
}
  • [For OpenLDAP backend] Create MySQL tables realtime_quota in database iredadmin used to store realtime quota info. (Of course you can use other database instead of iredadmin.)
Terminal: 
$ mysql -uroot -p iredadmin
mysql> CREATE TABLE IF NOT EXISTS realtime_quota (
    `username` VARCHAR(255) NOT NULL,
    `currentbytes` BIGINT(20) NOT NULL DEFAULT 0,
    `currentmessages` BIGINT(20) NOT NULL DEFAULT 0,
    PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  • [For MySQL backend] If you're using iRedMail-0.6.1 or order version, you can simply add two columns in table vmail.mailbox:
{{cmd|
$ mysql -uroot -p vmail
mysql> ALTER TABLE mailbox ADD COLUMN `currentbytes` BIGINT(20) NOT NULL DEFAULT 0;
mysql> ALTER TABLE mailbox ADD COLUMN `currentmessages` BIGINT(20) NOT NULL DEFAULT 0;
  • Create file /etc/dovecot-realtime-quota.conf:
File: /etc/dovecot-realtime-quota.conf 
connect = host=localhost dbname=iredadmin user=root password=passwd
map {
    pattern = priv/quota/storage
    table = realtime_quota
    username_field = username
    value_field = currentbytes
}
map {
    pattern = priv/quota/messages
    table = realtime_quota
    username_field = username
    value_field = currentmessages
}
  • Restart dovecot and it should work as expected now.

References

Retrieved from "http://www.iredmail.org/wiki/index.php?title=IRedMail/FAQ/Store.Realtime.Quota.In.MySQL"
Personal tools