MySQL to an LDIF file

Hi there,

I'm trying to take users I have in a mysql db (that I'm using for vpopmail) and put them into an ldif file so I can import them into an LDAP database.  I've been doing some digging but haven't really come up with anything as of yet.  I found one site (http://www.jasondearborn.com/linux/) that has a script that's outlined as such:



Vuserinfo2LDIF()

###################################################
#
# Vuserinfo2LDIF $1 $2
#
#  $1 is a file containing output from the command
#     "vuserinfo -D domain.name > OUTFILE"
#
#  $2 is the LDIF output file
#
###################################################
{
  ## Convert vuserinfo data into LDIF format

  awk 'BEGIN {

    ORS=""
    FS=": +"
    x=1
    print "version: 1\n\n" ## Header for openldap
                           ## Remove if unnecessary
  }


  {
    if ( $1 == "name" ) {
       email[x] = $2
    } else if ( $1 == "gecos" ) {
      fullname[x] = $2

    ## Break up the string into first and last names

      split($2, name, " ")
      namelen=1
      for ( n in name ) {
        namelen++
      }
      if ( namelen == 2 ) {
        fname[x]=name[1]
        lname[x]=name[1]
      } else
      if ( namelen == 3 ) {
        fname[x]=name[1]
        lname[x]=name[2]
      } else {
        fname[x]=name[1] " " name[2]
        lname[x]=name[3]
      }
      x++
    }


  }

  END { ## Print out the vpopmail LDIF
        ## Modify this section to match your LDAP schema
    i=1
    while ( i < x ) {
      print "dn: cn=" fullname[i] ",ou=staff,dc=sfpalm,dc=org\n"
      print "objectClass: top\n"
      print "objectClass: inetOrgPerson\n"
      print "objectClass: qmailUser\n"
      print "objectClass: person\n"
      print "cn: " fullname[i] "\n"
      print "givenName: " fname[i] "\n"
      print "sn: " lname[i] "\n"
      print "uid: " email[i] "\n"
      print "mail: " email[i] "@sfpalm.org\n\n"
      i++
      }


  }' $1 > $2

}

##########################################

I can't seem to get this script to work, though.  I'm not a programmer, so I'm having a bit of trouble understanding what I need to do to get this to work.  I've run "vuserinfo -D domain.name > OUTFILE" like it stated and it created the proper output file I need (named it holycrapola.txt) but I don't know if I'm supposed to run the script at the command line as such:

./vuserinfo2ldif holycrapola.txt import.ldif

or if I'm supposed to modify the code with my file names, etc..

If there's a much easier way I can get my data out of the vpopmail mysql database and into an ldif file (I'm just needing the name and username, essentially), then please tell me as I'm open for suggestions. :)

Thanks,

Mike
I-Land-NOCAsked:
Who is Participating?
 
Tol_cvConnect With a Mentor Commented:
i made some small modifications  and made it and execulable sh script:

#!/bin/sh

Vuserinfo2LDIF()

###################################################
#
# Vuserinfo2LDIF $1 $2
#
#  $1 is a file containing output from the command
#     "vuserinfo -D domain.name > OUTFILE"
#
#  $2 is the LDIF output file
#
###################################################
{
  ## Convert vuserinfo data into LDIF format

  awk 'BEGIN {

    ORS=""
    FS=": +"
    x=1
    print "version: 1\n\n" ## Header for openldap
                           ## Remove if unnecessary
  }


  {
    if ( $1 == "name" ) {
       email[x] = $2
    } else if ( $1 == "gecos" ) {
      fullname[x] = $2

    ## Break up the string into first and last names

      split($2, name, " ")
      namelen=1
      for ( n in name ) {
        namelen++
      }
      if ( namelen == 2 ) {
        fname[x]=name[1]
        lname[x]=name[1]
      } else
      if ( namelen == 3 ) {
        fname[x]=name[1]
        lname[x]=name[2]
      } else {
        fname[x]=name[1] " " name[2]
        lname[x]=name[3]
      }
      x++
    }


  }

  END { ## Print out the vpopmail LDIF
        ## Modify this section to match your LDAP schema
    i=1
    while ( i < x ) {
      print "dn: cn=" fullname[i] ",ou=staff,dc=sfpalm,dc=org\n"
      print "objectClass: top\n"
      print "objectClass: inetOrgPerson\n"
      print "objectClass: qmailUser\n"
      print "objectClass: person\n"
      print "cn: " fullname[i] "\n"
      print "givenName: " fname[i] "\n"
      print "sn: " lname[i] "\n"
      print "uid: " email[i] "\n"
      print "mail: " email[i] "@sfpalm.org\n\n"
      i++
      }


  }' $1 > $2

}

{

 Vuserinfo2LDIF $1 $2
}


then save into a file  named file i.e  Vui2LDIF, make it executable etc.
and it can be called like :
Vui2LDIF <vuseroutfile> <outldiffile>

i didn't try to see if  works corectly  and all.. hope this helps
0
 
Tol_cvCommented:
you need to look at the last part and match it with your ldap schema:

      dn: cn=" fullname[i] ",ou=staff,dc=sfpalm,dc=org\n"
      print "objectClass: top\n"
      print "objectClass: inetOrgPerson\n"
      print "objectClass: qmailUser\n"
      print "objectClass: person\n"
      print "cn: " fullname[i] "\n"
      print "givenName: " fname[i] "\n"
      print "sn: " lname[i] "\n"
      print "uid: " email[i] "\n"
      print "mail: " email[i] "@sfpalm.org\n\n"



what  ou,dc  you intend to  use  etc... also if you have the same objectclases available (objectClass: inetOrgPerson, objectClass: qmailUser)
 you can look in your ldap schema or  if  you have already some entries  you can export then from ldap as ldif and have a look at hpw your output should look like.. then modify script  and run/test it  until you get a similar output.

a nice  free ldap  java browser you can find here http://www-unix.mcs.anl.gov/~gawor/ldap/index.html 
0
 
I-Land-NOCAuthor Commented:
I see that I need to customize that section to my schema, but shouldn't the script at least *run* and output to a file (even if it's not in the exact format I need)?  If it would at least do that, I could then fiddle with that last part to get it into the format I need.  I just can't get it to even create an ldif file for me.

Thanks,
Mike
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Tol_cvCommented:
it's a awk script . try :

awk  -f vuserinfo2ldif holycrapola.txt import.ldif
0
 
I-Land-NOCAuthor Commented:
It unfortunately just told me:

awk: vuserinfo2ldif.sh:16:   awk 'BEGIN {
awk: vuserinfo2ldif.sh:16:       ^ invalid char ''' in expression

when I tried to run it that way.  Any other suggestions? ;)

Mike
0
 
I-Land-NOCAuthor Commented:
You're a genius.  That worked! :)


Thanks!!!!

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.