Per User Mailbox Backup (OE Version)

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Per user Mailbox Backup (Open Source version)

   KB 1923        Last updated on 2015-07-12  

(0 votes)

This is actually *Work in progress*


This is a solution to backup mails on a user basis for the Open Source Version. It will permit to restore a mailstore for a particular user without having to restore the full /opt/zimbra directory.

It is based on a cold backup method.


- User concerned by this backup have a a specific C.O.S.
- first script connects to ldap server and builds the list of concerned users
- second script loops on the user list and creates a specific sql file with the appropriate commands for a restore

Why 2 scripts ? Because it was easy to use python for the ldap lookup but the mysql connection only worked via command line (version mismatch for the authentication between mysql 5 and mysql 4 python libraries)


  • python
  • python-ldap module
  • some python xml module

How to use it

For the backup

This is how I plan to use it :

  • stop zimbra
  • take lvm snapshot
  • start ldap & mysql services
  • run the specific scripts for user backup (su - zimbra -c "/usr/local/sbin/")
  • start zimbra completely
  • run the backup as usual on the snapshot
  • close the snapshot

For the restore

  • recover the mailstore and put it back on /opt/zimbra/store/...
  • check that the sql file in /var/dumps/mysql/zimbra/mboxes/... looks good
  • as zimbra user, run "mysql -u root -p < /var/dumps/mysql/zimbra/mboxes/userid.sql"

The scripts


You just have to change the lines in bold (mentioning the COS, ldap server ip and domain)

   import ldap
   import xml.dom.minidom
   import os
   from xml.dom.minidom import Node

   # scan of zimbra's configuration (user & password retrieval)
   doc = xml.dom.minidom.parse("/opt/zimbra/conf/localconfig.xml")
   mapping = {}

   for node in doc.getElementsByTagName("key"):
     key = node.getAttribute("name")
     if key not in ( 'zimbra_ldap_userdn', 'zimbra_ldap_password', 'mysql_root_password' ):
     L = node.getElementsByTagName("value")
     for node2 in L:
       title = ""
       for node3 in node2.childNodes:
         if node3.nodeType == Node.TEXT_NODE:
           title +=
       mapping[key] = title

   # retrieval of users having specific ZimbraCOSId attribute
   # change the value by '*' to fetch everyone
   # Obtain zimbraID (su - zimbra -c "zmprov gac -v | grep -e zimbraId: | awk '{print $2}'")
   # searchFilter = "(zimbraId=*)" #In zimbra 6.X
   searchFilter = "(zimbraCOSId=67890ffa-ef21-4d27-a79d-e49da536fc13)"
   retrieveAttributes = ['zimbraId', 'uid']

     l.protocol_version = ldap.VERSION3
     l.simple_bind(mapping['zimbra_ldap_userdn'], mapping['zimbra_ldap_password'])
     ldap_result_id ="dc=your,dc=domain", ldap.SCOPE_SUBTREE, searchFilter, retrieveAttributes)
   except ldap.LDAPError, error:
     print 'problem with ldap',error

   user_ids = []
   while True:
     result_type, result_data = l.result(ldap_result_id, 0)
     if result_data == []:
     elif result_type == ldap.RES_SEARCH_ENTRY:
       user_ids.append( result_data[0][1]['zimbraId'][0] )


   userlist = open("/tmp/userlist.txt", "w")

   for user in user_ids:
     print >> userlist, user

   os.system('/usr/local/sbin/ ' + mapping['mysql_root_password'])


   # This script creates a user based dump of the mysql database
   # steps :
   # 1 - loop on the user list (/tmp/userlist.txt)
   # 2 - in the loop, fetch all records related to the user
   # 3 - puts these records in a temp database (backup)
   # 4 - makes a sql file mixing output from mysqldump on "backup" db and manual sql commands


   # Output folder for the resulting sql files
   if [ ! -d /var/dumps/mysql/zimbra/mboxes/ ]; then
     mkdir -p /var/dumps/mysql/zimbra/mboxes/

   # step 1
   while read user

     # step 2
     file_name=`mysql -u root -p$MYPWD -e "SELECT comment FROM zimbra.mailbox WHERE account_id='$user'" | tail -1 | awk '{print $1}' | awk 'BEGIN{FS="@"}{print $1}' `
     id=`mysql -u root -p$MYPWD -e "SELECT id FROM zimbra.mailbox WHERE account_id='$user'" | tail -1 | awk '{print $1}' `
     groupid=`mysql -u root -p$MYPWD -e "SELECT group_id FROM zimbra.mailbox WHERE account_id='$user'" | tail -1 | awk '{print $1}' `
     echo $file_name $id $mailbox
     if [ -z "$file_name" ];then
       echo "Not a real user"
       # step 3
       mysql -u root -p$MYPWD -e 'DROP DATABASE backup'
       mysql -u root -p$MYPWD -e 'CREATE DATABASE backup'
       mysql -u root -p$MYPWD backup -e "CREATE TABLE mailbox AS SELECT * FROM zimbra.mailbox WHERE id='$id'"
       mysql -u root -p$MYPWD backup -e "CREATE TABLE mailbox_metadata AS SELECT md.* FROM zimbra.mailbox_metadata md WHERE md.mailbox_id='$id'"
       mysql -u root -p$MYPWD backup -e "CREATE TABLE appointment AS SELECT * FROM $mailbox.appointment WHERE mailbox_id=$id"
       mysql -u root -p$MYPWD backup -e "CREATE TABLE mail_item AS SELECT * FROM $mailbox.mail_item WHERE mailbox_id=$id"
       mysql -u root -p$MYPWD backup -e "CREATE TABLE open_conversation AS SELECT * FROM $mailbox.open_conversation WHERE mailbox_id=$id"
       mysql -u root -p$MYPWD backup -e "CREATE TABLE pop3_message AS SELECT * FROM $mailbox.pop3_message WHERE mailbox_id=$id"
       # step 4
       echo "USE zimbra" > /var/dumps/mysql/zimbra/mboxes/$file_name.sql
       # Line added to remove INTEGRITY CHECK for SUBSEQUENT RECOVERY otherwise you will be unable to restore (for ZCS 6.x)
       echo "SET FOREIGN_KEY_CHECKS = 0;" >>/var/dumps/mysql/zimbra/mboxes/$file_name.sql
       echo "DELETE FROM mailbox WHERE id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$file_name.sql
       echo "DELETE FROM mailbox_metadata WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$file_name.sql
       /opt/zimbra/mysql/bin/mysqldump -h -P 7306  -n -t -u root -p$MYPWD backup mailbox mailbox_metadata >> /var/dumps/mysql/zimbra/mboxes/$file_name.sql
       echo "USE $mailbox" >> /var/dumps/mysql/zimbra/mboxes/$file_name.sql
       echo "DELETE FROM open_conversation WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$file_name.sql
       echo "DELETE FROM appointment WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$file_name.sql
       echo "DELETE FROM mail_item WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$file_name.sql
       /opt/zimbra/mysql/bin/mysqldump -h -P 7306  -n -t -u root -p$MYPWD backup pop3_message open_conversation  appointment mail_item  >> /var/dumps/mysql/zimbra/mboxes/$file_name.sql
   done < /tmp/userlist.txt

