Difference between revisions of "Per User Mailbox Backup (OE Version)"

m (zim_backGetList.py)
 
(24 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*
  
== Scope ==
+
== 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.
 
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.
 
It is based on a cold backup method.
  
== Method used ==
+
=== Summary ===
  
 
  - User concerned by this backup have a a specific C.O.S.
 
  - User concerned by this backup have a a specific C.O.S.
Line 12: Line 20:
 
  - second script loops on the user list and creates a specific sql file with the appropriate commands for a restore
 
  - 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)
+
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 ==
 
== How to use it ==
Line 24: Line 37:
 
* run the specific scripts for user backup (su - zimbra -c "/usr/local/sbin/zim_backGetList.py")
 
* run the specific scripts for user backup (su - zimbra -c "/usr/local/sbin/zim_backGetList.py")
 
* start zimbra completely
 
* start zimbra completely
* run the backup as usual
+
* run the backup as usual on the snapshot
 
* close the snapshot
 
* close the snapshot
  
Line 30: Line 43:
  
 
* recover the mailstore and put it back on /opt/zimbra/store/...
 
* recover the mailstore and put it back on /opt/zimbra/store/...
* check that the sql file in /var/dumps/mysql/zimbra/mailboxes/... looks good
+
* 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/mailboxes/userid.sql"
+
* as zimbra user, run "mysql -u root -p < /var/dumps/mysql/zimbra/mboxes/userid.sql"
  
 
== The scripts ==
 
== The scripts ==
Line 39: Line 52:
 
     #!/usr/bin/python
 
     #!/usr/bin/python
 
     import ldap
 
     import ldap
    import re
 
    import MySQLdb
 
    import exceptions
 
 
     import xml.dom.minidom
 
     import xml.dom.minidom
 
     import os
 
     import os
 
     from xml.dom.minidom import Node
 
     from xml.dom.minidom import Node
 
   
 
   
     # scan de la configuration de zimbra
+
     # scan of zimbra's configuration (user & password retrieval)
 
     doc = xml.dom.minidom.parse("/opt/zimbra/conf/localconfig.xml")
 
     doc = xml.dom.minidom.parse("/opt/zimbra/conf/localconfig.xml")
 
     mapping = {}
 
     mapping = {}
