IRedMail/FAQ/Store.Realtime.Quota.In.MySQL - Revision history

ZhangHuangbin: /* Test */

ZhangHuangbin — Sun, 27 May 2012 10:58:17 GMT

Test

← Older revision		Revision as of 10:58, 27 May 2012
Line 75:		Line 75:
	* Log into phpMyAdmin and check whether there's any records in table 'used_quota'. Sample data:		* Log into phpMyAdmin and check whether there's any records in table 'used_quota'. Sample data:
	{{cmd\|<pre>		{{cmd\|<pre>
-	mysql> select * from ~~realtime_quota~~;	+	mysql> select * from used_quota;
	+----------+-------+----------+		+----------+-------+----------+
	\| username \| bytes \| messages \|		\| username \| bytes \| messages \|

ZhangHuangbin: /* References */

ZhangHuangbin — Wed, 11 May 2011 06:25:36 GMT

References

← Older revision		Revision as of 06:25, 11 May 2011
Line 86:		Line 86:
	* Dovecot dictionary quota: http://wiki.dovecot.org/Quota/Dict		* Dovecot dictionary quota: http://wiki.dovecot.org/Quota/Dict
	* [http://www.iredmail.org/forum/topic1306-enh-mysqlbased-used-quota.html Article contributed by @maxie_ro, works with Dovecot-1.1.x]		* [http://www.iredmail.org/forum/topic1306-enh-mysqlbased-used-quota.html Article contributed by @maxie_ro, works with Dovecot-1.1.x]
		+
		+
		+	[[Category: iRedMail/FAQ]]
		+	[[Category: FAQ]]

ZhangHuangbin: /* Steps */

ZhangHuangbin — Tue, 12 Apr 2011 03:08:31 GMT

Steps

← Older revision		Revision as of 03:08, 12 April 2011
Line 42:		Line 42:
	mysql> ALTER TABLE mailbox ADD COLUMN `messages` BIGINT(20) NOT NULL DEFAULT 0;		mysql> ALTER TABLE mailbox ADD COLUMN `messages` BIGINT(20) NOT NULL DEFAULT 0;
	</pre>}}		</pre>}}
		+
		+	'''Note''': Column '''"bytes"''' stores size of all mails in user's mailbox, column '''"messages"''' stores number of all mails in user's mailbox.

	* Create file '''/etc/dovecot-used-quota.conf'''. Note:		* Create file '''/etc/dovecot-used-quota.conf'''. Note:

ZhangHuangbin: /* Requirements */

ZhangHuangbin — Thu, 14 Oct 2010 12:11:10 GMT

Requirements

← Older revision		Revision as of 12:11, 14 October 2010
Line 2:		Line 2:

	= Requirements =		= Requirements =
-	* Dovecot 1.2.x	+	* Dovecot 1.2.x. If you're using dovecot-1.1.x, please [[iRedMail/FAQ/Upgrade.Dovecot.1.1.to.1.2 \|Upgrade Dovecot 1.1.x to 1.2.x]] first.

	= Steps =		= Steps =

ZhangHuangbin: /* References */

ZhangHuangbin — Thu, 14 Oct 2010 12:07:06 GMT

References

← Older revision		Revision as of 12:07, 14 October 2010
Line 66:		Line 66:

	* Restart dovecot and it should work as expected now.		* Restart dovecot and it should work as expected now.
		+
		+	= Test =
		+	* Restart dovecot.
		+	* Restart postfix.
		+	* Log into webmail. Dovecot will update table 'used_quota' when mail arrived or removed.
		+	* Log into phpMyAdmin and check whether there's any records in table 'used_quota'. Sample data:
		+	{{cmd\|<pre>
		+	mysql> select * from realtime_quota;
		+	+----------+-------+----------+
		+	\| username \| bytes \| messages \|
		+	+----------+-------+----------+
		+	\| www@a.cn \| 9907 \| 2 \|
		+	+----------+-------+----------+
		+	</pre>}}

	= References =		= References =
	* Dovecot dictionary quota: http://wiki.dovecot.org/Quota/Dict		* Dovecot dictionary quota: http://wiki.dovecot.org/Quota/Dict
	* [http://www.iredmail.org/forum/topic1306-enh-mysqlbased-used-quota.html Article contributed by @maxie_ro, works with Dovecot-1.1.x]		* [http://www.iredmail.org/forum/topic1306-enh-mysqlbased-used-quota.html Article contributed by @maxie_ro, works with Dovecot-1.1.x]

ZhangHuangbin: /* Steps */

ZhangHuangbin — Thu, 14 Oct 2010 11:52:58 GMT

Steps

← Older revision		Revision as of 11:52, 14 October 2010
Line 44:		Line 44:

	* Create file '''/etc/dovecot-used-quota.conf'''. Note:		* Create file '''/etc/dovecot-used-quota.conf'''. Note:
-	** If you're using iRedAdmin (either Open Source Edition or iRedAdmin-Pro), you can find database user and password in its config file '''settings.ini''', under iRedAdmin installation root directory, in section '''[iredadmin]'''.	+	** If you're using iRedAdmin with OpenLDAP backend (either Open Source Edition or iRedAdmin-Pro), you can find database user and password in its config file '''settings.ini''', under iRedAdmin installation root directory, in section '''[iredadmin]'''.
	** Change '''dbname''', '''table''' if you're using MySQL backend.		** Change '''dbname''', '''table''' if you're using MySQL backend.

ZhangHuangbin: /* Steps */

ZhangHuangbin — Thu, 14 Oct 2010 11:52:34 GMT

Steps

← Older revision		Revision as of 11:52, 14 October 2010
Line 43:		Line 43:
	</pre>}}		</pre>}}

-	* Create file '''/etc/dovecot-used-quota.conf''' ('''~~Note~~''': Change '''dbname''', '''table''' if you're using MySQL backend):	+	* Create file '''/etc/dovecot-used-quota.conf'''. Note:
		+	** If you're using iRedAdmin (either Open Source Edition or iRedAdmin-Pro), you can find database user and password in its config file '''settings.ini''', under iRedAdmin installation root directory, in section '''[iredadmin]'''.
		+	** Change '''dbname''', '''table''' if you're using MySQL backend.
		+
	{{cfg\|/etc/dovecot-used-quota.conf\|<pre>		{{cfg\|/etc/dovecot-used-quota.conf\|<pre>
-	connect = host=localhost dbname=iredadmin user=~~root~~ password=passwd	+	connect = host=localhost dbname=iredadmin user=iredadmin password=passwd
	map {		map {
	pattern = priv/quota/storage		pattern = priv/quota/storage
Line 59:		Line 62:
	}		}
	</pre>}}		</pre>}}
		+
		+	Note: You can find

	* Restart dovecot and it should work as expected now.		* Restart dovecot and it should work as expected now.

ZhangHuangbin: /* Steps */

ZhangHuangbin — Wed, 13 Oct 2010 16:25:12 GMT

Steps

← Older revision		Revision as of 16:25, 13 October 2010
Line 19:		Line 19:
	{{cfg\|/etc/dovecot.conf\|<pre>		{{cfg\|/etc/dovecot.conf\|<pre>
	dict {		dict {
-	quotadict = mysql:/etc/dovecot-~~realtime~~-quota.conf # <- Add this line.	+	quotadict = mysql:/etc/dovecot-used-quota.conf # <- Add this line.

	# ... SKIP OTHER CONFIG HERE ...		# ... SKIP OTHER CONFIG HERE ...
Line 43:		Line 43:
	</pre>}}		</pre>}}

-	* Create file '''/etc/dovecot-~~realtime~~-quota.conf''' ('''Note''': Change '''dbname''', '''table''' if you're using MySQL backend):	+	* Create file '''/etc/dovecot-used-quota.conf''' ('''Note''': Change '''dbname''', '''table''' if you're using MySQL backend):
-	{{cfg\|/etc/dovecot-~~realtime~~-quota.conf\|<pre>	+	{{cfg\|/etc/dovecot-used-quota.conf\|<pre>
	connect = host=localhost dbname=iredadmin user=root password=passwd		connect = host=localhost dbname=iredadmin user=root password=passwd
	map {		map {

ZhangHuangbin: /* Steps */

ZhangHuangbin — Wed, 13 Oct 2010 16:23:46 GMT

Steps

← Older revision		Revision as of 16:23, 13 October 2010
Line 50:		Line 50:
	table = used_quota		table = used_quota
	username_field = username		username_field = username
-	value_field = ~~currentbytes~~	+	value_field = bytes
	}		}
	map {		map {
Line 56:		Line 56:
	table = used_quota		table = used_quota
	username_field = username		username_field = username
-	value_field = ~~currentmessages~~	+	value_field = messages
	}		}
	</pre>}}		</pre>}}

ZhangHuangbin: /* Steps */

ZhangHuangbin — Tue, 12 Oct 2010 15:57:34 GMT

Steps

← Older revision		Revision as of 15:57, 12 October 2010
Line 25:		Line 25:
	</pre>}}		</pre>}}

-	* '''[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'''.)	+	* '''[For OpenLDAP backend]''' Create MySQL tables '''used_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 `used_quota` (
	`username` VARCHAR(255) NOT NULL,		`username` VARCHAR(255) NOT NULL,
	`bytes` BIGINT(20) NOT NULL DEFAULT 0,		`bytes` BIGINT(20) NOT NULL DEFAULT 0,
Line 48:		Line 48:
	map {		map {
	pattern = priv/quota/storage		pattern = priv/quota/storage
-	table = ~~realtime_quota~~	+	table = used_quota
	username_field = username		username_field = username
	value_field = currentbytes		value_field = currentbytes
Line 54:		Line 54:
	map {		map {
	pattern = priv/quota/messages		pattern = priv/quota/messages
-	table = ~~realtime_quota~~	+	table = used_quota
	username_field = username		username_field = username
	value_field = currentmessages		value_field = currentmessages