Per User Mailbox Backup (OE Version): Difference between revisions

mNo edit summary
 
No edit summary
 
(43 intermediate revisions by 9 users not shown)
Line 1: Line 1:
{{BC|Community Sandbox}}
__FORCETOC__
<div class="col-md-12 ibox-content">
=Per user Mailbox Backup (Open Source version)=
{{KB|{{Unsupported}}|{{ZCS 7.0}}|{{ZCS 6.0}}|}}
{{Archive}}{{WIP}}
This is actually *Work in progress*
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.
'''
{{Article Footer|unknown|4/23/2007}}
[[Category:Backup and Restore]]

Latest revision as of 20:05, 12 July 2015

Per user Mailbox Backup (Open Source version)

   KB 1923        Last updated on 2015-07-12  




0.00
(0 votes)


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.

Verified Against: unknown Date Created: 4/23/2007
Article ID: https://wiki.zimbra.com/index.php?title=Per_User_Mailbox_Backup_(OE_Version) 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