Home > database, network, unix/linux > GeoIP Database MySQL

GeoIP Database MySQL

August 8th, 2008 Albertux Leave a comment Go to comments

GeoIP Database MySQL

Sometimes we need to know the country of the users on the website, you can know using only the ip address.
I write this simple Bash script to install on a new database (or existing database) the table “geoip”, now you can use PHP, Python, Perl, Ruby or whatever you want.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

#!/bin/bash
# Script: geoip.sh
# Author: Alberto Isaac Ayala Esquivias
# E-mail: <albertoi7@gmail.com>
# Web: http://albertux.ayalasoft.net
clear
echo "=====================================-"
echo " GeoIp MySQL Database Easy Installer. "
echo "======================================"
echo ""
echo "Script Created By http://AyalaSoft.com";
echo ""
stty -echo
read -p "MySQL root password: " mysql_pass; echo
stty echo
echo -n "Do you want to create database of MySQL (y/n): "
read op
if [ "$op" = "y" ]; then
echo -n "Create new database, name: "
	read database
	mysqladmin -u root --password=$mysql_pass create $database
else
echo -n "Which database do you want to use to insert 'geoip' table: "
read database
fi
echo -e "\nDownload  [ GeoIPCountryCSV.zip ]"
wget -c "http://www.maxmind.com/download/geoip/database/GeoIPCountryCSV.zip"
echo -e "\nUnZip [ GeoIPCountryCSV.zip ]"
unzip GeoIPCountryCSV.zip
echo -e "\nInsert geoip table to database $database"
awk -F, ' { print $3","$4","$5","$6 }' GeoIPCountryWhois.csv | sed s/\"//g > /tmp/geoip.txt
chmod 777 /tmp/geoip.txt
echo "
USE $database;
 
DROP TABLE IF EXISTS geoip;
 
CREATE TABLE geoip (
	ip_begin int unsigned,
	ip_end int unsigned,
	country varchar(2),
	country_name varchar(30)
);
 
LOAD DATA LOCAL INFILE '/tmp/geoip.txt'
INTO TABLE $database.geoip
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(ip_begin, ip_end, country, country_name);
" > geoip.sql
mysql -u root --password=$mysql_pass $database < geoip.sql
rm /tmp/geoip.txt
echo -e "\nFinish."
Categories: database, network, unix/linux Tags: , ,