Per User Mailbox Backup (OE Version)
From Zimbra :: Wiki
 - This article is a Work in Progress, and may be unfinished or missing sections.
|
| - This article is a community contribution and may include unsupported customizations.
|
This is actually *Work in progress*
Contents |
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 bewteen 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 re
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_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
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 1:
result_type, result_data = l.result(ldap_result_id, 0)
if result_data == []:
break
else:
if 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
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}' `
mailbox="mboxgroup$groupid"
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
# Line added to remove INTEGRITY CHECK for SUBSEQUENT RECOVERY otherwise you will be unable to restore (for ZCS 6.x)
echo "SET FOREIGN_KEY_CHECK = 0;" >>/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 127.0.0.1 -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 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/$id.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
- 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: http://wiki.zimbra.com/index.php?title=Per_User_Mailbox_Backup_%28OE_Version%29 | Date Modified: 11/29/2009 |
