Results 1 to 3 of 3

Thread: Rebuild ldap database

  1. #1
    jim@hctps.com is offline Starter Member
    Join Date
    Jun 2012
    Location
    Alpharetta, GA, USA
    Posts
    3
    Rep Power
    3

    Default 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

  2. #2
    jim@hctps.com is offline Starter Member
    Join Date
    Jun 2012
    Location
    Alpharetta, GA, USA
    Posts
    3
    Rep Power
    3

    Default Solution

    This turned out to be much easier than I expected, once I spent a little time with the the zimbra.mailbox table:

    Code:
    // from mysql CLI look at the zimbra.mailbox table
    USE zimbra;
    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';
    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.

    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.

  3. #3
    jim@hctps.com is offline Starter Member
    Join Date
    Jun 2012
    Location
    Alpharetta, GA, USA
    Posts
    3
    Rep Power
    3

    Default Solution

    This turned out to be easier than I expected.

    Following is the process I used to recover account after corruption of ldap database:

    1. Create a new zimbra instance. I think there was more than just ldap corruption so it was quicker to start fresh.
    2. Restore mailbox store (/opt/zimbra/store) from corrupt instance to new instance.
    3. Restore mysql database (from mysqldump) from corrupt instance to new instance.
    4. Recreate accounts in the new zimbra instance.
    5. 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.
    6. 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.
    7. Restart zimbra server. Your account displays to your "lost" mailbox. With some exceptions, see notes below

    Her are the SQL statements you will need.
    Code:
    USE zimbra
    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.
    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.

    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.

Thread Information

Users Browsing this Thread

There are currently 3 users browsing this thread. (0 members and 3 guests)

Similar Threads

  1. 80GB ldap database
    By Fava in forum Administrators
    Replies: 2
    Last Post: 05-22-2013, 10:03 AM
  2. [SOLVED] Rebuild logger database (ZCS 6.0.1 NE)
    By inigoml in forum Administrators
    Replies: 18
    Last Post: 09-16-2009, 04:30 PM
  3. change ldap database
    By Grejao in forum Administrators
    Replies: 1
    Last Post: 12-07-2007, 08:39 AM
  4. Replies: 3
    Last Post: 08-01-2007, 03:07 PM
  5. Replies: 1
    Last Post: 07-31-2007, 05:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •