Zentyal Forum, Linux Small Business Server

Zentyal Server => Email and Groupware => Topic started by: fatbob on March 11, 2013, 02:34:13 pm

Title: Where is the mail stored in the filesystem?
Post by: fatbob on March 11, 2013, 02:34:13 pm
HI

I have zentyal 3 with zarafa configured as the mail gateway.

Where in the file system is the user mail stored? I would like to know for backup purposes etc.

Ie if I was restoring the server from an image what would I need to restore from the backup to recover the mail.
Title: Re: Where is the mail stored in the filesystem?
Post by: christian on March 11, 2013, 03:41:18 pm
here (http://forum.zentyal.org/index.php/topic,5801.msg23558.html#msg23558) ?

Found using forum search feature.
Notice that this is quite hold. Zarafa design lay have slightly changed (I think there is something in the pipe at least for what concerns attachments.)
Anyway, best accurate source is Zarafa web site  ;)
Title: Re: Where is the mail stored in the filesystem?
Post by: half_life on March 11, 2013, 10:06:29 pm
Christian is correct.  It is stored in a mysql database.  Xtrabackup (Percona.com) for live incremental backups or mysqldump to backup just the email.  By default mysql is setup for one big file instead of table per file. 
Title: Re: Where is the mail stored in the filesystem?
Post by: fatbob on March 12, 2013, 04:43:59 pm
Thanks guys

I'm not very familiar with mysql, mostly sql server myself, but I've found the mysql workbench thing. Anyone know where i can find the connection details? (whatever the equivalent of sa is in sql server? root i think)

Cheers
Title: Re: Where is the mail stored in the filesystem?
Post by: half_life on March 13, 2013, 01:04:43 am
Use the ssh connection method with your regular user name. 
Mysql parameters are
hostname 127.0.0.1
port 3306
username sa

password is stored in /var/lib/zentyal/conf/sa-mysql.passwd.

This gets you initial connection as sa.  You can then add a user with needed privileges to do what it is you want to do.  I do not recommend using the sa regularly.   I assume that you are going to the mysql-dump route?
Title: Re: Where is the mail stored in the filesystem?
Post by: half_life on March 13, 2013, 01:13:02 am
Hmm. . . this gets asked often enough that maybe it should be a sticky or in the tips and tricks section.  Add on that eventually the email system will get sluggish and prone to crashing because of the "all tables in one file" decision and I think that a decent write up is in order.  Sort of follows along with my next free time (chuckle) project which is to play with clustering Zentyal.  I haven't forgotten that, christian.  Just incredibly busy.
Title: Re: Where is the mail stored in the filesystem?
Post by: half_life on March 16, 2013, 09:48:52 pm
Were you able to get this working?
Title: Re: Where is the mail stored in the filesystem?
Post by: robb on March 16, 2013, 10:11:19 pm
Good idea to set this topic as a sticky..... and done... :)

Lets say this can be 'unstickied' as soon someone makes a nice howto in the community documentation repository (http://trac.zentyal.org/wiki/Documentation/Community)...
*hint hint* volunteers please step forward.. ;)
Title: Re: Where is the mail stored in the filesystem?
Post by: fatbob on March 24, 2013, 03:06:02 pm
Hi there

Firstly thanks for everybodies help on this.

I'm running zentyal 3.0 (core 3.0.17) so what I'm about to say may not be relevant to earlier versions.

there are a few corrections to what folks have said on here.

Mysql parameters are
hostname 127.0.0.1
port 3306
username root

passord is in /var/lib/zentyal/conf/zentyal-mysql.passwd

The mail isn't actually stored in the MySql zarafa db. I loaded around 8000 mail items into the zarafa and it didn't significatnly affect the size of the database.

The mail and also the zarafa-search indexes (if enabled) are actually stored in the file systm under /var/lib/zarafa.

The configuration files would give the impression that this is just attachments but it appears to be all emails as well. Each email and attachement is stored in a nested, numbered folder structure as a gziped file.

I wanted all my data stored on a NAS rather then on the server itself. This was quite easy to achive for the mail. It was simply a matter of copying /var/lib/zarafa to a new location and then creating a symlink from the /var/lib folder to the new location.

Thanks for all your help folks
Title: Re: [SOLVED] Where is the mail stored in the filesystem?
Post by: half_life on March 24, 2013, 04:56:26 pm
I am taking a closer look at users and privileges concerning mysql in the latest version.  The file structure that you describe is really just the index. 
If you care to test, 
copy the directory somewhere for safe keeping.
Stop Zarafa.
Delete the directory structure
Start Zarafa

The emails will still be there

