Create MySQL database for IP addresses

[bosh]

Dear Tech Support,

I wonder if you can please give some suggestions and steps on how to go about creating a MySQL database for IP addresses.
I currently work as an IT support Officer in an academic/research institution, the raw data of IP addresses is currently stored in a file on a unix solaris server, using cmds such as ff: grep -i free mast 1.2.3.4, I currently interogate the file to assign free static IP address to a user. A Windows 2003 server acts as a DHCP server.
I've had a look at MyPHPAdmin and created 15 fields with relevant MySQL datatypes, the next step is to create a table and I've decided to use the Mac address field as the PK. I'm in a bit of a limbo as to how to proceed in moving raw IP addresses details from the unix server into a csv file and subsequently into a MySQL database. Also how and when to incorporate the php script used in converting IP addresses to IP numbers. Any form of assistance/suggestion would be highly appreciated.

Many thanks,

Ben

[jjinno]

Ben,

Im not sure what you are trying to accomplish by converting the IP addresses into numbers. If you are going to go to the effort of running a comparison between two numeric fields (lets say they are your IP numbers) then it is going to be just as efficient to run the comparison between two text fields (the IP addresses). One question is what do you mean by "raw IP addresses"? Do you wish to encrypt them?

Secondly, I have frequently come across such a daunting task as to merge 800k lines between 16 different files on 4 different servers all to a SQL database... and in every case I have scripted the process instead of doing any manual merging. Here is my reasoning: If the data requires more than 1 operation per entry (in your case 3: grepping, converting to numerics, and comma-delimited separation) then you are always going to be faster by writing a script to do those operations for you.

Am I right to assume that your grep that you explained is to verify that that address does not already exist, thus allowing you to assign it to somebody?

As far as using the MAC as the key, you are good to do so, as "theoretically" there are no duplicates, and even if there are (and accidents like that do happen) the likelihood that they will ever both reside on your network is less that 1/100 of a percent.

Anyway, just as a point of information, you are right to go the SQL way for this type of data. I worked on a project once that grepped a 200MB file for info... when we switched the information to an SQL database we decreased run-time from 58 seconds to 6 seconds.