Line 52: Line 62:
 
     for node in doc.getElementsByTagName("key"):
 
     for node in doc.getElementsByTagName("key"):
 
       key = node.getAttribute("name")
 
       key = node.getAttribute("name")
       if key not in [ 'zimbra_ldap_userdn', 'zimbra_ldap_password', 'mysql_root_passs
+
       if key not in ( 'zimbra_ldap_userdn', 'zimbra_ldap_password', 'mysql_root_password' ):
    word' ]:
 
 
         continue
 
         continue
 
       L = node.getElementsByTagName("value")
 
       L = node.getElementsByTagName("value")
Line 63: Line 72:
 
         mapping[key] = title
 
         mapping[key] = title
 
   
 
   
     # recuperation des account_id ayant la COS avec suffixe .backup
+
     # 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''')"
 
     searchFilter = "(zimbraCOSId='''67890ffa-ef21-4d27-a79d-e49da536fc13''')"
 
     retrieveAttributes = ['zimbraId', 'uid']
 
     retrieveAttributes = ['zimbraId', 'uid']
Line 71: Line 83:
 
       l.protocol_version = ldap.VERSION3
 
       l.protocol_version = ldap.VERSION3
 
       l.simple_bind(mapping['zimbra_ldap_userdn'], mapping['zimbra_ldap_password'])
 
       l.simple_bind(mapping['zimbra_ldap_userdn'], mapping['zimbra_ldap_password'])
       ldap_result_id = l.search("dc=your,dc=domain", ldap.SCOPE_SUBTREE, searchh
+
       ldap_result_id = l.search("'''dc=your,dc=domain'''", ldap.SCOPE_SUBTREE, searchFilter, retrieveAttributes)
    Filter, retrieveAttributes)
 
 
     except ldap.LDAPError, error:
 
     except ldap.LDAPError, error:
 
       l.unbind()
 
       l.unbind()
Line 78: Line 89:
 
   
 
   
 
     user_ids = []
 
     user_ids = []
     while 1:
+
     while True:
 
       result_type, result_data = l.result(ldap_result_id, 0)
 
       result_type, result_data = l.result(ldap_result_id, 0)
       if (result_data == []):
+
       if result_data == []:
 
         break
 
         break
       else:
+
       elif result_type == ldap.RES_SEARCH_ENTRY:
        if result_type == ldap.RES_SEARCH_ENTRY:
+
        user_ids.append( result_data[0][1]['zimbraId'][0] )
          user_ids.append( result_data[0][1]['zimbraId'][0] )
 
 
   
 
   
 
     l.unbind()
 
     l.unbind()
Line 91: Line 101:
 
   
 
   
 
     for user in user_ids:
 
     for user in user_ids:
       userlist.write( user + "\n")
+
       print >> userlist, user
 
     userlist.close()
 
     userlist.close()
 
   
 
   
Line 118: Line 128:
 
   
 
   
 
       # step 2
 
       # step 2
       id=`mysql -u root -p$MYPWD backup -e "SELECT id FROM zimbra.mailbox WHERE account_id='$user'" | tail -1 | awk '{print $1}' `
+
      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}' `
       groupid=`mysql -u root -p$MYPWD backup -e "SELECT group_id FROM zimbra.mailbox WHERE account_id='$user'" | tail -1 | awk '{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"
 
       mailbox="mboxgroup$groupid"
       echo $id $mailbox
+
       echo $file_name $id $mailbox
+
   
       # step 3
+
      if [ -z "$file_name" ];then
      mysql -u root -p$MYPWD -e 'DROP DATABASE backup'
+
   
      mysql -u root -p$MYPWD -e 'CREATE DATABASE backup'
+
        echo "Not a real user"
      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'"
+
       else
+
   
      mysql -u root -p$MYPWD backup -e "CREATE TABLE appointment AS SELECT * FROM $mailbox.appointment WHERE mailbox_id=$id"
+
        # step 3
      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 -e 'DROP DATABASE backup'
      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 -e 'CREATE DATABASE backup'
      mysql -u root -p$MYPWD backup -e "CREATE TABLE pop3_message AS SELECT * FROM $mailbox.pop3_message WHERE mailbox_id=$id"
+
        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'"
      # step 4
+
   
      echo "USE zimbra" > /var/dumps/mysql/zimbra/mboxes/$id.sql
+
        mysql -u root -p$MYPWD backup -e "CREATE TABLE appointment AS SELECT * FROM $mailbox.appointment WHERE mailbox_id=$id"
      echo "DELETE FROM mailbox WHERE id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql
+
        mysql -u root -p$MYPWD backup -e "CREATE TABLE mail_item AS SELECT * FROM $mailbox.mail_item WHERE mailbox_id=$id"
      echo "DELETE FROM mailbox_metadata WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql
+
        mysql -u root -p$MYPWD backup -e "CREATE TABLE open_conversation AS SELECT * FROM $mailbox.open_conversation WHERE mailbox_id=$id"
      /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/$id.sql
+
        mysql -u root -p$MYPWD backup -e "CREATE TABLE pop3_message AS SELECT * FROM $mailbox.pop3_message WHERE mailbox_id=$id"
+
   
      echo "USE $mailbox" >> /var/dumps/mysql/zimbra/mboxes/$id.sql
+
        # step 4
      echo "DELETE FROM open_conversation WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql
+
        echo "USE zimbra" > /var/dumps/mysql/zimbra/mboxes/$file_name.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
+
        # Line added to remove INTEGRITY CHECK for SUBSEQUENT RECOVERY otherwise you will be unable to restore (for ZCS 6.x)
      /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/mysqll
+
        echo "SET FOREIGN_KEY_CHECKS = 0;" >>/var/dumps/mysql/zimbra/mboxes/$file_name.sql
    /zimbra/mboxes/$id.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
 
     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