sync'ing ldap w/mysql data

[George Toft]

Mike Starke wrote:
> =

> I currently keep all of my information in
> a couple MySQL databases. Everything from
> contact info, to my own invoice system. I love
> having everything in one spot.
> =

> I have written a Perl script to generate a ldif file
> (by querying the MySQL database)
> which I initially used to populate my ldap server.
> What I would like to do/create is a means by which
> to periodically keep the ldap entries sync'd with
> what is in the mysql db. Nothing is ever modified
> in the ldap sever, it is just queried by email clients
> (Thank you COX for not blocking 389 :)
> =

> I am just looking for some thoughts on how to
> keep these sync'd. All of my entries in the ldap
> server take on the form
> =

> dn: cn=3DContact Name,ou=3DAddressbook,dc=3Dmydomain,dc=3Dcom
> =

> I wonder if it is just as simple as deleting everything
> in the ou=3DAddressbook on down, and then repopulating
> from a query from MySQL. If so, what is the correct way
> to do so?
> =

> v/r
> -Mike
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change  you mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss


Funny you should ask - I am running a mysql -> LDAP sync program on two
systems.  My sync system updates LDAP with all records that changed
during the previous day.  The script is horrendous as it will create
on-the-fly any ou that does not already exist in order to store the new
data - all the way up to the root if necessary.

Since I have over 720,000 rows (and therefore 720,000 entries in the
Directory), I'm not too hip on doing a delete and repopulate.  Ever try
to repopulate 700K entries?  It takes a really long time.

My script is almost 10KB, so I'll include some snippets.  Here's what I
suggest:


# Initialization (names have been changed . . .)
MYSQL_USER=3Dsqluser
MYSQL_PASSWD=3Dyeahright
MYSQL_HOST=3Ddatabase

LDAP_USER=3Dcn=3Dsyncuser,o=3Dcommode
LDAP_PASSWD=3Dsecret1
LDAP_HOST=3Dldap

DATE=3D`date -d "yesterday" "+%Y%m%d"`


echo "Retrieving data for changed on $DATE"

# Get the data from mysql
mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h$MYSQL_HOST commode -e "select *
from day where create_ts like '${DATE}%'" | grep $DATE > $TEMPFILE


while read SQL_LINE; do
	# Parse the data in $TEMPFILE to build an LDIF.  LDIF is in $0.ldif
	:
	:
	:
	# add LDIF to LDAP
	cat $0.ldif  | ldapadd -h $LDAP_HOST -x -D $LDAP_USER -w $LDAP_PASSWD
2>/dev/null >> $0.log
	ERR_CODE=3D$?
	case $ERR_CODE in
		32) # ou does not exist - create it
			# Maybe not for you?
			:
			:
			:
			;;
	        68) # Already exists
			# ldapmodify instead of ldapadd
                        # don't forget the changetype: modify
instruction :)
			:
			:
			:
			;;
	         0) # No problem
	                echo "LDAP: Added dn: blah blah blah" >> $0.log
			:
			:
			:
			;;
	=

	         *) # All other errors
	                echo "LDAP: Add failed (error=3D$ERR_CODE) dn: cn=3Dblah=

blah blah" >> $0.errorlog
			:
			:
			:
			;;
	esac

done < $TEMPFILE


# Clean up temp files




The trick to get the SQL to work is to create a field of type timestamp:
+---------------+------------------+------+-----+------------+-----------=
-----+
| Field         | Type             | Null | Key | Default    |
Extra          |
+---------------+------------------+------+-----+------------+-----------=
-----+
| count         | int(10) unsigned |      | MUL | NULL       |
auto_increment |
+---------------+------------------+------+-----+------------+-----------=
-----+
    :
    :
    :
+---------------+------------------+------+-----+------------+-----------=
-----+
| create_ts     | timestamp(14)    | YES  | MUL | NULL      =

|                |
+---------------+------------------+------+-----+------------+-----------=
-----+

I made mine 14 characters long - in retrospect, I should have made it 8
(YYYYMMDD), but I left it at 14 for future expansion - just in case I
need to know exactly what second an entry was created.  This feature
does give me the ability to historically review how fast the database
does inserts over time.

George
-- =

Discover . . .         | Free Computer Security Information
        <=B7=B7=B7> Secure   | http://www.georgetoft.com/security
         Networking    | =

@http://georgetoft.com | Lock your box - keep your affairs private!