Per User Mailbox Backup (OE Version)

1. Zimbra Tech Center
2. Community Sandbox
3. Per User Mailbox Backup (OE Version)

Per user Mailbox Backup (Open Source version)

 - This is archive documentation, which means it is not supported or valid for recent versions of Zimbra Collaboration.

 - This article is a Work in Progress, and may be unfinished or missing sections.

This is actually *Work in progress*

Introduction

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.

Summary

- 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)

Requirements

• 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/zim_backGetList.py")
• 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

/usr/local/sbin/zim_backGetList.py

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

   #!/usr/bin/python
   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' ):
       continue
     L = node.getElementsByTagName("value")
     for node2 in L:
       title = ""
       for node3 in node2.childNodes:
         if node3.nodeType == Node.TEXT_NODE:
           title += node3.data
       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']

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

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

   l.unbind()

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

   for user in user_ids:
     print >> userlist, user
   userlist.close()

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

/usr/local/sbin/zim_DoBackup.sh

   #!/bin/bash
   # 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

   MYPWD=$1

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

   # step 1
   while read user
   do

     # 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}' `
     mailbox="mboxgroup$groupid"
     echo $file_name $id $mailbox
    
     if [ -z "$file_name" ];then
   
       echo "Not a real user"
   
     else
   
       # 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 127.0.0.1 -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 127.0.0.1 -P 7306  -n -t -u root -p$MYPWD backup pop3_message open_conversation  appointment mail_item  >> /var/dumps/mysql/zimbra/mboxes/$file_name.sql
       
     fi
   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.