Rebuild ldap database
I have managed to destroy my ldap database. My mail store (/opt/zimbra/store) and mysql databases are intact (restored). I created new ldap accounts matching my old accounts but, of course, the new accounts point to new mail stores.
How can I make my ldap accounts (zimbraPrefIdentityId) match the old account_id's (zimbra.mailbox.account_id) in the mysql database or vice versa?
I can see the accounts in the mysql database (zimbra.mailbox.account_id) but there is a foreign key constraint preventing me from doing an UPDATE on the account_id field. I don't want to remove the foreign key for fear of messing up something else.
I can see the ldap zimbraPrefIdentityId, using zmprov gid. However, I cannot modify it using "zmprov mid". "Zmprov mid" returns an error, probably for some very good reason.
I am running zimbra 7.2.5 community edition on Centos 6.4.
HELP! I have seen multiple similar questions on the forums, but nobody has actually addressed a solution. And please don't tell me that I need to do better backups, it's a little late for that.
Thanks for any help
This turned out to be much easier than I expected, once I spent a little time with the the zimbra.mailbox table:
You may now log in to the new zimbra account. This restored the mailbox, address book, calendar, tasks, and brief case. It did not restore account preferences such as: personas, filters, or any other preference items. Account preferences must be stored in ldap, I could not find them in mysql. AND, obviously, it did not recover any server settings.
// from mysql CLI look at the zimbra.mailbox table
SELECT id, account_id, comment FROM mailbox;
//NOTE: The "comment" field contains the email address of each account.
//Copy and Paste the account_id to someplace you can find it again.
//Change the account_id of the NEW account you created
UPDATE mailbox SET account_id = 'xxxxx...' WHERE id = 'new id';
//Change the account_id of the OLD account to the value you saved above
UPDATE mailbox SET account_id='saved account_id" WHERE id='old id';
I used ZCS-to-ZCS Migrations to migrate the mail box to my new server.
I only had a few mailboxes to recover on my test server. However, much of this process could be automated if you have tens or hundreds of mailboxes to recover. Mysql workbench, or some other SQL editor, will also make the SQL part much easier.
This turned out to be easier than I expected.
Following is the process I used to recover account after corruption of ldap database:
- Create a new zimbra instance. I think there was more than just ldap corruption so it was quicker to start fresh.
- Restore mailbox store (/opt/zimbra/store) from corrupt instance to new instance.
- Restore mysql database (from mysqldump) from corrupt instance to new instance.
- Recreate accounts in the new zimbra instance.
- The mysql table zimbra.mailbox will now have two records for each of your accounts. One is from the mysql restore and the other you just created in the previous step.
- Modify zimbra.mailbox.account_id such that the account record from the old record has the value from the new record. You will need to set the value of the new record to some arbitrary value to avoid duplicate key constraints. See code below.
- Restart zimbra server. Your account displays to your "lost" mailbox. With some exceptions, see notes below
Her are the SQL statements you will need.
This restored my mail box, address book, calendar, tasks, and brief case. It did not restore my account preferences. Account preferences must be stored in ldap. And, obviously, I lost all of my server settings when I created a new instance.
SELECT id, account_id, comment FROM mailbox
// This will show you the mailboxes on your instance. The comment field displays the email address of the account for easy reference
// Note the account_id for the the row of your NEW id. If you used the same account names for new and old accounts, the value of id will be large for the new account.
UPDATE mailbox SET account_id='xxxxx...' WHERE id = 'value of NEW id';
//Change the value of account_id to avoid unique key constraint.
UPDATE mailbox SET account_id='new account_id' WHERE id = 'value of OLD id';
//Change the account_id on the old id record. Now your account will display your restored mail box.
By modifying the mysql database I may have created unintended consequences for myself later on. Just to be safe I created a third instance and used ZCS-to-ZCS Migrations to migrate my mailboxes from the recovered instance to my new production instance.
Fortunately, this was a test server with a few accounts on it. So it was not like I had to create tens or hundreds of mailboxes. However, if I had to recreate more than a few accounts much of the work could be scripted.