Difference between revisions of "Steps to create users using mysql details where accounts accidently removed from the LDAP"

(Created page with "=== <h1>Steps_to_create_users_using_mysql_details_where_accounts_accidently_removed_from_the_LDAP</h1> === <hr> <br> <h2>Problem: </h2> <p>1st Scenario: LDAP got corrupt and...")
 
Line 1: Line 1:
=== <h1>Steps_to_create_users_using_mysql_details_where_accounts_accidently_removed_from_the_LDAP</h1> ===
+
=== <h1>Create users using MySQL details where accounts accidently removed from the LDAP</h1> ===
 
<hr>
 
<hr>
 
<br>
 
<br>
  
 
<h2>Problem: </h2>
 
<h2>Problem: </h2>
<p>1st Scenario:  LDAP got corrupt and we do not have latest LDAP backup.  
+
<p>1st Scenario:  LDAP got corrupt and we do not have latest LDAP backup. <br>
 
OR<br>
 
OR<br>
 
2nd Scenario:  Some how users removed from LDAP and we still have account entries in MySQL db. .</p>
 
2nd Scenario:  Some how users removed from LDAP and we still have account entries in MySQL db. .</p>
Line 17: Line 17:
  
 
This is the complete description of activity which we have to perform:-  
 
This is the complete description of activity which we have to perform:-  
========================================================================================================
+
 
 
'''1)'''.  Missing accounts' list extract from MySQL database:-
 
'''1)'''.  Missing accounts' list extract from MySQL database:-
  
 
* Using the following command we can extract accounts' list from a particular domain.   
 
* Using the following command we can extract accounts' list from a particular domain.   
  
<pre>$ mysql -e 'select id, account_id, comment from zimbra.mailbox where comment regexp "@mytestdomain.com";'</pre>
+
mysql -e 'select id, account_id, comment from zimbra.mailbox where comment regexp "@mytestdomain.com";'  
<pre>
+
+-----+--------------------------------------+----------------------------------+
+-----+--------------------------------------+----------------------------------+
+
| id  | account_id                          | comment                          |
| id  | account_id                          | comment                          |
+
+-----+--------------------------------------+----------------------------------+
+-----+--------------------------------------+----------------------------------+
+
| 771 | ee4b049a-3d99-4908-b2b8-5cb069350ec8 | it@mytestdomain.com              |
| 771 | ee4b049a-3d99-4908-b2b8-5cb069350ec8 | it@mytestdomain.com              |
+
| 773 | c01cfd8d-69d9-44c8-8234-99371fd19205 | testadmin@mytestdomain.com      |
| 773 | c01cfd8d-69d9-44c8-8234-99371fd19205 | testadmin@mytestdomain.com      |
+
| 774 | 1e4645e8-2e37-48be-ac71-34bd144850f6 | test1pop@mytestdomain.com        |
| 774 | 1e4645e8-2e37-48be-ac71-34bd144850f6 | test1pop@mytestdomain.com        |
+
| 775 | d2a8b102-76df-4b99-a06a-ca64be3eae5d | testuser@mytestdomain.com        |
| 775 | d2a8b102-76df-4b99-a06a-ca64be3eae5d | testuser@mytestdomain.com        |
+
+-----+--------------------------------------+----------------------------------+
+-----+--------------------------------------+----------------------------------+
+
 
</pre>
 
 
* If we have only one domain then we can run MySQL query without where clause.  
 
* If we have only one domain then we can run MySQL query without where clause.  
  
<pre>$ mysql -e 'select id, account_id, comment from zimbra.mailbox;'</pre>
+
mysql -e 'select id, account_id, comment from zimbra.mailbox;'
  
  
Line 41: Line 40:
 
'''2)'''.  Now we have to re-create accounts on LDAP with same zimbraId:-   
 
'''2)'''.  Now we have to re-create accounts on LDAP with same zimbraId:-   
  
