IP 2 Country
Glenn asked about this in our private SEO forum, but thought I’d share it here too. I wrote this dirty shell script a couple of years ago for Wotbox, but I think it should still work. Just set it to a cron job once a day and it will give you a nice IP to country database.
workdir=/scripts/ipdb
savfile=$workdir/ipdb.$thedate
dbserver=127.0.0.1
database=ipdb
dbuserpass=“–user=user –password=pass”
cd $workdir
echo “Downloading ARIN IP database”
wget -q “ftp://ftp.arin.net/pub/stats/arin/delegated-arin-$thedate”
echo “Downloading APNIC IP database”
wget -q “ftp://ftp.arin.net/pub/stats/apnic/delegated-apnic-$thedate”
echo “Downloading LACNIC IP database”
wget -q “ftp://ftp.arin.net/pub/stats/lacnic/delegated-lacnic-$thedate”
echo “Downloading RIPE IP database”
wget -q “ftp://ftp.arin.net/pub/stats/ripencc/delegated-ripencc-$thedate”
echo “Cleaning downloaded IP databases”
tail -100000 $workdir/delegated-arin-$thedate | grep “ipv4″ | grep -v “*” | grep -v “-” | sed ’s/|assigned//’ | sed ’s/|allocated//’ | sed ’s/|ipv4//’ | sed ’s/|……..$//’ > $savfile
tail -100000 $workdir/delegated-apnic-$thedate | grep “ipv4″ | grep -v “#” | grep -v “*” | grep -v “+” | sed ’s/|assigned//’ | sed ’s/|allocated//’ | sed ’s/|ipv4//’ | sed ’s/|……..$//’ >> $savfile
tail -100000 $workdir/delegated-lacnic-$thedate | grep “ipv4″ | grep -v “*” | grep -v “-” | sed ’s/|assigned//’ | sed ’s/|allocated//’ | sed ’s/|ipv4//’ | sed ’s/|……..$//’ >> $savfile
tail -100000 $workdir/delegated-ripencc-$thedate | grep “ipv4″ | grep -v “*” | grep -v “+” | sed ’s/|assigned//’ | sed ’s/|allocated//’ | sed ’s/|ipv4//’ | sed ’s/|……..$//’ >> $savfile
echo “Removing downloaded IP databases”
rm delegated-arin-$thedate
rm delegated-apnic-$thedate
rm delegated-lacnic-$thedate
rm delegated-ripencc-$thedate
query1=“CREATE DATABASE IF NOT EXISTS $database”
query2=“DROP TABLE IF EXISTS tblips2;”
query3=“CREATE TABLE tblips2 (ID int(5) unsigned NOT NULL auto_increment,NIC varchar(7) default NULL,Country char(2) default NULL,StartIPreal varchar(15) default NULL,StartIPint int(20) unsigned default NULL,Subnetreal varchar(15) default NULL,Subnetint int(20) unsigned default NULL,EndIPreal varchar(15) default NULL,EndIPint int(20) unsigned default NULL,PRIMARY KEY (ID)) TYPE=MyISAM;”
query4=“LOAD DATA LOCAL INFILE ‘$savfile’ INTO TABLE $database.tblips2 FIELDS TERMINATED BY ‘|’ LINES TERMINATED BY ‘\n‘ (NIC,Country,StartIPreal,Subnetint);”
query5=“DROP TABLE IF EXISTS tblips;”
query6=“CREATE TABLE tblips (ID int(5) unsigned NOT NULL auto_increment,NIC varchar(7) default NULL,Country char(2) default NULL,StartIPreal varchar(15) default NULL,StartIPint int(20) unsigned default NULL,Subnetreal varchar(15) default NULL,Subnetint int(20) unsigned default NULL,EndIPreal varchar(15) default NULL,EndIPint int(20) unsigned default NULL,PRIMARY KEY (ID)) TYPE=MyISAM;”
query7=“INSERT INTO tblips (NIC,Country,StartIPReal,StartIPInt,SubnetReal,SubnetInt,EndIPInt,EndIPReal) SELECT tblips2.NIC,tblips2.Country,tblips2.StartIPReal,inet_aton(tblips2.StartIPReal) as StartIPInt,inet_ntoa(tblips2.SubnetInt) as SubnetReal,tblips2.SubnetInt,inet_aton(tblips2.StartIPReal)+tblips2.SubnetInt as EndIPInt,inet_ntoa(inet_aton(tblips2.StartIPReal)+tblips2.SubnetInt) as EndIPReal FROM tblips2;”
query8=“DROP TABLE IF EXISTS tblips2;”
echo “Importing IP databases to MySql”
echo “MySql Query 1″
mysql –host=$dbserver $dbuserpass -e “$query1″
echo “MySql Query 2″
mysql –host=$dbserver $dbuserpass $database -e “$query2″
echo “MySql Query 3″
mysql –host=$dbserver $dbuserpass $database -e “$query3″
echo “MySql Query 4″
mysql –host=$dbserver $dbuserpass $database -e “$query4″
echo “MySql Query 5″
mysql –host=$dbserver $dbuserpass $database -e “$query5″
echo “MySql Query 6″
mysql –host=$dbserver $dbuserpass $database -e “$query6″
echo “MySql Query 7″
mysql –host=$dbserver $dbuserpass $database -e “$query7″
echo “MySql Query 8″
mysql –host=$dbserver $dbuserpass $database -e “$query8″
rm $savfile
echo “Process completed”
