Account mailbox database structure

From Zimbra :: Wiki

Jump to: navigation, search

Zimbra uses mysql databases to store mail header information (To, From, Subject, Date, Read/Unread Status, Flags, Tags), contacts, calendar items, and tasks. Zimbra automatically assigns a zimbra account to a mailbox group when the account is created. The mailbox group corresponds to a database located in /opt/zimbra/db/data such as mboxgroup1, mboxgroup2, ...etc. There are a maximum of 100 mailbox groups per mailbox server.

Beginning with ZCS 7, Zimbra has added a local configuration parameter that causes the Zimbra Backup Process to perform periodic mysqldumps of the mysql data (mboxgroups) to facilitate recovery in the rare case of mysql database corruption. (zmlocalconfig –e mysql_backup_retention=<N>).

The mailbox group for the zimbra account is based on the mailbox id number. (The mailboxId is specific to the store, the zimbraId on the other hand is system wide). You can determine the mailbox id for an account (this example account is named "b@test.test") like this:

$ zmprov getMailboxInfo b@test.test
mailboxId: 5247
quotaUsed: 1951021

Mailbox users are members of mailbox groups on a rotation up to a maximum of 100 mailbox groups; you can determine the mailbox group by modulo division of the mailbox id by 100 (the remainder of dividing the mailbox id by 100, i.e only the last two digits). Note that if the result is zero, the mailbox group is 100, not 0.

$ expr 5247 % 100
47

Take a look at the mail_item table in the mailbox group database, particularly the date, size, sender, and subject columns.

$ mysql mboxgroup47
mysql> describe mail_item;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| mailbox_id   | int(10) unsigned    | NO   | PRI |         |       |
| id           | int(10) unsigned    | NO   | PRI |         |       |
| type         | tinyint(4)          | NO   |     |         |       |
| parent_id    | int(10) unsigned    | YES  |     | NULL    |       |
| folder_id    | int(10) unsigned    | YES  |     | NULL    |       |
| index_id     | int(10) unsigned    | YES  |     | NULL    |       |
| imap_id      | int(10) unsigned    | YES  |     | NULL    |       |
| date         | int(10) unsigned    | NO   |     |         |       |
| size         | int(10) unsigned    | NO   |     |         |       |
| volume_id    | tinyint(3) unsigned | YES  | MUL | NULL    |       |
| blob_digest  | varchar(28)         | YES  |     | NULL    |       |
| unread       | int(10) unsigned    | YES  |     | NULL    |       |
| flags        | int(11)             | NO   |     | 0       |       |
| tags         | bigint(20)          | NO   |     | 0       |       |
| sender       | varchar(128)        | YES  |     | NULL    |       |
| subject      | text                | YES  |     | NULL    |       |
| name         | varchar(128)        | YES  |     | NULL    |       |
| metadata     | text                | YES  |     | NULL    |       |
| mod_metadata | int(10) unsigned    | NO   |     |         |       |
| change_date  | int(10) unsigned    | YES  |     | NULL    |       |
| mod_content  | int(10) unsigned    | NO   |     |         |       |
+--------------+---------------------+------+-----+---------+-------+

Let's examine a single mail item.

mysql> select * from mail_item where id>26000 and mailbox_id=5247 limit 1 \G
*************************** 1. row ***************************
  mailbox_id: 5247
          id: 26001
        type: 5
   parent_id: NULL
   folder_id: 2
    index_id: 26001
     imap_id: 26001
        date: 1182380903
        size: 95842
   volume_id: 1
 blob_digest: rbrw+fj0tvyvTPt2haxssued7,A=
      unread: 1
       flags: 2
        tags: 0
      sender: sender@domain.com
     subject: Message subject
        name: NULL
    metadata: d1:f147:This message...1:p8:Re: SF: 1:s21:sender@domain.com1:vi10ee
mod_metadata: 30102
 change_date: 1182380905
 mod_content: 30102
1 row in set (0.00 sec)

With the volume_id, mailbox_id, id, and mod_content fields, you can determine the location of the message "blob" file. Volume 1 is the default message store:

