Per User Mailbox Backup (OE Version)

Revision as of 08:24, 24 April 2007 by Artturi (talk | contribs) (/usr/local/sbin/

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.

Method used

- 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 bewteen mysql 5 and mysql 4 client libraries)

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/mailboxes/... looks good
  • as zimbra user, run "mysql -u root -p < /var/dumps/mysql/zimbra/mailboxes/userid.sql"

The scripts


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

   import ldap
   import re
   import MySQLdb
   import exceptions
   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_passs
   word' ]:
     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
   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, searchh
   Filter, retrieveAttributes)
   except ldap.LDAPError, error:
     print 'problem with ldap',error

   user_ids = []
   while 1:
     result_type, result_data = l.result(ldap_result_id, 0)
     if (result_data == []):
       if 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:
     userlist.write( user + "\n")

   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
     id=`mysql -u root -p$MYPWD backup -e "SELECT id FROM zimbra.mailbox WHERE account_id='$user'" | tail -1 | awk '{print $1}' `
     groupid=`mysql -u root -p$MYPWD backup -e "SELECT group_id FROM zimbra.mailbox WHERE account_id='$user'" | tail -1 | awk '{print $1}' `
     echo $id $mailbox

     # 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/$id.sql
     echo "DELETE FROM mailbox WHERE id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql
     echo "DELETE FROM mailbox_metadata WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql
     /opt/zimbra/mysql/bin/mysqldump -h -P 7306  -n -t -u root -p$MYPWD backup mailbox mailbox_metadata >> /var/dumps/mysql/zimbra/mboxes/$id.sql

     echo "USE $mailbox" >> /var/dumps/mysql/zimbra/mboxes/$id.sql
     echo "DELETE FROM open_conversation WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql
     echo "DELETE FROM appointment WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql
     echo "DELETE FROM mail_item WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.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/mysqll
   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)
  • 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
  • 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.

Jump to: navigation, search