One not of caution --  I don't store attachments outside the database so I can't verify where they are.
Title: Re: Where is the mail stored in the filesystem?
Post by: fatbob on March 24, 2013, 05:33:26 pm
Well then I still have a problem. The zarafa database in /var/lib/mysql/zarafa is only 2.6mb in size and I can't find the mail in there. So I'm back to the same question

Where is the mail?

Title: Re: Where is the mail stored in the filesystem?
Post by: half_life on March 24, 2013, 08:23:05 pm
Did you setup file per table?  If not they are stored in /var/lib/mysql/ibdata1.   You will find that ibdata1 is rather large.
Title: Re: Where is the mail stored in the filesystem?
Post by: half_life on March 24, 2013, 09:45:05 pm
BTW mysql doen't play nice over nfs.  If you want real-time redundancy,  you will have to setup replication (now you are getting into HA and load balancing).
Title: Re: Where is the mail stored in the filesystem?
Post by: fatbob on March 25, 2013, 01:02:04 am
Yes indeed there is a very large /var/lib/mysql/ibdata1 file. :) I'm afraid i didn't specify anything I just installed the zarafa module on zentyal and this is just a straight install from the zentyal cd so if this type of configuration is bad then I guess the developers should sort it :)

I was wondering about backing this file up. I run automysqlbackup which backs up all the databases. Will this be included somewhere? In the zarafa db backup? I wonder because it's sat outside the zarafa folder?
Title: Re: Where is the mail stored in the filesystem?
Post by: half_life on March 25, 2013, 03:04:57 am
If you configured it to backup all databases then yes it will do the work.  There are pluses and minuses to using mysql-dump.  While every mysql install will have the tool,  it still demands server downtime while backing up.  Incremental, no  downtime backups can be had with xtrabackup.

The design decision (not bug) in mysql s innodb engine causes it to become unstable over time in my experience.  The database doesn't shrink as you remove data.  Instead the file stays the same size and just the row information is removed. It tends to make things too big for the memory and storage footprint after some time.  It also fragments the data causing queries to run slower.  Normal optimization tools don't work on innodb in the single file configuration.  Some will find this as an issue and some will never know.  I know because I support 100+ email accounts on my system and it is very active.

There are ways of dealing with the issues that I talked about above.  Robb is nudging me to do a write-up on it.  At the rate that I am getting projects thrown in my lap at work it might be awhile.  I run a shop of three  (me, myself, and I)

Title: Re: Where is the mail stored in the filesystem?
Post by: auerhaan on March 06, 2014, 04:59:51 pm
Mail is stored in a MYSQL db and attachments are stored in the filesystem when you are using Zarafa.

I've used the following procedure to backup mail and attachments on a server and restore it on another server.

Commands have to be performed with sudo or after sudo su

Backup
-------

STOP ZARAFA-SERVER
Code: [Select]
service zarafa-server stop
MAKE COPY OF EXISTING ATTACHMENTS
Code: [Select]
cd /var/lib/zarafa
tar -cvzf /var/tmp/attachments.tgz ./

FIND DATABASE PASSWORD
Code: [Select]
cat /var/lib/zentyal/conf/ebox-zarafa.passwd
DUMP MYSQL DATABASE
Code: [Select]
mysqldump -u zarafa -p -v --single-transaction zarafa > /var/tmp/zarafadump.sql
In /var/tmp/ you've got the files attachments.tgz and zarafadump.sql to backup or move to another server.


Restore
--------

STOP ZARAFA-SERVER
Code: [Select]
service zarafa-server stop
IMPORT ATTACHMENTS
Code: [Select]
mv /var/lib/zarafa /var/lib/zarafaOLD
mkdir /var/lib/zarafa
cd /var/lib/zarafa
tar -zxvf /var/tmp/attachments.tgz
chown root:root -R /var/lib/zarafa

Code: [Select]
cd /var/lib/zarafa
find . -type d -print0 | xargs -0 chmod 0700 # for directories
find . -type f -print0 | xargs -0 chmod 0644 # for files

FIND DATABASE PASSWORD
Code: [Select]
cat /var/lib/zentyal/conf/ebox-zarafa.passwd
IMPORT MYSQLDUMP (this may take a while depending on the size of your DB)
Code: [Select]
mysql -u zarafa -p zarafa < /var/tmp/zarafadump.sql
RESTART ZARAFA-SERVER
Code: [Select]
service zarafa-server start

TOOLS TO REATTACH MISSING STORES
Code: [Select]
zarafa-admin --list-orphans
zarafa-admin --hook-store STOREGUID -u username
zarafa-admin --remove-store STOREGUID
Title: Where is the mail stored in the filesystem
Post by: KonstantinPr on November 23, 2015, 10:47:40 am
Can someone please take me through the steps of how to install PHPVMS please. Im having trouble when it goes to the tables and then i say next step and it doesnt load. Then when i go on the website its half loaded?

I Need Help


Cheers
Myles