mysql> select * from zimbra.volume;
+----+------+----------+-------------------+-----------+-----------------+--------------+--------------------+----------------+-----------------------+
| id | type | name     | path              | file_bits | file_group_bits | mailbox_bits | mailbox_group_bits | compress_blobs | compression_threshold |
+----+------+----------+-------------------+-----------+-----------------+--------------+--------------------+----------------+-----------------------+
|  1 |    1 | message1 | /opt/zimbra/store |        12 |               8 |           12 |                  8 |              0 |                  4096 |
|  2 |   10 | index1   | /opt/zimbra/index |        12 |               8 |           12 |                  8 |              0 |                  4096 |
+----+------+----------+-------------------+-----------+-----------------+--------------+--------------------+----------------+-----------------------+

On the filesystem, the user directories and the message directories within each user directory are split so that there are a maximum of 4096 (that is, 212) files in each directory. To determine the appropriate directory "hash" numbers, bitshift the mailbox_id and the mail item id to the right by 12 bits (i.e. divide by 212, rounding down). It is significant to note that if the id is less than 4096, the hash number will be 0.

$ perl -le 'print $ARGV[1] >> $ARGV[0]' 12 5247
1
$ perl -le 'print $ARGV[1] >> $ARGV[0]' 12 26001
6

The mod_content field is used to keep track of message blob file revisions. If a blob file is ever updated (for example, if a calendar appointment is modified), the filename and this field are updated. So, if the user with mailbox_id 5427 has a message on volume 1 with id 26001 and mod_content 30102, we'll see it like this:

$ ls -l /opt/zimbra/store/1/5247/msg/6/26001-30102.msg
-rw-r-----  1 zimbra zimbra 95842 Jun 20 16:08 /opt/zimbra/store/1/5247/msg/6/26001-30102.msg

Assuming only the default message store is in use, the filename for an item may be derived with a query.

mysql> select id, 
    concat('/opt/zimbra/store/', (mailbox_id >> 12), '/', mailbox_id, '/msg/',
    (id >> 12), '/', id, '-', mod_content, '.msg') as file
    from mail_item where mailbox_id="723" and id="261" limit 1;
+-----+-------------------------------------------+
| id  | file                                      |
+-----+-------------------------------------------+
| 261 | /opt/zimbra/store/0/723/msg/0/261-103.msg |
+-----+-------------------------------------------+

--NOTICE---, you must specify the mailbox_id, id and mod_content (these are placed in concat function) by hand and the mailbox_id, id which are placed in where condition are the mailbox_id and id you want to search;

--FIXME--, I had problems with ID's greater than 1048575 (which is 1024*1024 -1) for mail_items. It seems the calculation for the mail_item.id only allows upto 255 directories below the /msg/ directory. I had to modify the query like this to get correct values for the greater id's:

mysql> select id, 
    concat('/opt/zimbra/store/', (mailbox_id >> 12), '/', mailbox_id, '/msg/',
    (id - (floor(id / 1048575) * 1048575)  >> 12), '/', id, '-', mod_content, '.msg') as file
    from mail_item where mailbox_id="723" limit 1;

The floor(id / 1048575) * 1048575 part is for:

if id < 1048575, this evaluates to 0, so nothing happens

if id > 1048575, 1048575 will be subtracted

if id > 2097150, 2097150 will be subtracted

and so on, so you will have always a value from 1 to at most 1048575 for the bitshifting, which will result in the correct directory, at least for me in my data.

If someone has more insight please correct if it's not correct. Best Ray

A better way to do this is using the mod operator, ie (id % (1024*1024) >> 12) or for the complete query:

mysql> select id, 
    concat('/opt/zimbra/store/', (mailbox_id >> 12), '/', mailbox_id, '/msg/',
    (id % (1024*1024) >> 12), '/', id, '-', mod_content, '.msg') as file
    from mail_item where mailbox_id="723" limit 1;

-Reinard

Verified Against: zimbra 6.0.1_GA_1816 FOSS edition Date Created: 7/12/2007
Article ID: http://wiki.zimbra.com/index.php?title=Account_mailbox_database_structure Date Modified: 06/5/2014
Personal tools