Import MAXMIND GEOLITE COUNTRY csv format into mysql .

MaxmindGeoIPCountryWhois.sql

--
-- this sql structure is for knowledgebase or educational only.
-- please do this on your localhost first before ypu test on your server
--
-- @author : awan
--
-- github : https://github.com/aufa/
--
-- @description this sql is for importing the MaxminD GeoLite Country to mysql data
-- first! we must download the csv format on maxmind developer site  :http://dev.maxmind.com/geoip/legacy/geolite/
-- choose the CSV / zip , and extract it on your Computer drive
--


-- --------------------------------------------------------
-- creating the table
--
-- Table structure for table `Maxmind_geoIP`
--
-- /// MAXMIND GEOIP
CREATE TABLE IF NOT EXISTS `Maxmind_geoIP` (
	`id`	 		INT(1) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT, -- the id just for numeric
	`maxmind_ipstart` 	VARCHAR(50) COLLATE UTF8_GENERAL_CI NOT NULL, -- the ip start from maxmind data
	`maxmind_ipend` 	VARCHAR(50) COLLATE UTF8_GENERAL_CI NOT NULL, -- the ip end of maxmind data
	`maxmind_locid_start`  	INT(1) UNSIGNED ZEROFILL NOT NULL, -- the start of maxmind location id 
	`maxmind_locid_end` 	INT(1) UNSIGNED ZEROFILL NOT NULL, -- the end of maxmind location id
	`maxmind_country_code` 	VARCHAR(4) COLLATE UTF8_GENERAL_CI NOT NULL, -- the country code
	`maxmind_country` 	VARCHAR(100) COLLATE UTF8_GENERAL_CI NOT NULL, -- the country name

	PRIMARY KEY( `id`,`maxmind_ipstart`,`maxmind_ipend`, `maxmind_locid_end`, `maxmind_country` )

) DEFAULT CHARSET=UTF8 COLLATE=UTF8_GENERAL_CI AUTO_INCREMENT=1 ;

-- 
-- IMPORT THE CSV DATA with load data

LOAD DATA INFILE 'E:\\PROJECT\\GeoIPCountryWhois.csv' INTO TABLE `Maxmind_geoIP` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'(
 `maxmind_ipstart` , `maxmind_ipend` , `maxmind_locid_start` , `maxmind_locid_end` , `maxmind_country_code` , `maxmind_country`
 );
 
 
 
 -- IMPORTANT 
 -- please change the 'E:\\PROJECT\\GeoIPCountryWhois.csv' with your path of the GEOLITE COuntry CSV file.
 -- if on windows the directory separator is backslash , so .. you must fill with double backslash on the file structures like 
 -- the file import. 
 -- example if you have on linux os , and your file of GeoIPCountryWhois.csv , is on /home/admin/ directory
 -- just change the 'E:\\PROJECT\\GeoIPCountryWhois.csv' with : '/home/admin/GeoIPCountryWhois.csv'
 
 

Hi Aufa thanks for the script. Could you please add some sample queries on how to make this work?

After importing the data, you can make a query like this...

SELECT * FROM Maxmind_geoIP
WHERE maxmind_locid_start <= INET_ATON('203.11.201.12') 
AND   maxmind_locid_end   >= INET_ATON('203.11.201.12');

It's inaccurate to call those columns "locid". They are actually a numerical representation of the IP address which is necessary to perform >= and <= searches.

Thanks this was very helpful http://stayat.org/bootstrap05.nsf/Country.xsp