Zimbra internals

To understand these scripts, here's some background information I've come to learn/guess through the forums and the zimbra's database structure :

  • Messages are stored (by default) in /opt/zimbra/store, each user having a dedicated subdirectory
  • It seems appointments are stored in the same folder but at this stage, this is a guess (if you know, please edit)
  • Hierarchy of messages is not in the filesystem but in mysql databases
  • zimbra creates several databases :
 * one named zimbra, containing metadata for all users
 * several prefixed by 'mboxgroup', followed by a group id
  • each user belongs to a group_id, and its related infos are therefore stored in database mboxgroup+group_id. There are a maximum of 100 user databases, so each mboxgroup+group_id database may contain more than 1 user, e.g. mboxgroup3 could contain user_id 3, 103, 203, etc.
  • each user has an object in the ldap server with several attributes (in particular, the ZimbraCOSId one)

Please take into account that this is not 100% sure information and at this date (2007-04-24), this should be tested before anyone rely on this.

Verified Against: unknown Date Created: 4/23/2007
Article ID: Date Modified: 2015-07-12

Try Zimbra

Try Zimbra Collaboration with a 60-day free trial.
Get it now »

Want to get involved?

You can contribute in the Community, Wiki, Code, or development of Zimlets.
Find out more. »

Looking for a Video?

Visit our YouTube channel to get the latest webinars, technology news, product overviews, and so much more.
Go to the YouTube channel »

Jump to: navigation, search