https://wiki.zimbra.com/api.php?action=feedcontributions&user=Leocor&feedformat=atomZimbra :: Tech Center - User contributions [en]2024-03-29T09:27:03ZUser contributionsMediaWiki 1.39.0https://wiki.zimbra.com/index.php?title=Per_User_Mailbox_Backup_(OE_Version)&diff=18165Per User Mailbox Backup (OE Version)2010-02-23T09:24:45Z<p>Leocor: /* /usr/local/sbin/zim_DoBackup.sh */</p>
<hr />
<div>{{WIP}}<br />
{{Unsupported}}<br />
<br />
This is actually *Work in progress*<br />
<br />
== Introduction ==<br />
<br />
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.<br />
<br />
It is based on a cold backup method.<br />
<br />
=== Summary ===<br />
<br />
- User concerned by this backup have a a specific C.O.S.<br />
- first script connects to ldap server and builds the list of concerned users<br />
- second script loops on the user list and creates a specific sql file with the appropriate commands for a restore<br />
<br />
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)<br />
<br />
=== Requirements ===<br />
* python<br />
* python-ldap module<br />
* some python xml module<br />
<br />
== How to use it ==<br />
<br />
=== For the backup ===<br />
This is how I plan to use it :<br />
<br />
* stop zimbra<br />
* take lvm snapshot<br />
* start ldap & mysql services<br />
* run the specific scripts for user backup (su - zimbra -c "/usr/local/sbin/zim_backGetList.py")<br />
* start zimbra completely<br />
* run the backup as usual on the snapshot<br />
* close the snapshot<br />
<br />
=== For the restore ===<br />
<br />
* recover the mailstore and put it back on /opt/zimbra/store/...<br />
* check that the sql file in /var/dumps/mysql/zimbra/mboxes/... looks good<br />
* as zimbra user, run "mysql -u root -p < /var/dumps/mysql/zimbra/mboxes/userid.sql"<br />
<br />
== The scripts ==<br />
=== /usr/local/sbin/zim_backGetList.py ===<br />
<br />
You just have to change the lines in bold (mentioning the COS, ldap server ip and domain)<br />
#!/usr/bin/python<br />
import ldap<br />
import re<br />
import exceptions<br />
import xml.dom.minidom<br />
import os<br />
from xml.dom.minidom import Node<br />
<br />
# scan of zimbra's configuration (user & password retrieval)<br />
doc = xml.dom.minidom.parse("/opt/zimbra/conf/localconfig.xml")<br />
mapping = {}<br />
<br />
for node in doc.getElementsByTagName("key"):<br />
key = node.getAttribute("name")<br />
if key not in [ 'zimbra_ldap_userdn', 'zimbra_ldap_password', 'mysql_root_password' ]:<br />
continue<br />
L = node.getElementsByTagName("value")<br />
for node2 in L:<br />
title = ""<br />
for node3 in node2.childNodes:<br />
if node3.nodeType == Node.TEXT_NODE:<br />
title += node3.data<br />
mapping[key] = title<br />
<br />
# retrieval of users having specific ZimbraCOSId attribute<br />
# change the value by '*' to fetch everyone<br />
searchFilter = "(zimbraCOSId='''67890ffa-ef21-4d27-a79d-e49da536fc13''')"<br />
retrieveAttributes = ['zimbraId', 'uid']<br />
<br />
try:<br />
l=ldap.open(''''ldap-server-ip'''')<br />
l.protocol_version = ldap.VERSION3<br />
l.simple_bind(mapping['zimbra_ldap_userdn'], mapping['zimbra_ldap_password'])<br />
ldap_result_id = l.search("'''dc=your,dc=domain'''", ldap.SCOPE_SUBTREE, searchFilter, retrieveAttributes)<br />
except ldap.LDAPError, error:<br />
l.unbind()<br />
print 'problem with ldap',error<br />
<br />
user_ids = []<br />
while 1:<br />
result_type, result_data = l.result(ldap_result_id, 0)<br />
if result_data == []:<br />
break<br />
else:<br />
if result_type == ldap.RES_SEARCH_ENTRY:<br />
user_ids.append( result_data[0][1]['zimbraId'][0] )<br />
<br />
l.unbind()<br />
<br />
userlist = open("/tmp/userlist.txt", "w")<br />
<br />
for user in user_ids:<br />
print >> userlist, user<br />
userlist.close()<br />
<br />
os.system('/usr/local/sbin/zim_DoBackup.sh ' + mapping['mysql_root_password'])<br />
<br />
=== /usr/local/sbin/zim_DoBackup.sh ===<br />
<br />
#!/bin/bash<br />
# This script creates a user based dump of the mysql database<br />
# steps :<br />
# 1 - loop on the user list (/tmp/userlist.txt)<br />
# 2 - in the loop, fetch all records related to the user<br />
# 3 - puts these records in a temp database (backup)<br />
# 4 - makes a sql file mixing output from mysqldump on "backup" db and manual sql commands<br />
<br />
MYPWD=$1<br />
<br />
# Output folder for the resulting sql files<br />
if [ ! -d /var/dumps/mysql/zimbra/mboxes/ ]; then<br />
mkdir -p /var/dumps/mysql/zimbra/mboxes/<br />
fi<br />
<br />
# step 1<br />
while read user<br />
do<br />
<br />
# step 2<br />
id=`mysql -u root -p$MYPWD backup -e "SELECT id FROM zimbra.mailbox WHERE account_id='$user'" | tail -1 | awk '{print $1}' `<br />
groupid=`mysql -u root -p$MYPWD backup -e "SELECT group_id FROM zimbra.mailbox WHERE account_id='$user'" | tail -1 | awk '{print $1}' `<br />
mailbox="mboxgroup$groupid"<br />
echo $id $mailbox<br />
<br />
# step 3<br />
mysql -u root -p$MYPWD -e 'DROP DATABASE backup'<br />
mysql -u root -p$MYPWD -e 'CREATE DATABASE backup'<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE mailbox AS SELECT * FROM zimbra.mailbox WHERE id='$id'"<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE mailbox_metadata AS SELECT md.* FROM zimbra.mailbox_metadata md WHERE md.mailbox_id='$id'"<br />
<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE appointment AS SELECT * FROM $mailbox.appointment WHERE mailbox_id=$id"<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE mail_item AS SELECT * FROM $mailbox.mail_item WHERE mailbox_id=$id"<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE open_conversation AS SELECT * FROM $mailbox.open_conversation WHERE mailbox_id=$id"<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE pop3_message AS SELECT * FROM $mailbox.pop3_message WHERE mailbox_id=$id"<br />
<br />
# step 4<br />
echo "USE zimbra" > /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
<br />
# Line added to remove INTEGRITY CHECK for SUBSEQUENT RECOVERY otherwise you will be unable to restore (for ZCS 6.x)<br />
echo "SET FOREIGN_KEY_CHECKS = 0;" >>/var/dumps/mysql/zimbra/mboxes/$id.sql<br />
<br />
echo "DELETE FROM mailbox WHERE id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
echo "DELETE FROM mailbox_metadata WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
/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<br />
<br />
echo "USE $mailbox" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
echo "DELETE FROM open_conversation WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
echo "DELETE FROM appointment WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
echo "DELETE FROM mail_item WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
/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<br />
done < /tmp/userlist.txt<br />
<br />
== Zimbra internals ==<br />
<br />
To understand these scripts, here's some background information I've come to learn/guess through the forums and the zimbra's database structure :<br />
<br />
* Messages are stored (by default) in /opt/zimbra/store, each user having a dedicated subdirectory<br />
* It seems appointments are stored in the same folder but at this stage, this is a guess (if you know, please edit)<br />
* Hierarchy of messages is not in the filesystem but in mysql databases<br />
* zimbra creates several databases :<br />
* one named '''zimbra''', containing metadata for all users<br />
* several prefixed by ''''mboxgroup'''', followed by a group id<br />
* each user belongs to a group_id, and its related infos are therefore stored in database mboxgroup+group_id<br />
* each user has an object in the ldap server with several attributes (in particular, the ZimbraCOSId one)<br />
<br />
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.<br />
'''<br />
<br />
{{Article Footer|unknown|4/23/2007}}<br />
<br />
[[Category:Backup and Restore]]</div>Leocorhttps://wiki.zimbra.com/index.php?title=Per_User_Mailbox_Backup_(OE_Version)&diff=15291Per User Mailbox Backup (OE Version)2009-11-11T16:12:35Z<p>Leocor: /* /usr/local/sbin/zim_DoBackup.sh */</p>
<hr />
<div>{{WIP}}<br />
{{Unsupported}}<br />
<br />
This is actually *Work in progress*<br />
<br />
== Introduction ==<br />
<br />
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.<br />
<br />
It is based on a cold backup method.<br />
<br />
=== Summary ===<br />
<br />
- User concerned by this backup have a a specific C.O.S.<br />
- first script connects to ldap server and builds the list of concerned users<br />
- second script loops on the user list and creates a specific sql file with the appropriate commands for a restore<br />
<br />
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)<br />
<br />
=== Requirements ===<br />
* python<br />
* python-ldap module<br />
* some python xml module<br />
<br />
== How to use it ==<br />
<br />
=== For the backup ===<br />
This is how I plan to use it :<br />
<br />
* stop zimbra<br />
* take lvm snapshot<br />
* start ldap & mysql services<br />
* run the specific scripts for user backup (su - zimbra -c "/usr/local/sbin/zim_backGetList.py")<br />
* start zimbra completely<br />
* run the backup as usual on the snapshot<br />
* close the snapshot<br />
<br />
=== For the restore ===<br />
<br />
* recover the mailstore and put it back on /opt/zimbra/store/...<br />
* check that the sql file in /var/dumps/mysql/zimbra/mboxes/... looks good<br />
* as zimbra user, run "mysql -u root -p < /var/dumps/mysql/zimbra/mboxes/userid.sql"<br />
<br />
== The scripts ==<br />
=== /usr/local/sbin/zim_backGetList.py ===<br />
<br />
You just have to change the lines in bold (mentioning the COS, ldap server ip and domain)<br />
#!/usr/bin/python<br />
import ldap<br />
import re<br />
import exceptions<br />
import xml.dom.minidom<br />
import os<br />
from xml.dom.minidom import Node<br />
<br />
# scan of zimbra's configuration (user & password retrieval)<br />
doc = xml.dom.minidom.parse("/opt/zimbra/conf/localconfig.xml")<br />
mapping = {}<br />
<br />
for node in doc.getElementsByTagName("key"):<br />
key = node.getAttribute("name")<br />
if key not in [ 'zimbra_ldap_userdn', 'zimbra_ldap_password', 'mysql_root_password' ]:<br />
continue<br />
L = node.getElementsByTagName("value")<br />
for node2 in L:<br />
title = ""<br />
for node3 in node2.childNodes:<br />
if node3.nodeType == Node.TEXT_NODE:<br />
title += node3.data<br />
mapping[key] = title<br />
<br />
# retrieval of users having specific ZimbraCOSId attribute<br />
# change the value by '*' to fetch everyone<br />
searchFilter = "(zimbraCOSId='''67890ffa-ef21-4d27-a79d-e49da536fc13''')"<br />
retrieveAttributes = ['zimbraId', 'uid']<br />
<br />
try:<br />
l=ldap.open(''''ldap-server-ip'''')<br />
l.protocol_version = ldap.VERSION3<br />
l.simple_bind(mapping['zimbra_ldap_userdn'], mapping['zimbra_ldap_password'])<br />
ldap_result_id = l.search("'''dc=your,dc=domain'''", ldap.SCOPE_SUBTREE, searchFilter, retrieveAttributes)<br />
except ldap.LDAPError, error:<br />
l.unbind()<br />
print 'problem with ldap',error<br />
<br />
user_ids = []<br />
while 1:<br />
result_type, result_data = l.result(ldap_result_id, 0)<br />
if (result_data == []):<br />
break<br />
else:<br />
if result_type == ldap.RES_SEARCH_ENTRY:<br />
user_ids.append( result_data[0][1]['zimbraId'][0] )<br />
<br />
l.unbind()<br />
<br />
userlist = open("/tmp/userlist.txt", "w")<br />
<br />
for user in user_ids:<br />
userlist.write( user + "\n")<br />
userlist.close()<br />
<br />
os.system('/usr/local/sbin/zim_DoBackup.sh ' + mapping['mysql_root_password'])<br />
<br />
=== /usr/local/sbin/zim_DoBackup.sh ===<br />
<br />
#!/bin/bash<br />
# This script creates a user based dump of the mysql database<br />
# steps :<br />
# 1 - loop on the user list (/tmp/userlist.txt)<br />
# 2 - in the loop, fetch all records related to the user<br />
# 3 - puts these records in a temp database (backup)<br />
# 4 - makes a sql file mixing output from mysqldump on "backup" db and manual sql commands<br />
<br />
MYPWD=$1<br />
<br />
# Output folder for the resulting sql files<br />
if [ ! -d /var/dumps/mysql/zimbra/mboxes/ ]; then<br />
mkdir -p /var/dumps/mysql/zimbra/mboxes/<br />
fi<br />
<br />
# step 1<br />
while read user<br />
do<br />
<br />
# step 2<br />
id=`mysql -u root -p$MYPWD backup -e "SELECT id FROM zimbra.mailbox WHERE account_id='$user'" | tail -1 | awk '{print $1}' `<br />
groupid=`mysql -u root -p$MYPWD backup -e "SELECT group_id FROM zimbra.mailbox WHERE account_id='$user'" | tail -1 | awk '{print $1}' `<br />
mailbox="mboxgroup$groupid"<br />
echo $id $mailbox<br />
<br />
# step 3<br />
mysql -u root -p$MYPWD -e 'DROP DATABASE backup'<br />
mysql -u root -p$MYPWD -e 'CREATE DATABASE backup'<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE mailbox AS SELECT * FROM zimbra.mailbox WHERE id='$id'"<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE mailbox_metadata AS SELECT md.* FROM zimbra.mailbox_metadata md WHERE md.mailbox_id='$id'"<br />
<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE appointment AS SELECT * FROM $mailbox.appointment WHERE mailbox_id=$id"<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE mail_item AS SELECT * FROM $mailbox.mail_item WHERE mailbox_id=$id"<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE open_conversation AS SELECT * FROM $mailbox.open_conversation WHERE mailbox_id=$id"<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE pop3_message AS SELECT * FROM $mailbox.pop3_message WHERE mailbox_id=$id"<br />
<br />
# step 4<br />
echo "USE zimbra" > /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
<br />
# Line added to remove INTEGRITY CHECK for SUBSEQUENT RECOVERY otherwise you will be unable to restore (for ZCS 6.x)<br />
echo "SET FOREIGN_KEY_CHECK = 0;" >>/var/dumps/mysql/zimbra/mboxes/$id.sql<br />
<br />
echo "DELETE FROM mailbox WHERE id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
echo "DELETE FROM mailbox_metadata WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
/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<br />
<br />
echo "USE $mailbox" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
echo "DELETE FROM open_conversation WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
echo "DELETE FROM appointment WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
echo "DELETE FROM mail_item WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
/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<br />
done < /tmp/userlist.txt<br />
<br />
== Zimbra internals ==<br />
<br />
To understand these scripts, here's some background information I've come to learn/guess through the forums and the zimbra's database structure :<br />
<br />
* Messages are stored (by default) in /opt/zimbra/store, each user having a dedicated subdirectory<br />
* It seems appointments are stored in the same folder but at this stage, this is a guess (if you know, please edit)<br />
* Hierarchy of messages is not in the filesystem but in mysql databases<br />
* zimbra creates several databases :<br />
* one named '''zimbra''', containing metadata for all users<br />
* several prefixed by ''''mboxgroup'''', followed by a group id<br />
* each user belongs to a group_id, and its related infos are therefore stored in database mboxgroup+group_id<br />
* each user has an object in the ldap server with several attributes (in particular, the ZimbraCOSId one)<br />
<br />
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.<br />
'''<br />
<br />
{{Article Footer|unknown|4/23/2007}}<br />
<br />
[[Category:Backup and Restore]]</div>Leocorhttps://wiki.zimbra.com/index.php?title=Per_User_Mailbox_Backup_(OE_Version)&diff=15290Per User Mailbox Backup (OE Version)2009-11-11T15:58:08Z<p>Leocor: /* /usr/local/sbin/zim_DoBackup.sh */</p>
<hr />
<div>{{WIP}}<br />
{{Unsupported}}<br />
<br />
This is actually *Work in progress*<br />
<br />
== Introduction ==<br />
<br />
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.<br />
<br />
It is based on a cold backup method.<br />
<br />
=== Summary ===<br />
<br />
- User concerned by this backup have a a specific C.O.S.<br />
- first script connects to ldap server and builds the list of concerned users<br />
- second script loops on the user list and creates a specific sql file with the appropriate commands for a restore<br />
<br />
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)<br />
<br />
=== Requirements ===<br />
* python<br />
* python-ldap module<br />
* some python xml module<br />
<br />
== How to use it ==<br />
<br />
=== For the backup ===<br />
This is how I plan to use it :<br />
<br />
* stop zimbra<br />
* take lvm snapshot<br />
* start ldap & mysql services<br />
* run the specific scripts for user backup (su - zimbra -c "/usr/local/sbin/zim_backGetList.py")<br />
* start zimbra completely<br />
* run the backup as usual on the snapshot<br />
* close the snapshot<br />
<br />
=== For the restore ===<br />
<br />
* recover the mailstore and put it back on /opt/zimbra/store/...<br />
* check that the sql file in /var/dumps/mysql/zimbra/mboxes/... looks good<br />
* as zimbra user, run "mysql -u root -p < /var/dumps/mysql/zimbra/mboxes/userid.sql"<br />
<br />
== The scripts ==<br />
=== /usr/local/sbin/zim_backGetList.py ===<br />
<br />
You just have to change the lines in bold (mentioning the COS, ldap server ip and domain)<br />
#!/usr/bin/python<br />
import ldap<br />
import re<br />
import exceptions<br />
import xml.dom.minidom<br />
import os<br />
from xml.dom.minidom import Node<br />
<br />
# scan of zimbra's configuration (user & password retrieval)<br />
doc = xml.dom.minidom.parse("/opt/zimbra/conf/localconfig.xml")<br />
mapping = {}<br />
<br />
for node in doc.getElementsByTagName("key"):<br />
key = node.getAttribute("name")<br />
if key not in [ 'zimbra_ldap_userdn', 'zimbra_ldap_password', 'mysql_root_password' ]:<br />
continue<br />
L = node.getElementsByTagName("value")<br />
for node2 in L:<br />
title = ""<br />
for node3 in node2.childNodes:<br />
if node3.nodeType == Node.TEXT_NODE:<br />
title += node3.data<br />
mapping[key] = title<br />
<br />
# retrieval of users having specific ZimbraCOSId attribute<br />
# change the value by '*' to fetch everyone<br />
searchFilter = "(zimbraCOSId='''67890ffa-ef21-4d27-a79d-e49da536fc13''')"<br />
retrieveAttributes = ['zimbraId', 'uid']<br />
<br />
try:<br />
l=ldap.open(''''ldap-server-ip'''')<br />
l.protocol_version = ldap.VERSION3<br />
l.simple_bind(mapping['zimbra_ldap_userdn'], mapping['zimbra_ldap_password'])<br />
ldap_result_id = l.search("'''dc=your,dc=domain'''", ldap.SCOPE_SUBTREE, searchFilter, retrieveAttributes)<br />
except ldap.LDAPError, error:<br />
l.unbind()<br />
print 'problem with ldap',error<br />
<br />
user_ids = []<br />
while 1:<br />
result_type, result_data = l.result(ldap_result_id, 0)<br />
if (result_data == []):<br />
break<br />
else:<br />
if result_type == ldap.RES_SEARCH_ENTRY:<br />
user_ids.append( result_data[0][1]['zimbraId'][0] )<br />
<br />
l.unbind()<br />
<br />
userlist = open("/tmp/userlist.txt", "w")<br />
<br />
for user in user_ids:<br />
userlist.write( user + "\n")<br />
userlist.close()<br />
<br />
os.system('/usr/local/sbin/zim_DoBackup.sh ' + mapping['mysql_root_password'])<br />
<br />
=== /usr/local/sbin/zim_DoBackup.sh ===<br />
<br />
#!/bin/bash<br />
# This script creates a user based dump of the mysql database<br />
# steps :<br />
# 1 - loop on the user list (/tmp/userlist.txt)<br />
# 2 - in the loop, fetch all records related to the user<br />
# 3 - puts these records in a temp database (backup)<br />
# 4 - makes a sql file mixing output from mysqldump on "backup" db and manual sql commands<br />
<br />
MYPWD=$1<br />
<br />
# Output folder for the resulting sql files<br />
if [ ! -d /var/dumps/mysql/zimbra/mboxes/ ]; then<br />
mkdir -p /var/dumps/mysql/zimbra/mboxes/<br />
fi<br />
<br />
# step 1<br />
while read user<br />
do<br />
<br />
# step 2<br />
id=`mysql -u root -p$MYPWD backup -e "SELECT id FROM zimbra.mailbox WHERE account_id='$user'" | tail -1 | awk '{print $1}' `<br />
groupid=`mysql -u root -p$MYPWD backup -e "SELECT group_id FROM zimbra.mailbox WHERE account_id='$user'" | tail -1 | awk '{print $1}' `<br />
mailbox="mboxgroup$groupid"<br />
echo $id $mailbox<br />
<br />
# step 3<br />
mysql -u root -p$MYPWD -e 'DROP DATABASE backup'<br />
mysql -u root -p$MYPWD -e 'CREATE DATABASE backup'<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE mailbox AS SELECT * FROM zimbra.mailbox WHERE id='$id'"<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE mailbox_metadata AS SELECT md.* FROM zimbra.mailbox_metadata md WHERE md.mailbox_id='$id'"<br />
<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE appointment AS SELECT * FROM $mailbox.appointment WHERE mailbox_id=$id"<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE mail_item AS SELECT * FROM $mailbox.mail_item WHERE mailbox_id=$id"<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE open_conversation AS SELECT * FROM $mailbox.open_conversation WHERE mailbox_id=$id"<br />
mysql -u root -p$MYPWD backup -e "CREATE TABLE pop3_message AS SELECT * FROM $mailbox.pop3_message WHERE mailbox_id=$id"<br />
<br />
# step 4<br />
echo "USE zimbra" > /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
<br />
# Line added to remore INTEGRITY CHECK for SUBSEQUENT RECOVERY otherwise you will be unable to restore (at least with ZCS 6.x)<br />
echo "SET FOREIGN_KEY_CHECK = 0;" >>/var/dumps/mysql/zimbra/mboxes/$id.sql<br />
<br />
echo "DELETE FROM mailbox WHERE id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
echo "DELETE FROM mailbox_metadata WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
/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<br />
<br />
echo "USE $mailbox" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
echo "DELETE FROM open_conversation WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
echo "DELETE FROM appointment WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
echo "DELETE FROM mail_item WHERE mailbox_id=$id;" >> /var/dumps/mysql/zimbra/mboxes/$id.sql<br />
/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<br />
done < /tmp/userlist.txt<br />
<br />
== Zimbra internals ==<br />
<br />
To understand these scripts, here's some background information I've come to learn/guess through the forums and the zimbra's database structure :<br />
<br />
* Messages are stored (by default) in /opt/zimbra/store, each user having a dedicated subdirectory<br />
* It seems appointments are stored in the same folder but at this stage, this is a guess (if you know, please edit)<br />
* Hierarchy of messages is not in the filesystem but in mysql databases<br />
* zimbra creates several databases :<br />
* one named '''zimbra''', containing metadata for all users<br />
* several prefixed by ''''mboxgroup'''', followed by a group id<br />
* each user belongs to a group_id, and its related infos are therefore stored in database mboxgroup+group_id<br />
* each user has an object in the ldap server with several attributes (in particular, the ZimbraCOSId one)<br />
<br />
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.<br />
'''<br />
<br />
{{Article Footer|unknown|4/23/2007}}<br />
<br />
[[Category:Backup and Restore]]</div>Leocor