Per User Mailbox Backup (OE Version): Difference between revisions
No edit summary |
|||
(17 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. | 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. | ||
== | === 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 | 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 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/ | * 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/ | * 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 xml.dom.minidom | import xml.dom.minidom | ||
import os | import os | ||
Line 51: | 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 | if key not in ( 'zimbra_ldap_userdn', 'zimbra_ldap_password', 'mysql_root_password' ): | ||
continue | continue | ||
L = node.getElementsByTagName("value") | L = node.getElementsByTagName("value") | ||
Line 64: | Line 74: | ||
# retrieval of users having specific ZimbraCOSId attribute | # retrieval of users having specific ZimbraCOSId attribute | ||
# change the value by '*' to fetch everyone | # 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, | ldap_result_id = l.search("'''dc=your,dc=domain'''", ldap.SCOPE_SUBTREE, searchFilter, retrieveAttributes) | ||
except ldap.LDAPError, error: | except ldap.LDAPError, error: | ||
l.unbind() | l.unbind() | ||
Line 78: | Line 89: | ||
user_ids = [] | user_ids = [] | ||
while | while True: | ||
result_type, result_data = l.result(ldap_result_id, 0) | result_type, result_data = l.result(ldap_result_id, 0) | ||
if | if result_data == []: | ||
break | break | ||
elif result_type == ldap.RES_SEARCH_ENTRY: | |||
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 | print >> userlist, user | ||
userlist.close() | userlist.close() | ||
Line 118: | Line 128: | ||
# step 2 | # step 2 | ||
id=`mysql -u root -p$MYPWD | 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 | 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 | ||
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 | done < /tmp/userlist.txt | ||
Line 154: | Line 176: | ||
* Messages are stored (by default) in /opt/zimbra/store, each user having a dedicated subdirectory | * 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) | * 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 : | * zimbra creates several databases : | ||
* one named '''zimbra''', containing metadata for all users | * one named '''zimbra''', containing metadata for all users | ||
* several prefixed by ''''mboxgroup'''', followed by a group id | * 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 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) | * 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. | 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)
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.