<pre>$ zmprov ca it@mytestdomain.com test1234 zimbraId 'ee4b049a-3d99-4908-b2b8-5cb069350ec8'
+
zmprov ca it@mytestdomain.com test1234 zimbraId 'ee4b049a-3d99-4908-b2b8-5cb069350ec8'
$ zmprov ca testadmin@mytestdomain.com test1234 zimbraId 'c01cfd8d-69d9-44c8-8234-99371fd19205'
+
zmprov ca testadmin@mytestdomain.com test1234 zimbraId 'c01cfd8d-69d9-44c8-8234-99371fd19205'
$ zmprov ca test1pop@mytestdomain.com test1234 zimbraId '1e4645e8-2e37-48be-ac71-34bd144850f6'
+
zmprov ca test1pop@mytestdomain.com test1234 zimbraId '1e4645e8-2e37-48be-ac71-34bd144850f6'
$ zmprov ca testuser@mytestdomain.com test1234 zimbraId 'd2a8b102-76df-4b99-a06a-ca64be3eae5d'</pre>
+
zmprov ca testuser@mytestdomain.com test1234 zimbraId 'd2a8b102-76df-4b99-a06a-ca64be3eae5d'  
  
  
 
'''3)'''. After above steps we have to check and verify the mailbox id, quota details, folder and mail counts of created accounts:-
 
'''3)'''. After above steps we have to check and verify the mailbox id, quota details, folder and mail counts of created accounts:-
  
<pre>$ zmprov gmi it@mytestdomain.com
+
zmprov gmi it@mytestdomain.com
mailboxId: 771
+
mailboxId: 771
quotaUsed: 5668</pre>
+
quotaUsed: 5668  
 +
 
 +
zmmailbox -z -m it@mytestdomain.com gaf
 +
 
  
<pre>$ zmmailbox -z -m it@mytestdomain.com gaf </pre>
 
  
Regards,<br>
+
Submitted by:  Heera Singh Koranga
Heera Singh Koranga
 

Revision as of 03:12, 19 August 2017

Create users using MySQL details where accounts accidently removed from the LDAP



Problem:

1st Scenario: LDAP got corrupt and we do not have latest LDAP backup.
OR
2nd Scenario: Some how users removed from LDAP and we still have account entries in MySQL db. .

Solution:

In this situation we have to restore LDAP with available backup and then we will extract accounts' entry from MySQL db. From MySQL we have to extract mailbox id, account id, and email-addresses of missing accounts.

After collecting all missing or removed accounts' details we will create account with the zimbra id.


This is the complete description of activity which we have to perform:-

1). Missing accounts' list extract from MySQL database:-

  • Using the following command we can extract accounts' list from a particular domain.
mysql -e 'select id, account_id, comment from zimbra.mailbox where comment regexp "@mytestdomain.com";' 
+-----+--------------------------------------+----------------------------------+
| id  | account_id                           | comment                          |
+-----+--------------------------------------+----------------------------------+
| 771 | ee4b049a-3d99-4908-b2b8-5cb069350ec8 | it@mytestdomain.com              |
| 773 | c01cfd8d-69d9-44c8-8234-99371fd19205 | testadmin@mytestdomain.com       |
| 774 | 1e4645e8-2e37-48be-ac71-34bd144850f6 | test1pop@mytestdomain.com        |
| 775 | d2a8b102-76df-4b99-a06a-ca64be3eae5d | testuser@mytestdomain.com        |
+-----+--------------------------------------+----------------------------------+
  • If we have only one domain then we can run MySQL query without where clause.
mysql -e 'select id, account_id, comment from zimbra.mailbox;'


2). Now we have to re-create accounts on LDAP with same zimbraId:-

zmprov ca it@mytestdomain.com test1234 zimbraId 'ee4b049a-3d99-4908-b2b8-5cb069350ec8'
zmprov ca testadmin@mytestdomain.com test1234 zimbraId 'c01cfd8d-69d9-44c8-8234-99371fd19205'
zmprov ca test1pop@mytestdomain.com test1234 zimbraId '1e4645e8-2e37-48be-ac71-34bd144850f6'
zmprov ca testuser@mytestdomain.com test1234 zimbraId 'd2a8b102-76df-4b99-a06a-ca64be3eae5d' 


3). After above steps we have to check and verify the mailbox id, quota details, folder and mail counts of created accounts:-

zmprov gmi it@mytestdomain.com
mailboxId: 771
quotaUsed: 5668 
zmmailbox -z -m it@mytestdomain.com gaf


Submitted by: Heera Singh Koranga

Jump to: navigation, search