MySQL replication for Zimbra

[msallee]

Can someone provide information on how to replicate the Zimbra MySQL databases to a secondary Zimbra server set as a cold standby? I guess we could always manually import the latest backup but that wouldn't be optimal.

I just did a check on mysql.com and found this document, but I doubt it would be easy to do since Zimbra appears to create new databases for each mailbox - such as mboxgroup1 - and I have around 50 right now and it may grow.

MySQL :: Re: Adding Multiple Databases to replication... This is How...

and here is the helpful how-to for one MySQL db replication:

How To Set Up Database Replication In MySQL | HowtoForge - Linux Howtos and Tutorials

Thanks for sharing your experience on this.

[Rich Graves]

Not a useful thing to do. MySQL is only one part of the system.

You really need to replicate all of /opt/zimbra, data/amavisd/tmp and logs being the only exceptions that come to mind. And you need to get a point-in-time consistent copy. If you have a big budget, use a replicating SAN; a small budget and adequate expertise, DRDB or one of the free(-ish) NAS/iSCSI distributions; no budget, accept that the best you can do is scripted nightly cold backups, using LVM snaphsots to catch the filesystem in a quiet state.

[msallee]

Thanks for the suggestions on the best ways to replicate the servers. The reason I asked about this is that creating a clustered file system on a SAN will not allow me to test upgrades, patches without changing the whole system at once, thus the motivation to have a cold standby.

Just for reference, I did find a script that shows how to copy over the MySQL dbs: the script dumprestore.sh at:

Harley77-Backup - Zimbra :: Wiki

which shows how to use a loop to back up the zimbra and the mbox MySQL dbs and restore them.