Per User Mailbox Backup (OE Version): Difference between revisions
Line 86: | Line 86: | ||
os.system('/usr/local/sbin/zim_DoBackup.sh ' + mapping['mysql_root_password']) | 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 | |||
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/mysqll | |||
/zimbra/mboxes/$id.sql | |||
done < /tmp/userlist.txt |
Revision as of 13:29, 23 April 2007
This is actually *Work in progress*
Scope
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.
Method used
- 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
How to use it
This is how I plan to use it :
- stop zimbra
- take lvm snapshot
- start ldap & mysql services
- run the specific scripts for user backup
- start zimbra completely
- run the backup as usual
- close the snapshot
The scripts
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 MySQLdb import exceptions import xml.dom.minidom import os from xml.dom.minidom import Node # scan de la configuration de zimbra 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_passs word' ]: 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 # recuperation des account_id ayant la COS avec suffixe .backup 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, searchh Filter, 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: userlist.write( user + "\n") 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 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/mysqll /zimbra/mboxes/$id.sql done < /tmp/userlist.txt